Monday, 7 December 2009

Adding MySQL server instances using mysqlmanager

The MySQL instance manager - mysqlmanager - provides a way to manage multiple MySQL server instances on the same installation. All these instances use a common my.cnf file - but each can be configured individually (using the same file). mysqlmanager itself provides a command line interface to control the individual instances.

Part of a sample mysql.cnf with multiple mysql instances

[mysqld1]
user = mysql
datadir = /data/mysql-1
socket = /tmp/mysql-1.sock
port = 3306

[mysqld2]
user = mysql
datadir = /data/mysql-2
socket = /tmp/mysql-2.sock
port = 3307

The ability to setup multiple database servers fast is particularly useful in development boxes where fresh DBs need to be created often. In my team, we often need to do this. Every time a new DB has to be setup, we have to go through the steps of creating a datadir, installing the system tables, adding a root password, adding the entries to the my.cnf file and starting the instance using the mysqlmanager shell.

So I whipped up a small Linux shell script which automates this process.

Here it is.

It's still in a quite primitive state - but it works!

Usage is simple -
add-mysql-instance.sh mysql config-file-location datadir groupname username password instance port instance-name mysqlmanager-user mysqlmanager-password mysqlmanager-socket-file

Of course, mysqlmanager has to be running for this to work.

I'll be adding improvements to this script - like the ability to generate a mysql instance name based on existing instances (instance names are usually mysqld1, mysqld2 etc), picking up the user name from the file itself etc.

No comments:

Post a Comment