5.12. Running Multiple MySQL Servers on the Same Machine
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.3, “Specifying Program Options”.
At least the following options must be different for each server:
-
--port=port_num
--port controls the port number for TCP/IP
connections.
-
--socket=path
--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.
-
--pid-file=file_name
This option is used only on Unix. It indicates the pathname 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:
--log=file_name
--log-bin=file_name
--log-update=file_name
--log-error=file_name
--bdb-logdir=file_name
Section 5.11.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:
--tmpdir=path
--bdb-tmpdir=path
Having different temporary directories is also recommended to make
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=path option.
Warning: 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 filenames 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=path 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 --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.
