Tuesday, April 29, 2008

backup by slave, oh yes!

A well thought backup saved my skin last Saturday.
It's a simple setup: many copies. Using MySQL replication, the master is for writes.
Four slaves for reads. One slave for backups only (*). In a different server room. In a different building. (**)
The backup slave has a  cron job, which stops the slave, makes a dump, removes the oldest one, and resumes replication.
The same job works hourly (keeps 30 dumps), daily (keeps 7 dumps), and weekly (keeps 8 dumps).
The disaster occurred yesterday. A colleague who was working too much (***) made a destructive query on the wrong server. He thought he was using the development server, but it turned out to be the master. Fortunately, nobody else was working on a Saturday, so there weren't any changes, besides his. I zeroed the database on the master and reloaded the latest hourly dump. No suffering. No bad blood, and my morning coffee is paid till the end of the quarter!

(*) Replication is not a backup.
In this particular case, if you rely only on replication, your data is gone forever. 
You have five copies, all of them FUBAR.
(**) Keep the backup slave in the same room, and a flooding will make both master and slave useless at once.
(***) Seriously, Mel! You should have been holding a beer in a pub downtown instead of being in the office.