5.7.3 Running Multiple MySQL Instances on Unix
The discussion here uses 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, mysqld_safe is not installed because it is unnecessary. For information about using systemd to handle multiple MySQL instances, see Section 2.5.10, “Managing MySQL Server with 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.32 server to have different operating parameters, use a CMake command something like this:
shell> cmake . -DMYSQL_TCP_PORT=port_number \ -DMYSQL_UNIX_ADDR=file_name \ -DCMAKE_INSTALL_PREFIX=/usr/local/mysql-5.7.32
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:
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.
If you specify
localhost as the host name, mysqladmin defaults to using a Unix socket file rather than TCP/IP. To explicitly specify the transport protocol, use the
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:
shell> mysqld_safe --socket=file_name --port=port_number
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.cnf2, start the servers like this: command:
shell> mysqld_safe --defaults-file=/usr/local/mysql/my.cnf shell> 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:
shell> MYSQL_UNIX_PORT=/tmp/mysqld-new.sock shell> MYSQL_TCP_PORT=3307 shell> export MYSQL_UNIX_PORT MYSQL_TCP_PORT shell> mysqld --initialize --user=mysql ...set root password... 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 apply to any client programs that you invoke from the same shell. Thus, connections for those clients are automatically directed to the second server.
Section 4.9, “Environment Variables”, includes a list of other environment variables you can use to affect MySQL programs.
On Unix, the mysqld_multi script provides another way to start multiple servers. See Section 4.3.4, “mysqld_multi — Manage Multiple MySQL Servers”.