Last Updated: February 25, 2016
·
5.975K
· dmtrs

Restore a Single Table From mysqldump

This is a copy of the Restore a Single Table From mysqldump blog post by gtowey

While the method there with juggling grants works fine, I'd like to highlight another way that I think is much simpler: use grep & sed to create a file which contains only the table you want to restore.

mysql> show tables;
+------------------+
| Tables_in_gtowey |
+------------------+
| t1               |
| t2               |
| t3               |
+------------------+
3 rows in set (0.00 sec)


# mysqldump gtowey > dump.sql

Given 3 tables in a mysqldump file, lets restore just t2

# grep -n 'Table structure' dump.sql
19:-- Table structure for table `t1`
40:-- Table structure for table `t2`
61:-- Table structure for table `t3`

Now just use the line numbers to extract the table with sed:

sed -n '40,61 p' dump.sql > t2.sql

That's it, you have everything you need with no cleanup of mysql grants to do afterward.