Using mysqld_multi to Manage Multiple Servers

I recently needed to set up multiple MySQL servers on a test computer to simulate a master-slave setup. I had never done this before, so I think it might be useful for others if I documented what occurred. This setup was done on a Linux server, however other platforms should operate similarly. I choose to use the mysqld_multi script to manage these instances. There is a way that you can compile the MySQL server with different network interfaces, but in my opinion, using mysqld_multi to manage activities is much easier.

So shall we begin?

The syntax for invoking mysqld_multi is:

mysqld_multi [options] {start|stop|report} [GNR[, GNR] ...]

There are at most one option and two arguments given when using the mysqld_multi script. The option name specifies the location of the configuration file to use. If no options are specified, the server searches in the normal locations, so most people won’t need to specify this option.

The first argument, which is mandatory, has three possibilities: start, stop, and report. Each of these is an available operation on your server instances: start (start the server(s)); stop (stop the server(s)); and report (report the status of the server(s)). The second argument list, which is the servers on which the operation will be performed, is optional. If there is no second argument given, the operation is performed on all servers.

When using the mysqld_multi script, you will need to do some extra work in your my.cnf file in order to use the extra server instances. The mysqld_multi script looks in your my.cnf file for groups named [mysqldN] where N is any positive integer. This integer is used to distinguish the option groups from each other. In addition, these same integers are used as the secondary arguments to mysqld_multi to specify which of the instances you want to manage. The second argument is a list of the servers for which you want the operation performed. If you want the operation performed on more than one server, they are either separated by a comma or a hyphen. If a hyphen is used it signifies a range of servers. For example, mysqld_multi stop 1-3 would stop the first three instances on the server.

There are a minimum of four areas that you will want to specify for each instance that you are creating.

1. datadir — the location of the data directory for the mysql instance
2. socket — the location of the socket file for the instance
3. port — the port number to be used by the instance
4. pid-file — the pid file location for the instance

Here is my my.cnf:

[mysqld_multi]mysqld = /usr/bin/mysqld_safe

mysqladmin = /usr/bin/mysqladmin

[mysqld1]

datadir = /var/lib/mysql1

socket = /var/lib/mysql1/mysql.sock

pid-file = /var/run/mysqld1/mysqld1.pid

user = mysql

port = 3306

server-id=1

log-bin=mysql-bin

log-error=/var/log/mysqld1.log

[mysqld2]

datadir=/var/lib/mysql2

socket=/var/lib/mysql2/mysql.sock

pid-file = /var/run/mysqld2/mysqld2.pid

user=mysql

port = 3307

server-id=2

log-bin=mysql-bin

log-error=/var/log/mysqld2.log

skip-slave-start

Of course, you can have any normal configuration options for each of these instances as well. This is a very basic my.cnf to give you an idea of what you will need. I specify the location of the items where I don’t want interaction between the instances (socket, pid-file, port, data file and error log).

Once you have configured your my.cnf file, you will need to initialize each database server before starting them for the first time. Because you have multiple database instances, you have to specify the data directory for each server you are initializing.

For my first instance, I executed this:

sudo /usr/bin/mysql_install_db --user=mysql --datadir=/var/lib/mysql1

For the second, this:

sudo /usr/bin/mysql_install_db --user=mysql --datadir=/var/lib/mysql2

Continue until you have all instances initialized.

Once the initialization is done, you can start up and manage each instance. In my case I have two instances, so I could run:

shell> sudo mysqld_multi start 1
shell> sudo mysqld_multi start 2

or

shell> sudo mysqld_multi start

And to see the status:

shell>  sudo mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
shell>

Of course, to stop one or more instances, I run:

shell> sudo mysqld_multi stop 1
shell> sudo mysqld_multi stop 2

or

shell> sudo mysqld_multi stop

The last command stops all server instances, so be careful.

That is really it. I hope you find this useful. It’s great for development servers and test boxes. If you need more information, here is the MySQL Manual reference page for the mysqld_multi script.