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.