The easiest way is to run multiple servers on Unix is to compile them with different TCP/IP ports and Unix socket files so that each one is listening 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.0.19 server is configured for the
default TCP/IP port number (3306) and Unix socket file
(/tmp/mysql.sock
). To configure a new
5.1.47 server to have different operating parameters,
use a configure command something like this:
shell>./configure --with-tcp-port=
port_number
\--with-unix-socket-path=
file_name
\--prefix=/usr/local/mysql-5.1.47
Here, port_number
and
file_name
must be different from the
default TCP/IP port number and Unix socket file path name, and the
--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.
Note that if you specify localhost
as a host
name, mysqladmin defaults to using a Unix
socket file connection rather than TCP/IP. You can explicitly
specify the connection protocol to use by using the
--protocol={TCP|SOCKET|PIPE|MEMORY}
option.
You don't have to 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
To start a second server, provide different
--socket
and
--port
option values, and pass a
--datadir=
option to mysqld_safe so that the server uses a
different data directory.
path
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>mysql_install_db --user=mysql
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 2.14, “Environment Variables”, includes a list of other environment variables you can use to affect mysqld.
For automatic server execution, the startup script that is executed at boot time should execute the following command once for each server with an appropriate option file path for each command:
shell> mysqld_safe --defaults-file=file_name
Each option file should contain option values specific to a given server.
On Unix, the mysqld_multi script is another way to start multiple servers. See Section 4.3.4, “mysqld_multi — Manage Multiple MySQL Servers”.
User Comments
If you already have a two separate instances of MySQL installed, you can just specify the port and socket in the server specific my.cnf file located in the data directory. See the section on 'Using Option Files' for more information.
A quick way of setting up one MySQL instance without manually fiddling with the configuration is the MySQL Sandbox
(https://launchpad.net/mysql-sandbox)
Beware when trying to install another MySQL on Linux where there's already one in place: suppose that the existing one is installed by the OS, with an /etc/my.cnf file. When attempting to run ./scripts/mysql_install_db for another installation in a completely different folder you need to explicitely pass the --basedir and/or --datadir to this script, otherwise it'll take the values from the /etc/my.cnf file!
Another easy way I found was:
Create a duplicate of /etc/my.cnf as /etc/my2.cnf with different values for port, pid, datadir, sock, etc.. and start mysql as "mysqld_safe --defaults-file=/etc/my2.cnf"
You can connect on localhost with --host=127.0.0.1 --port=xxxx
You can connect from remote host with --host=x.x.x.x --port=xxxx
When I started the server with mysqld_safe using the --defaults-file parameter, the server was showing the wrong version. It was showing the version of my other older server. So to fix this I had to create a small wrapper script:
#!/bin/sh
export PATH=/usr/local/mysql51/bin:${PATH}
./mysqld_safe --defaults-file=/etc/my51.cnf &
exit $?
where "/usr/local/mysql51" is the location of the new server I installed.
Add your own comment.