How to: Restore a single table in MySQL

Customers with a Professional Services Agreement who utilise Backup Services with Servers Australia will have their Databases exported from cPanel and backed up nightly. However, sometimes you may not require a whole database to be restored, you may just need a table. The following process shares the steps to restore a single table.

In the example below, we will be restoring a table called 'users' in the database 'business_db' from a backup of the sites database.

1. Through an SSH Connection to your server, locate the backup file captured

cd /home/r1softtemp/
tar -zxvf cpmove-business.tar.gz cpmove-business/mysql

The files will now be extracted, ready for use.

2. From here, we'll create a temporary database so we can import the table that is required.

mysql
mysql> CREATE database TEMP_SAU;
Query OK, 1 row affected (0.00 sec)
mysql> exit
Bye

NOTE: It is important to check the SQL File before continuing.

Do not proceed if it contains 'DROP DATABASE IF EXISTS `business_db`; It will wipe out the running database.

3. Import the backup database extracted earlier into the temporary database we created

mysql TEMP_SAU < cpmove-business/mysql/business_db.sql

4. Run a 'mysqldump' on the table that is required

mysqldump --add-drop-table TEMP_SAU users > users.sql

5. With that table extracted into its own SQL file, we can import that into the live database 'business_db'

mysql business_db < users.sql

6. Once everything is confirmed done, we can clean up the restored files and temporary database created from earlier

rm -rf cpmove-business users.sql

mysql
mysql> drop database TEMP_SAU;
Query OK, 199 rows affected (1.92 sec)