Extracting a Table from a MySQL Dump

Occasionally, I need to use the data in a single table from a full MySQL database backup dump. Ordinarily, you’d just import the database dump into MySQL and go. Unfortunately, if the dump file is several Gigabytes (even when compressed), it becomes time-consuming to SCP this file around and then load it into MySQL.

In order to speed up the process, I extract the table I need from the full database dump. Then, I can take that table, SCP it to where I need it, load it into MySQL, and perform necessary operations with it. Fortunately, complicated programs and commands are not necessary for this: only grep and sed are needed.

The first step is to determine the table structure from the decompressed MySQL database dump:

$ grep -n "Table structure" [MySQL_dump_filename].sql

• Example:
  Let’s say we want to extract the phpbb_config table from a giant MySQL phpBB database dump.

[kulesza@db ~]$ grep -n "Table structure" global_us_phpbb.sql
19:-- Table structure for table `ajax_chat_bans`
43:-- Table structure for table `ajax_chat_messages`
73:-- Table structure for table `ajax_chat_online`
100:-- Table structure for table `phpbb_acl_groups`
129:-- Table structure for table `phpbb_acl_options`
157:-- Table structure for table `phpbb_acl_roles`
186:-- Table structure for table `phpbb_acl_roles_data`
212:-- Table structure for table `phpbb_acl_users`
241:-- Table structure for table `phpbb_attachments`
283:-- Table structure for table `phpbb_banlist`
317:-- Table structure for table `phpbb_bbcodes`
349:-- Table structure for table `phpbb_bookmarks`
373:-- Table structure for table `phpbb_bots`
402:-- Table structure for table `phpbb_config`
428:-- Table structure for table `phpbb_confirm`
456:-- Table structure for table `phpbb_disallow`

• This will provide you with the starting line number in the MySQL dump file which defines each table. Using this, determine the starting and ending line numbers of the table you need (the ending line number will be the starting line number of the next table, minus one).

The next step is to actually extract the table from the MySQL database dump file:

$ sed -n '[starting_line_number],[ending_line_number] p' [MySQL_dump_filename].sql > [table_output_filename].sql

• Example:
  I extract the phpbb_config table into tmp.sql, and then use head to check that the SQL in the file is what I am expecting.

[kulesza@db ~]$ sed -n '402,427 p' global_us_phpbb.sql > tmp.sql
[kulesza@db ~]$ head tmp.sql 
-- Table structure for table `phpbb_config`
--
DROP TABLE IF EXISTS `phpbb_config`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `phpbb_config` (
  `config_name` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
  `config_value` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
  `is_dynamic` tinyint(1) unsigned NOT NULL DEFAULT '0',

The last remaining step is to use the extracted table:

$ mysql -u root -p [some_database_name] < [table_output_filename].sql

• Example: Now I load the phpbb_config table into a temporary MySQL database.

[kulesza@db ~]$ mysql -u root -p my_temp_database < tmp.sql

Note that these steps presume a decompressed, full MySQL database dump which contains table DROP, CREATE, and INSERT SQL.