Dumping multiple databases, but to separate files

Dumping multiple databases, but to separate files

As we’ve seen in the second section, the option –all-databases gives you the possibility to backup all the databases available. However, the databases will all be backed up to the same dump file, so sometimes you may prefer having a separate dump for each database. This can be useful in the case you need to restore one database, but need to leave the other ones alone. First, we need to find out which databases are available, and perhaps exclude some of them from the backup. Say, for instance, that you want to backup all the databases but mysql, information_schema. First, we list all the databases with

mysql -u... -p... -e "show databases;"

1 2	mysql -u... -p... -e "show databases;"

You should see something similar to this:

+-----------------------------+ | Database | +-----------------------------+ | information_schema | | database1 | | database2 | | mysql | +-----------------------------+

1 2 3 4 5 6 7 8 9

+-----------------------------+ | Database                    | +-----------------------------+ | information_schema          | | database1                   |       | database2                   |           | mysql                       | +-----------------------------+

We need to manipulate this output a little bit, so that we can extract from it just the database names, filtering out all the other characters that are not needed:

mysql -u... -p... -e "show databases;" | tr -d "| "

1 2	mysql -u... -p... -e "show databases;" | tr -d "| "

Which produces:

Database information_schema database1 database2 mysql

1 2 3 4 5 6

Database information_schema database1 database2 mysql

Almost there. We now want to remove the Database heading, and also exclude the databases mysql and information_schema, as per the example (unless you want to back them up too):

mysql -u... -p... -e "show databases;" \ | tr -d "| " \ | egrep -v "(Database|mysql|information_schema)"

1 2 3 4

mysql -u... -p... -e "show databases;" \ | tr -d "| "  \ | egrep -v "(Database|mysql|information_schema)"

Here we are:

database1 database2

1 2 3

database1 database2

We now have the clean list of the names of the databases we want to backup to separate files. All we have to do is chain this command with a simple for..do loop that will execute mysqldump for each database:

for db in \ `mysql -u... -p... -e "show databases;" \ | tr -d "| " \ | egrep -v "(Database|mysql|information_schema)"`; \ do mysqldump -u... -p... --opt --routines --databases $db > "$db.sql"; done

1 2 3 4 5 6

for db in \   `mysql -u... -p... -e "show databases;" \   | tr -d "| "  \   | egrep -v "(Database|mysql|information_schema)"`; \   do  mysqldump -u... -p... --opt --routines --databases $db  > "$db.sql"; done

By executing the command above, you will create as many .sql dump files as the number of databases you have backed up.

Restoring from multiple dumps

If you dump each database to a different file, you can restore all these databases -should you need to restore all of them- this way:

for file in `ls *.sql`; do echo $file && mysql -u... -p... \ "`echo $file | sed "s/\.sql//"`" < $file; done

1 2 3

for file in `ls *.sql`; do  echo $file && mysql -u... -p...  \   "`echo $file | sed "s/\.sql//"`" < $file; done