MySQL

General MySQL Administration

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 instances of the server binary is one way to work around that limitation.

. Internet service providers often provide individual customers with their own MySQL installation, which necessarily requires multiple servers. This may involve running multiple instances of the same binary if all customers run the same version of MySQL, or different binaries if some customers run different versions than others.

Those are some of the more common reasons to run multiple servers, but there are others. For example, if you write MySQL documentation, it's often necessary to test various server versions empirically to see how their behavior differs.

I fall into this category, for which reason I have lots of servers installed (more than 30 at the moment). However, I run just a couple of them all the time. The others I run only on occasion for testing purposes, so I need to be able to start and stop them easily on demand.

General Multiple Server Issues

Running several servers is more complicated than running just one because you need to keep them from interfering with each other. Some of the issues that arise occur when you install MySQL. If you use different versions, they must each be placed into a different location. For precompiled binary distributions, you can accomplish this by unpacking them into different directories. ;

For source distributions that you compile yourself, you can use the --prefix option for configure to specify a different installation location for each distribution. Other issues occur at runtime when you start up the servers. Each server process must have unique values for several parameters. For example, every server must listen to a different TCP/IP port for incoming connections or else they will collide with each other.

This is true whether you run different server binaries or multiple instances of the same binary. A similar problem occurs if you enable logging. Each server should write to its own set of log files because having different servers write to the same files is sure to cause problems.

You can specify a server's options at runtime when you start it, typically in an option file. Alternatively, if you run several server binaries that you compile from source yourself, you can specify during the build process a different set of parameter values for each server to use. These become its built-in defaults, and you need not specify them explicitly at runtime.

When you 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 happening. One way to do this is to use option files to specify the parameters. (This can be useful even for servers that have unique parameter values compiled in because the option files serve as a form of explicit documentation.)

The following discussion enumerates several types of options that have the potential for causing conflicts if they're not set on a per-server basis. Note that some options will influence others, and thus you may not need to set each one explicitly for every server. For example, every server must use a unique process ID file when it runs. But the data directory is the default location for the PID file, so if each server has a different data directory, that will implicitly result in different default PID files.

If you're running different server versions, it's typical for each distribution to be installed under a different base directory. It's also best if each server uses a separate data directory. To specify these values explicitly, use the following options:

Option	Purpose
--basedir=dir_name	Pathname to root directory of MySQL installation
--datadir=dir_name	Pathname to data directory

In many cases, the data directory will be a subdirectory of the base directory, but not always. For example, an ISP may provide a common MySQL installation for its customers (that is, the same set of client and server binaries) but run different instances of the server, each of which manages a given customer's data directory. In this case, the base directory may be the same for all servers, but individual data directories may be located elsewhere, perhaps under customer home directories.

. The following options must have different values for each server, to keep servers from stepping on each other:

Option	Purpose
--port=port_num	Port number for TCP/IP 410
--socket=file_name	Pathname to UNIX domain socket file
--pid-file=file_name	Pathname to file in which server writes its process ID

. If you enable logging, any log names that you use must be different for each server. Otherwise, you'll have multiple servers contending to log to the same files. That is at best confusing, and at worst it prevents things like replication from working correctly.

Log files named by the options in the following table are created under the server's data directory if you specify relative filenames. If each server uses a different data directory, you need not specify absolute pathnames to get each one to log to a distinct set of files

Logging Option	Log Enabled by Option
--log[=file_name]	General log file
--log-bin[=file_name]	Binary update log file
--log-bin-index=file_name	Binary update log index file
--log-update[=file_name]	Update log file
--log-slow-queries[=file_name]	Slow-query log file
--log-isam[=file_name]	ISAM/MyISAM log file

. If the BDB or InnoDB table handlers are enabled, the directories in which they write their logs must be unique per server. By default, the server writes these logs in the data directory. To change the location, use the following options:

Logging Option	Purpose
--bdb-logdir=dir_name	BDB log file directory
--innodb_log_arch_dir=dir_name	InnoDB log archive directory
--innodb_log_group_home_dir=dir_name	InnoDB log file directory

. If you specify either of the InnoDB options, you should specify both, and you must give both the same value.

. Under UNIX, if you use mysql_safe to start your servers, it creates an error log (by default in the data directory). You can specify the error log name explicitly with --err-log=file_name. Note that this option must be given to mysqld_safe rather than to mysqld, and that relative pathnames are interpreted with respect to the directory from which mysqld_safe is invoked, not with respect to the data directory as for the other log files. If you use this option, specify an absolute pathname to make sure you always create the error log in the same location.

