5.7 Running Multiple MySQL Instances on One Machine

Menu:
multiple-data-directories:: Setting Up Multiple Data Directories
multiple-windows-servers:: Running Multiple MySQL Instances on Windows
multiple-unix-servers:: Running Multiple MySQL Instances on Unix
multiple-server-clients:: Using Client Programs in a Multiple-Server Environment

In some cases, you might want to run multiple instances of MySQL on a single machine. You might want to test a new MySQL release while leaving an existing production setup undisturbed. Or you might want to give different users access to different *note 'mysqld': mysqld. servers that they manage themselves. (For example, you might be an Internet Service Provider that wants to provide independent MySQL installations for different customers.)

It is possible to use a different MySQL server binary per instance, or use the same binary for multiple instances, or any combination of the two approaches. For example, you might run a server from MySQL 5.6 and one from MySQL 5.7, to see how different versions handle a given workload. Or you might run multiple instances of the current production version, each managing a different set of databases.

Whether or not you use distinct server binaries, each instance that you run must be configured with unique values for several operating parameters. This eliminates the potential for conflict between instances. Parameters can be set on the command line, in option files, or by setting environment variables. See note program-options::. To see the values used by a given instance, connect to it and execute a note 'SHOW VARIABLES': show-variables. statement.

The primary resource managed by a MySQL instance is the data directory. Each instance should use a different data directory, the location of which is specified using the '--datadir=DIR_NAME' option. For methods of configuring each instance with its own data directory, and warnings about the dangers of failing to do so, see *note multiple-data-directories::.

In addition to using different data directories, several other options must have different values for each server instance:

'--port=PORT_NUM'

'--port' controls the port number for TCP/IP connections. Alternatively, if the host has multiple network addresses, you can set the 'bind_address' system variable to cause each server to listen to a different address.
'--socket={FILE_NAME|PIPE_NAME}'

'--socket' controls the Unix socket file path on Unix or the named-pipe name on Windows. On Windows, it is necessary to specify distinct pipe names only for those servers configured to permit named-pipe connections.
'--shared-memory-base-name=NAME'

This option is used only on Windows. It designates the shared-memory name used by a Windows server to permit clients to connect using shared memory. It is necessary to specify distinct shared-memory names only for those servers configured to permit shared-memory connections.
'--pid-file=FILE_NAME'

This option indicates the path name of the file in which the server writes its process ID.

If you use the following log file options, their values must differ for each server:

'--general_log_file=FILE_NAME'
'--log-bin[=FILE_NAME]'
'--slow_query_log_file=FILE_NAME'
'--log-error[=FILE_NAME]'

For further discussion of log file options, see *note server-logs::.

To achieve better performance, you can specify the following option differently for each server, to spread the load between several physical disks:

'--tmpdir=DIR_NAME'

Having different temporary directories also makes it easier to determine which MySQL server created any given temporary file.

If you have multiple MySQL installations in different locations, you can specify the base directory for each installation with the '--basedir=DIR_NAME' option. This causes each instance to automatically use a different data directory, log files, and PID file because the default for each of those parameters is relative to the base directory. In that case, the only other options you need to specify are the '--socket' and '--port' options. Suppose that you install different versions of MySQL using 'tar' file binary distributions. These install in different locations, so you can start the server for each installation using the command 'bin/mysqld_safe' under its corresponding base directory. note 'mysqld_safe': mysqld-safe. determines the proper '--basedir' option to pass to note 'mysqld': mysqld, and you need specify only the '--socket' and '--port' options to *note 'mysqld_safe': mysqld-safe.

As discussed in the following sections, it is possible to start additional servers by specifying appropriate command options or by setting environment variables. However, if you need to run multiple servers on a more permanent basis, it is more convenient to use option files to specify for each server those option values that must be unique to it. The '--defaults-file' option is useful for this purpose.

File: manual.info.tmp, Node: multiple-data-directories, Next: multiple-windows-servers, Prev: multiple-servers, Up: multiple-servers

5.7.1 Setting Up Multiple Data Directories

Each MySQL Instance on a machine should have its own data directory. The location is specified using the '--datadir=DIR_NAME' option.

There are different methods of setting up a data directory for a new instance:

Create a new data directory.
Copy an existing data directory.

The following discussion provides more detail about each method.

Warning:

Normally, you should never have two servers that update data in the same databases. This may lead to unpleasant surprises if your operating system does not support fault-free system locking. If (despite this warning) you run multiple servers using the same data directory and they have logging enabled, you must use the appropriate options to specify log file names that are unique to each server. Otherwise, the servers try to log to the same files.

