5.13. Running Multiple MySQL Servers on the Same Machine

To start multiple servers manually from the command line, you can specify the appropriate options on the command line or in an option file. It is more convenient to place the options in an option file, but it is necessary to make sure that each server gets its own set of options. To do this, create an option file for each server and tell the server the filename with a --defaults-file option when you run it.

Suppose that you want to run mysqld on port 3307 with a data directory of C:\mydata1, and mysqld-max on port 3308 with a data directory of C:\mydata2. (To do this, make sure that before you start the servers, each data directory exists and has its own copy of the mysql database that contains the grant tables.) Then create two option files. For example, create one file named C:\my-opts1.cnf that looks like this:

[mysqld]
datadir = C:/mydata1
port = 3307

Create a second file named C:\my-opts2.cnf that looks like this:

[mysqld]
datadir = C:/mydata2
port = 3308

Then start each server with its own option file:

C:\> C:\mysql\bin\mysqld --defaults-file=C:\my-opts1.cnf
C:\> C:\mysql\bin\mysqld-max --defaults-file=C:\my-opts2.cnf

On NT, each server starts in the foreground (no new prompt appears until the server exits later), so you will need to issue those two commands in separate console windows.

To shut down the servers, you must connect to each using the appropriate port number:

C:\> C:\mysql\bin\mysqladmin --port=3307 shutdown
C:\> C:\mysql\bin\mysqladmin --port=3308 shutdown

Servers configured as just described allow clients to connect over TCP/IP. If your version of Windows supports named pipes and you also want to allow named-pipe connections, use the mysqld-nt or mysqld-max-nt servers and specify options that enable the named pipe and specify its name. Each server that supports named-pipe connections must use a unique pipe name. For example, the C:\my-opts1.cnf file might be written like this:

[mysqld]
datadir = C:/mydata1
port = 3307
enable-named-pipe
socket = mypipe1

Then start the server this way:

C:\> C:\mysql\bin\mysqld-nt --defaults-file=C:\my-opts1.cnf

Modify C:\my-opts2.cnf similarly for use by the second server.

A similar procedure applies for servers that you want to support shared-memory connections. Enable such connections with the --shared-memory option and specify a unique shared-memory name for each server with the --shared-memory-base-name option.

On NT-based systems, a MySQL server can run as a Windows service. The procedures for installing, controlling, and removing a single MySQL service are described in Section 2.3.11, “Starting MySQL as a Windows Service”.

You can also install multiple MySQL servers as services. In this case, you must make sure that each server uses a different service name in addition to all the other parameters that must be unique for each server.

For the following instructions, assume that you want to run the mysqld-nt server from two different versions of MySQL that are installed at C:\mysql-4.1.8 and C:\mysql-5.0.25, respectively. (This might be the case if you're running 4.1.8 as your production server, but also want to conduct tests using 5.0.25.)

The following principles apply when installing a MySQL service with the --install or --install-manual option:

Note: Before MySQL 4.0.17, only a server installed using the default service name (MySQL) or one installed explicitly with a service name of mysqld read the [mysqld] group in the standard option files. As of 4.0.17, all servers read the [mysqld] group if they read the standard option files, even if they are installed using another service name. This allows you to use the [mysqld] group for options that should be used by all MySQL services, and an option group named after each service for use by the server installed with that service name.

Based on the preceding information, you have several ways to set up multiple services. The following instructions describe some examples. Before trying any of them, be sure that you shut down and remove any existing MySQL services first.

To remove multiple services, use mysqld --remove for each one, specifying a service name following the --remove option. If the service name is the default (MySQL), you can omit it.