|
|
Running Multiple ServersMost people run a single MySQL server on a given machine, but there are circumstances under which it can be useful to run multiple servers:
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 IssuesRunning 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.
Configuring and Compiling Different ServersIf 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 OptionsAfter 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:
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 ManagementOn 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 WindowsThere 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 |
|
|