Category Archives: Replication

How to keep a passive-master MySQL database warm

When you have a passive master (in a master-master setup), there is usually no queries going to it. If there are no queries it will not have its buffers/caches at least semi-ready for a production workload. Maatkit (love this toolkit) has a nice little tool to help do this, mk-query-digest. Now this is only 1 of many uses for this tool. So visit the mk-query-digest doc to find out the rest of its many uses.
Continue reading

Taking a hot backup of a MySQL database with XtraBackup

A hot backup means you don’t have to take the database offline or stop sending traffic to it. This is especially useful for creating new replication slaves. A hot backup takes minutes vs the hours a dump/import would take. Ideally, all the tables in the database need to be InnoDB; however, Percona has adapted a script (innobackupex) to also backup MyISAM tables (if there are MyISAM tables then you probably should stop sending traffic to it as those require table locks). What makes XtraBackup so nice (besides being free) is that it logs C_UD statements to applied during restoration and it can do incremental backups as well.
Continue reading

Verifying replication slave data

I read about this wonderful toolkit called Maatkit in an equally wonderful book High Performance MySQL. I highly recommend both to anyone having to deal with MySQL. I’ll definitely do more posts about Maatkit and its tools as I use them.

I’m going to be up-front now, this is not the best method to run this. In the mk-table-checksum documentation it details the use of the faster/more accurate FNV1A_64 hash UDF; I would recommend using it over MD5 or SHA1. Also, if all your slaves are accessible from the machine running mk-table-checksum then I also recommend the –replicate-check flag instead of manually running the SQL.
Continue reading

Recovering from catastrophic failure of the replication master

This applies to a replication architecture like the one detailed here Improving Replication Performance.

My situation was Master2 (which runs the blackhole engine) ran out of disk space and the current binlog was lost. After freeing up disk space on Master2 and restarting its replication it was now missing hours worth of binlog that its slaves are expecting to find. So to remedy this, I pointed the slaves directly back to Master1 to slurp up the missing binlog and then switch them back to Master2 once they were caught up.

Continue reading