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:


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.

Be Sociable, Share!


No comments yet.

Post a Comment