Installing Multiple MySQL Servers on the Same Machine
|
|
There are few reasons you would want to run multiple MySQL servers on the same machine. You will not gain any performance, and you will almost definitely not want to allow the multiple versions to access the same data. Most likely, you'll be doing this to test a new version of MySQL without removing your previous installation.
If you plan to run multiple versions of MySQL on the same machine, you'll need to ensure that they do not attempt to use the same socket file or listen on the same TCP/IP port. They will also have their own pid file. The defaults are port 3306 and /tmp/mysql.sock on most systems. A convenient way of managing this is with the mysqld_multi utility, discussed later in this chapter. You can change the default port and TCP/IP settings in the configuration file, assuming it's a different configuration file than the other server. For example:
socket=/tmp/mysql2.sock port=3307
Clients can connect to servers running on a different socket by using the --socket option:
% mysql --socket=/tmp/mysql2.sock -uroot -pg00r002b
You can also specify the server to connect to by specifying the configuration file to use for the client. For example:
% mysql --defaults-file=/usr/local/mysql2/etc/my.cnf -uroot -pg00r002b
If you're compiling MySQL, configure the second server with a new port number, socket path, and installation directory. For example:
% ./configure --with-tcp-port=3307 \ --with-unix-socket-path=/tmp/mysql2.sock \ --prefix=/usr/local/mysql2
| Warning | Never have more than one server controlling the same data, which is a foolproof recipe for corruption! They should not need to write to the same log files either. |
MySQL distributions come with a handy utility by the name of mysqld_multi, which is a useful tool for managing multiple MySQL servers (running on different sockets and ports). To use mysqld_multi, you'll need to set up your configuration file with a mysqld_multi section, as well as sections for each MySQL server you'll be running. For example:
[mysqld_multi] mysqld = /usr/local/bin/mysqld_safe mysqladmin = /usr/local/bin/mysqladmin user = root password = g00r002b [mysqld1] socket = /tmp/mysql.sock port = 3306 pid-file = /usr/local/mysql/var/hostname.pid datadir = /usr/local/mysql/var language = /usr/local/share/mysql/english user = hartmann [mysqld2] socket = /tmp/mysql.sock2 port = 3307 pid-file = /usr/local/mysql/var2/hostname.pid datadir = /usr/local/mysql/var2 language = /usr/local/share/mysql/french user = yves [mysqld3] socket = /tmp/mysql.sock3 port = 3308 pid-file = /usr/local/mysql/var3/hostname.pid datadir = /usr/local/mysql/var3 language = /usr/local/share/mysql/german user = cleo [mysqld4] socket = /tmp/mysql.sock4 port = 3309 pid-file = /usr/local/mysql/var4/hostname.pid datadir = /usr/local/mysql/var4 language = /usr/local/share/mysql/english user = caledon
The syntax for mysqld_multi is as follows:
mysqld_multi [option/s] {start|stop|report} [group_no,group_no2...] Assuming a setup as in the example you've just seen of a configuration file, the following example shows mysqld_multi reporting on the status of a server and then being used to shut it down:
% mysqld_multi --user=root --password=g00r002b report 1 Reporting MySQL servers MySQL server from group: mysqld1 is running % mysqld_multi --user=root --password=g00r002b stop 1 % 020729 04:20:50 mysqld ended % mysqld_multi --user=root --password=g00r002b report 1 Reporting MySQL servers MySQL server from group: mysqld1 is not running
Table 15.5 describes the mysqld_multi options.
| Option | Description |
|---|---|
| --config-file=... | Set an alternative configuration file for the groups. (It will not affect the [mysqld_multi] group.) |
| --example | Provides a sample configuration file. |
| --help | Displays help and exits. |
| --log=... | Specifies the log file, taking the full path and name of the file. If this file already exists, logs will be appended on the end of the file. |
| --mysqladmin=... | The full path and name of the mysqladmin binary, used to shut down the server. |
| --mysqld=... | The full path and name of the mysqld binary to be used, or more often the mysqld_safe binary. Options are passed to mysqld. You'll need to make changes to mysqld_safe or ensure that it's in your PATH environment variable. |
| --no-log | Outputs to standard output instead of a log file. The default is to write to alogfile. |
| --password=... | The password for the user for mysqladmin. |
| --tcp-ip | Causes mysqld_multi to connect to the MySQL servers via TCP/IP instead of a Unix socket. By default the connection is via socket on Unix. |
| --user=... | The user for mysqladmin. Make sure this user has the correct privileges to do what is needed (shutdown_priv). |
| --version | Displays the version number and exits. |
|
|
