Administering Multi Tenent MySQL Installations

Problem

When we have many MySQL installation in same hardware and many MySQL instances running from different base directories,Administration can get into messy ebecause we need to remember each instance’s base directory, port, socket, data directory, error log file etc.
ofcource DBA can maintain this information in a spreadsheet and copy / paste.
it works if you have only few databases to manage. and things can get even messy if you are not regularly updating your spreadsheet.

Idea !

configuration file .cnf (parameter file) contains all information about an instance.
why can’t i read those files and switch the environment variables accordingly?

How to read .cnf file ?

its simple mysql comes with a utility named my_print_defaults!. so work is done!.
Hay wait..wait. but how to parse it and put it in environment variables?
Eureka!..mysqld_safe already does it..let me copy and paste few lines from there

Desinging the solution

for easy administration i decide to keep all configuraiton files (.cnf) files in same location:
/u01/app/oracle/mysql
Now it is matter of just listing all .cnf file and set the environment variable accoding to selection.
for parsing the parameter file values, use parse_arguments function from mysqld_safe.

Code

How to use it

I put the about into a file named “my” and given execute permission.
and kept this file in a directory in PATH.
here it is:

Now All environment variables and aliases will be set accordingly
see this

Leave a Reply

Your email address will not be published. Required fields are marked *