2 Minutes to Nightly MySQL Backups

I've been meaning to add an extra layer of backupness to one of my server's, by explicitly dumping the database every night. Here's how I did this for MySQL on a Linux box.

Nothing Earth shattering here, but still, it's worth taking 2 minutes to do:

1. Give root permission to run the database dump command without a password. See this article for how to do this. The very short version. Create a file named /root/.my.cnf and put:

   user=root
   password=YourSecretRootPassword
   

2. sudo mkdir -p /var/backup/db/
3. sudo crontab -e
4. Enter the following in the cron tab:

   MAILTO=your@email.address
   0 3 * * * /usr/bin/mysqldump --all-databases > /var/backup/db/`date +%a`.sql
   

   This will run the backup command every morning at 3:00am.

And you're done. I love having root access on my hosting servers. Definitely don't want to abuse it, but it's really handy when you need it.