MySQL import - How to ignore Drop table if exists line?

Question

I exported 2 identical databases(identical in terms of names and structures of tables) into two .sql files using mysqldump. I want to merge them into one file. However, both the databases have a 'Drop table' line before every table. What that means is if I import db1 and then db2, db1 tables are dropped before db2 tables are imported.

The files are huge and I am not able to open them in the editor. Also, there are 50 tables in each databases.

How can I ignore the Drop table command during mysql import?

Edit the file and comment these sommands --> http://dev.mysql.com/doc/refman/5.1/en/comments.html — krokodilko, Dec 13 '13 at 18:48
The files are huge. Won't be able to open them in the editor. Also, there are 50 tables in each databases. — zenCoder, Dec 13 '13 at 18:49

GoingMyWay · Answer 1 · 2021-08-07T08:22:38.203

44

All you need is to add --skip-add-drop-table option when using mysqldump.

$ mysqldump --databases --skip-add-drop-table -u root db1 > /tmp/qqq.2

So, there would not DROP TABLE IF EXISTS in sql files.

see docs of mysql on --skip-add-drop-table

edited Aug 07 '21 at 08:22

answered Apr 21 '16 at 12:52

GoingMyWay

16,802
32
96
149

6

And `--no-create-info` avoids the table creation. – robsch Dec 10 '19 at 12:25

score 18 · Accepted Answer · answered Dec 13 '13 at 21:40

18

If you do not want to make dump once again and you are using Linux you can go with:

awk '!/^DROP TABLE IF EXISTS/{print}' <dump.file> | mysql <db_name>

If you want to dump data once again you should pass --skip-add-drop-table to mysqldump utility.

answered Dec 13 '13 at 21:40

ravnur

2,772
19
28

2

No need to edit the file if you can pass a mysqldump argument doing what you want. See @GoingMyWay answer. – Yohan Obadia Jun 15 '18 at 15:16
No need to if you _know_ what you will want to do with the backup later. However, it's easier to filter it out, than add it in if it's missing and you want it. – Ian Jul 08 '21 at 11:36

score 1 · Answer 3 · answered Dec 13 '13 at 18:50

I guess I don't see why a DROP TABLE statement should be problematic or why you need to merge dumps for two IDENTICAL databases.

That being said, you should probably just not add DROP TABLE in the initial dump. This would be controlled via flag use in your mysqldump command as noted in the documention at http://dev.mysql.com/doc/refman/5.5/en/mysqldump.html

This probably means you need to use --skip-opt flag if you were using default options (default is to run as if --opt flag is passed). You will then need to specify all flags within --opt that you still want to use.

These dumps cannot be recreated. They are identical in terms of structure, ofcourse. — zenCoder, Dec 01 '14 at 06:37

MySQL import - How to ignore Drop table if exists line?

3 Answers3

Linked