Chapter 13. Database Backups, Maintenance, and Repair

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

• You want to try replication to familiarize yourself with it, but you have only a single server host and must run the master and slave on the same machine.

• Operating systems typically impose per-process limits 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 might require recompiling the kernel, and you cannot 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 dozens of servers installed. However, I run just a couple of them all the time. The others I run only on occasion for testing purposes, so I must 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 want to have several different versions installed simultaneously, 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 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 they will collide with each other. This is true whether you run different server binaries or multiple instances of a single binary. The same principle applies to other connection interfaces: Unix socket files, Windows named pipes, or shared memory. If you enable logging, each server must 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 going on. 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 runtime 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 set of log files when it runs. But the data directory is the default location for all of them, so if each server has a different data directory, that implicitly results in different sets of log files.

• If you're running different server versions, it's typical for each distribution to be installed under a different base directory. Each server also should have a separate data directory. (Use of separate data directories is mandatory on Windows and strongly recommended on Unix.) 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 Internet service provider might provide a common set of MySQL server and client programs for its customers, but run for each customer an instance of the server that uses a customer-specific data directory. In this case, the base directory would be the same for all servers, but individual data directories would be located in different places, 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	Unix domain socket file pathname or Windows named pipe name
  --pid-file=file_name	Pathname to file in which server writes its process ID
  --shared-memory-base-name=name	Name of shared memory to use for shared-memory connections (Windows only)

  
  

  On Windows, the --socket or --shared-memory-base-name options need be given only for those servers that are run with the --enable-named-pipe or --shared-memory options to enable named-pipe or shared-memory connections. In this case, one server can use the default named pipe and shared memory names (MySQL and MYSQL, respectively), but any others must specify different names.

• If you enable logging, any log filenames that you use must be different for each server. Otherwise, you'll have multiple servers contending to write to the same log files. That is at best confusing, and at worst 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 "Maintaining Log Files" for more information about naming log files.)

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

  
  

• If you are using servers as replication slaves, each must have a unique set of master and relay log information files. These are created in the data directory by default and are set explicitly with the --master-info-file and --relay-log-info-file options.

• Under Unix, if you use mysqld_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 --log-error=file_name. However, note that if you specify a relative pathname, mysqld_safe interprets it as relative to the directory from which it is invoked, not relative to the data directory. Specify an absolute pathname to make sure you always create the error log in the proper location.

• If the BDB or InnoDB storage engines 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 these 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 give them both the same value.

  Each server that uses InnoDB must be configured to use its own shared tablespace. The options for this are described in "Configuring the InnoDB Tablespace."

• Under Unix, it may 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 each must 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 goal. It describes the particular configuration conventions that I use to keep my own MySQL installations separate.

My layout 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/40109 as the installation base directory for MySQL 4.1.9, which can be accomplished by running configure with a --prefix=/var/mysql/40109 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 this (note that the data directory option for configure is --localstatedir, not --datadir):

 VERSION=40109 BASEDIR=/var/mysql/$VERSION TCP_PORT=$VERSION HANDLERS="--with-innodb --with-berkeley-db" OTHER="--enable-local-infile --with-vio --with-openssl" rm -f config.cache ./configure \     --prefix=$BASEDIR \     --localstatedir=$BASEDIR/data \     --with-unix-socket-path=$BASEDIR/mysql.sock \     --with-tcp-port=$TCP_PORT \     $HANDLERS $OTHER 

I make sure the first line is set to the proper version number and modify the other values as necessary according to which of the optional storage engines I want to compile in, whether to enable 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 

These commands work for a source distribution that has been released by MySQL AB. If you are working with a source tree obtained as a clone of the latest BitKeeper development sources, the configure script must be created as described in the MySQL Reference Manual before you can use config-ver.

After installing a given version of MySQL, it's necessary to change location into its installation base directory and initialize the data directory and grant tables:

 # cd /var/mysql/40109 # ./bin/mysql_install_db --user=user_name 

user_name is the name of the login account to be used for running the server (for example, the mysql account). You should run these commands while logged in as root or as user_name.