Even when the preceding precautions are observed, this kind of setup works only with 'MyISAM' and 'MERGE' tables, and not with any of the other storage engines. Also, this warning against sharing a data directory among servers always applies in an NFS environment. Permitting multiple MySQL servers to access a common data directory over NFS is a very bad idea. The primary problem is that NFS is the speed bottleneck. It is not meant for such use. Another risk with NFS is that you must devise a way to ensure that two or more servers do not interfere with each other. Usually NFS file locking is handled by the 'lockd' daemon, but at the moment there is no platform that performs locking 100% reliably in every situation.

Create a New Data Directory

With this method, the data directory is in the same state as when you first install MySQL. It has the default set of MySQL accounts and no user data.

On Unix, initialize the data directory. See *note postinstallation::.

On Windows, the data directory is included in the MySQL distribution:

MySQL Zip archive distributions for Windows contain an unmodified data directory. You can unpack such a distribution into a temporary location, then copy it 'data' directory to where you are setting up the new instance.
Windows MSI package installers create and set up the data directory that the installed server uses, but also create a pristine 'template' data directory named 'data' under the installation directory. After an installation has been performed using an MSI package, the template data directory can be copied to set up additional MySQL instances.

Copy an Existing Data Directory

With this method, any MySQL accounts or user data present in the data directory are carried over to the new data directory.

Stop the existing MySQL instance using the data directory. This must be a clean shutdown so that the instance flushes any pending changes to disk.
Copy the data directory to the location where the new data directory should be.
Copy the 'my.cnf' or 'my.ini' option file used by the existing instance. This serves as a basis for the new instance.
Modify the new option file so that any pathnames referring to the original data directory refer to the new data directory. Also, modify any other options that must be unique per instance, such as the TCP/IP port number and the log files. For a list of parameters that must be unique per instance, see *note multiple-servers::.
Start the new instance, telling it to use the new option file.

File: manual.info.tmp, Node: multiple-windows-servers, Next: multiple-unix-servers, Prev: multiple-data-directories, Up: multiple-servers

5.7.2 Running Multiple MySQL Instances on Windows

Menu:
multiple-windows-command-line-servers:: Starting Multiple MySQL Instances at the Windows Command Line
multiple-windows-services:: Starting Multiple MySQL Instances as Windows Services

You can run multiple servers on Windows by starting them manually from the command line, each with appropriate operating parameters, or by installing several servers as Windows services and running them that way. General instructions for running MySQL from the command line or as a service are given in note windows-installation::. The following sections describe how to start each server with different values for those options that must be unique per server, such as the data directory. These options are listed in note multiple-servers::.

File: manual.info.tmp, Node: multiple-windows-command-line-servers, Next: multiple-windows-services, Prev: multiple-windows-servers, Up: multiple-windows-servers

5.7.2.1 Starting Multiple MySQL Instances at the Windows Command Line .....................................................................

The procedure for starting a single MySQL server manually from the command line is described in *note windows-start-command-line::. To start multiple servers this way, you can specify the appropriate options on the command line or in an option file. It is more convenient to place the options in an option file, but it is necessary to make sure that each server gets its own set of options. To do this, create an option file for each server and tell the server the file name with a '--defaults-file' option when you run it.

Suppose that you want to run one instance of *note 'mysqld': mysqld. on port 3307 with a data directory of 'C:1', and another instance on port 3308 with a data directory of 'C:2'. Use this procedure:

Make sure that each data directory exists, including its own copy of the 'mysql' database that contains the grant tables.
Create two option files. For example, create one file named 'C:-opts1.cnf' that looks like this:
```
  [mysqld]
  datadir = C:/mydata1
  port = 3307
```
Create a second file named 'C:-opts2.cnf' that looks like this:
```
  [mysqld]
  datadir = C:/mydata2
  port = 3308
```
Use the '--defaults-file' option to start each server with its own option file:
```
  C:\> C:\mysql\bin\mysqld --defaults-file=C:\my-opts1.cnf
  C:\> C:\mysql\bin\mysqld --defaults-file=C:\my-opts2.cnf
```
Each server starts in the foreground (no new prompt appears until the server exits later), so you must issue those two commands in separate console windows.

To shut down the servers, connect to each using the appropriate port number:

 C:\> C:\mysql\bin\mysqladmin --port=3307 --host=127.0.0.1 --user=root --password shutdown
 C:\> C:\mysql\bin\mysqladmin --port=3308 --host=127.0.0.1 --user=root --password shutdown

