Using mysqld_multi to Manage Multiple Servers

Jul 30, 2008 / By Keith Murphy

Tags:

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.

9 Responses to “Using mysqld_multi to Manage Multiple Servers”

  • mysql_multi is such an old technology!
    If you want to start multiple servers in one host easily, use MySQL Sandbox.

    http://launchpad.net/mysql-sandbox

    No need to write longish sections of my.cnf. No need to remember which options to set.

    ./make_sandbox /path/to/tarball.tar.gz
    or
    ./make_replication_sandbox /path/to/tarball.tar.gz

    Giuseppe

  • Xaprb says:

    I second Giuseppe! It can be a single command and takes about 10 seconds. I use a slightly modified version of it for Maatkit’s unit-test framework. Maatkit’s test suite actually sets up and tears down little throwaway servers, sometimes as many as 4 replicated machines in a tree, for its tests.

  • Keith Murphy says:

    Thanks for pointing that out guys. Honestly, I guess at this point I think of sandbox as something to use when quickly setting up a test instance (or instances). What I described was for a long-term situation. However, I suppose you could use sandbox for something long term just as well.

  • peanut says:

    would you use this in production env (vhost)? How to control cpu priority over the nth- instances (if one goes through the roof)?

  • pietrop says:

    I can’t do this on OS X. I’ve created a my.cnf similar to this, but mysqld_multi doesn’t start anything… how is it possible?! It’s driving me crazy…

  • Sheeri Cabral says:

    pietrop — what command do you run to start an instance, and is there anything in the error logs? Does

    ps -ef | grep mysqld

    in the Terminal window show that MySQL is running?

  • pietrop says:

    if i try:
    ./mysqld_multi start
    and then
    ./mysqld_multi report
    i obtain the following message:
    MySQL server from group: mysqld1 is not running
    MySQL server from group: mysqld2 is not running

    Your command shows this:
    0 4030 3927 0 0:00.00 ttys001 0:00.00 grep mysqld

    but if I run “ps -u mysql” nothing is displayed…

  • pietrop says:

    I’ve found the mysqld_multi.log file and I think to have located the problem, but I don’t know how to solve the problem. It’s a “chown” issue..
    Here’s the log error:

    [ERROR] Fatal error: Please read “Security” section of the manual to find out how to run mysqld as root!

    I’ve tried the following commands:
    chown -R mysql /usr/local/mysql
    chown -R mysql /usr/local/mysql/data1
    chown -R mysql /usr/local/mysql/data2
    chown -R root /usr/local/mysql
    chown -R root /usr/local/mysql/data1
    chown -R root /usr/local/mysql/data2

    but the error persists.. can you help me please?

  • Nathan says:

    Probably has to do with AppArmor. Add the new datadirs to the mysqld profile.

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>