How to import only a specific database from a .sql file?

Question

I have a file dump.sql with many databases in it: mydb1, mydb2, mydb3, etc.

How to import only mydb3 and not the other databases?

Won't:

mysql> create database mydb3;
mysql> use mydb3;
mysql> source /path/to/dump.sql;

import all databases?

• Rick James: source does not recognize that you want only a subset of the file.
• Basj: @RickJames How would you do it easily?
• Basj: @ErgestBasha Thanks! Out of curiosity, is sed adapter for such kind of tasks? How would you use it here?
• Rick James: @Basj - sed is a simple, general-purpose, command-line editor; it dates back decades, long before the term "adapter" was invented.
• Basj: @RickJames sorry, I meant "is it well adapted for this task?" it was a typo :) I don't know what an "adapter" is in this context. My mistake!

Answer 1

You can use the --one-database option or -o for short:

mysql ... -o mysb3 < /path/to/dump.sql

Note that the documentation page in the link above states:

This option is rudimentary and should be used with care.

However, I think dump files created by mysqldump should be safe enough.

Note: this doesn't run the actual CREATE DATABASE ... statement, so you have to do that before you import.

• RolandoMySQLDBA: IHMO This is the most expedient answer without creating another file. If you were doing mydb1 or mydb2 like this as well and hit Ctrl-C when it finishes loading mydb1 or mydb2.
• Basj: Thank you! Do you think this is safe, and won't overwrite mydb1 in the current mysql install if dump.sql also contains mydb1?
• dbdemon: @Basj Yes, it should be safe as long as the dump was created by mysqldump or the dump file is organised so that everything to do with database x is preceded by USE x;.

Answer 2

Try this:

sed -n '/^-- Current Database: `mydb1`/,/^-- Current Database: `/p' dump.sql > mydb1.sql

Other ways of using sed to get specific tables:

sed -n -e '/CREATE TABLE.*`table_name`/,/CREATE TABLE/p' "dump_file" > table_name.sql

If you have .gz or .bz2 dumps:

gunzip < fulldump.sql.gz | sed -n -e '/DROP TABLE.*`table_name`/,/UNLOCK TABLES/p' | gzip -c > table_name.gz 
sudo bzip2 -d < fulldump.bz2 | sed -n -e '/DROP TABLE.*`table_name`/,/UNLOCK TABLES/p' | bzip2 -z > table_name.gz 

Answer 3

awk and perl are also good tools for this task.

awk '/^-- Current Database: `mydb1`/,/^-- Current Database: `/' dump.sql > mydb1.sql
perl -ne 'print if /^-- Current Database: `mydb1`/../^-- Current Database: `/' dump.sql > mydb1.sql

Substitute your schema name for "mydb1" in the above examples.

See:

• https://www.shellhacks.com/sed-awk-print-lines-between-two-patterns/
• https://stackoverflow.com/questions/38972736/how-to-print-lines-between-two-patterns-inclusive-or-exclusive-in-sed-awk-or

Answer 4

Plan A:

Go back to the original server and dump only mydb3:

mysqldump ... mydb3 > db3.sql
mysql ... < db3.sql

Plan B:

Edit the dump to extract just the statements needed, then load that subset. sed is one way, most editors is another way; there may be other ways.

Caution: Be careful to include/exclude the extra commands such as DROP, USE, etc.

Plan C:

Load the whole dump into a separate instance of MySQL, then do Plan A to copy the db to the ultimate destination.

• Basj: Thank you. Plan A is impossible, the original server has been wiped now, and I only have a dump of all databases in a single file. Would you have more details about how to use sed for Plan B?
• Rick James: @Basj - 30 years ago, I could rattle off the details for sed, awk, ex, and maybe some others; I haven't use sed in more than a decade. Let's hope someone else will jump in. Suggest you try your favorite text editor (not Word).