Menu
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?
484
To remove a table in MySQL, use the DROP TABLE statement. The basic syntax of the command is as follows: DROP [TEMPORARY] TABLE [IF EXISTS] table_name [, table_name] [RESTRICT | CASCADE];
In order to tell mysqldump you want to exclude a single table, all you have to do is add the flag --ignore-table followed by the name of the table you want to exclude. After executing the command from the example above, the output file my_backup.
mysqldump can retrieve and dump table contents row by row, or it can retrieve the entire content from a table and buffer it in memory before dumping it. Buffering in memory can be a problem if you are dumping large tables. To dump tables row by row, use the --quick option (or --opt , which enables --quick ).
SELECT CONCAT( 'DROP TABLE ', GROUP_CONCAT(table_name) , ';' ) AS statement FROM information_schema. tables WHERE table_name LIKE 'myprefix_%'; This will generate a DROP statement which you can than copy and execute to drop the tables.
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
82
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.
30
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
