Pulling single tables out of a large MySQL dump file

This is a problem I run into occasionally when I need to restore only one or two tables from a large database dump file in text format.

It can be done with many scripting languages, but I found a nice awk command on the T-sheets blog. To use the awk command, you need to know the names and order of the tables in the dump file. First, grep for "CREATE TABLE" to find the names and order of tables in the dump:

grep -n "CREATE TABLE" dumpfile.sql

The -n switch adds the line number which is not really needed. Next, plug in the name of the table you want to extract and the name of the table immediately following it:

awk ‘/Table structure for table .table-to-extract./,/Table structure for table .table-after./{print}’ dumpfile.sql > /tmp/extracted_table.sql

Next, I usually go in and add a "use my_database_name" command at the top and remove any unnecessary commands added by the mysqldump program. The last step is to feed the extracted table SQL to mysql.