MySQL Back-up: Take a mysqldump with each database in its own SQL File

Mattias Geniar, Monday, August 24, 2015 - last modified: Thursday, June 16, 2016

If's often very useful to have a couple of MySQL oneliners nearby. This guide will show you how to take a mysqldump of all databases on your server, and write each database to its own SQL file. As a bonus, I'll show you how to compress the data and import it again -- if you ever need to restore from those files.

Take a mysqldump back-up to separate files

To take a back-up, run the mysqldump tool on each available database.

$ mysql -N -e 'show databases' | while read dbname; do mysqldump --complete-insert --routines --triggers --single-transaction "$dbname" > "$dbname".sql; done

The result is a list of all your database files, in your current working directory, suffixed with the .sql file extension.

$ ls -alh *.sql

-rw-r--r-- 1 root root  44M Aug 24 22:39 db1.sql
-rw-r--r-- 1 root root  44M Aug 24 22:39 db2.sql

If you want to write to a particular directory, like /var/dump/databases/, you can change the output of the command like this.

$ mysql -N -e 'show databases' | while read dbname; do mysqldump --complete-insert --routines --triggers --single-transaction "$dbname" > /var/dump/databases/"$dbname".sql; done

Mysqldump each database and compress the SQL file

If you want to compress the files, as you're taking them, you can run either gzip or bzip on the resulting SQL file.

$ mysql -N -e 'show databases' | while read dbname; do mysqldump --complete-insert --routines --triggers --single-transaction "$dbname" > "$dbname".sql; [[ $? -eq 0 ]] && gzip "$dbname".sql; done

The result is again a list of all your databases, but gzip'd to save diskspace.

$ ls -alh *.gz

-rw-r--r--  1 root root  30K Aug 24 22:42 db1.sql.gz
-rw-r--r--  1 root root 1.6K Aug 24 22:42 db1.sql.gz

This can significantly save you on diskspace at the cost of additional CPU cycles while taking the back-up.

Import files to mysql from each .SQL file

Now that you have a directory full of database files, with the database name in the SQL file, how can you import them all again?

The following for-loop will read all files, strip the ".sql" part from the filename and import to that database.

Warning: this overwrites your databases, without prompting for confirmation. Use with caution!

$ for sql in *.sql; do dbname=${sql/\.sql/}; echo -n "Now importing $dbname ... "; mysql $dbname < $sql; echo " done."; done

The output will tell you which database has been imported already.

$ for sql in *.sql; do dbname=${sql/\.sql/}; echo -n "Now importing $dbname ... "; mysql $dbname < $sql; echo " done."; done 

Now importing db1 ...  done.
Now importing db2 ...  done.

These are very simple one-liners that come in handy when you're migrating from server-to-server.



Hi! My name is Mattias Geniar. I'm a Support Manager at Nucleus Hosting in Belgium, a general web geek & public speaker. Currently working on DNS Spy & Oh Dear!. Follow me on Twitter as @mattiasgeniar.

Share this post

Did you like this post? Will you help me share it on social media? Thanks!

Comments

Zane Monday, May 22, 2017 at 23:47 - Reply

Hi Mattias,
I was inspired by this article, so I spent the last weekend building a simple script around it, with the ability to install it system-wide to have it handy and multiple “profile” support. Have a look: https://github.com/TurboLabIt/zzmysqldump

p.s.: A shout in cron.weekly would be the peak of my month :-P


Matthias Monday, November 19, 2018 at 17:42 - Reply

Hello Mattias,

thank you for your article. I bookmarked it some weeks ago but was busy to implement your example for my database-backup cronjob. Great, short and simple

Best regards


Leave a Reply

Your email address will not be published. Required fields are marked *

Inbound links