Score:0

MySQL Replication Master => Slave => Master

cn flag

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!

us flag
Rob
You should probably check https://dev.mysql.com/doc/refman/5.7/en/replication-solutions-switch.html (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).
ua flag
Sounds like you missed a detail of "dual-master". `SHOW REPLICA STATUS;` and `SHOW MASTER STATUS;` on both servers.
mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.