4.9.2 Running Multiple Servers on Unix - MySQL

The easiest way to run multiple servers on Unix is to compile them with different TCP/IP ports and Unix socket files so that each one is listening on different network interfaces. Also, by compiling in different base directories for each installation, that automatically results in different compiled-in data directory, log file, and PID file locations for each of your servers.

Assume that an existing server is configured for the default TCP/IP port number (3306) and Unix socket file (/tmp/mysql.sock). To configure a new server to have different operating parameters, use a configure command something like this:

shell> ./configure --with-tcp-port=port_number \

--with-unix-socket-path=file_name \

--prefix=/usr/local/mysql-4.0.17

Here, port_number and file_name must be different from the default TCP/IP port number and Unix socket file pathname, and the --prefix value should specify an installation directory different than the one under which the existing MySQL installation is located.

If you have a MySQL server listening on a given port number, you can use the following command to find out what operating parameters it is using for several important configurable variables, including the base directory and Unix socket filename:

shell> mysqladmin --host=host_name

--port=port_number variables

With the information displayed by that command, you can tell what option values not to use when configuring an additional server.

Note that if you specify localhost as a hostname, mysqladmin will default to using a Unix socket file connection rather than TCP/IP. In MySQL 4.1, you can explicitly specify the connection protocol to use by using the --protocol={TCP | SOCKET | PIPE | MEMORY} option.

You don't have to compile a new MySQL server just to start with a different Unix socket file and TCP/IP port number. It is also possible to specify those values at runtime. One way to do so is by using command-line options:

shell> mysqld_safe --socket=file_name

--port=port_number

To start a second server, provide different --socket and --port option values, and pass a --datadir=path option to mysqld_safe so that the server uses a different data directory.

Another way to achieve a similar effect is to use environment variables to set the Unix socket filename and TCP/IP port number:

shell> MYSQL_UNIX_PORT=/tmp/mysqld-new.sock
shell> MYSQL_TCP_PORT=3307
shell> export MYSQL_UNIX_PORT MYSQL_TCP_PORT
shell> mysql_install_db --user=mysql
shell> mysqld_safe --datadir=/path/to/datadir &

This is a quick way of starting a second server to use for testing. The nice thing about this method is that the environment variable settings will apply to any client programs that you invoke from the same shell. Thus, connections for those clients automatically will be directed to the second server!

Appendix B, "Environment Variables," includes a list of other environment variables you can use to affect mysqld.

For automatic server execution, your startup script that is executed at boot time should execute the following command once for each server with an appropriate option file path for each command:

mysqld_safe --defaults-file=path

Each option file should contain option values specific to a given server.

On Unix, the mysqld_multi script is another way to start multiple servers. See Section 4.1.5, "The mysqld_multi Program for Managing Multiple MySQL Servers."

4.9.3 Using Client Programs in a Multiple-Server Environment

When you want to connect with a client program to a MySQL server that is listening to different network interfaces than those compiled into your client, you can use one of the following methods:

• Start the client with --host=host_name --port=port_number to connect via TCP/IP to a remote server, with --host=127.0.0.1 --port=port_number to connect via TCP/IP to a local server, or with --host=localhost --socket=file_name to connect to a local server via a Unix socket file or a Windows named pipe.

• As of MySQL 4.1, start the client with --protocol=tcp to connect via TCP/IP, --protocol=socket to connect via a Unix socket file, --protocol=pipe to connect via a named pipe, or --protocol=memory to connect via shared memory. For TCP/IP connections, you may also need to specify --host and --port options. For the other types of connections, you may need to specify a --socket option to specify a Unix socket file or named pipe name, or a --shared-memory-base-name option to specify the shared memory name. Shared memory connections are supported only on Windows.

• On Unix, set the MYSQL_UNIX_PORT and MYSQL_TCP_PORT environment variables to point to the Unix socket file and TCP/IP port number before you start your clients. If you normally use a specific socket file or port number, you can place commands to set these environment variables in your .login file so that they apply each time you log in. See Appendix B, "Environment Variables."

• Specify the default Unix socket file and TCP/IP port number in the [client] group of an option file. For example, you can use C:\my.cnf on Windows, or the .my.cnf file in your home directory on Unix. See Section 3.3.2, "Using Option Files."

• In a C program, you can specify the socket file or port number arguments in the mysql_real_connect() call. You can also have the program read option files by calling mysql_options().

• If you are using the Perl DBD::mysql module, you can read options from MySQL option files. For example:

  $dsn = "DBI:mysql:
  
  test;mysql_read_default_group=client;"
  . "mysql_read_default_file=/usr/local/
  
  mysql/data/my.cnf";
  $dbh = DBI->connect($dsn, $user, $password);

• Other programming interfaces may provide similar capabilities for reading option files.