HOW TO: Dump or Backup all MySQL databases to separate files

I needed to transfer about 40 databases from a new clients server over to my hosting platform.

When doing a couple database exports, I might use PHPMyAdmin to do the export. Heck, it’s convenient because I usually want to look around at the same time. For doing a straight export, mysqldump is a great program that is even easier than phpmyadmin. There’s really nothing to it! (NOTE: For large databases with a LARGE number of tables you need to add –skip-lock-tables)

mysqldump –all-databases > dbdump.sql

If you want to get fancy, you can even compress the file

mysqldump –all-databases | gzip -9 > dbdump.sql.gz

But I wanted to go one step further. I wanted every database to have a separate file, compressed and correctly named.

I was shocked how simple it was to write. First I had to ask mysql for a list of the databases. Then I needed extract just the database name, removing the table borders. Then I needed to tell mysqldump to use each database name in the export source and destination.

In no time I had a 3 line script that was incredibly powerful.

I give you backupdbs.sh

#!/bin/bash
for database in $(mysql -e "show databases"|awk -F " " '{print $1}'); do
  mysqldump $database | gzip -9 > $database.sql.gz
done

 

 

Note that you may need to provide the -u and -p parameters (username and password).

That’s all there is to it!