How to restore a single table from a database backup

When working with huge databases it can sometimes take an age to restore a complete backup. This is a real pain when working locally when you just need a fraction of the data, especially if you have the patience of a developer!

As an example, sometimes it's really useful to get the contents of core_config_data table from a Magento site to see exactly what has changed in configuration on live without needing to import in huge amounts of pointless data.

We can use Linux's built in sed command to grab just the right content in a MySQL dump without any of the extra bits.

This all works as standard MySQL dumps always have the same structure for each table. Here's a quick example:

Let's break this down quickly.

1. Existing table is dropped
2. New table is created
3. Tables are locked
4. New data is inserted
5. Tables are unlocked

Our sed command defines the start and end point for the data we want as the drop and the unlock. You could change this around for lots of use cases, like other tables, or just getting the structure of a table.