Today’s sysadmin tip: Using a mysql replicate for backup: Remember to set log_slave_updates

The other day I was setting up a mysql slave in a an application where we needed to daisy-chain one slave from another. With just adding the slave the standard way, the first slave kept happily replicating, but we got no updates on the second slave. After a bit of debugging, we found log_slave_updates in the documentation. This makes the slave actually binlog changes from the master. Without this, only changes done one the slave is added to the binlog. Adding log_slave_updates to my.cnf on the first slave, the second in the chain started replicating as well.

So far, so good. But considering using replication for backup purposes, which is something I guess is quite common, the binlog of changes to the database is necessary to be able to do a point in time recovery. To get that binlog, you have to add log_slave_updates. Unless you have backup of the binlog files from the master, of course, but the point was using the replicate for backup, wasn’t it?

If you are unsure, compare the size of the binlog files on the master and the backup replicate slave.

Tags: ,

Leave a Reply