MySQL rolling backups
From Julian Yap
Quick and easy way to back up MySQL databases.
This will back up the database daily and keep a backup for the past month by day.
[edit]
Backup script
run_mysql_backup.sh
mysqldump --user=[Username] --password=[Password] [Database] | gzip -c > [Path to Backup Directory]/`date +%d`.gz
NOTE:
Adding the option '--single-transaction' avoids locking tables during the dump for InnoDB tables.
[edit]
Cron entry
20 02 * * * [Path to Script]/run_mysql_backup.sh
[edit]
Outcome
You will now have something similar:
# ls -l [Path to Backup Directory] total 5084 -rw-r--r-- 1 [user] [user] [file size] May 1 02:20 01.gz -rw-rw-r-- 1 [user] [user] [file size] May 2 02:20 02.gz -rw-rw-r-- 1 [user] [user] [file size] May 3 02:20 03.gz -rw-rw-r-- 1 [user] [user] [file size] May 4 02:20 04.gz -rw-rw-r-- 1 [user] [user] [file size] May 5 02:20 05.gz -rw-rw-r-- 1 [user] [user] [file size] May 6 02:20 06.gz -rw-rw-r-- 1 [user] [user] [file size] May 7 02:20 07.gz -rw-rw-r-- 1 [user] [user] [file size] Apr 8 02:20 08.gz -rw-rw-r-- 1 [user] [user] [file size] Apr 9 02:20 09.gz -rw-r--r-- 1 [user] [user] [file size] Apr 10 02:20 10.gz -rw-r--r-- 1 [user] [user] [file size] Apr 11 02:20 11.gz -rw-r--r-- 1 [user] [user] [file size] Apr 12 02:20 12.gz -rw-r--r-- 1 [user] [user] [file size] Apr 13 02:20 13.gz -rw-r--r-- 1 [user] [user] [file size] Apr 14 02:20 14.gz -rw-r--r-- 1 [user] [user] [file size] Apr 15 02:20 15.gz -rw-r--r-- 1 [user] [user] [file size] Apr 16 02:20 16.gz -rw-r--r-- 1 [user] [user] [file size] Apr 17 02:20 17.gz -rw-r--r-- 1 [user] [user] [file size] Apr 18 02:20 18.gz -rw-r--r-- 1 [user] [user] [file size] Apr 19 02:20 19.gz -rw-r--r-- 1 [user] [user] [file size] Apr 20 02:20 20.gz -rw-r--r-- 1 [user] [user] [file size] Apr 21 02:20 21.gz -rw-r--r-- 1 [user] [user] [file size] Apr 22 02:20 22.gz -rw-r--r-- 1 [user] [user] [file size] Apr 23 02:20 23.gz -rw-r--r-- 1 [user] [user] [file size] Apr 24 02:20 24.gz -rw-r--r-- 1 [user] [user] [file size] Apr 25 02:20 25.gz -rw-r--r-- 1 [user] [user] [file size] Apr 26 02:20 26.gz -rw-r--r-- 1 [user] [user] [file size] Apr 27 02:20 27.gz -rw-r--r-- 1 [user] [user] [file size] Apr 28 02:20 28.gz -rw-rw-r-- 1 [user] [user] [file size] Apr 29 02:20 29.gz -rw-rw-r-- 1 [user] [user] [file size] Apr 30 02:20 30.gz -rw-r--r-- 1 [user] [user] [file size] Mar 31 03:20 31.gz
