3

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?

8
  • 1
    You need to extract mydb3 from your dump file using sed into another file Dec 4 '21 at 14:04
  • source does not recognize that you want only a subset of the file.
    – Rick James
    Dec 4 '21 at 17:13
  • @RickJames How would you do it easily?
    – Basj
    Dec 4 '21 at 17:40
  • @ErgestBasha Thanks! Out of curiosity, is sed adapter for such kind of tasks? How would you use it here?
    – Basj
    Dec 4 '21 at 17:45
  • 1
    @Basj - sed is a simple, general-purpose, command-line editor; it dates back decades, long before the term "adapter" was invented.
    – Rick James
    Dec 4 '21 at 17:46
5

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.

3
  • 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. Dec 4 '21 at 23:19
  • Thank you! Do you think this is safe, and won't overwrite mydb1 in the current mysql install if dump.sql also contains mydb1?
    – Basj
    Dec 5 '21 at 19:13
  • @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;.
    – dbdemon
    Dec 5 '21 at 20:43
3

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 
2

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:

1

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.

2
  • 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?
    – Basj
    Dec 4 '21 at 17:58
  • @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).
    – Rick James
    Dec 4 '21 at 18:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.