Skip navigation links
MySQL 3.23, 4.0, 4.1 Reference Manual :: 5 MySQL Server Administration :: 5.7 Running Multiple MySQL Servers on the Same Machine
5.7.1 Running Multiple Servers on Windows »
Section Navigation      [Toggle]

5.7. Running Multiple MySQL Servers on the Same Machine

[+/-]

5.7.1. Running Multiple Servers on Windows
5.7.2. Running Multiple Servers on Unix
5.7.3. Using Client Programs in a Multiple-Server Environment

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=port_num

    --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=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

    The name of shared memory to use for shared-memory connections. This option is available only on Windows. The default name is MYSQL. The name is case sensitive. This option was added in MySQL 4.1.

  • --pid-file=file_name

    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.3.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=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 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 ISAM, 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. Permitting 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. Suppose that you install different versions of MySQL using tarfile binary distributions. These install in different locations, so you can start the server for each installation using bin/mysqld_safe under its own 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. (For versions of MySQL older than 4.0, use safe_mysqld rather than 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.