Running Multiple MySQL Instances on One Machine

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.

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.5 and one from MySQL 5.6, 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. To see the values used by a given instance, connect to it and execute a 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.

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. 
• --socket={file_name|pipe_name}
  --socket controls the Unix socket file path on Unix or the named pipe name on Windows. 
• --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]

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 commandbin/mysqld_safe under its corresponding base directory. mysqld_safe determines the proper --basedir option to pass to mysqld, and you need specify only the --socket and --port options to 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.

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.

 Initializing the Data Directory or Creating New Data Directory

In the examples shown here, the server runs under the user ID of the mysql login account. This assumes that such an account exists. Either create the account if it does not exist, or substitute the name of a different existing login account that you plan to use for running the server.

1. Create a new dir where you want to store mysql data:

   shell> mkdir -p /var/lib/mysql2
   

   BASEDIR is likely to be something like /usr/local/mysql, /usr/local, or /usr/bin (for installation wtih MySQL Yum repository, or other means). The following steps assume that you have changed location to this directory.
   You will find several files and subdirectories in the BASEDIR directory. The most important for installation purposes are the bin and scriptssubdirectories, which contain the server as well as client and utility programs.
2. Change dir ownership to mysql by executing the following commands as root in the installation directory. The first command changes the owner attribute of the files to the mysql user. The second changes the group attribute to the mysql group.

   shell> chown -R mysql .
   shell> chgrp -R mysql .
   

3. Initialize the data directory, including the mysql database containing the initial MySQL grant tables that determine how users are permitted to connect to the server.
   The command that initializes the data directory does not overwrite any existing privilege tables, so it should be safe to run in any circumstances.

   shell> scripts/mysql_install_db --user=mysql
   

   It is important to make sure that the database directories and files are owned by the mysql login account so that the server has read and write access to them when you run it later. To ensure this if you run mysql_install_db as root, include the --user option as shown. Otherwise, you should execute the program while logged in as mysql, in which case you can omit the --user option from the command.
   The mysql_install_db command creates the server's data directory. Under the data directory, it creates directories for the mysql database that holds the grant tables and the test database that you can use to test MySQL. The program also creates privilege table entries for the initial account or accounts. test_. For a complete listing and description of the grant tables.
   It might be necessary to specify other options such as --basedir or --datadir if mysql_install_db does not identify the correct locations for the installation directory or data directory. For example:

   shell> scripts/mysql_install_db --user=mysql \
            --basedir=/usr/bin/mysql \
            --datadir=/var/lib/mysql2

4. After initializing the data directory, you can establish the final installation ownership settings. The exception is that the data directory must be owned by mysql. To accomplish this, run the following commands as root in the installation directory. 

   shell> chown -R mysql:mysql /var/lib/mysql2

5. To specify options that the MySQL server should use at startup, put them in a /etc/my.cnf or /etc/mysql/my.cnf file. If you do not do this, the server starts with its default settings.

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.

1. 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.
2. Copy the data directory to the location where the new data directory should be.
3. Copy the my.cnf or my.ini option file used by the existing instance. This serves as a basis for the new instance.
4. 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.
5. Start the new instance, telling it to use the new option file.

Starting Multiple MySQL Instances at the 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 mysqld on port 3306 with a data directory of /varlib/mysql, and another instance on port 3307 with a data directory of /var/lib/mysql2. Use this procedure:

1. Make sure that each data directory exists, including its own copy of the mysql database that contains the grant tables.
2. Create two option files. For example, create one file named /etc/my-opts1.cnf that looks like this:

   [mysqld]
   datadir = /var/lib/mysql
   port = 3306
   

   Create a second file named /etc/my-opts2.cnf that looks like this:

   [mysqld]
   datadir = /var/lib/mysql2
   port = 3307
   

3. Use the --defaults-file option to start each server with its own option file:

   /usr/bin/mysqld_safe --defaults-file=/etc/my-opts1.cnf &
   /usr/bin/mysqld_safe --defaults-file=/etc/my-opts2.cnf &

   Each server starts in the foreground (no new prompt appears until the server exits later), so you will need to issue those two commands in separate console windows or use & for running it in background.

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

/usr/bin/mysqladmin --port=3306 --host=127.0.0.1 --user=root --password shutdown
/usr/bin/mysqladmin --port=3307 --host=127.0.0.1 --user=root --password shutdown

Servers configured as just described permit clients to connect over TCP/IP. 

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.

[root@localhost ~]# mysql -uroot -p --socket=/tmp/mysql2.sock --port=3307

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.6.36 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like '%port%';

+---------------------+-------+

| Variable_name       | Value |

+---------------------+-------+

| port                | 3307  |

1 rows in set (0.00 sec)

mysql>