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…