How to backup your MySQL databases to separate files

I wanted to dump all my MySQL databases using the mysqldump utility, but I wanted each database to be in a separate file. I couldn’t find a solution online, so I wrote my own script. Here it is:

#!/bin/sh
USERNAME=admin
PASSWORD=topsecret
BACKUPDIR=/var/local/mysql-backups

for i in $(mysql -u $USERNAME -p$PASSWORD -e "SHOW DATABASES;" --skip-column-names --batch)
do
   echo "Backing up database $i"
   mysqldump -u $USERNAME -p$PASSWORD --opt $i | gzip > $BACKUPDIR/$i.sql.gz
done

Of course, you will need to edit the first two lines to reflect your own username and password. When you run this script, it will create a bunch of files in the /var/local/mysql-backups directory, one for each database. The files are compressed to save space.  Of course, you’ll need to make sure the destination directory exists.