Wednesday, May 12, 2010

Backup MySQL via Crontab

After spending a half an hour figuring out how to make noupe's No. 2 solution work, I finally got my crontab entry that'll execute on my Ubuntu workstation. It took me a while since I stumbled upon a couple of errors, mostly syntax, and the messages wasn't helpful. But here it is:

00 * * * * /usr/bin/mysqldump -h HOSTNAME -u MYUSERNAME -pMYPASSWORD mydatabase | gzip > /home/dax/Backups/db_`/bin/date +\%Y\%m\%d\%H\%M`.sql.gz

What it  does is execute the mysqldump command every hour, pipe it through gzip, and save the compressed file on my Backups directory as db_201005122300.sql.gz (since it executed on May 12, 2010 at 11:00PM)

Notes:

  • The HOSTNAME, MYUSERNAME, and MYPASSWORD should be filled up by you
  • ! % \ etc. should be escaped as such: \! \% \\
  • /bin/date + is very important, it won't execute if you only place date there