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