MySQL DBA Tutorial - How to restore single table from full database backup in MySQL Server

Scenario:

You have used mysqldump to take the full backup of a database. The database has multiple tables. Now you need to restore only single table from full backup in MySQL Server, how would you restore single table in MySQL Server?

Solution:

First of all you will be separating the data for deleted table from full backup. You can set program to extract the data for the table you want to restore. The below command will extract the data for table and create new file. Once we have the data for single table, then we will restore.

shell > sed -n -e '/DROP TABLE.*`YourTableName`/,/UNLOCK TABLES/p' /SourceDirection/FullbackupName.sql > Extracted_YourTableData.sql

Let's say our table name is employee, then we will modify above as shown below.

shell > sed -n -e '/DROP TABLE.*`employee`/,/UNLOCK TABLES/p' /mysqlbackup/testdb.sql > Employee.sql 

Now you are ready to restore employee table to testdb.

shell > sudo mysql --user=root --password=DBA123 testdb< /mysqlbackup/Employee.sql

MysQL DBA Tutorial - Restore single table from full database backup in MySQL