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:
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
