42.1. Using Multiple Servers

It's common to run a single MySQL server on a given machine, but it's possible to run multiple servers. This is a common scenario when you want to test a new release of MySQL on the same machine where you run your production server. Use of multiple servers also might be desirable for administrative purposes. If clients can be partitioned into groups that use distinct databases, you can run multiple servers, each one serving a single group of clients. Each group can have its own designated root user, and that root user won't be able to see databases that belong to other groups, as would be possible if all clients were to share the same server.

Managing multiple servers is a more complex undertaking than running a single server because you must make sure that the servers do not interfere with each other. None of the servers can share resources that must be used exclusively by a single server. These resources include the following:

• Each server normally manages its own data directory. On Windows, this is a requirement. On Unix, it is possible (though not recommended) for servers to share a data directory under certain circumstances:

  • If the data directory is located on read-only media, there won't be a problem of multiple servers attempting updates of the same data simultaneously. (This precludes use of InnoDB tables because InnoDB currently cannot be used on read-only media.)

  • On read-write media, external locking must be enabled so that servers can cooperate for access to database files. However, external locking does not work on all systems, is disabled by default, and does not apply to the InnoDB storage engine anyway.

• Each server must have its own network interfaces, including the TCP/IP port, the named pipe or shared memory (on Windows), and the Unix socket file (on Unix). One server cannot use network interfaces that are used by another server; it will not even start up properly if it discovers that its network interfaces are already in use. Note that it isn't necessary to set up multiple hostnames for the server host. All the MySQL servers running on a given host can share the same hostname. They can also share the same IP address as long as they listen on different TCP/IP port numbers.

• Under Windows, each server that is run as a service must use a unique service name.

• Each server must have its own log files. Multiple servers writing to the same log files results in unusable logs. This is also true for status files such as the PID file in which a server records its process ID.

• InnoDB tablespace files cannot be shared by multiple servers. Each server that uses InnoDB must have its own tablespace. The same is true of the InnoDB log files.

To make sure that each server manages a different data directory, start each one with a unique value for the --datadir option. Normally, having distinct data directories is sufficient to ensure distinct sets of log files and InnoDB files as well, because those files are created by default in the data directory if you specify their names using relative pathnames.

On Unix, you can ensure that each server uses its own network interfaces by starting each with a unique value for the --port and --socket options to set up the TCP/IP and Unix socket file interfaces. On Windows, the --port option applies for TCP/IP; for servers that allow named-pipe or shared-memory connections, the options are as follows:

• To enable named-pipe connections, use the mysqld-nt or mysqld-max-nt server and start each instance with the --enable-named-pipe option. Each server must have a distinct pipe name, which you specify using the --socket option.

• To enable shared-memory connections, start each server with the --shared-memory option. Each server must use a different shared-memory name, which you specify using the --shared-memory-base-name option.

To set up Windows servers with distinct service names, follow the --install option of the service installation command with a service name. For example:

 shell> mysqld --install mysql1 shell> mysqld --install mysql2 

Installed that way, when the servers start, they will read options from the [mysql1] and [mysql2] groups, respectively, in the standard option files. They'll also read options in the [mysqld] group as usual, which you can use for any options that are common to both services.

Another way to install MySQL as a service is to follow the service name with an option naming the file from which the server should read options when it starts:

 shell> mysqld --install mysql1 --defaults-file=C:\mysql1.cnf shell> mysqld --install mysql2 --defaults-file=C:\mysql2.cnf 

In this case, each server ignores the standard option files when it starts and instead reads options only from the [mysqld] group of the option file named by the --defaults-file option.

Each server installed as a Windows service can read the appropriate options for the data directory location and network interfaces from whichever option file and group is unique to it.

On Windows, management of multiple servers is easiest if they all run as Windows services. Then you can use the native Windows tools such as the Services program that provides a graphical interface for service management. MySQL Administrator also can control MySQL services. If the MySQL servers do not run as services, you must start them manually and stop them using mysqladmin.

On Unix, some administrative assistance is available for controlling multiple servers. mysqld_multi is a Perl script intended to make it easier to manage multiple servers on a single host. It can start or stop servers, or report on whether servers are running. mysqld_multi can either start servers directly, or indirectly by invoking mysqld_safe. (An advantage of using mysqld_safe is that it sets up the error log and monitors the server.) mysqld_multi requires installation of the Perl DBI module.

MySQL AB currently is developing another program called MySQL Instance Manager to be used for multiple-server management. This program will offer some significant improvements over mysqld_multi and eventually will replace it. For example, mysqld_multi can stop local or remote servers, but can start only local servers. With MySQL Instance Manager, it will be possible to start remote servers as well.

MySQL Instance Manager is planned for cross-platform deployment, so it will also be able to control servers on Windows.