Restore single MySQL table from a full mysqldump backup file

Scenario:

You are working as MySQL/ MariaDB Admin, you have schedule daily full backup of a database. Once of the Junior DBA dropped the table from a database. You are asked to restore only single Table from full backup that were taking last night, What steps you will follow to restore Single Table from MySQL or MariaDB Backup?

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

For Demo : Check the video step by step - How to restore single table from full backup in MySQL or MariaDB.