Home > Misc > My database backup cron job

My database backup cron job

August 12th, 2009

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.

Categories: Misc
Comments are closed.