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 instances of the server binary 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.)

• 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.^[2] To specify these values explicitly, use the following options:

  ^[2] It's sometimes possible to have different servers share the same directory, but I don't recommend it.

  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 connections
  --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. (See the "Maintaining Log Files" section earlier in this chapter for more information about naming log 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="40005"  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 

At this point, I perform the MySQL installation lockdown procedure described briefly in the "Arranging for MySQL Server Startup and Shutdown" section earlier in this chapter and in more detail in Chapter 12.

After that, all that remains is to set up any options that I want to use in option files and to arrange for starting up the server. One way to do this is discussed in the "Using mysqld_multi for Server Management" section later in this chapter.

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:

  • 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.)

  • 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.

  • 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.)

  • 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.

The following sections show some ways to apply these strategies by demonstrating how to use mysqld_multi and how to run multiple servers under Windows.

Using mysqld_multi For Server Management

On UNIX, the mysqld_safe and mysql.server scripts that are commonly used to start up servers both work best in a single-server setting. To make it easier to handle several servers, the mysqld_multi script can be used instead.

mysqld_multi works on the basis that you assign a specific number to each server setup you want to create and then list that server's options in an option file group [mysqldn], where n is the number. The option file can also contain a group [mysqld_multi] that lists options specifically for mysqld_multi itself. For example, if I have servers installed for MySQL 3.23.51, 4.0.5, and 4.1.0, I might designate their option groups as [mysqld32351], [mysqld40005], and [mysqld40100] and set up the options in the /etc/my.cnf file as follows:

 [mysqld32351]  basedir=/var/mysql/32351 datadir=/var/mysql/32351/data mysqld=/var/mysql/32351/bin/mysqld_safe socket=/var/mysql/32351/mysql.sock port=32351 local-infile=1 user=mysqladm log=log log-update=update-log innodb_data_file_path = ibdata1:10M [mysqld40005] basedir=/var/mysql/40005 datadir=/var/mysql/40005/data mysqld=/var/mysql/40005/bin/mysqld_safe socket=/var/mysql/40005/mysql.sock port=40005 local-infile=1 user=mysqladm log=log log-bin=binlog innodb_data_file_path = ibdata1:10M:autoextend [mysqld40100] basedir=/var/mysql/40100 datadir=/var/mysql/40100/data mysqld=/var/mysql/40100/bin/mysqld_safe socket=/var/mysql/40100/mysql.sock port=40100 local-infile=1 user=mysqladm log=log log-bin=binlog skip-innodb skip-bdb language=french default-character-set=utf8 

The layout parameters that I've set up here for each server correspond to the directory configuration described earlier in this chapter in the "Configuring and Compiling Different Servers" section. I've also specified additional server-specific parameters that correspond to variations in types of logs, tables handlers, and so forth.

To start a given server, invoke mysqld_multi with a command word of start and the server's option group number on the command line:

 % mysqld_multi --no-log start 32351  

The --no-log option causes status messages to be sent to the terminal rather than to a log file. This allows you to see what's going on more easily. You can specify more than one server by giving the group numbers as a comma-separated list. A range of server numbers can be specified by separating the numbers with a dash. However, there must be no whitespace in the server list:

 % mysqld_multi --no-log start 32351,40005-40100  

To stop servers or obtain a status report indicating whether or not they are running, use a command word of stop or report followed by the server list. For these commands, mysqld_multi will invoke mysqladmin to communicate with the servers, so you'll also need to specify a username and password for an administrative account:

 % mysqld_multi --nolog --user=root --password=rootpass stop 32351  % mysqld_multi --nolog --user=root --password=rootpass report 32351,40100 

The username and password must be applicable to all servers that you want to control with a given command. mysqld_multi attempts to determine the location of mysqladmin automatically, or you can specify the path explicitly in the [mysqld_multi] group of an option file. You can also list a default administrative username and password in that option group to be used for the stop and report commands for example:

 [mysqld_multi]  mysqladmin=/usr/local/mysql/bin/mysqladmin user=leeloo password=multipass 

If you put the administrative username and password in the file, make sure that it isn't publicly readable!

Running Multiple Servers on Windows

There are a couple ways to run multiple servers on Windows. One method is based on starting the servers manually, and the other is to use multiple services.

To start multiple servers manually, create an option file for each one that lists its parameters. For example, to run two servers that use the same program binaries but different data directories, you might create two option files that look like the following:

C:\my.cnf1 file:

 [mysqld]  basedir=C:/mysql datadir=C:/mysql/data port=3306 

C:\my.cnf2 file:

 [mysqld]  basedir=C:/mysql datadir=C:/mysql/data2 port=3307 

Then start the servers from the command line, using --defaults-file to tell each one to read a specific option file:

 C:\> mysqld --defaults-file=C:\my.cnf1  C:\> mysqld --defaults-file=C:\my.cnf2 

Clients should connect by specifying the port number appropriate for the server they wish to access. This includes the use of mysqladmin for shutting down the servers. The first of the following commands uses the default port (3306) and the second specifies port 3307 explicitly:

 C:\> mysqladmin -p -u root shutdown  C:\> mysqladmin -P 3307 -p -u root shutdown 

Windows NT-based systems have service support, and it's possible as of MySQL 4.0.2 to specify the service name when you install a server:^[3]

^[3] It's now also possible to specify a service name argument in the 3.23 series as of MySQL 3.23.54.

 C:\> mysql-nt --install service_name  

This allows you to run multiple MySQL servers by choosing different service names. The rules that govern this capability are as follows:

• With no service_name argument, the server uses the default service name (MySql) and reads the [mysqld] group from option files.

• With a service_name argument, the server uses that name as the service name and reads the [service_name] group from option files.

• The server that runs using the default service name will support named pipes using the pipe name MySql. Any server for which you specify a service name explicitly will not support named pipes and listens only for TCP/IP connections, unless you specify a --socket option to indicate a different pipe name.

• Each server must manage a different data directory.

Suppose you want to run two instances of mysqld-nt, using service and named pipe names of MySql and mysqlsvc2, and the same data directories shown in the previous example. Set up the options for each server in one of the standard option files (such as C:\my.cnf) as follows:

 # group for default (MySql) service  [mysqld] basedir=C:/mysql datadir=C:/mysql/data port=3306 enable-named-pipe # group for mysqlsvc2 service [mysqlsvc2] basedir=C:/mysql datadir=C:/mysql/data2 port=3307 enable-named-pipe socket=mysqlsvc2 

To install and start up the services, use the following commands:

 C:\> mysql-nt --install  C:\> net start MySql C:\> mysql-nt --install mysqlsvc2 C:\> net start mysqlsvc2 

Clients can connect to the default server using the default port or pipe name. To connect to the second server, specify its port number or pipe name explicitly:

 C:\> mysql --port=3307  C:\> mysql --host=. --socket=mysqlsvc2 

To shut down the servers, use mysqladmin shutdown, net stop, or the Services Manager. To uninstall the servers, shut them down if they are running and then remove them by specifying --remove and the same service name that you used at server installation time:

 C:\> mysql-nt --remove  C:\> mysql-nt --remove mysqlsvc2 

As of MySQL 4.0.3, you can specify a --defaults-file option as the final option on the command line when you install the server:

 C:\> mysqld-nt --install service_name --defaults-file=file_name  

This gives you an alternative means of providing server-specific options. The name of the file will be remembered and used by the server whenever it starts up, and it will read options from the [mysqld] group of the file. To use this service installation syntax, you must specify a service name; to use the default service, use the name MySql explicitly.