Friday, September 26, 2008

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.

No comments:

Post a Comment