> ## Content Index
> Fetch the complete content index at: https://www.blog.nestict.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# MySQL Back-up: mysqldump with each database in its own SQL File
- URL: https://www.blog.nestict.com/mysql-back-up-mysqldump-with-each-database-in-its-own-sql-file/
- Published: 2023-08-01T07:57:19.000Z
- Updated: 2025-06-16T07:18:07.000Z
- Author: Nestict News Team (CSR)
- Tags: Featured, News, Technology, Webhosting, #Migrated-1750058186872, #wp, #wp-post, #Import 2025-06-16 10:16

## 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.