0

If I make a full dump using mysqldump --all-databases > dump.sql and then I want to import just a single database, I can use mysql --one-database db1 < dump.sql.

According to the documentation, This option is rudimentary and should be used with care because statement filtering is based only on USE statements. Here is an example of how this could be dangerous:

mysql --one-database db1

DELETE FROM db2.t2;
USE db2;
DROP TABLE db1.t1;
CREATE TABLE db1.t1 (i INT);
USE db1;
INSERT INTO t1 (i) VALUES(1);
CREATE TABLE db2.t1 (j INT);

But this scenario seems very unlikely because I have never found a dump to contain statements that specify a database.table syntax. So the question is, if I make a full dump as written above, is there any risk of using the --one-database option to restore?

  • "safe"?? Watch out for DROP statements in the dump. – Rick James May 21 at 4:55
0

The only risk is time and missing out on trigger stored procedures.

Normally, mysqldumps are done in alphabetical order.

When loading a dump, expect databases aardvark, alligator and antelope to load early. Any database like zoo and zebra would require time because --one-database does not force a mysqldump to load the specified database. The entire dump must be read until the specified database is in use.

For example, mysql --one-database zebra will read the entire mysqldump but start loading when it gets to the line the dump that says

USE zebra

Then, all INSERT INTO commands are processed until the EOF or until it see another line like

USE zimbabwe

Some developers I know would wait until the database they chose is loaded. Then, hit Ctrl-C. I would not risk that. You should make sure that all triggers and stored procedures are loaded as well.

If you cannot wait and want to Ctrl-C, login to mysql while the dump is still loading and make sure all tables, views, triggers, and stored procedures have been loaded.

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.