Restore a single MySQL table from a mysqldump (gzip)

So you have a large gzipped backup and you only need one table from that backup. What do you do? I used to just import the whole backup to another server first and then copy over the data, but that was very slow. Here’s a handy solution I found that will extract the required SQL for the table in question for importing:

zcat my_database_backup.gz | sed -n -e '/DROP TABLE IF EXISTS `my_table`/,/UNLOCK TABLES/p' > my_table.sql

Note that you can also use “gzip -cd” instead of “zcat”.

If the resulting file will be large, you can also gzip it.

zcat my_database_backup.gz | sed -n -e '/DROP TABLE IF EXISTS `my_table`/,/UNLOCK TABLES/p' | gzip > my_table.sql.gz

Importing the gzipped SQL-file is easy.

zcat my_table.sql.gz | mysql -u USER -p DATABASE