It’s a rather common thing to use a slave DB for backup purposes in a MySQL shop. This ensures that the application is always available since no table locks will be acquired for taking the backup. This is usually not a big problem on less busy sites and most times goes by unnoticed. However, on slightly more heavily trafficked sites, a table lock on a sizeable table while it’s being dumped can affect more than a handful of users. So while having a slave or two solves that problem, it can also bring another advantage;
Your DB backup never have to be more than x amount of minutes old! By using the superb mysql-slave-delay tool from maatkit, you can delay the slave with a certain amount of minutes. That way you can always keep your slave, say, 30 minutes behind. So if/when disaster strike and someone/something runs DROP TABLE or DELETE FROM on the wrong table (and you notice it within 30 minuets), you can just stop the slave thread altogether on the slave machine, and you have a snapshot of your database 30 minutes ago!
Simply download, extract and run: mysql-slave-delay –daemonize -d 30m u=root,p=notbloodylikely,h=127.0.0.1
on the slave machine. There are a few different options, so have a look at mysql-slave-delay –help.
But that’s the most basic command line.
The down side of this is that you lose the last 30 minutes of data in a failover situation where the master machine for some reason goes down, and the crisis does not have anything to do with the data set being damaged. To remedy this, you could either run two mysql processes on the same machine provided that the resources allow so, or if your budget allows, a third machine.
If this is a bit overkill for you and you want some efficient backup system, have a look at mylvmbackup This – of course – requires that the data directories are housed on an LVM volume. More about that in another post another time maybe.
