Running Two MySQL instance in One Linux Machine

For running second MySQL instance, we need to follow few steps

Create MySQL instance setup environment

mkdir /var/lib/mysql2
chown -R mysql:mysql /var/lib/mysql2/
mkdir /var/log/mysql2
chown -R mysql.mysql /var/log/mysql2

Copy MySQL configuration file, sometime configuration file (/etc/my.cnf) are not present. We need to copy files from /usr/share/doc/mysql-server<version> (/usr/share/doc/mysql-server-5.0.45) directory.

[root@srv110 mysql-server-5.0.45]# pwd
/usr/share/doc/mysql-server-5.0.45
[root@srv110 mysql-server-5.0.45]# cp my-medium.cnf /etc/my.cnf

Changes (copy) it for new instance.

cp /etc/my.cnf /etc/my_1.cnf
sed -i 's/3306/3307/g' my_1.cnf
sed -i 's/mysqld.sock/mysqld_1.sock/g' my_1.cnf
sed -i 's/mysqld.pid/mysqld_1.pid/g' my_1.cnf
sed -i 's/var/lib/mysql/var/lib/mysql_1/g' my_1.cnf
sed -i 's/var/log/mysql/var/log/mysql_1/g' my_1.cnf

Initialize the default database

mysql_install_db --user=mysql --datadir=/var/lib/mysql2/

Start the second MySQL instance

mysqld_safe --defaults-file=/etc/my_1.cnf

Now we can see both instance

[root@srv110 ~]# ps -ef | grep mysql
root     24065     1  0 16:31 pts/2    00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid
mysql    24137 24065  0 16:31 pts/2    00:00:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --port=3306 --socket=/var/lib/mysql/mysql.sock
root     24919 14565  0 16:40 pts/2    00:00:00 /bin/sh /usr/bin/mysqld_safe --defaults-file=/etc/my_1.cnf
mysql    24953 24919  0 16:40 pts/2    00:00:00 /usr/libexec/mysqld --defaults-file=/etc/my_1.cnf --basedir=/usr --datadir=/var/lib/mysql2 --user=mysql --pid-file=/var/run/mysqld/mysqld2.pid --skip-external-locking --port=3307 --socket=/var/lib/mysql2/mysql.sock