Backing up MySQL with Bacula
I’m currently building some backup tools to do online backups of MySQL on Linux using LVM and MySQL binary logs.
I’ve got two LVs:
- /var/lib/mysql - containing standard MySQL data files
- /var/lib/mysqlbin - containing MySQL binary logs
There are two backup schedules, one for each LV.
- `mysql’ is backed up weekly on Monday mornings, a full on the first Monday of each month, and differential from then on.
- `mysqlbin’ is backed up every Tuesday-Sunday morning (ie, not Monday), a full backup on the Tuesday, and incremental after that.
Prior to the `mysql’ backup taking place, a perl script runs and does the following in order:
- Runs “FLUSH TABLES WITH READ LOCK” in MySQL
- Creates an LVM snapshot of the `mysql’ LV
- Runs “RESET LOGS” in MySQL
- Runs “UNLOCK TABLES” in MySQL
The logs are reset each week so that the Tuesday backup of the log files doesn’t transfer logs we’ve already got.
Prior to the `mysqlbin’ backup taking place, a similar perl script runs and does the following in order:
- Runs “FLUSH TABLES WITH READ LOCK” in MySQL
- Runs “FLUSH LOGS” in MySQL
- Creates an LVM snapshot of the `mysqlbin’ LV
- Runs “UNLOCK TABLES” in MySQL
If any of the steps in the above scripts fail, “UNLOCK TABLES” is run, and then exists with an error. Bacula then drops me an email to tell me the backup failed, and I’ll go in and do some tinkering.
By doing a full backup each month, and then weekly differentials, I get a per-week base to work from with my binary logs. From there I can get the binary logs per day (or per hour if I get around to working out some of the nitty-gritty details).
My fairly busy MySQL server does the Flush+Lock, Snapshot, Reset, Unlock process in a few seconds. SELECT queries still work, but INSERT statements block until UNLOCK is called.