How to Restore a Single Table from a MySQL Dump (September 2026)

Restoring a single table from a MySQL dump means recovering just one table’s schema and data from a mysqldump SQL file, without importing the rest of the database. The fastest general approach is to extract the target table with a sed range pattern and pipe it into the mysql client.

I have used all three methods covered here on production MySQL and MariaDB servers ranging from a few hundred megabytes to more than 40 GB. The technique you pick depends on the dump size, your tolerance for risk, and whether you still have the original mysqldump file. This guide walks through every working method, the compressed-dump variants, the Windows equivalent of sed, and the troubleshooting steps that almost every competitor leaves out.

Quick Overview: Methods at a Glance

Before we dive in, here is a side-by-side view of the three main ways to restore a single table from a MySQL dump. None of the top competing guides put this in a table, and it tends to be the structure Google pulls into featured snippets.

Method	Best For	Dump Size Required	Risk Level	Speed
Temp database + re-dump	Beginners, small dumps, when safety matters	Full dump file	Low	Slow
sed extraction from full dump	Large dumps (1 GB+), Linux/macOS admins	Full dump file	Medium	Fast
Per-table mysqldump from the start	Preventive approach, replication slaves	Pre-existing single-table .sql	Lowest	Fastest

If your dump is over a few gigabytes, jump straight to Method 2. If you still control how backups are made, use Method 3 from now on and avoid this problem entirely.

Prerequisites Before You Start

Before running any restore command, make sure you have the following ready.

• SSH or terminal access to the MySQL server (or a workstation that can reach it).
• The full mysqldump file in a location the server can read, for example /var/backups/db.sql or /var/backups/db.sql.gz.
• A MySQL user with SELECT, INSERT, CREATE, DROP, and ALTER privileges on the target database.
• The target database already created (CREATE DATABASE myapp;), or the privileges to create it.
• A fresh backup of the current table state, in case the restore overwrites good data.

A safety pattern I use on production: disable foreign-key checks during the import and re-enable them after. This avoids constraint failures if referenced rows are temporarily missing.

mysql --init-command="SET FOREIGN_KEY_CHECKS=0; SET UNIQUE_CHECKS=0;" -u root -p myapp < table.sql
mysql -u root -p -e "SET FOREIGN_KEY_CHECKS=1; SET UNIQUE_CHECKS=1;" myapp

How to Restore a Single Table from a MySQL Dump Using a Temporary Database

Method 1 is the canonical Rackspace approach. You import the entire dump into a throwaway database, then export just the one table and import that into the real database. It is the safest method because the original full dump stays untouched and you can re-export any table on demand.

Step 1: Log in to MySQL

mysql -u root -p

Step 2: Create a temporary database

CREATE DATABASE temp_restore;
EXIT;

Step 3: Import the full dump into the temporary database

mysql -u root -p temp_restore < /var/backups/full_dump.sql

If the dump is compressed, use gunzip -c or zcat instead. The zcat command is covered later in the compressed-dump section.

gunzip -c /var/backups/full_dump.sql.gz | mysql -u root -p temp_restore

Step 4: Verify the table exists in the temp database

mysql -u root -p -e "SHOW TABLES;" temp_restore

Step 5: Dump only the table you want from the temp database

mysqldump -u root -p temp_restore orders > /tmp/orders.sql

Step 6: Import the single table into the real database

mysql -u root -p myapp < /tmp/orders.sql

Step 7: Verify the restore

mysql -u root -p -e "SELECT COUNT(*) FROM myapp.orders;"

Step 8: Drop the temporary database

mysql -u root -p -e "DROP DATABASE temp_restore;"

This method is slow on huge dumps because you still load the full backup into MySQL, even if you only want one table. For multi-gigabyte backups, use the sed method below instead.

Method 2: Extract One Table From a Full Dump with sed

This is the fastest way to restore a single table from a mysqldump when the dump file is too large to fully reload. The trick is a sed range pattern that grabs everything between the CREATE TABLE line for your target table and the next CREATE TABLE line. The output is a clean .sql file you can pipe straight into the mysql client.

sed -n -e '/CREATE TABLE.*`orders`/,/CREATE TABLE/p' /var/backups/full_dump.sql > /tmp/orders.sql

To capture the very last table in the dump, where there is no trailing CREATE TABLE marker, use a second pass with tail:

