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.5 server is configured for
the default TCP/IP port number (3306) and Unix socket file
/tmp/mysql.sock). To configure a new
5.6.51 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.6.51
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,
mysqladmin defaults to using a Unix socket file
rather than TCP/IP. To explicitly specify the transport protocol,
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
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:
$> 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 $> mysql_install_db --user=mysql $> 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”.