MySQL-specific information

From The Open Source Backup Wiki (Amanda, MySQL Backup, BackupPC)
Jump to navigationJump to search

There are multiple ways to performs MySQL backups using Amanda. Which one you can and should use depends on your situation.

Small DB, plenty of disk space

If you don't have a lot of data (less than a couple of gigabytes) in your MySQL databases, you can simply dump the contents of the databases to a file before the Amanda backup is started. The dump file will be backed up using Amanda.

Note that when using this method a large file will be backed up every backup run.

Here is an example script for backing up the data of a MySQL instance:

#!/bin/sh
# Prevent access to the data in the backup
umask 037
# Remove the backup from the day before yesterday to prevent disk space from filling up
/bin/rm /backups/MySQL/mysql-5.0-`date --date="2 days ago" +%F`.sql.gz 2>/dev/null
# Dump the contents of all databases
/usr/bin/mysqldump --all-databases --routines --opt | /bin/gzip - > /backups/MySQL/mysql-5.0-`date +%F`.sql.gz

Add a crontab entry to run this dump before amdump; either leave enough time between the two crontab entries, or run one after the other with &&. Add the MySQL data directory to the exclude list to prevent backing up data twice. For example: /var/lib/mysql

Large DB

For larger databases the Zmanda Recovery Manager (ZRM) for MySQL can be used.