Backing up MySQL

The contents of this post have been superceded

Using Movable type, the site is based on a MySQL database, and it's important to back this up.

Therefore I've put the following commands into crontab - I think they're okay (if anyone spots errors, please let me know!)

The first line will dump the sql database into a file at 1:08am. This time was arbitrarily chosen

The remaining lines make daily copies of the latest backup, about an hour before making the new latest backup.

In this fashion there will always be a backup every day for the last week. I'll also make weekly backups for the last month and a monthly backup for the past year using the same principle. I've shown the line which will create the monthly backup.

Of course, you should replace anything (in brackets) with appropriate values - localhost may not be right for you either. Passwords are stored in ~/.my.cnf - I will store more variables here and quietly change this entry to make the command better.

Modified thanks to Aquarion and links to manuals - some mods likely to occur in the future. The $(date +\%B) would be $(date +%B) on the command line, the backslash is so that cron doesn't treat % as a new line.

8 1 * * * mysqldump -h localhost -u (username) (database name) | gzip -c > ~/(backup directory)/latest/backup-mt.sql.gz
50 23 * * * cp ~/(backup directory)/latest/backup-mt.sql.gz ~/(backup directory)/daily/$(date +\%A).sql.gz
55 23 28 * * cp ~/(backup directory)/latest/backup-mt.sql.gz ~/(backup directory)/monthly/$(date +\%B).sql.gz

Contents of ~/.my.cnf:

[client]
password = "(password)"

The size of the backup directory should be limited, as old backups are overwritten, and I can't forget to make a backup. In principle(!) I could lose 24 hours worth of updates at most.

I should also look into smiling sweetly at someone I trust who would be willing to let me ftp the latest backup to them each day. As I trust nobody that much, and don't quite know if anything sensitive is visible in the SQLdump, I'd have to work out how to encrypt it first.