Video: Building a MySQL Slave and Keeping it in Sync

Nov 10, 2009 / By Sheeri Cabral

Tags: , , ,

Last night at the Boston MySQL User Group I presented on how to get a consistent snapshot to build a slave, how to use mk-table-checksum to check for differences between masters and slaves on an ongoing basis, and how to use tools such as mk-table-sync and mysqldump to sync the data if there are any discrepancies.

The slides are online at http://technocation.org/files/doc/slave_sync.pdf.

The video can be watched on youtube at http://www.youtube.com/watch?v=Un0wqYKmbWY or directly in your browser with the embedded player below:

5 Responses to “Video: Building a MySQL Slave and Keeping it in Sync”

  • Ken Garland says:

    I find this part of the PDF most interesting:

    Ok, slave is out of sync. What’s next?
    ? 1. Try to use mk-table-sync tool.

    There is no mention of HOW to use it. I’ve read the documentation and it appears that the sync tool is not truly capable of syncing the slave with it’s master but only altering the master to match the slave. The checksums work great and it is correctly reporting the changes I have made. However I’d like to try and get an automation tool that would kick off replication on the master when a slave is out of sync. It appears I may have to write something that does this making use of checksum and mysqldump to get the bin-logs back into position.

  • Ken,

    I’m not sure what you mean — when a slave is out of sync, why would you *start* replication on the *master* then? What would the master be a slave of?

    In general, it’s difficult to automate actual syncing after the data is out of sync. The problem is that you need a bit of human logic to figure out if you should change the master or the slave.

    We don’t actually use mk-table-sync at Pythian, because it hasn’t always worked (it says things are in sync when it’s not).

    mk-table-checksum works, though, and so we use that. If we find that there are differences, we look at the actual rows in the table to see what the differences are, to figure out what the fix should be.

    We have found cases where a script runs against the slave and changes data against the slave (therefore slave would need to be changed).

    We have found cases where a master has the wrong data and the slave has the right data (in cases where the master and slave were switched, so the master became the slave and the slave became the master).

    One time, the problem was that MySQL had different minor versions on the master and slave and rounding was changed in that minor version, so either of the servers had the “right” data — we just had to pick which one we deemed “right”.

  • I really enjoyed the video. However, I’m currently doing some research regarding the mk-tools and I’m a little bit sad that mk-table-sync seems to be a little bit “unstable”? Can you provide a more specific explanation of the problems? Maybe a way to replicate the problems could help solving them?
    However, thanks for the video.

  • Jan,

    We have found that it will say a table is out of sync, we’ll use the mode to get the statement(s) to fix it, run the statements by hand, then run the sync again and it will give the exact same statement(s) to fix it….even though theoretically we just did fix it.

    It may be different now, that was just our experience at the time, and at this point the bug(s) may be fixed.

  • Garen says:

    Sheeri, when you say: “use the mode to get the statement(s) to fix it” … how do you get those statements manually?

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>