There are just a few steps necessary to set up multiple servers on one system. First, the configuration file for MySQL will need to be adjusted. A separate group of options will need to be entered for each server. At a minimum, each server should be assigned a unique TCP/IP port and either a different socket file.
Server groups for multiple servers in the configuration file are identified by a mysqld prefix followed by a number suffix. For instance, the first server might be identified by a group heading like [mysqld1], you could start with 0 or some other number. The second server could be [mysqld2]. The numbers don’t have to be sequential, per se. Numbers may be skipped. All of the options that can be given for a [mysqld] group may be given to any additional server. This allows you to change server options to meet any special requirements that may be needed for a database. Below are the contents from a /etc/my.cnf file that is configured for three servers.
# vi /etc/my.cnf [mysqld_multi] mysqld = /usr/local/bin/mysqld_safe [mysqld0] port = 3306 socket = /tmp/mysql.sock0 pid-file = /usr/local/mysql/server0/ user = mysql0 datadir = /usr/local/mysql/server0 [mysqld1] port = 3307 socket = /tmp/mysql.sock1 pid-file = /usr/local/mysql/server1/ user = mysql1 datadir = /usr/local/mysql/server1 [mysqld2] port = 3308 socket = /tmp/mysql.sock2 pid-file = /usr/local/mysql/server2/ user = mysql2 datadir = /usr/local/mysql/server2
First, notice that there is no group labeled [mysqld]. That would confuse things. In this configuration file, each server uses a different port. This will require adjustments to any scripts that access the server. The port will have to be given so that the default port (3306) isn’t used. The first server above uses the default port. In this example, a separate file system user is named for each server. This isn’t necessary. However, it can be useful for better security control. It will require the creation of each system user at the file system level, of course.
The last option set in each group of the configuration file above is the data directory for each server. The servers cannot share the same data directory. Therefore, this will require the creation of a separate directory on the file system. Be sure to change the ownership of the data directories to the respective system user (e.g., mysql0).
Once the configuration file has been set up properly, the system users created, and the related directories are in place, then the servers may be started. To do this easily, use the mysqld_multi script. This script will read the MySQL configuration file and look for server groups following the naming convention mentioned earlier. To start all of the servers, the following would be entered from the command-line:
$ mysqld_multi start
This line will start a separate instance of mysqld_safe for each server group listed in the configuration file. Incidentally, to use a special configuration file for mysqld_multi, just add the “–config-file = file” option before the start parameter. To stop all of the servers, replace the start parameter with stop. For server status reports, use the report parameter. To start a specific server, give the identifying number of the server after the start parameter. For instance, to stop the server labeled [mysqld2] in the configuration file shown earlier, the following would be entered:
$ mysqld_multi stop 2
The mysqld prefix is omitted. A range of servers can be named by giving the number of the first server, followed by a dash, and then the number of the last one (e.g., 2-5). Servers can be listed individually by separating their identifier by commas (e.g., 2, 4). And, a combination of both methods may be used (e.g., 2, 4-7).
For a longer set of examples of how a configuration file may be constructed, enter the following from the command-line:
$ mysqld_multi --example