Multiple MySQL databases on one binary install using mysqld_multi

One very cool (and often overlooked) feature of mysql is the use of mysqld_multi.

This script allows you to run multiple instances of MySQL server on a single box with one set of MySQL server binaries. You’ll just need to specify different configuration parameters (port, socket, log file locations, etc.) in your my.cnf file. I’ll go through how simple this can be so setup below.

First, you’ll need to create a new user that will exist on all server instances and will only need the SHUTDOWN privilege. You can probably guess this, but this user is needed to initiate a shutdown on the specified server. Since you will have multiple instances running on one server, the default init scripts will no longer work to shutdown anything but the default instance.

mysql> GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost' IDENTIFIED BY 'my-pwd';

Next, we will need to edit our my.cnf file. The first difference here is the general layout. First, you will have a section for [mysqld_multi], then each section after will be for the specified mysql instance. For example, if you have 3 sections defined – [mysqld1], [mysqld2], [mysqld3] – you will have places to configure 3 different mysql databases.

The [mysqld_multi] section is very simple, it just defines the mysqld_safe and mysqladmin binaries as well as the username and password that we previously created for shutting down the server. For example, it may look something like this:

[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user    =       multi_admin
password =      my-pass

Next, we can begin defining our first database. I like to define all of the following as a minimum to get the server up and running. All other variables not defined will use the defaults given to it.

[mysqld1]
datadir=/var/lib/mysql
tmpdir=/tmp/mysqltmp
socket=/var/lib/mysql/mysql.sock
pid-file=/var/lib/mysql/mysql.pid
log-bin=/var/lib/mysql_logs/binlogs/dbsvr01-binlog
relay-log=/var/lib/mysql_logs/relaylogs/dbsvr01-relaylog
slow-query-log-file = /var/lib/mysql_logs/dbsvr01.slow_queries.log
log-error=/var/lib/mysql_logs/dbsvr01.mysql.err

That’s it! Your first database is ready to go. I wouldn’t recommend leaving all other variables at their default, so be sure to know what parameters you want to configure and which ones you can leave alone.

For safety, I usually delete the default mysql init script and replace it with one that will utilize the multiple databases. The MySQL RPM will provide this for you here: /usr/share/mysql/mysqld_multi.server. Copy this to the init run level of your choosing and don’t forget to make the necessary changes to chkconfig.

A little bit about the mysqld_multi.server script. By running the command you will quickly find that there are only 4 parameters to choose from:

Usage: /etc/init.d/mysqld_multi.server {start|stop|report|restart}

Issuing a “start” followed by a number (in our example 1) will start the first database. If you then wanted to start the second database, issue a “mysqld_multi.server start 2”. The same logic carries over for ‘stop’ and ‘restart’. The ‘report’ option is similar to ‘status’ in other common init scripts. This will give you a list of all of the mysql database instances that are defined, followed by if they’re started or stopped. For example, If I have 3 instances all defined and running it will look something similar to this:

[joe@DBSVR01 /]$ /etc/init.d/mysqld_multi.server report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
MySQL server from group: mysqld3 is running

So there you have it! Enjoy :)

More Info: http://dev.mysql.com/doc/refman/5.5/en/mysqld-multi.html

Leave a Reply

Your email address will not be published.