MYSQL: Multiple MySQL instance in One Linux Machine

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