GNU Info

(mysql.info)Multiple servers

---

Prev: Installing many servers Up: Configuring MySQL

---

Enter node , (file) or (file)node

Running Multiple MySQL Servers on the Same Machine
--------------------------------------------------

There are circumstances when you might want to run multiple servers on
the same machine.  For example, you might want to test a new MySQL
release while leaving your existing production setup undisturbed.  Or
you might be an Internet service provider that wants to provide
independent MySQL installations for different customers.

If you want to run multiple servers, the easiest way is to compile the
servers with different TCP/IP ports and socket files so they are not
both listening to the same TCP/IP port or socket file. Note:
`mysqld_multi'.

Assume an existing server is configured for the default port number and
socket file.  Then configure the new server with a `configure' command
something like this:

     shell> ./configure  --with-tcp-port=port_number \
                  --with-unix-socket-path=file_name \
                  --prefix=/usr/local/mysql-3.22.9

Here `port_number' and `file_name' should be different than the default
port number and socket file pathname, and the `--prefix' value should
specify an installation directory different than the one under which
the existing MySQL installation is located.

You can check the socket used by any currently executing MySQL server
with this command:

     shell> mysqladmin -h hostname --port=port_number variables

Note that if you specify "`localhost'" as a hostname, `mysqladmin' will
default to using Unix sockets instead of TCP/IP.

If you have a MySQL server running on the port you used, you will get a
list of some of the most important configurable variables in MySQL,
including the socket name.

You don't have to recompile a new MySQL server just to start with a
different port and socket.  You can change the port and socket to be
used by specifying them at run time as options to `safe_mysqld':

     shell> /path/to/safe_mysqld --socket=file_name --port=port_number

`mysqld_multi' can also take `safe_mysqld' (or `mysqld') as an argument
and pass the options from a configuration file to `safe_mysqld' and
further to `mysqld'.

If you run the new server on the same database directory as another
server with logging enabled, you should also specify the name of the log
files to `safe_mysqld' with `--log', `--log-update', or
`--log-slow-queries'.  Otherwise, both servers may be trying to write
to the same log file.

*WARNING*: Normally you should never have two servers that update data
in the same database!  If your OS doesn't support fault-free system
locking, this may lead to unpleasant surprises!

If you want to use another database directory for the second server, you
can use the `--datadir=path' option to `safe_mysqld'.

*NOTE* also that starting several MySQL servers (`mysqlds') in
different machines and letting them access one data directory over
`NFS' is generally a *BAD IDEA*! The problem is that the `NFS' will
become the bottleneck with the speed. It is not meant for such use. And
last but not least, you would still have to come up with a solution how
to make sure that two or more `mysqlds' are not interfering with each
other. At the moment there is no platform that would 100% reliable do
the file locking (`lockd' daemon usually) in every situation. Yet there
would be one more possible risk with `NFS'; it would make the work even
more complicated for `lockd' daemon to handle. So make it easy for your
self and forget about the idea. The working solution is to have one
computer with an operating system that efficiently handles threads and
have several CPUs in it.

When you want to connect to a MySQL server that is running with a
different port than the port that is compiled into your client, you can
use one of the following methods:

   * Start the client with `--host 'hostname' --port=port_number' to
     connect with TCP/IP, or `[--host localhost] --socket=file_name' to
     connect via a Unix socket.

   * In your C or Perl programs, you can give the port or socket
     arguments when connecting to the MySQL server.

   * If your are using the Perl `DBD::mysql' module you can read the
     options from the MySQL option files.  Note: Option files.

          $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);

   * Set the `MYSQL_UNIX_PORT' and `MYSQL_TCP_PORT' environment
     variables to point to the Unix socket and TCP/IP port before you
     start your clients.  If you normally use a specific socket or
     port, you should place commands to set these environment variables
     in your `.login' file.  Note: Environment variables.

   * Specify the default socket and TCP/IP port in the `.my.cnf' file
     in your home directory. Note: Option files.