Saturday, 28 November 2009

Restore a Single Table From mysqldump

Restore a Single Table From mysqldump: "Ok, this is inspired by another post on the same subject. I would have just commented, but there was a required signup. Ew.


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.

PlanetMySQL Voting:
Vote UP /
Vote DOWN"

No comments:

Sike's shared items