Apr 9, 2024

How to restore a single table from a database backup

Using sed to quickly get the contents of 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.

sed -n -e '/DROP TABLE.*`core_config_data`/,/UNLOCK TABLES/p' database_backup.sql > core_config_data.sql

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.