Its been a while since I posted anything. My new baby is keeping me pretty busy when I’m not working.

Anyway, I was doing some simple maintenance today and thought I’d share it, while at the same time do something useful with my blog. This is just a quick rundown on how I’m backing up a database every day automatically and have it email to myself.

I start off by editing the crontab, since I want this to run automatically every day:
$ crontab -e

10 01 * * * /usr/bin/mysqldump -pSOMEPASSWD DBNAME | gzip > /root/sqlbackups/DBNAME.`date +"%Y%m%d"`.sql.gz

That line tells it to run 1:10am every day. You need to replace “SOMEPASSWD” and “DBNAME” with your password and database name. “%Y%m%d” will display the date as “year month day”, for eg:
DBNAME.20100201.sql.gz
Note that I’m saving it in the /root/sqlbackups/ path, so make sure it exists.

Next line:

15 01 * * * /bin/echo | /usr/bin/mutt -s "MySQL backup `date +"%F"`" YOUR@EMAILADDRESS -a /root/sqlbackups/DBNAME.`date +"%Y%m%d"`.sql.gz

This job will run 5 minutes later. You will need Mutt installed on your system.

Next line, cleanup:

20 01 * * * /usr/bin/find /root/sqlbackups/DBNAME* -mtime +14 -exec rm {} \;

This will check if any of the backups are older than 14 days and delete them. You can change the amount of days to whatever you like.

There you have it! Quick and simple MySQL database backup solution.
Note that I was using the root user. It is probably safer using a user other than root.

– EDIT –

This crap above wont work if you put it into cron like that because of the quotes. Rather put them in a script, chmod +x it and then add it into cron.