Removing "Insert" statements from Mysql Full DB dump file (to be able to create a DB with empty tables)

http://stackoverflow.com/questions/16735344/mysql-import-database-but-ignore-specific-table

(...)

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".

(...)