How to Restore a Single MySQL Database or Table from a Full Dump

In many production environments a MySQL instance hosts multiple databases, and administrators usually perform a full backup that dumps all databases into a single file. Occasionally you need to restore only one database or even a single table, and this guide shows practical ways to do it.

1. Restoring a Single Database from a Full Dump

Two approaches are common:

Use the --one-database option of mysqldump . This option tells the restore process to load only the specified database, but it is prone to problems and is generally not recommended.

mysql -uroot -pxx -D db1 -o

Manually extract the desired database’s CREATE statements and data from the dump file and import them.

sed -n '/^-- Current Database: `db1`/,/^-- Current Database: `/p' all.dmp > db1.sql

Then import the extracted file:

mysql -uroot -pxx -D db1 < db1.sql

2. Restoring a Single Table from a Full Dump

This scenario often arises when a careless UPDATE without a WHERE clause corrupts an entire table. Instead of reloading the whole database, you can recover just the affected table.

Extract the table definition (CREATE statement):

sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `ecs_ugo_order_info`/!d;q' mysqldump_2017-05-23.sql > table_structure.sql

Extract the INSERT statements for the table’s data:

grep -i 'INSERT INTO `ecs_ugo_order_info`' mysqldump_2017-05-23.sql > data.sql

Create the table in the target database and load the data:

mysql -uroot -pxxx target_db < table_structure.sql

mysql -uroot -pxxx target_db < data.sql

Generate UPDATE statements to restore the original order_status values (example for recovery):

mysql&gt; SELECT CONCAT('update xx.ecs_ugo_order_info set order_status=',order_status,' where order_id=',order_id,';') FROM ecs_ugo_order_info INTO OUTFILE '/tmp/ecs_ugo_order_info_recovery.sql';

Sample output lines:

update xx.ecs_ugo_order_info set order_status=6 where order_id=3254778;

update xx.ecs_ugo_order_info set order_status=6 where order_id=3254824;

update xx.ecs_ugo_order_info set order_status=6 where order_id=3254870;

Apply the generated UPDATE script to the production database:

mysql -uroot -pxxx production_db < /tmp/ecs_ugo_order_info_recovery.sql

These steps let you recover only the needed database or table, saving time and avoiding unnecessary data processing.