4.1.3 Running Multiple MySQL Servers on the Same Machine

In some cases you might want to run multiple mysqld servers on the same machine. For example, you might want to test a new MySQL release while leaving your existing production setup undisturbed. Or you may 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.)

When you run multiple servers on a single machine, each server must have unique values for several operating parameters. At least the following options must be different for each server:

• --port=port_num
• --socket=path
• --shared-memory-base-name=name (Windows only; new in MySQL 4.1)
• --pid-file=path (Unix only)

--port controls the port number for TCP/IP connections. --socket controls the socket file path on Unix and the name of the named pipe on Windows. (It's necessary to specify distinct pipe names on Windows only for those servers that support named pipe connections.) --shared-memory-base-name designates the shared memory name used by a Windows server to allow clients to connect via shared memory. --pid-file indicates the name of the file in which a Unix server writes its process ID.

If you use the following options, they must be different for each server:

• --log=path
• --log-bin=path
• --log-update=path
• --log-error=path
• --log-isam=path
• --bdb-logdir=path

If you want more performance, you can also specify the following options differently for each server, to spread load between several physical disks:

• --tmpdir=path
• --bdb-tmpdir=path

See section 4.1.1 mysqld Command-line Options.

Generally, each server should also use a different data directory, which is specified using the --datadir=path option.

If you have multiple MySQL installations in different locations, normally 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 you install binary MySQL versions (`.tar' files) in different locations and start them using the command ./bin/mysqld_safe under the corresponding base directory of each installation. mysqld_safe will determine 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 will be more convenient to use option files to specify for each server those option values that must be unique to it. See section 4.1.2 `my.cnf' Option Files.

Warning: Normally you should never have two servers that update data in the same databases! If your OS doesn't support fault-free system locking, this may lead to unpleasant surprises! If (despite this warning) you run multiple servers using the same data directory and they have logging enabled, you must specify the names of the log files using the appropriate options. Otherwise, the servers may try to log to the same files.

This warning against sharing a data directory among servers also applies in an NFS environment. Allowing several MySQL servers to access a common data directory over NFS is a bad idea!

• The primary problem is that NFS will become the speed bottleneck. It is not meant for such use.
• You also will have to come up with a solution how to make sure that two or more servers do not interfere with each other. At the moment there is no platform that will 100% reliably do the file locking in every situation (usually this is handled by the lockd daemon). Yet there would be one more possible risk with NFS; it would make the work even more complicated for lockd daemon to handle.

Make it easy for yourself: Forget about sharing a data directory among servers over NFS. A better solution is to have one computer with an operating system that efficiently handles threads and have several CPUs in it.

Subsections

• 4.1.3.1 Running Multiple Servers on Windows
• 4.1.3.2 Running Multiple Servers on Unix
• 4.1.3.3 Using Client Programs in a Multiple-Server Environment

User Comments

Add your own comment.