How to run multiple MySQL instances on Unix

There are a few ways to run multiple MySQL instances on the same Unix machine.

1. compile a MySQL server with a different TCP/IP port and socket file

 I don't really like it because it doesn't need to be that complicated. I'd like to have more control on the application level.

 2. use the same MySQL server and specify different TCP/IP port and socket file using command line option

For eaxmple: mysqld_safe --socket=file_name --port=port_number --datadir=new_path

I don't like it either because too many options in command line is not good for management.

3.  use mysqld_multi to manage multiple MySQL servers

This is my favour. You can use one my.cnf to specify different options of your MySQL instance and use command mysqld_multi to start/stop a single instance. It is much easier for you to manage your multiple servers.

Edit your cnf file as follows

[mysqld_multi]
mysqld = /usr/local/bin/mysqld_safe
mysqladmin = /usr/local/bin/mysqladmin

[mysqld1]
.
.
port=3306
datadir=/home/mysql-data-1
.
.

[mysqld2]
.
.
port=3307
datadir=/home/mysql-data-2
.
.

Now you are ready to start your multiple MySQL database servers

mysqld_multi start 1
mysqld_multi start 2


To log into your MySQL database server

mysql -uusername -p -h127.0.0.1 -P3306
mysql -uusername -p -h127.0.0.1 -P3307


To shutdown a MySQL database server

mysqladmin -uusername -p -h127.0.0.1 -P3306
mysqladmin -uusername -p -h127.0.0.1 -P3307


If you are having problems starting your new MySQL database server, carefully analyse your mysqld log files. If you see errors like "mysql.host does not exist", you may need to initialize the database in the new server and it should be good to go

mysql_install_db --user=mysql --datadir=new_db_directory
chown -R mysql:mysql new_db_directory