MySQL import database but ignore specific table

Question

I have a large SQL file with one database and about 150 tables. I would like to use mysqlimport to import that database, however, I would like the import process to ignore or skip over a couple of tables. What is the proper syntax to import all tables, but ignore some of them? Thank you.

Answer 1

The accepted answer by RandomSeed could take a long time! Importing the table (just to drop it later) could be very wasteful depending on size.

For a file created using

mysqldump -u user -ppasswd --opt --routines DBname > DBdump.sql 

I currently get a file about 7GB, 6GB of which is data for a log table that I don't 'need' to be there; reloading this file takes a couple of hours. If I need to reload (for development purposes, or if ever required for a live recovery) I skim the file thus:

sed '/INSERT INTO `TABLE_TO_SKIP`/d' DBdump.sql > reduced.sql 

And reload with:

mysql -u user -ppasswd DBname < reduced.sql 

This gives me a complete database, with the "unwanted" table created but empty. If you really don't want the tables at all, simply drop the empty tables after the load finishes.

For multiple tables you could do something like this:

sed '/INSERT INTO `TABLE1_TO_SKIP`/d' DBdump.sql | \ sed '/INSERT INTO `TABLE2_TO_SKIP`/d' | \ sed '/INSERT INTO `TABLE3_TO_SKIP`/d' > reduced.sql 

There IS a 'gotcha' - watch out for procedures in your dump that might contain "INSERT INTO TABLE_TO_SKIP".