4

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?

Deniz Dogan
  • 143
  • 1
  • 3

2 Answers2

3

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

Charles Hooper
  • 1,520
  • 9
  • 8
  • This won't work if data was written to HostA meanwhile. – tex Dec 17 '10 at 20:18
  • This won't work, if you have disabled binlog on HostB. The better way is to use mk-table-checksum to check, what tables are out of sync. After check extract modifications form binlog using mysqlbinlog command or use mk-table-sync to synchronize tables. – sumar Dec 17 '10 at 22:46
0

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.

John M
  • 159
  • 1
  • 9