Can I restore a single table from a full mysql mysqldump file?

Question

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

A simple solution would be to simply create a dump of just the table you wish to restore separately. You can use the mysqldump command to do so with the following syntax:

mysqldump -u [user] -p[password] [database] [table] > [output_file_name].sql

Then import it as normal, and it will only import the dumped table.

One way or another, any process doing that will have to go through the entire text of the dump and parse it in some way. I'd just grep for

INSERT INTO `the_table_i_want`

and pipe the output into mysql. Take a look at the first table in the dump before, to make sure you're getting the INSERT's the right way.

Edit: OK, got the formatting right this time.

1. Backup

   $ mysqldump -A | gzip > mysqldump-A.gz
   

2. Restore single table

   $ mysql -e "truncate TABLE_NAME" DB_NAME
   $ zgrep ^"INSERT INTO \`TABLE_NAME" mysqldump-A.gz | mysql DB_NAME
   

You should try @bryn command but with the ` delimiter otherwise you will also extract the tables having a prefix or a suffix, this is what I usually do:

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

Also for testing purpose, you may want to change the table name before importing:

sed -n -e 's/`mytable`/`mytable_restored`/g' mytable.sql > mytable_restored.sql

To import you can then use the mysql command:

mysql -u root -p'password' mydatabase < mytable_restore.sql