How to convert master-slave to master-master replication in MySQL

Recently, I have worked on one of this case where customer wanted to convert master-slave to master-master replication. They wanted to write on one master and keep another master as a hotspare. Many people are using this kind of environment with MySQL replication.

I would like to explain here in very simple steps that how we can do that. You can also follow the same steps if you want to promote only slave as a master in master-slave replication. Sometimes, this process will be helpful if you have master-slave replication and you want to rebuild or upgrade both the servers one by one. In that case, you have to shift traffic from one server to the other, take down the first server, rebuild it and then reverse the process.

I would also like to mention here that  master-master (active-active) replication is quite risky and it can lead to data inconsistency between the servers if writes are enable on both masters at the same time. But still it depends on how we mange it.

Below are the simple steps for how we can do it. Here, we can use one of the best utilities by Percona. pt-table-checksum and pt-table-sync from Percona Toolkit.

1. Check consistency between master and slave with pt-table-checksum utility.
You can get more information here about pt-table-checksum.
http://www.percona.com/doc/percona-toolkit/2.1/pt-table-checksum.html

2. If its not consistent than first make it with pt-table-sync
You can get more information here about pt-table-sync
http://www.percona.com/doc/percona-toolkit/2.1/pt-table-sync.html

3. Enable binary log on slave if its not, check server id should be different for both nodes and set auto_increment_* variables in my.cnf
You can get more information here about auto_increment_* variables.
http://dev.mysql.com/doc/refman/5.1/en/replication-options-master.html

4. As both servers are already consistent, we don’t need to do but still if you want, you can stop writes to the old master with “Flush tables with Read Lock“, run “show full processlist” to make sure,¬† no query is active and note down binary log file and position from old master by running “SHOW MASTER STATUS” command.

5. From slave(new master), use CHANGE MASTER TO command and coordinated from #4/#16 (as per MySQL version) to connect to old master and start slave i.e
CHANGE MASTER TO
MASTER_HOST=’127.0.0.1′,
MASTER_USER=’repl’,
MASTER_PASSWORD=’repl’,
MASTER_PORT=3306,
MASTER_LOG_FILE=’master-bin.001′,
MASTER_LOG_POS=4;

START SLAVE;

6. Check replication is working properly or not by running SHOW SLAVE STATUS on both the servers.

7. If everything is fine than point write traffic to the new master server if you want to upgrade/rebuild servers otherwise, its done..

Leave a Reply