4.7.3 mysqld_multi, A Program for Managing Multiple MySQL Servers

mysqld_multi is meant for managing several mysqld processes that listen for connections on different Unix sockets and TCP/IP ports.

The program will search for group(s) named [mysqld#] from `my.cnf' (or the file named by the --config-file=... option), where # can be any positive number starting from 1. This number is referred to in the following discussion as the option group number, or GNR. Group numbers distinquish option groups from one another and are used as arguments to mysqld_multi to specify which servers you want to start, stop, or obtain status for. Options listed in these groups should be the same as you would use in the usual [mysqld] group used for starting mysqld. (See, for example, section 2.4.3 Starting and Stopping MySQL Automatically.) However, for mysqld_multi, be sure that each group includes options for values such as the port, socket, etc., to be used for each individual mysqld process.

mysqld_multi is invoked using the following syntax:

Usage: mysqld_multi [OPTIONS] {start|stop|report} [GNR,GNR,GNR...]
or     mysqld_multi [OPTIONS] {start|stop|report} [GNR-GNR,GNR,GNR-GNR,...]

Each GNR represents an option group number. You can start, stop or report any GNR, or several of them at the same time. For an example of how you might set up an option file, use this command:

shell> mysqld_multi --example

The GNR values in the list can be comma-separated or combined with a dash; in the latter case, all the GNRs between GNR1-GNR2 will be affected. With no GNR argument, all groups listed in the option file will be either started, stopped, or reported. Note that you must not have any white spaces in the GNR list. Anything after a white space is ignored.

mysqld_multi supports the following options:

--config-file=...

Alternative config file. Note: This will not affect this program's own options (group [mysqld_multi]), but only groups [mysqld#]. Without this option, everything will be searched from the ordinary `my.cnf' file.

--example

Display an example option file.

--help

Print this help and exit.

--log=...

Log file. Full path to and the name for the log file. Note: If the file exists, everything will be appended.

--mysqladmin=...

mysqladmin binary to be used for a server shutdown.

--mysqld=...

mysqld binary to be used. Note that you can give mysqld_safe to this option also. The options are passed to mysqld. Just make sure you have mysqld in your environment variable PATH or fix mysqld_safe.

--no-log

Print to stdout instead of the log file. By default the log file is turned on.

--password=...

Password for user for mysqladmin.

--tcp-ip

Connect to the MySQL server(s) via the TCP/IP port instead of the Unix socket. This affects stopping and reporting. If a socket file is missing, the server may still be running, but can be accessed only via the TCP/IP port. By default, connections are made using the Unix socket.

--user=...

MySQL user for mysqladmin.

--version

Print the version number and exit.

Some notes about mysqld_multi:

• Make sure that the MySQL user, who is stopping the mysqld services (e.g using the mysqladmin program) have the same password and username for all the data directories accessed (to the mysql database) And make sure that the user has the SHUTDOWN privilege! If you have many data directories and many different mysql databases with different passwords for the MySQL root user, you may want to create a common multi_admin user for each using the same password (see below). Example how to do it:

  shell> mysql -u root -S /tmp/mysql.sock -proot_password -e
  "GRANT SHUTDOWN ON *.* TO multi_admin@localhost IDENTIFIED BY 'multipass'"
  

  See section 4.2.6 How the Privilege System Works. You will have to do the above for each mysqld running in each data directory, that you have (just change the socket, -S=...).
• pid-file is very important, if you are using mysqld_safe to start mysqld (for example, --mysqld=mysqld_safe) Every mysqld should have its own pid-file. The advantage using mysqld_safe instead of mysqld directly here is, that mysqld_safe ``guards'' every mysqld process and will restart it, if a mysqld process terminates due to a signal sent using kill -9, or for other reasons such as a segmentation fault (which MySQL should never do, of course ;). Please note that the mysqld_safe script may require that you start it from a certain place. This means that you may have to cd to a certain directory, before you start the mysqld_multi. If you have problems starting, please see the mysqld_safe script. Check especially the lines:

  --------------------------------------------------------------------------
  MY_PWD=`pwd` Check if we are starting this relative (for the binary
  release) if test -d /data/mysql -a -f ./share/mysql/english/errmsg.sys
  -a -x ./bin/mysqld
  --------------------------------------------------------------------------
  

  See section 4.7.2 mysqld_safe, The Wrapper Around mysqld. The above test should be successful, or you may encounter problems.
• Beware of the dangers starting multiple mysqlds in the same data directory. Use separate data directories, unless you know what you are doing!
• The socket file and the TCP/IP port must be different for every mysqld.
• The first and fifth mysqld group were intentionally left out from the example. You may have 'gaps' in the config file. This gives you more flexibility. The order in which the mysqlds are started or stopped depends on the order in which they appear in the config file.
• When you want to refer to a certain group using GNR with this program, just use the number in the end of the group name. For example, the GNR for a group named [mysqld17] is 17.
• You may want to use option --user for mysqld, but in order to do this you need to run the mysqld_multi script as the Unix root user. Having the option in the config file doesn't matter; you will just get a warning, if you are not the superuser and the mysqlds are started under your Unix account. Important: Make sure that the pid-file and the data directory are read+write(+execute for the latter one) accessible for that Unix user, who the specific mysqld process is started as. Do not use the Unix root account for this, unless you know what you are doing!
• Most important: Make sure that you understand the meanings of the options that are passed to the mysqlds and why one would want to have separate mysqld processes. Starting multiple mysqlds in one data directory will not give you extra performance in a threaded system!

See section 4.1.3 Running Multiple MySQL Servers on the Same Machine.

This is an example of the config file on behalf of mysqld_multi.

# This file should probably be in your home dir (~/.my.cnf) or /etc/my.cnf
# Version 2.1 by Jani Tolonen

[mysqld_multi]
mysqld     = /usr/local/bin/mysqld_safe
mysqladmin = /usr/local/bin/mysqladmin
user       = multi_admin
password   = multipass

[mysqld2]
socket     = /tmp/mysql.sock2
port       = 3307
pid-file   = /usr/local/mysql/var2/hostname.pid2
datadir    = /usr/local/mysql/var2
language   = /usr/local/share/mysql/english
user       = john

[mysqld3]
socket     = /tmp/mysql.sock3
port       = 3308
pid-file   = /usr/local/mysql/var3/hostname.pid3
datadir    = /usr/local/mysql/var3
language   = /usr/local/share/mysql/swedish
user       = monty

[mysqld4]
socket     = /tmp/mysql.sock4
port       = 3309
pid-file   = /usr/local/mysql/var4/hostname.pid4
datadir    = /usr/local/mysql/var4
language   = /usr/local/share/mysql/estonia
user       = tonu

[mysqld6]
socket     = /tmp/mysql.sock6
port       = 3311
pid-file   = /usr/local/mysql/var6/hostname.pid6
datadir    = /usr/local/mysql/var6
language   = /usr/local/share/mysql/japanese
user       = jani

See section 4.1.2 `my.cnf' Option Files.

User Comments

Add your own comment.