Restoring a single table from a large MySQL dump file

From lxadm | Linux administration tips, tutorials, HOWTOs and articles
Jump to: navigation, search

If you have a large MySQL dump file, but only need to restore a single table, you may use the following:

Please substitute “interesting_table” with the table you’d like to extract from the dump file:

zcat database-2013-03-03-weekly.sql.gz | sed -n -e '/CREATE TABLE.*interesting_table/,/CREATE TABLE/p' > interesting_table.sql

Please make sure to review the end of the file, as it may contain “DROP TABLE” for the next table – just remove these entries.

Also, when importing, you may need to remove statements like from the dump file:

/*!40101 SET character_set_client = @saved_cs_client */;