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

databaseMySQLmysql-replicationreplication

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?

Best Answer

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

Related Topic