Running Multiple Servers

Most people run a single MySQL server on a given machine, but there are circumstances under which it can be useful to run multiple servers:

• You may want to test a new version of the server while leaving your production server running. In this case, you'll be running different server binaries.

• Operating systems typically impose per-process limitations on the number of open file descriptors. If your system makes it difficult to raise the limit, running multiple servers is one way to work around that limitation. (For example, raising the limit may require recompiling the kernel, and you may not be able to do that if you're not in charge of administering the machine.) In this case, you may be running multiple instances of the same server binary.

• Internet service providers often provide customers with their own MySQL installation, which necessarily involves separate servers. In this case, you may be running multiple instances of the same binary or different binaries if different customers want different versions of MySQL.

Naturally, running several servers is more complicated than running just one. If you're installing multiple versions, you can't just install them all in the same place. When the servers run, certain parameters must be or are likely to be unique to each server. Some of these include where the server is installed, the pathname to its data directory, its TCP/IP port and UNIX domain socket pathname, and the UNIX account used to run the server (if you don't run all servers under the same account). If you decide to run multiple servers, be sure to keep good notes on the parameters you're using so that you don't lose track of what's going on.

Configuring and Installing Multiple Servers

If you're going to run different versions of the server rather than multiple instances of the same server, you should install them in different locations. If you install binary (not RPM) distributions, they'll be installed under a directory name that includes the version number. If you install from source, the easiest way to keep different distributions separate is to use the --with-prefix option when you run configure during the MySQL installation process for each version. That will cause everything to be installed under a single directory, and you can tie that directory to the distribution version number. For example, you can configure a MySQL distribution as follows, where version is the MySQL version number:

% ./configure --with-prefix=/usr/local/mysql-version
						

The --with-prefix option will also determine a unique data directory for the server. You may want to add options to configure other server-specific values, such as the TCP/IP port number and socket pathname (--with-tcp-port, and --with-unix-socket).

If you're going to run multiple instances of the same server binary, any option that must be set on a server-specific basis will need to be specified at run time.

Multiple Server Startup Procedures

Starting up multiple servers is more complicated than using a single server, because safe_mysqld and mysql.server both work best in a single-server setting. I recommend that you study safe_mysqld carefully and use it as a basis for your startup procedures, but that you use modified copies that you can tailor more precisely to your own requirements.

One problem you must deal with is how to specify options in option files. With multiple servers, you can't use /etc/my.cnf for settings that vary on a per-server basis. You can only use that file for settings that you want to be the same for all servers. If each server has a different compiled-in data directory location, you can specify server-specific parameters in the my.cnf file in each server's data directory. In other words, use /etc/my.cnf for settings you want all servers to use and DATADIR/my.cnffor server-specific settings, where DATADIR varies per server.

Another way to specify server options is to use --defaults-file=path_name as the first option on the command line to tell the server to read options from the file named by path_name. This way, you can put a server's options in a file unique to that server, then tell it to read that file at startup time. Note that if you specify this option, none of the usual option files, such as /etc/my.cnf, will be used.