[+/-]
In some cases, you might want to run multiple mysqld servers on the same machine. You might want to test a new MySQL release while leaving your existing production setup undisturbed. Or you might want to give different users access to different mysqld servers that they manage themselves. (For example, you might be an Internet Service Provider that wants to provide independent MySQL installations for different customers.)
To run multiple servers on a single machine, each server must have unique values for several operating parameters. These can be set on the command line or in option files. See Section 4.2.3, “Specifying Program Options”.
At least the following options must be different for each server:
--port
controls the port number
for TCP/IP connections. (Alternatively, if the host has multiple
network addresses, you can use
--bind-address
to cause different
servers to listen to different interfaces.)
--socket
controls the Unix socket
file path on Unix and the name of the named pipe on Windows. On
Windows, it is necessary to specify distinct pipe names only for
those servers that support named-pipe connections.
--shared-memory-base-name=
name
This option currently is used only on Windows. It designates the shared-memory name used by a Windows server to allow clients to connect via shared memory. It is necessary to specify distinct shared-memory names only for those servers that support shared-memory connections.
This option is used only on Unix. It indicates the path name of the file in which the server writes its process ID.
If you use the following log file options, they must be different for each server:
Section 5.2.6, “Server Log Maintenance”, discusses the log file options further.
For better performance, you can specify the following options differently for each server, to spread the load between several physical disks:
Having different temporary directories also makes it easier to determine which MySQL server created any given temporary file.
With very limited exceptions, each server should use a different
data directory, which is specified using the
--datadir=
option.
path
Normally, you should never have two servers that update data in
the same databases. This may lead to unpleasant surprises if your
operating system does not support fault-free system locking. If
(despite this warning) you run multiple servers using the same
data directory and they have logging enabled, you must use the
appropriate options to specify log file names that are unique to
each server. Otherwise, the servers try to log to the same files.
Please note that this kind of setup only works with
MyISAM
and MERGE
tables, and
not with any of the other storage engines.
The warning against sharing a data directory among servers also applies in an NFS environment. Allowing multiple MySQL servers to access a common data directory over NFS is a very bad idea.
The primary problem is that NFS is the speed bottleneck. It is not meant for such use.
Another risk with NFS is that you must devise a way to ensure
that two or more servers do not interfere with each other.
Usually NFS file locking is handled by the
lockd
daemon, but at the moment there is no
platform that performs locking 100% reliably in every situation.
Make it easy for yourself: Forget about sharing a data directory among servers over NFS. A better solution is to have one computer that contains several CPUs and use an operating system that handles threads efficiently.
If you have multiple MySQL installations in different locations, you
can specify the base installation directory for each server with the
--basedir=
option to cause each server to use a different data directory, log
files, and PID file. (The defaults for all these values are
determined relative to the base directory). In that case, the only
other options you need to specify are the
path
--socket
and
--port
options. For example, suppose
that you install different versions of MySQL using
tar
file binary distributions. These install in
different locations, so you can start the server for each
installation using the command bin/mysqld_safe
under its corresponding base directory.
mysqld_safe determines the proper
--basedir
option to pass to
mysqld, and you need specify only the
--socket
and
--port
options to
mysqld_safe.
As discussed in the following sections, it is possible to start
additional servers by setting environment variables or by specifying
appropriate command-line options. However, if you need to run
multiple servers on a more permanent basis, it is more convenient to
use option files to specify for each server those option values that
must be unique to it. The
--defaults-file
option is useful for
this purpose.
User Comments
You can use "mysqladmin variables" to see the values that your existing server uses, even if you don't know your host name, port number, or don't use a .cnf file.
Be careful when running multiple mysql servers with PHP and persistent connections. It seems that PHP does not check the socket when reusing a connection. That is, PHP will see an existing connection, and reuse it even if the connection is actually to a different server.
Some distributions also require providing --pid_file and --log-error in order to start a new server using mysqld_safe. In that case the complete command line to start a second server would look like
mysqld_safe --socket=/u/mysql2/mysql.sock --port=3307 --pid-file=/u/mysql2/mysql2.pid --datadir=/u/mysql2 --log-error=/u/mysql2/mysqld2.log
Optionally all configuration options can be put into a configuration file (a copy of my.cnf) and the server would be started using
mysqld_safe --defaults-file this_my.cnf
For 'personal use' starting a second server is particularly simple and convenient. Just create a configuration file named .my.cnf in the user home directory (with all necessary options required to startup a server) and start the server using mysqld_safe (no further options required). By default every mysql command issued by the user will look at that configuration file and connect to the 'personal' server.
Add your own comment.