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?

  • 1
    For failover you should really be using master-master. Oct 27 '10 at 21:20
  • Master-Master replication is only good for pain. Don't do it.
    – tex
    Dec 17 '10 at 20:18
  • You should use read_only option on all slaves to prefent unplanned data modification. When read_only option is set, only users with SUPER privilege can modify data.
    – sumar
    Dec 17 '10 at 22:48

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

  • 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

Alternative solution:

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

Data should be good to go.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.