Your database is treasure trove filled with wonders and delights. It sure would be a real shame if something happened to it.
Back it up! You don’t even have to be there to do it! Why you could be lounging poolside at the Bellagio, beer in hand while your minions handle it for you. Didn’t know you had minions? You do. They’re called cron jobs and they will gladly bear your burden. Firstly, let’s talk about the mysqldump utility that comes with MySQL.
Just like it says, it will dump all you database data out, you just need to aim the firehose. We do that by piping it. (See what I did there?) A pipe is just this guy: | , and it sends the output of one process to another process. In this case, gzip.
This statement turns on the hose:
mysqldump -u USER -pPASS DATABASE
Where USER is the username, PASS is the password (yes, it is smashed against the -p), and DATABASE is the name of the database you want to dump. (Use “–all-databases” to snag ‘em all.) So then we pipe it to gzip:
mysqldump -u USER -pPASS DATABASE | gzip
and redirect the output to the file of our choosing.
mysqldump -u USER -pPASS DATABASE | gzip > /your/path/db_backup.sql.gz
Database backed up. But we want this to happen automatically. If we use this command, our database backup file gets rewritten each time. So let’s use some date magic to put in the current date.
mysqldump -u USER -pPASS DATABASE | gzip > /your/path/db_`date '+%Y-%m-%d'`.sql.gz
Marvelous, now the file comes out looking like this: db_YYYY-MM-DD.sql.gz. Now all that is left is to schedule the cron. A lot of hosts have built in cron schedulers, so go look in your control panel and add this command. I set my cron to go off every morning at 2:00am, and just like that, I’ve got piece of mind. Another Dos Equis, please?