In some cases, you might want to run multiple mysqld servers on the same machine. You might want to test a new MySQL release while leaving your existing production setup undisturbed. Or you might want to give different users access to different mysqld servers that they manage themselves. (For example, you might be an Internet Service Provider that wants to provide independent MySQL installations for different customers.)
To run multiple servers on a single machine, each server must have unique values for several operating parameters. These can be set on the command line or in option files. See Section 4.3, “Specifying Program Options”.
At least the following options must be different for each server:
--port controls the port number for TCP/IP
--socket controls the Unix socket file path
on Unix and the name of the named pipe on Windows. On Windows,
it is necessary to specify distinct pipe names only for those
servers that support named-pipe connections.
This option currently is used only on Windows. It designates the shared-memory name used by a Windows server to allow clients to connect via shared memory. It is necessary to specify distinct shared-memory names only for those servers that support shared-memory connections.
This option is used only on Unix. It indicates the pathname of the file in which the server writes its process ID.
If you use the following log file options, they must be different for each server:
Section 5.12.5, “Server Log Maintenance”, discusses the log file options further.
For better performance, you can specify the following options differently for each server, to spread the load between several physical disks:
Having different temporary directories is also recommended to make it easier to determine which MySQL server created any given temporary file.
With very limited exceptions, each server should use a different
data directory, which is specified using the
Warning: Normally, you should
never have two servers that update data in the same databases.
This may lead to unpleasant surprises if your operating system
does not support fault-free system locking. If (despite this
warning) you run multiple servers using the same data directory
and they have logging enabled, you must use the appropriate
options to specify log filenames that are unique to each server.
Otherwise, the servers try to log to the same files. Please note
that this kind of setup only works with
MERGE tables, and not with any of the other
The warning against sharing a data directory among servers also applies in an NFS environment. Allowing multiple MySQL servers to access a common data directory over NFS is a very bad idea.
The primary problem is that NFS is the speed bottleneck. It is not meant for such use.
Another risk with NFS is that you must devise a way to ensure
that two or more servers do not interfere with each other.
Usually NFS file locking is handled by the
lockd daemon, but at the moment there is no
platform that performs locking 100% reliably in every
Make it easy for yourself: Forget about sharing a data directory among servers over NFS. A better solution is to have one computer that contains several CPUs and use an operating system that handles threads efficiently.
If you have multiple MySQL installations in different locations,
you can specify the base installation directory for each server
to cause each server to use a different data directory, log files,
and PID file. (The defaults for all these values are determined
relative to the base directory). In that case, the only other
options you need to specify are the
--port options. For example, suppose that you
install different versions of MySQL using
file binary distributions. These install in different locations,
so you can start the server for each installation using the
command bin/mysqld_safe under its corresponding
base directory. mysqld_safe determines the
--basedir option to pass to
mysqld, and you need specify only the
--port options to
As discussed in the following sections, it is possible to start
additional servers by setting environment variables or by
specifying appropriate command-line options. 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
You can run multiple servers on Windows by starting them manually from the command line, each with appropriate operating parameters. On Windows NT-based systems, you also have the option of installing several servers as Windows services and running them that way. General instructions for running MySQL servers from the command line or as services are given in Section 2.3, “Installing MySQL on Windows”. This section describes how to make sure that you start each server with different values for those startup options that must be unique per server, such as the data directory. These options are described in Section 5.13, “Running Multiple MySQL Servers on the Same Machine”.
To start multiple servers manually from the command line, 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 filename
--defaults-file option when you run
Suppose that you want to run mysqld on port
3307 with a data directory of
and mysqld-max on port 3308 with a data
C:\mydata2. (To do this,
make sure that before you start the servers, each data
directory exists and has its own copy of the
mysql database that contains the grant
tables.) Then create two option files. For example, create one
C:\my-opts1.cnf that looks
[mysqld] datadir = C:/mydata1 port = 3307
Create a second file named
C:\my-opts2.cnf that looks like this:
[mysqld] datadir = C:/mydata2 port = 3308
Then start each server with its own option file:
On NT, 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.
To shut down the servers, you must connect to each using the appropriate port number:
C:\mysql\bin\mysqladmin --port=3307 shutdownC:\>
C:\mysql\bin\mysqladmin --port=3308 shutdown
Servers configured as just described allow clients to connect
over TCP/IP. If your version of Windows supports named pipes
and you also want to allow named-pipe connections, use the
mysqld-max-nt servers and specify options
that enable the named pipe and specify its name. Each server
that supports named-pipe connections must use a unique pipe
name. For example, the
file might be written like this:
[mysqld] datadir = C:/mydata1 port = 3307 enable-named-pipe socket = mypipe1
Then start the server this way:
C:\my-opts2.cnf similarly for use
by the second server.
A similar procedure applies for servers that you want to
support shared-memory connections. Enable such connections
--shared-memory option and specify a
unique shared-memory name for each server with the
On NT-based systems, a MySQL server can run as a Windows service. The procedures for installing, controlling, and removing a single MySQL service are described in Section 2.3.11, “Starting MySQL as a Windows Service”.
You can also install multiple MySQL servers as services. In this case, you must make sure that each server uses a different service name in addition to all the other parameters that must be unique for each server.
For the following instructions, assume that you want to run
the mysqld-nt server from two different
versions of MySQL that are installed at
(This might be the case if you're running 4.1.8 as your
production server, but also want to conduct tests using
The following principles apply when installing a MySQL service
If you specify no service name, the server uses the
default service name of
MySQL and the
server reads options from the
group in the standard option files.
If you specify a service name after the
--install option, the server ignores the
[mysqld] option group and instead reads
options from the group that has the same name as the
service. The server reads options from the standard option
If you specify a
after the service name, the server ignores the standard
option files and reads options only from the
[mysqld] group of the named file.
Note: Before MySQL 4.0.17,
only a server installed using the default service name
MySQL) or one installed explicitly with a
service name of mysqld read the
[mysqld] group in the standard option
files. As of 4.0.17, all servers read the
[mysqld] group if they read the standard
option files, even if they are installed using another service
name. This allows you to use the
group for options that should be used by all MySQL services,
and an option group named after each service for use by the
server installed with that service name.
Based on the preceding information, you have several ways to set up multiple services. The following instructions describe some examples. Before trying any of them, be sure that you shut down and remove any existing MySQL services first.
Approach 1: Specify the
options for all services in one of the standard option
files. To do this, use a different service name for each
server. Suppose that you want to run the 4.1.8
mysqld-nt using the service name of
mysqld1 and the 5.0.23
mysqld-nt using the service name
mysqld2. In this case, you can use the
[mysqld1] group for 4.1.8 and the
[mysqld2] group for 5.0.23.
For example, you can set up
# options for mysqld1 service [mysqld1] basedir = C:/mysql-4.1.8 port = 3307 enable-named-pipe socket = mypipe1 # options for mysqld2 service [mysqld2] basedir = C:/mysql-5.0.23 port = 3308 enable-named-pipe socket = mypipe2
Install the services as follows, using the full server pathnames to ensure that Windows registers the correct executable program for each service:
C:\mysql-4.1.8\bin\mysqld-nt --install mysqld1C:\>
C:\mysql-5.0.23\bin\mysqld-nt --install mysqld2
To start the services, use the services manager, or use NET START with the appropriate service names:
NET START mysqld1C:\>
NET START mysqld2
To stop the services, use the services manager, or use NET STOP with the appropriate service names:
NET STOP mysqld1C:\>
NET STOP mysqld2
Approach 2: Specify
options for each server in separate files and use
--defaults-file when you install the
services to tell each server what file to use. In this
case, each file should list options using a
With this approach, to specify options for the 4.1.8
mysqld-nt, create a file
C:\my-opts1.cnf that looks like this:
[mysqld] basedir = C:/mysql-4.1.8 port = 3307 enable-named-pipe socket = mypipe1
For the 5.0.23 mysqld-nt,
create a file
looks like this:
[mysqld] basedir = C:/mysql-5.0.23 port = 3308 enable-named-pipe socket = mypipe2
Install the services as follows (enter each command on a single line):
C:\mysql-4.1.8\bin\mysqld-nt --install mysqld1
C:\mysql-5.0.23\bin\mysqld-nt --install mysqld2
To use a
--defaults-file option when you
install a MySQL server as a service, you must precede the
option with the service name.
After installing the services, start and stop them the same way as in the preceding example.
To remove multiple services, use mysqld
--remove for each one, specifying a service name
--remove option. If the service
name is the default (
MySQL), you can omit
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 4.1.8 server is configured for the
default TCP/IP port number (3306) and Unix socket file
/tmp/mysql.sock). To configure a new
5.0.23 server to have different operating parameters,
use a configure command something like this:
file_name must be different from the
default TCP/IP port number and Unix socket file pathname, and
--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 filename:
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
hostname, mysqladmin defaults to using a Unix
socket file connection rather than TCP/IP. From MySQL 4.1
onward, you can explicitly specify the connection protocol to
use by using the
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:
To start a second server, provide different
values, and pass a
option to mysqld_safe so that the server uses
a different data directory.
Another way to achieve a similar effect is to use environment variables to set the Unix socket filename and TCP/IP port number:
export MYSQL_UNIX_PORT MYSQL_TCP_PORTshell>
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.
Appendix F, 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:
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 5.4.3, “mysqld_multi — Manage Multiple MySQL Servers”.
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
connect via TCP/IP to a remote server, with
connect via TCP/IP to a local server, or with
connect to a local server via a Unix socket file or a
Windows named pipe.
As of MySQL 4.1, start the client with
--protocol=tcp to connect via TCP/IP,
--protocol=socket to connect via a Unix
--protocol=pipe to connect via
a named pipe, or
connect via shared memory. For TCP/IP connections, you may
also need to specify
--port options. For the other types of
connections, you may need to specify a
--socket option to specify a Unix socket
file or Windows named-pipe name, or a
--shared-memory-base-name option to specify
the shared-memory name. Shared-memory connections are
supported only on Windows.
On Unix, set the
MYSQL_TCP_PORT environment variables to
point to the Unix socket file and TCP/IP port number before
you start your clients. If you normally use a specific
socket file or port number, you can place commands to set
these environment variables in your
.login file so that they apply each
time you log in. See
Appendix F, Environment Variables.
Specify the default Unix socket file and TCP/IP port number
[client] group of an option file.
For example, you can use
Windows, or the
.my.cnf file in your
home directory on Unix. See Section 4.3.2, “Using Option Files”.
In a C program, you can specify the socket file or port
number arguments in the
mysql_real_connect() call. You can also
have the program read option files by calling
Section 22.2.3, “C API Function Descriptions”.
If you are using the Perl
module, you can read options from MySQL option files. For
$dsn = "DBI:mysql:test;mysql_read_default_group=client;" . "mysql_read_default_file=/usr/local/mysql/data/my.cnf"; $dbh = DBI->connect($dsn, $user, $password);
Other programming interfaces may provide similar capabilities for reading option files.