Setting-up second MySQL instance & replication on Linux in 10 steps

This post will help you configure second MySQL instance on same machine and also setup replication between them. We often see requirement for having multiple MySQL instances on same machine for restore, testing or backup purposes.

This is a quick setup guide of 10-steps to install and configure MySQL instance on 3307 port, and make it slave from MySQL running on port 3306.

This post assumes you already have a MySQL instance running on port 3306.

1. Make datadir, tmpdir and correct permissions:

 mkdir -p /var/lib/mysql3307/tmp/
 chown -R mysql:mysql /var/lib/mysql3307

2. Create config

 cp /etc/my.cnf /etc/my3307.cnf (Correct relevant parameters)
# Usually we will set following parameters:

    datadir=/var/lib/mysql3307
    socket=/var/run/mysql/mysqld3307.sock
    server-id=<different-than-master>
    port=3307
    pid-file=/var/lib/mysql3307/mysqld3307.pid
    log-error=/var/lib/mysql3307/mysqld3307.err
    tmpdir=/var/lib/mysql3307/tmp/

3. Install basic auth system / dbs:

mysql_install_db --user=mysql --basedir=/usr --datadir=/var/lib/mysql3307 --defaults-file=/etc/my3307.cnf

4. Start mysql

 mysqld_safe --defaults-file=/etc/my3307.cnf --user=mysql &

5. Take fulldump from 3306 instance:

 mysqldump -uroot -p --all-databases --master-data=2 | gzip > backup.sql.gz

6. Load it to new 3307 instance

 zcat backup.sql.gz  | MySQL -uroot -p --port=3307 --socket=/var/run/mysql/mysqld3307.sock

7. Grab binary log co-ordinates from backup:

 zcat backup.sql.gz | head -30 | grep -i change

8. Create replication user on master (3306 MySQL instance):

 grant replication slave on *.* to 'repluser'@'%' identified by 'replpass';

9. Run change master to on 3307 slave using binlogs take from step #7:

#You may connect to MySQL as:
$ mysql  -uUSER -p --socket=/path/to/3307/socket


mysql>CHANGE MASTER TO MASTER_HOST='localhost', MASTER_USER='repluser', MASTER_PASSWORD='replpass', MASTER_PORT=3306, MASTER_LOG_FILE='XXXX', MASTER_LOG_POS=XXXX;

10. Start slave on 3307.

start slave;

Hope this helps.