2

I'm running MySQL 5.5.31 and I have a master/slave setup. I accidentally ran a mysql UPDATE on the slave, a simple one-row update like:

UPDATE thetable SET somefield = 'asdf' WHERE id = 1234

I realized my mistake quickly and then ran the same exact statement on master. I probably should have looked into possible issues after the update to slave before the update to master, but I wasn't thinking clearly, so I don't know what the state was like between these operations.

Anyway, after that, worried that I may have caused a sync problem, I did a SHOW SLAVE STATUS and the replication seemed to be working fine, fully caught up with master, and with no error messages. I also did a CHECKSUM TABLE thetable on both master and slave, and the value returned was identical.

So everything seems to be running fine, but I want to avoid running into any potential problems. Given what I described, should everything be okay here on out? Is something about this resulting state going to pop up and bite me? In general, if you make an update to slave followed immediately by an identical update to master, will sync problems be avoided?

migrated from serverfault.com Jul 3 '13 at 5:26

This question came from our site for system and network administrators.

1

Running SHOW SLAVE STATUS is not the way to check if you have a sync problem. It only tells you if you ran into the sync problem in a certain way. Your data can be way out of sync with replication still running happily. Checking the checksum is the more proper way indeed, and can be done automatically for you with percona table checksum (http://www.percona.com/doc/percona-toolkit/2.2/pt-table-checksum.html), formerly called maatkit.

  • Thanks, I didn't know that about SHOW SLAVE STATUS, that's good to know. So if the checksums come out equivalent is it safe to assume everything's good? – Ben Lee Jul 3 '13 at 15:16
  • Yeah, entire tables or even databases could be missing, but if there are no writes replicated to them, SHOW SLAVE STATUS would be none the wiser and happily continue. – arjarj Jul 4 '13 at 5:49
2

Since the end result on both master and slave was identical, I doubt you'll have any real issues. Though it's definitely best to avoid this whenever possible, since your next query might not be so benign...

2

Assuming that before the UPDATE statements replication has been working correctly and the data was the same on both servers, the UPDATE you ran on master has replicated to the salve and overwritten the UPDATE you ran earlier. Therefore the data should be the same on both servers. The best way to see if that is so is to use pt-table-checksum from percona-toolkit as stated earlier.

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.