. Under UNIX, it may also be necessary to specify a --user option on a per-server basis to indicate the login account to use for running each server. This is very likely if you're providing individual MySQL server instances for different users, each of whom "owns" a separate data directory.

. Under Windows, different servers that are installed as services must each use a unique service name.

Configuring and Compiling Different Servers

If you're going to build different versions of the server, you should install them in different locations. The easiest way to keep different distributions separate is to indicate a different installation base directory for each one by using the --prefix option when you run configure. If you incorporate the version number into the base directory name, it's easy to tell which directory corresponds to which version of MySQL. This section illustrates one way to accomplish that, by describing the particular configuration conventions that I use to keep my own MySQL installations separate.

The layout I use places all MySQL installations under a common directory: /var/mysql. To install a given distribution, I put it in a subdirectory of /var/mysql named using the distribution's version number. For example, I use /var/mysql/40005 as the installation base directory for MySQL 4.0.5, which can be accomplished by running configure with a --prefix=/var/mysql/40005 option. I also use other options for additional server-specific values, such as the TCP/IP port number and socket pathname. The configuration I use makes the TCP/IP port number equal to the version number, puts the socket file directly in the base directory, and names the data directory as data there.

To set up these configuration options, I use a shell script named config-ver that looks like the following (note that the data directory option for configure is --localstatedir, not --datadir):

VERSION="50002"

PREFIX="/var/mysql/$VERSION"

# InnoDB is included by default as of MySQL 4:

# - prior to 4.x, include InnoDB with --with-innodb

# - from 4.x on, exclude InnoDB with --without-innodb

HANDLERS="--with-berkeley-db"

OTHER="--enable-local-infile --with-embedded-server"

rm -f config.cache

./configure \

    --prefix=$PREFIX \

    --localstatedir=$PREFIX/data \

    --with-unix-socket-path=$PREFIX/mysql.sock \

    --with-tcp-port=$VERSION \

    $HANDLERS $OTHER

I make sure the first line is set to the proper version number and modify the other values as necessary, according to whether or not I want the InnoDB and BDB table handlers, LOCAL support for LOAD DATA, and so forth. That done, the following commands configure, build, and install the distribution:

% sh config-ver
% make
% make install

Next, I change location into the installation base directory and initialize its data directory and grant tables:

% cd /var/mysql/40005
% ./bin/mysql_install_db

Strategies for Specifying Startup Options

After you have your servers installed, how do you get them started up with the proper set of runtime options that each one needs? You have several choices:

. If you run different servers that you build yourself, you can compile in a different set of defaults for each one, and no options need to be given at runtime. This has the disadvantage that it's not necessarily obvious what parameters any given server is using.

. To specify options at runtime, you can list them on the command line or in option files. If you need to specify lots of options, writing them on the command line is likely to be impractical. Putting them in option files is more convenient, although then the trick is to get each server to read the proper set of options. Strategies for accomplishing this include the following:

o Use a --defaults-file option to specify the file that the server should read to find all of its options, and specify a different file for each server. This way, you can put all the options needed by a given server into one file to fully specify its setup in a single place. (Note that when you use this option, none of the usual option files, such as /etc/my.cnf, will be read.)

o Put any options that are common to all servers in a global option file such as /etc/my.cnf and use a --defaults-extra-file option on the command line to specify a file that contains additional options that are specific to a given server. For example, use the [mysqld] group in /etc/my.cnf for options that should apply to all servers. These need not be replicated in individual per-server option files.

Be sure that any options placed into a common option group are understood by all servers that you run. For example, you can't use local-infile to enable the use of LOAD DATA LOCAL if any of your servers are older than version 3.23.49 because that is when that option was introduced. Its presence in a common option group will cause startup failure for older servers.

o Servers look for an option file named my.cnf in the compiled-in data directory location. If each server has a different data directory pathname compiled in, you can use these my.cnf files to list options specific to the corresponding servers. In other words, use /etc/my.cnf for any common settings that you want all servers to use, and use DATADIR/my.cnf for server-specific settings where DATADIR varies per server. (Note that this strategy does not work if you need to specify the data directory location at runtime. Nor will it work if you're running multiple instances of a given server binary.)

o Use the mysqld_multi script to manage startup for multiple servers. This script allows you to list the options for all servers in a single file, but associate each server with its own particular option group in the file.

. Under Windows, you can run multiple services, using special option file group-naming conventions specific to this style of server setup.