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

mysql backup restore
Mobius picture Mobius · Jun 18, 2009 · Viewed 194.3k times · Source

I have a mysqldump backup of my mysql database consisting of all of our tables which is about 440 megs. I want to restore the contents of just one of the tables from the mysqldump. Is this possible? Theoretically, I could just cut out the section that rebuilds the table I want but I don't even know how to effectively edit a text document that size.

Answer

uloBasEI picture uloBasEI · Jun 18, 2009

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).

Related questions

How to take backup of a single table in a MySQL database?

By default, mysqldump takes the backup of an entire database. I need to backup a single table in MySQL. Is it possible? How do I restore it?

Read More
Restoring MySQL database from physical files

Is it possible to restore a MySQL database from the physical database files. I have a directory that has the following file types: client.frm client.MYD client.MYI but for about 20 more tables. I usually use mysqldump or a …

Read More
Speeding up mysql dumps and imports

Are there any documented techniques for speeding up mySQL dumps and imports? This would include my.cnf settings, using ramdisks, etc. Looking only for documented techniques, preferably with benchmarks showing potential speed-up.

Read More