Servers configured as just described permit clients to connect over TCP/IP. If your version of Windows supports named pipes and you also want to permit named-pipe connections, specify options that enable the named pipe and specify its name. Each server that supports named-pipe connections must use a unique pipe name. For example, the 'C:-opts1.cnf' file might be written like this:

 [mysqld]
 datadir = C:/mydata1
 port = 3307
 enable-named-pipe
 socket = mypipe1

Modify 'C:-opts2.cnf' similarly for use by the second server. Then start the servers as described previously.

A similar procedure applies for servers that you want to permit shared-memory connections. Enable such connections by starting the server with the 'shared_memory' system variable enabled and specify a unique shared-memory name for each server by setting the 'shared_memory_base_name' system variable.

File: manual.info.tmp, Node: multiple-windows-services, Prev: multiple-windows-command-line-servers, Up: multiple-windows-servers

5.7.2.2 Starting Multiple MySQL Instances as Windows Services .............................................................

On Windows, a MySQL server can run as a Windows service. The procedures for installing, controlling, and removing a single MySQL service are described in *note windows-start-service::.

To set up multiple MySQL services, you must make sure that each instance uses a different service name in addition to the other parameters that must be unique per instance.

For the following instructions, suppose that you want to run the *note 'mysqld': mysqld. server from two different versions of MySQL that are installed at 'C:-5.7.9' and 'C:-5.7.44', respectively. (This might be the case if you are running 5.7.9 as your production server, but also want to conduct tests using 5.7.44.)

To install MySQL as a Windows service, use the '--install' or '--install-manual' option. For information about these options, see *note windows-start-service::.

Based on the preceding information, you have several ways to set up multiple services. The following instructions describe some examples. Before trying any of them, shut down and remove any existing MySQL services.

Approach 1: Specify the options for all services in one of the standard option files. To do this, use a different service name for each server. Suppose that you want to run the 5.7.9 note 'mysqld': mysqld. using the service name of 'mysqld1' and the 5.7.44 note 'mysqld': mysqld. using the service name 'mysqld2'. In this case, you can use the '[mysqld1]' group for 5.7.9 and the '[mysqld2]' group for 5.7.44. For example, you can set up 'C:.cnf' like this:
```
  # options for mysqld1 service
  [mysqld1]
  basedir = C:/mysql-5.7.9
  port = 3307
  enable-named-pipe
  socket = mypipe1

  # options for mysqld2 service
  [mysqld2]
  basedir = C:/mysql-5.7.44
  port = 3308
  enable-named-pipe
  socket = mypipe2
```
Install the services as follows, using the full server path names to ensure that Windows registers the correct executable program for each service:
```
  C:\> C:\mysql-5.7.9\bin\mysqld --install mysqld1
  C:\> C:\mysql-5.7.44\bin\mysqld --install mysqld2
```
To start the services, use the services manager, or 'NET START' or 'SC START' with the appropriate service names:
```
  C:\> SC START mysqld1
  C:\> SC START mysqld2
```
To stop the services, use the services manager, or use 'NET STOP' or 'SC STOP' with the appropriate service names:
```
  C:\> SC STOP mysqld1
  C:\> SC STOP mysqld2
```
Approach 2: Specify options for each server in separate files and use '--defaults-file' when you install the services to tell each server what file to use. In this case, each file should list options using a '[mysqld]' group.

With this approach, to specify options for the 5.7.9 *note 'mysqld': mysqld, create a file 'C:-opts1.cnf' that looks like this:
```
  [mysqld]
  basedir = C:/mysql-5.7.9
  port = 3307
  enable-named-pipe
  socket = mypipe1
```
For the 5.7.44 *note 'mysqld': mysqld, create a file 'C:-opts2.cnf' that looks like this:
```
  [mysqld]
  basedir = C:/mysql-5.7.44
  port = 3308
  enable-named-pipe
  socket = mypipe2
```
Install the services as follows (enter each command on a single line):
```
  C:\> C:\mysql-5.7.9\bin\mysqld --install mysqld1
             --defaults-file=C:\my-opts1.cnf
  C:\> C:\mysql-5.7.44\bin\mysqld --install mysqld2
             --defaults-file=C:\my-opts2.cnf
```
When you install a MySQL server as a service and use a '--defaults-file' option, the service name must precede the option.

After installing the services, start and stop them the same way as in the preceding example.

To remove multiple services, use 'SC DELETE MYSQLD_SERVICE_NAME' for each one. Alternatively, use note 'mysqld --remove': mysqld. for each one, specifying a service name following the '--remove' option. If the service name is the default ('MySQL'), you can omit it when using note 'mysqld --remove': mysqld.

