Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
1  
For failover you should really be using master-master. –  John Gardeniers 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

2 Answers 2

up vote 3 down vote accepted

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

share|improve this answer
    
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.
  • START the MASTER AND SLAVE.

Data should be good to go.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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