//
you're reading...

How To's

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.

Be Sociable, Share!

Discussion

No comments yet.

Post a Comment