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?

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.

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.