I’m off on holiday next week and I’d like a way to automatically backup my database at work (it runs on MySQL). I can schedule a cron job, but is there a way I can script this?

This question came from someone I knew who was off to Florida for a few days taking the family and himself for a much needed break. Anyway, the question didn’t contain much information, but I do know that in order to back up MySQL you can use mysqldump which is a backup program for MySQL.

Here’s a cute little script that will backup and Zip up your database:

#!/bin/sh
date='date -I'
mysqldump -u <username> -p <password> <database> | gzip -a > /home/mysql-backup/database-$date.sql.gz


where

<username> is a user who has access to the database
<password> is the password for this user
<database> is the name of the database that needs to be backed up

The script will save the backup with a timestamp so if you need to run this more than once it will make sure each database has a unique name.

There are a few desktop management tools out there that can help you schedule the job, but cron will do just fine. And next time you go on one of those Disney vacations, please, please .. can I come with you?