I have set up MySQL master-slave replication successfully. But I realized it doesn't work if I would like to simulate the real-world case. Here is my scenario:

  1. Make sure the data have been fully replicated to Salve from Master.
  2. Shutdown the Master node.
  3. Insert some new data(say test-data) into the table in Slave.

Now the master is back to normal. I would like to sync up the test-data back to Master node. Here are 2 methods I tried:

  1. Restart the Master node and reset the Slave node.
  2. Switch over the role of them, change original Slave to Master, original Master to Slave.

Looks like both 2 methods can only sync up the data which are inserted after I bring Master up. But both 2 methods miss test-data which was inserted into the table during Master down. So far I can only think about copying data by mysqldump before I sync two nodes again. Then the data will be consistent between 2 nodes. But it is not very effective. How do I sync up the test-data backup to master in a more effective way?

I am very new to MySQL, any help is appreciated!

  • You should probably check dev.mysql.com/doc/refman/5.7/en/… (or the relevant docs for the version you're using) Also be aware that jargon has moved away from master-slave to source-replica (although command-line switches and SQL commands have not).
    – Rob
    Apr 12, 2022 at 14:58
  • Sounds like you missed a detail of "dual-master". SHOW REPLICA STATUS; and SHOW MASTER STATUS; on both servers.
    – Rick James
    Apr 12, 2022 at 23:46


You must log in to answer this question.

Browse other questions tagged .