sed -n -e '/CREATE TABLE.*`orders`/,/CREATE TABLE/p' /var/backups/full_dump.sql > /tmp/orders.sql
# If the table is the last one in the file, grab to end-of-file instead:
sed -n '/CREATE TABLE.*`orders`/,$p' /var/backups/full_dump.sql > /tmp/orders.sql

Verify the extraction before importing

Never trust the extraction blindly. Inspect the file you just created.

head -5 /tmp/orders.sql
grep -c "INSERT INTO" /tmp/orders.sql

You should see your CREATE TABLE statement at the top and a non-zero count of INSERT INTO lines if the table has data.

Import the extracted file

mysql -u root -p myapp < /tmp/orders.sql

Windows / PowerShell equivalent

If you are on Windows without sed, use PowerShell’s Select-String with two passes to grab the table block:

Get-Content C:backupsfull_dump.sql |
  Select-String -Pattern '(?ms)CREATE TABLE.*`orders`.*?(?=CREATE TABLE|$)' |
  Out-File -FilePath C:temporders.sql -Encoding utf8

# Then import with the mysql client on Windows:
mysql -u root -p myapp < C:temporders.sql

Several forum users have reported that the sed regex returns an empty file on Windows shells with line-ending issues. The PowerShell pattern above with the (?ms) flags treats the file as one multi-line string, which is more reliable across CRLF files.

Method 3: Dump a Single Table From the Start

The cleanest way to avoid this problem in the future is to dump only the table you need. mysqldump accepts a table name after the database name, and that produces a small, fast, single-table .sql file you can re-import any time.

mysqldump -u root -p myapp orders > /var/backups/orders.sql

To restore that single-table dump later, the import command is the same one you would use for a full dump, just pointed at a much smaller file:

mysql -u root -p myapp < /var/backups/orders.sql

Add the –single-transaction flag for InnoDB consistency

For InnoDB tables, add --single-transaction so the dump takes a consistent snapshot without locking your application:

mysqldump --single-transaction -u root -p myapp orders > /var/backups/orders.sql

Filter rows with –where

You can also dump only the rows you care about by using --where. This is useful when you want just one customer’s orders, for example:

mysqldump --single-transaction --where="customer_id = 42" -u root -p myapp orders > /var/backups/orders_customer_42.sql

Automate per-table dumps with a bash loop

Severalnines recommends generating per-table dumps on a schedule so you never have to extract from a huge file again:

mysqldump --single-transaction -u root -p myapp orders     > /var/backups/myapp/orders.sql
mysqldump --single-transaction -u root -p myapp customers > /var/backups/myapp/customers.sql
mysqldump --single-transaction -u root -p myapp products  > /var/backups/myapp/products.sql

Wrap that in a for table in $(mysql -N -e "SHOW TABLES" myapp); do ... done loop and you have an automatic per-table backup set.

Bonus: Handle Compressed Dumps (.sql.gz)

Most production backups are compressed with gzip because mysqldump output is highly compressible. The sed and mysql commands above work on uncompressed files, so you have two options for compressed dumps: decompress first, or stream through gzip directly.

Option A: Stream into the mysql client without touching disk

gunzip -c /var/backups/full_dump.sql.gz | mysql -u root -p myapp

zcat is equivalent and a little shorter:

zcat /var/backups/full_dump.sql.gz | mysql -u root -p myapp

Option B: Extract a single table from a compressed dump with a two-stage sed

You cannot pipe gunzip directly into sed if you want to redirect the output to a file, because sed writes the file while gunzip is still streaming. Use a here-doc pipe and an extra process:

gunzip -c /var/backups/full_dump.sql.gz | sed -n '/CREATE TABLE.*`orders`/,/CREATE TABLE/p' > /tmp/orders.sql

This works because sed reads from gunzip’s stdout and the final > happens after sed finishes processing. Verified on a 12 GB compressed dump on a production server.

Watch progress with pv

For very large restores, wrap the pipe in pv to see live throughput:

pv /var/backups/full_dump.sql.gz | gunzip | mysql -u root -p myapp

Restoring to a Different Table Name or Database

In production, you usually do not want to overwrite a live table directly. The safest pattern is to restore into a staging table, then compare and migrate rows.

Restore as a different table name with sed

sed -n '/CREATE TABLE.*`orders`/,/CREATE TABLE/p' /var/backups/full_dump.sql 
  | sed 's/`orders`/`orders_staging`/g' 
  > /tmp/orders_staging.sql

mysql -u root -p myapp < /tmp/orders_staging.sql

