MySQL Replication

A few thoughts on MySQL replication:

  • Since a “READ DATA FROM MASTER” will issue locks until the slave is up to sync, it’s a horrible way to bring up a new slave to an existing setup. What’s not written about a lot, but isn’t really a big secret, is that you should use the latest backup/dump of the database to get the slave almost up to speed, and make sure you log its log position (from a SHOW SLAVE STATUS and SHOW MASTER STATUS). From there, you can just tell MySQL the binary log and the log position to resume at. In my case this means catching up on about 45,000 seconds, but it’s better than locking the database until it’s done.
  • MySQL replicates temporary tables. I didn’t believe this at first, but it’s true. This means that replicating to a machine used for lots of data mining that creates temporary tables from SELECTs that take 30 minutes to complete is a bad idea. As best as I can see, there’s no, “Don’t copy over temporary tables, silly” option, either.
  • Running STOP SLAVE will seemingly wait for the current query to finish. (You could kill it, but that’d be bad…)
  • If, in the process of bringing up a replicant for the first time, it’s trying to create temporary tables based on temporary tables…. You can tell it to ignore “table not found” errors by putting slave-skip-errors = 1146 in your my.cnf: 1146 is the error for missing tables. This will keep replication going. Risky, but if you restored from a recent, complete backup, you’ll just skip creating temporary tables…

2 thoughts on “MySQL Replication

  1. MySQL replicates statements, not data, so temporary tables need to be replicated in case there’s a future statement dependent upon them. If you coordinate the names of the temporary tables, you should be able to exclude them from replication with a wildcard.

  2. Yeah, I eventually realized that — it’s possible (albeit weird) to create a table based on the results from a temporary table or something. Still, I wish there were an easy way to skip this… The slave that makes nightly snapshots is also the node that we do all of our data-mining on, and the hassle of changing all of that exceeds the hassle of letting MySQL spend a few hours creating temporary tables and then deleting them.

    Though it’s sort of a bit of an obscure thing that I might bring up if I ever (for some bizarre reason?) find myself interviewing candidates to be a DBA. 😉

Leave a Reply

Your email address will not be published. Required fields are marked *