It's always useful, when you have large MySQL dumps, to be able to extract a specific table from the middle of the file. And preferably without having to re-insert everything or open the file and manually select from it... 🙂

I'll take as an example the compressed SQL file: mysql_dump.sql.gz.

The first step is to get the list of CREATE TABLE statements as well as their position in the file. Grep (and zgrep) will help us:

$ zgrep -ni '^CREATE TABLE' mysql_dump.sql.gz
25:CREATE TABLE `admin_database` (
62:CREATE TABLE `admin_db_server` (
95:CREATE TABLE `admin_server` (
125:CREATE TABLE `agregat_client` (
174:CREATE TABLE `agregat_client_domain` (
202:CREATE TABLE `agregat_client_themes` (
232:CREATE TABLE `agregat_collect_tool` (
261:CREATE TABLE `agregat_consent` (
298:CREATE TABLE `agregat_coreg` (
331:CREATE TABLE `agregat_filter_collect_tool` (
355:CREATE TABLE `agregat_import` (
383:CREATE TABLE `agregat_segment` (
414:CREATE TABLE `business_line` (
439:CREATE TABLE `campaign` (
...

Then we get the line number of the table we want to retrieve, for example agregat_collect_tool, as well as the line number of the next table, which gives us: 232 and 261.

Since mysqldump adds some instructions and comments before, we subtract 5 from the starting line.

To extract the agregat_collect_tool table, we find the 2 numbers: 232 and 261. The start of the lines to retrieve is 232 – 5 = 227 The number of lines to retrieve is 261 – 232 = 29

And here's the command line to extract the table:

$> zcat mysql_dump.sql.gz | tail -n + 227 | head -n 29 > extract.sql

All that's left is to quickly check the file and re-import it into MySQL...