Can I Restore A Single Table From A Full Mysql Mysqldump File?

Answer :

You can try to use sed in order to extract only the table you want.

Let say the name of your table is mytable and the file mysql.dump is the file containing your huge dump:

$ sed -n -e '/CREATE TABLE.*`mytable`/,/Table structure for table/p' mysql.dump > mytable.dump 

This will copy in the file mytable.dump what is located between CREATE TABLE mytable and the next CREATE TABLE corresponding to the next table.

You can then adjust the file mytable.dump which contains the structure of the table mytable, and the data (a list of INSERT).

I used a modified version of uloBasEI's sed command. It includes the preceding DROP command, and reads until mysql is done dumping data to your table (UNLOCK). Worked for me (re)importing wp_users to a bunch of Wordpress sites.

sed -n -e '/DROP TABLE.*`mytable`/,/UNLOCK TABLES/p' mydump.sql > tabledump.sql 

This can be done more easily? This is how I did it:

Create a temporary database (e.g. restore):

mysqladmin -u root -p create restore

Restore the full dump in the temp database:

mysql -u root -p restore < fulldump.sql

Dump the table you want to recover:

mysqldump restore mytable > mytable.sql

Import the table in another database:

mysql -u root -p database < mytable.sql