My database backup cron job
Once upon a time, I wasn’t satisfied with WordPress’s backup feature for some reason. I can’t remember why. This was way back in 1.2 or something, and I’m sure it’s fixed now. Nevertheless, I set up a system for database backups which I think works pretty well. It runs as a cron job on my host, backs up the database, compresses the backup file, uuencodes it, and emails it to my Gmail account. There I have a filter which directs such emails to the Trash. This way I never have to actually deal with the backups, but they stay in the Trash for 30 days until automatic deletion – so I have backups of every day for a month.
So I thought I’d share. My cron job is set to run at 3 AM every day. It’s a string of semicolon-separated individual commands:
mysqldump -A -C –add-drop-table -u DB_USER_HERE –password=DB_PASSWD_HERE > ~/dailybackup.sql; tar -pczf ~/dailybackup.tar.gz ~/dailybackup.sql; uuencode ~/dailybackup.tar.gz ~/dailybackup.uue | mail -s “Daily Backup” YOUREMAILHERE; rm ~/dailybackup.sql; rm ~/dailybackup.tar.gz
To break down the commands one by one:
mysqldump -A -C --add-drop-table -u DB_USER_HERE --password=DB_PASSWD_HERE > ~/dailybackup.sql;
Use the mysqldump utility to dump everything, with compression, using add/drop table syntax, with user DB_USER_HERE and password DB_PASSWD_HERE, and store the results in dailybackup.sql in the user’s home directory.
tar -pczf ~/dailybackup.tar.gz ~/dailybackup.sql;
Create a gzipped tarball of the file in the user’s home directory.
uuencode ~/dailybackup.tar.gz ~/dailybackup.uue | mail -s "Daily Backup" YOUREMAILHERE;
Uuencode the backup file and name it dailybackup.uue, and send that into the mail to the address YOUREMAILHERE with the subject “Daily Backup.” (In Gmail, I put a filter on “Daily Backup”.)
rm ~/dailybackup.sql; rm ~/dailybackup.tar.gz
Remove the temporary backup files that were created during the process.
Voila, a shiny new database backup every morning.
Related posts:
