I'm just putting some additional refinements to my automated server backup process, and have rolled together a handy script to backup each database into its own backup file (so I can restore a single database, rather than blowing them all away to restore from an --all-databases backup.
I'm going to work on making a fancier / more dynamic script based on MySOL's show databases command to get all databases backed up individually without having to remember to add them to the backup script. In the meantime, here's how I'm backing up my databases.
In a script creatively named "backup_databases.sh" - which has been added to the crontab on the server - I have this:
Update: A much better script was provided in the comments for this post. Definitely use that one rather than this one that I cobbled together. I'm leaving this script up in case it comes in handy, but have switched my servers to use the script provided by Jon.
#!/bin/sh
# Customize these variables to match what you have
MYSQL_ACCOUNT=root
MYSQL_PASSWORD=password
BACKUP_DIRECTORY="/Users/Shared/backup/mysql/"
backupdb() {
DATABASE_NAME=$1
FILE_NAME=${2:-$DATABASE_NAME}
echo "dumping database " $DATABASE_NAME " to " $FILE_NAME
/usr/bin/mysqldump -u $MYSQL_ACCOUNT -p$MYSQL_PASSWORD -q $DATABASE_NAME | gzip > $BACKUP_DIRECTORY$FILE_NAME.sql.gz
}
# add lines for each database you want to back up
backupdb "first_database"
backupdb "database2"
# keep adding databases...
# finish up by grabbing the whole works Just In Case
backupdb "--all-databases" "mysqldump" The script has a function that is called for each database you want to back up, passing in the database name, and optionally the name of the output file. I'll be tweaking the script over the next few days to make it more robust and flexible, but it's a decent starting point anyway.
Of course, if you don't need to restore individual databases, you can simply call
mysqldump -u USER -pPASSWORD -q --all-databases | gzip > mysqldump.sql.gz
Update: A much better, more flexible, and robust script was provided by Jon in the comments for this post. I'm using that script now. Thanks!