Extract single table from a mysql dump

Update: I have written a  wrapper script for extracting single/multiple tables from a dumpfile. Now it is possible to extract tables with single command. Visit

The other day, while working with the MySQL database of one of my sites, I accidentally damaged one of the tables irrecoverably. Fortunately, I was using AutoMySQLBackup script to backup all my databases at 12 AM every day. To save time, I decided to import only the damaged table. But when I tried to open the .sql file created by the mysqldump program, I understood that it was not going to as easy as I thought. The dump file was over 100 MB in size and  none of my text editors allowed me to open a file of that size.

As usual, I approached Google for a solution and it introduced me to two different solutions – AWK (a programming language) and Sed (a unix utility). There is only a very slight difference between the two commands.

awk '/-- Table structure for table .tbl_first./,/-- Table structure for table .tbl_second./{print}' mydumpfile.sql > mytable.sql

sed -ne '/-- Table structure for table .tbl_first./,/-- Table structure for table .tbl_second./p' mydumpfile.sql > mytable.sql

Here tbl_first is the table I wanted to extract and tbl_second was the table below that. The above commands will search the file mydumpfile.sql and extract the text between the start string (— Table structure for table .tbl_first.) and end string (— Table structure for table .tbl_second.). The dots before and after the table name are wildcard character to match the engrave character, which has a special meaning in shell commands. The {print} option (p in sed) prints the extracted string, which is then redirected to the file mytable.sql.

But that didn’t solve my problem completely. I was not sure of the order of tables in the .sql file. This time  grep (another powerful unix utility) came to my rescue. The following command lists all the tables in the file mydumpfile.sql in the same order in which they appear in the file.

grep 'Table structure' mydumpfile.sql | cut -d'`' -f2

I don’t know a lot about shell commands. But with my very limited experience I can say that they are extremely powerful. Two small lines of code saved me a lot of time.