How to restore a single table in MySQL

Follow

Sometimes you wouldn't need a whole database to be restored, just a table. This is how you would restore just a single table.

In this example, I'll be restoring a table called 'users' in the database business_db, from a daily backup created the morning of the request. 

1. Locate backup

cd /home/r1softtemp/

tar -zxvf cpmove-business.tar.gz cpmove-business/mysql

The files are now extracted and ready to go

2. Create temporary database so we can import what we need.

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

CHECK THE SQL FILE BEFORE DOING STEP 3 - DON'T DO IF IT HAS:
DROP DATABASE IF EXISTS `business_db`;
IT'LL WIPE OUT THE RUNNING DATABASE!

3. Import yesterdays backup

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

4. Dump just the table we need

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

5. Import just that table into the live database

mysql business_db < users.sql

6. Clean up after yourself

rm -rf cpmove-business users.sql

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

Have more questions? Submit a request

Comments

Powered by Zendesk