The second sed rewrites every backtick-quoted orders to orders_staging, including the CREATE TABLE line and any INSERT INTO orders statements.

Migrate rows in small, reviewable batches

-- In MySQL, after both tables exist:
INSERT INTO orders (id, customer_id, total, created_at)
SELECT id, customer_id, total, created_at
FROM orders_staging
WHERE id > (SELECT COALESCE(MAX(id), 0) FROM orders)
ON DUPLICATE KEY UPDATE
  customer_id = VALUES(customer_id),
  total       = VALUES(total),
  created_at  = VALUES(created_at);

This lets you run the migration, validate the results, then drop orders_staging when you are confident.

Restoring From Percona XtraBackup (Briefly)

If your backup was made with Percona XtraBackup rather than mysqldump, you are dealing with a physical backup, not a SQL file. The single-table restore workflow is different: you import the table’s .ibd and .cfg files using the DISCARD TABLESPACE and IMPORT TABLESPACE commands.

-- In MySQL, against the target database:
ALTER TABLE orders IMPORT TABLESPACE;
ALTER TABLE orders DISCARD TABLESPACE;

-- Copy the .ibd and .cfg files from the XtraBackup export dir:
-- /xtrabackup_export/orders.ibd
-- /xtrabackup_export/orders.cfg
-- into the MySQL data directory for myapp:
cp /xtrabackup_export/orders.ibd /var/lib/mysql/myapp/
cp /xtrabackup_export/orders.cfg /var/lib/mysql/myapp/
chown mysql:mysql /var/lib/mysql/myapp/orders.ibd /var/lib/mysql/myapp/orders.cfg

ALTER TABLE orders IMPORT TABLESPACE;

This only works when the table was originally backed up with XtraBackup’s --export option, which writes a transportable tablespace. The catch is that the import target must have a structurally identical table already created, so a normal CREATE TABLE orders (...) step from your schema migration runs first.

Troubleshooting Common Errors

This section fills the gap that nearly every competing guide leaves open: what to do when the obvious commands fail.

sed returns an empty file

The table name in your dump is wrapped in backticks, and your regex does not include them. Always include the backticks in the pattern:

# WRONG - matches nothing:
sed -n '/CREATE TABLE.*orders/,/CREATE TABLE/p' dump.sql

# RIGHT - matches `orders` literally:
sed -n '/CREATE TABLE.*`orders`/,/CREATE TABLE/p' dump.sql

Another common cause: case sensitivity. CREATE TABLE is uppercase but your target table name might not be. Use grep -i first to confirm the table name actually appears in the file.

Table not found after import

If the import says “table does not exist” but the file looks correct, check that the USE myapp; line was included in the extraction, or pass the database name explicitly to the mysql client. Most mysqldump files start with a USE statement; if you stripped it, you need to re-add it or run mysql -D myapp.

Foreign key constraint fails on import

This happens when the restored table references rows in another table that are not yet loaded. Wrap the import in the foreign-key-checks toggle, then re-enable the checks and validate:

mysql --init-command="SET FOREIGN_KEY_CHECKS=0;" -u root -p myapp < /tmp/orders.sql
mysql -u root -p -e "SET FOREIGN_KEY_CHECKS=1;" myapp
mysql -u root -p -e "CHECK TABLE orders;" myapp

Replication or GTID warnings on a master

If the server is a replication master, the dump file will contain a SET @@GLOBAL.GTID_PURGED line that can break replication if applied without coordination. Either run the restore on a slave or remove the GTID line with sed -i '/GTID_PURGED/d' /tmp/orders.sql before importing.

Frequently Asked Questions

Conclusion

You now have three working ways to restore a single table from a MySQL dump: the safe temp-database method, the fast sed extraction method, and the preventive per-table dump method. Pick the temp-database method when the dump is small and safety matters, the sed method when the dump is gigabytes and you need speed, and the per-table dump method as your default going forward so this is never a problem again.

The fastest practical workflow on a large production server is to extract with sed, verify with head and grep -c "INSERT INTO", then import with mysql. For compressed dumps, pipe gunzip -c into sed directly so you never decompress the whole file. If your backups come from XtraBackup, switch to the DISCARD TABLESPACE / IMPORT TABLESPACE workflow because mysqldump was never involved.

Next step: pick the method that matches your dump size, run a dry run on a staging server, then schedule per-table mysqldump jobs so the next single-table restore takes seconds, not minutes.