At this point, I perform the lockdown procedure for the MySQL installation directory that is described briefly in "Running the Server Using an Unprivileged Login Account" 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 the server. One way to do this is discussed in "Using mysqld_multi for Server Management."

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.

    If all of your servers are from MySQL 4.0.2 or newer, you can use the loose-opt_name syntax to specify options that are not understood by all servers. Servers that do not understand an option given this way will ignore the option and continue to execute after logging a warning. See "Program Option Conventions," in Appendix F for more information about "loose" options.

  • On Unix, 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 DATADIR/my.cnf for server-specific settings, where DATADIR varies per server. Note that this strategy is subject to several constraints:

  • It doesn't work if you need to specify the data directory location at runtime.

  • It doesn't work if you're running multiple instances of a given server binary.

  • If you make the data directory accessible only to the login account used to run the server, you cannot use the option file to list any client program options. They won't be able to read the file unless run from the same account.

  • 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 the special option file group naming conventions specific to this style of server setup. (See "Running the Server as a Windows Service.")

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 the server 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 [mysqldn] group, where n is the number. The option file can also contain a [mysqld_multi] group that lists options specifically for mysqld_multi itself. For example, if I have servers installed for MySQL 3.23.59, 4.1.9, and 5.0.3, I might designate their option groups as [mysqld32359], [mysqld40109], and [mysqld50003] and set up the options in the /etc/my.cnf file like this:

 [mysqld32359] basedir=/var/mysql/32359 datadir=/var/mysql/32359/data mysqld=/var/mysql/32359/bin/safe_mysqld socket=/var/mysql/32359/mysql.sock port=32359 user=mysql log=qlog log-bin=binlog innodb_data_file_path = ibdata1:10M [mysqld40109] basedir=/var/mysql/40109 datadir=/var/mysql/40109/data mysqld=/var/mysql/40109/bin/mysqld_safe socket=/var/mysql/40109/mysql.sock port=40109 user=mysql log=qlog log-bin=binlog innodb_data_file_path = ibdata1:10M:autoextend [mysqld50003] basedir=/var/mysql/50003 datadir=/var/mysql/50003/data mysqld=/var/mysql/50003/bin/mysqld_safe socket=/var/mysql/50003/mysql.sock port=50003 user=mysql log=qlog log-bin=binlog skip-innodb skip-bdb language=french character-set-server=utf8 

The layout parameters that I've set up here for each server correspond to the directory configuration described earlier in "Configuring and Compiling Different Servers." I've also specified additional server-specific parameters that correspond to variations in types of logs, storage engines, 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 40109 

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. For example:

 % mysqld_multi --no-log start 32359,40109-50003 

To stop servers or obtain a status report indicating whether 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 32359 % mysqld_multi --nolog --user=root --password=rootpass report 32359,50003 

The user 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 

From a security standpoint, it is preferable to list the administrative password in an option file rather than to expose it on the command line. If you put the password in a file, make sure that the file isn't publicly readable! For instructions on doing this, see "Securing Option Files," in Chapter 12.

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 Windows services. You can mix the two approaches if you like.

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 this:

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 

The data directory must exist before you can start a server, because there is no mysql_install_db equivalent for Windows. C:\mysql\data should already have been created for you if you performed a default install of MySQL. The easiest way to set up C:\mysql\data2 is to create it as a copy of C:\mysql\data. Use the following command (while the server is not running):

 C:\> xcopy C:\mysql\data C:\mysql\data2 /E 

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 using by specifying the port number appropriate for the server they want 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 

NT-based versions of Windows have service support. To install a MySQL server as a Windows service, use the --install option. For example, to install mysqld-nt as a service, you might use one of these commands:

 C:\> mysql-nt --install C:\> mysql-nt --install service_name 

With no service_name argument or a name of MySQL, the default service name (MySQL) is used; otherwise, the given name is used. (The rules about which option groups are read in the two cases are given in "Running the Server as a Windows Service.")

Suppose that 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 

The order of the groups is significant. The server installed under the default service name of MySQL reads only the [mysqld] option group. However, the server installed under the non-default service name of mysqlsvc2 reads both the [mysqld] and [mysqlsvc2] groups. By placing the [mysqlsvc2] group second in the option file, it can be used to override all the options in the [mysqld] group with values that are appropriate for the server running as the mysqlsvc2 service.

To install and start the services, use these commands:

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

If you provide a service name, you can also specify a --defaults-file option as the final option on the command line when you install a 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, and it will read options from the [mysqld] group of the file.

When there are multiple servers running, clients can connect to the default server using the default TCP/IP port or pipe name. To connect to the second server, specify its TCP/IP 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. You can omit the service name if it is the default name (MySQL):

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