mysqld_multi, program for managing multiple MySQL servers
---------------------------------------------------------
`mysqld_multi' is meant for managing several `mysqld' processes running
in different UNIX sockets and TCP/IP ports.
The program will search for group(s) named [mysqld#] from my.cnf (or the
given -config-file=...), where # can be any positive number starting
from 1. These groups should be the same as the usual `[mysqld]' group
(e.g. options to mysqld, see MySQL manual for detailed information
about this group), but with those port, socket etc. options that are
wanted for each separate `mysqld' processes. The number in the group
name has another function; it can be used for starting, stopping, or
reporting some specific `mysqld' servers with this program. See the
usage and options below for more information.
Usage: mysqld_multi [OPTIONS] {start|stop|report} [GNR,GNR,GNR...]
or mysqld_multi [OPTIONS] {start|stop|report} [GNR-GNR,GNR,GNR-GNR,...]
The GNR above means the group number. You can start, stop or report any
GNR, or several of them at the same time. (See -example) The GNRs list
can be comma separated, or a dash combined, of which the latter means
that all the GNRs between GNR1-GNR2 will be affected. Without GNR
argument all the found groups 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'
Give an example of a config 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 `safe_mysqld'
to this option also. The options are passed to `mysqld'. Just make
sure you have `mysqld' in your environment variable `PATH' or fix
`safe_mysqld'.
`--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 connecting is done via 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') 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_priv'
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'"
Note:Privileges.
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 `safe_mysqld' to
start `mysqld' (e.g. -mysqld=safe_mysqld) Every `mysqld' should
have its own `pid-file'. The advantage using `safe_mysqld' instead
of `mysqld' directly here is, that `safe_mysqld' 'guards' every
`mysqld' process and will restart it, if a `mysqld' process fails
due to signal kill -9, or similar. (Like segmentation fault, which
MySQL should never do, of course ;) Please note that `safe_mysqld'
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 `safe_mysqld' 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
--------------------------------------------------------------------------
Note:`safe_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 (
[mysqld# <== ).
* You may want to use option '-user' for `mysqld', but in order to
do this you need to be root when you start the `mysqld_multi'
script. 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. *DON'T* 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 *WOULD YOU
WANT* to have separate `mysqld' processes. Starting multiple
`mysqlds' in one data directory *WILL NOT* give you extra
performance in a threaded system!
Note:Multiple servers.
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/safe_mysqld
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
Note:Option files.