MySQL replication: Getting the master in sync with the slave again

Question

I have two computers on the same network, in a master-slave replication setup.

Simply to test failover, I inserted some data straight into the slave database. So now the slave database contains more information than the master database.

Now I want to get the master in sync with the slave again to get back to the real master-slave setup. How would I do that?

Answer 1

Instead of calling them "master" and "slave," let's outline them this way:

• HostA: The original "master"
• HostB: The original "slave"

When you inserted data directly into HostB, HostA fell behind the binary oplog. You'll first need to overcome this by making HostA a slave of HostB by using the CHANGE MASTER TO command ( http://dev.mysql.com/doc/refman/5.0/en/change-master-to.html ). This should cause HostA to sync up with HostB.

Once HostA has caught up with HostB, you'll want to reset HostA's binary log and promote it to master.

1. On HostA: RESET MASTER;
2. On HostB: CHANGE MASTER TO ...

For more information, take a look at http://dev.mysql.com/doc/refman/5.0/en/replication-solutions-switch.html#figure_replication-redundancy-after

Answer 2

Alternative solution:

• Stop the SLAVE and MASTER.
• Run a program like Navicat to Data Sync from SLAVE to MASTER.
• Reset the SLAVE AND MASTER.
• START the MASTER AND SLAVE.

Data should be good to go.