File: manual.info.tmp, Node: multiple-unix-servers, Next: multiple-server-clients, Prev: multiple-windows-servers, Up: multiple-servers

5.7.3 Running Multiple MySQL Instances on Unix

Note:

The discussion here uses note 'mysqld_safe': mysqld-safe. to launch multiple instances of MySQL. For MySQL installation using an RPM distribution, server startup and shutdown is managed by systemd on several Linux platforms. On these platforms, note 'mysqld_safe': mysqld-safe. is not installed because it is unnecessary. For information about using systemd to handle multiple MySQL instances, see *note using-systemd::.

One way is to run multiple MySQL instances on Unix is to compile different servers with different default TCP/IP ports and Unix socket files so that each one listens on different network interfaces. Compiling in different base directories for each installation also results automatically in a separate, compiled-in data directory, log file, and PID file location for each server.

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

 $> cmake . -DMYSQL_TCP_PORT=PORT_NUMBER \
              -DMYSQL_UNIX_ADDR=FILE_NAME \
              -DCMAKE_INSTALL_PREFIX=/usr/local/mysql-5.7.44

Here, PORT_NUMBER and FILE_NAME must be different from the default TCP/IP port number and Unix socket file path name, and the 'CMAKE_INSTALL_PREFIX' value should specify an installation directory different from 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 file name:

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

If you specify 'localhost' as the host name, *note 'mysqladmin': mysqladmin. defaults to using a Unix socket file rather than TCP/IP. To explicitly specify the transport protocol, use the '--protocol={TCP|SOCKET|PIPE|MEMORY}' option.

You need not compile a new MySQL server just to start with a different Unix socket file and TCP/IP port number. It is also possible to use the same server binary and start each invocation of it with different parameter values at runtime. One way to do so is by using command-line options:

 $> mysqld_safe --socket=FILE_NAME --port=PORT_NUMBER

To start a second server, provide different '--socket' and '--port' option values, and pass a '--datadir=DIR_NAME' option to *note 'mysqld_safe': mysqld-safe. so that the server uses a different data directory.

Alternatively, put the options for each server in a different option file, then start each server using a '--defaults-file' option that specifies the path to the appropriate option file. For example, if the option files for two server instances are named '/usr/local/mysql/my.cnf' and '/usr/local/mysql/my.cnf2', start the servers like this: command:

 $> mysqld_safe --defaults-file=/usr/local/mysql/my.cnf
 $> mysqld_safe --defaults-file=/usr/local/mysql/my.cnf2

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

 $> MYSQL_UNIX_PORT=/tmp/mysqld-new.sock
 $> MYSQL_TCP_PORT=3307
 $> export MYSQL_UNIX_PORT MYSQL_TCP_PORT
 $> mysqld --initialize --user=mysql
 ...SET ROOT PASSWORD...
 $> 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 apply to any client programs that you invoke from the same shell. Thus, connections for those clients are automatically directed to the second server.

*note environment-variables::, includes a list of other environment variables you can use to affect MySQL programs.

On Unix, the note 'mysqld_multi': mysqld-multi. script provides another way to start multiple servers. See note mysqld-multi::.

File: manual.info.tmp, Node: multiple-server-clients, Prev: multiple-unix-servers, Up: multiple-servers

5.7.4 Using Client Programs in a Multiple-Server Environment

To connect with a client program to a MySQL server that is listening to different network interfaces from 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 using TCP/IP to a remote server, with '--host=127.0.0.1' '--port=PORT_NUMBER' to connect using TCP/IP to a local server, or with '--host=localhost' '--socket=FILE_NAME' to connect to a local server using a Unix socket file or a Windows named pipe.
Start the client with '--protocol=TCP' to connect using TCP/IP, '--protocol=SOCKET' to connect using a Unix socket file, '--protocol=PIPE' to connect using a named pipe, or '--protocol=MEMORY' to connect using 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 Windows 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 *note 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:.cnf' on Windows, or the '.my.cnf' file in your home directory on Unix. See *note option-files::.
In a C program, you can specify the socket file or port number arguments in the 'mysql_real_connect()' (https://dev.mysql.com/doc/c-api/5.7/en/mysql-real-connect.html) call. You can also have the program read option files by calling 'mysql_options()' (https://dev.mysql.com/doc/c-api/5.7/en/mysql-options.html). See C API Basic Function Descriptions (https://dev.mysql.com/doc/c-api/5.7/en/c-api-function-descriptions.html).
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);
```
See *note apis-perl::.

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

File: manual.info.tmp, Node: debugging-mysql, Prev: multiple-servers, Up: server-administration