Extract a Table from Mysqldump File

Sometimes, you may need to extract a single table from a mysqldump file, because the database size is very large and you are not being able to open that file with any text editor. In such a case you will have to use the "awk" command in Linux to extract the required table from the dump. You can use your innovation to use this concept for other file manipulation of similar kinds.

First, you have to know where in your mysqldump output you want to begin your extraction, and where you want to end it. The key here is finding something unique at the beginning and ending of the block that won’t be found anywhere else.

A sample mysqldump contains something like the following:

--
-- Table structure for table `table1`
--
...
DROP TABLE IF EXISTS `table1`;
CREATE TABLE `test1` ( ...
LOCK TABLES `test1` WRITE;
INSERT INTO `test1` VALUES (1,0,’2 ...
UNLOCK TABLES;
...
–-
–- Table structure for table `table2`
–-
As you can see, we have a line with the comment "Table structure for table `table1`", then all of the dropping, creating, and inserting for the table, and then another comment for the next table. These two lines are perfect for grabbing all of the operations pertinent to our one table.

To extract the dump for a single table from an entire database dump, run the following from a command prompt:

#     awk '/Table structure for table `table1`/,/Table structure for table `table2`/{print}' databasedump.sql > extracted_table.sql

The above command searches through the dump file, and as soon as it matches a line containing the first search string (denoted by the first set of slashes), it prints that line and every subsequent line until it encounters a line containing the second search string (denoted by the second set of slashes). FYI, the periods surrounding the table names above are wildcard characters.
Now the extracted_table.sql file contains the SQL to restore your table.
Finally, There are usually various parameters at the top of your mysqldump file that you may need to set before restoring your table, depending on the complexity of your database (i.e. disabling foreign key checks.)

To restore your table, run:

# mysql -u user -ppassword databasename < extracted_table.sql