Running Multiple Instances of MySQL on Centos

Today I needed to setup multiple instances of mysql on centos5. To be honest there are numerous ways of doing the same. I have used very basic method of doing the same.

Before you follow these steps:

1. Setup one MySQL configuration file per instance e.g. /etc/my_1.cnf , /etc/my_2.cnf

2. Setup separate data directory for each instance

3. Different port number for each instance

4. Different PID file for each instance.

MySQL Configuration for 1st instance: /etc/my_1.cnf

#########################################

[mysqld]

datadir=/var/lib/mysql_1

port=3306

[mysqld_safe]

log-error=/var/log/mysqld_1.log

pid-file=/var/run/mysqld/mysqld_1.pid

MySQL Configuration for 2nd instance: /etc/my_2.cnf

#########################################

[mysqld]

datadir=/var/lib/mysql_2

port=3307

[mysqld_safe]

log-error=/var/log/mysqld_2.log

pid-file=/var/run/mysqld/mysqld_2.pid

Initializing and starting MySQL:

-- initializing database for 1st instance

shell# mysql_install_db --user=mysql --datadir=/var/lib/mysql_1

-- initializing database for 2nd instance

shell# mysql_install_db --user=mysql --datadir=/var/lib/mysql_2

-- Starting 1st instance

shell# mysqld_safe --user=mysql --defaults-file=/etc/my_1.cnf &

-- Starting 2nd instance

shell# mysqld_safe --user=mysql --defaults-file=/etc/my_2.cnf &