[MySQL]: Running Multiple MySQL Instances on One Machine

Running Multiple MySQL Instances on One Machine

 

In some cases, you might want to run multiple instances of MySQL on a single machine. You might want to test a new MySQL release while leaving an existing production setup undisturbed. Or you might want to give different users access to different mysqld servers that they manage themselves.

 

It is possible to use a different MySQL server binary per instance, or use the same binary for multiple instances, or any combination of the two approaches. For example, you might run a server from MySQL 5.0 and one from MySQL 5.1, to see how different versions handle a given workload. Or you might run multiple instances of the current production version, each managing a different set of databases.

Whether or not you use distinct server binaries, each instance that you run must be configured with unique values for several operating parameters. This eliminates the potential for conflict between instances.

 

12.1.  Setting Up Multiple Data Directories

 

Each MySQL Instance on a machine should have its own data directory. The location is specified using the –datadir=path option.

There are different methods of setting up a data directory for a new instance:

 

• Create a new data directory
• Copy an existing data directory

 

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

 

Create a New Data Directory:

 

With this method, the data directory will be in the same state as when you first install MySQL. It will have the default set of MySQL accounts and no user data.On Unix, initialize the data directory by running mysql_install_db.

 

Copy an Existing Data Directory:

 

With this method, any MySQL accounts or user data present in the data directory are carried over to the new data directory.

 

1. Stop the existing MySQL instance using the data directory. This must be a clean shutdown so that the instance flushes any pending changes to disk.
2. Copy the data directory to the location where the new data directory should be.
3. Copy the my.cnf or my.ini option file used by the existing instance. This serves as a basis for the new instance.
4. Modify the new option file so that any pathnames referring to the original data directory refer to the new data directory. Also, modify any other options that must be unique per instance, such as the TCP/IP port number and the log files.
5. Start the new instance, telling it to use the new option file.

 

12.2.  Running Multiple MySQL Instances on Unix:

 

One way is to run multiple MySQL instances on Unix is to compile different servers with different default TCP/IP ports and Unix socket files so that each one listens 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 server is configured for the default TCP/IP port number (3306) and Unix socket file (/tmp/mysql.sock). To configure a new 5.1.57 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.57

 

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

 

You need not 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=path option to mysqld_safe so that the server uses a different data directory.

 

Alternatively, put the options for each server in a different option file, then start each server using a –defaults-file option that specifies the path to the appropriate option file. For example, if the option files for two server instances are named /usr/local/mysql/my.cnf and /usr/local/mysql/my.cnf2, start the servers like this: command:

 

shell> mysqld_safe –defaults-file=/usr/local/mysql/my.cnf

shell> mysqld_safe –defaults-file=/usr/local/mysql/my.cnf2

 

Using Client Programs in a Multiple-Server Environment:

 

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 –host=host_name –port=port_number to connect using TCP/IP to a remote server, with –host=127.0.0.1 –port=port_number to connect using TCP/IP to a local server, or with –host=localhost –socket=file_name to connect to a local server using a Unix socket file.

Ask Question