Home arrow MySQL arrow Advanced MySQL Database Administration

Advanced MySQL Database Administration

If you need to administer MySQL, this article gets you off to a good start. In this section, we discuss running multiple MySQL servers on the same machine, and the MySQL Query cache. The final installment of a multi-part series, it is excerpted from chapter four of the book MySQL Administrator's Guide, written by Paul Dubois (Sams; ISBN: 0672326345).

  1. Advanced MySQL Database Administration
  2. Starting Multiple Windows Servers as Services
  3. 4.9.2 Running Multiple Servers on Unix
  4. 4.10 The MySQL Query Cache
  5. 4.10.4 Query Cache Status and Maintenance
By: Sams Publishing
Rating: starstarstarstarstar / 18
July 06, 2006

print this article



4.9 Running Multiple MySQL Servers on the Same Machine

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 may 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 3.3, "Specifying Program Options."

At least the following options must be different for each server:

  • --port=port_num

    --port controls the port number for TCP/IP connections.

  • --socket=path

    --socket controls the Unix socket file path on Unix and the name of the named pipe on Windows. On Windows, it's necessary to specify distinct pipe names only for those servers that support named pipe connections.

  • --shared-memory-base-name=name

    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. This option is new in MySQL 4.1.

  • --pid-file=path

    This option is used only on Unix. It indicates the name 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:

  • --log=path

  • --log-bin=path

  • --log-update=path

  • --log-error=path

  • --log-isam=path

  • --bdb-logdir=path

Log file options are described in Section 4.8.6, "Log File Maintenance."

If you want more performance, you can also specify the following options differently for each server, to spread the load between several physical disks:

  • --tmpdir=path

  • --bdb-tmpdir=path

Having different temporary directories is also recommended, to make it easier to determine which MySQL server created any given temporary file.

Generally, each server should also use a different data directory, which is specified using the --datadir=path option.

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 doesn't 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 file names that are unique to each server. Otherwise, the servers will try to log to the same files.

This 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 bad idea!

  • The primary problem is that NFS will become the speed bottleneck. It is not meant for such use.

  • Another risk with NFS is that you will have to come up with a way to make sure 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 will perform locking 100% reliably in every situation.

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, normally you can specify the base installation directory for each server with the --basedir=path option 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 --socket and --port options. For example, suppose that you install different versions of MySQL using tar file binary distributions. These will 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 will determine the proper --basedir option to pass to mysqld, and you need specify only the --socket and --port options to mysqld_safe. (For versions of MySQL older than 4.0, use safe_mysqld rather than mysqld_safe.)

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 will be more convenient to use option files to specify for each server those option values that must be unique to it.

4.9.1 Running Multiple Servers on Windows

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.2.1, "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 4.9, "Running Multiple MySQL Servers on the Same Machine." Starting Multiple Windows Servers at the Command Line

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's more convenient to place the options in an option file, but it's 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 with a --defaults-file option when you run it.

Suppose that you want to run mysqld on port 3307 with a data directory of C:\mydata1, and mysqld-max on port 3308 with a data directory of 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 file named C:\my-opts1.cnf that looks like this:

datadir = C:/mydata1
port = 3307

Create a second file named C:\my-opts2.cnf that looks like this:

datadir = C:/mydata2
port = 3308

Then start each server with its own option file:

C:\> C:\mysql\bin\mysqld
C:\> C:\mysql\bin\mysqld-max

On NT, each server will start in the foreground (no new prompt appears until the server exits later); you'll need to issue those two commands in separate console windows.

To shut down the servers, you must connect to the appropriate port number:

C:\> C:\mysql\bin\mysqladmin --port=3307 shutdown
C:\> C:\mysql\bin\mysqladmin --port=3308 shutdown

Servers configured as just described will 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-nt or 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 C:\my-opts1.cnf file might be written like this:

datadir = C:/mydata1
port = 3307
socket = mypipe1

Then start the server this way:

C:\> C:\mysql\bin\mysqld-nt

Modify C:\my-opts2.cnf similarly for use by the second server.

>>> More MySQL Articles          >>> More By Sams Publishing

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates


Dev Shed Tutorial Topics: