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

Question

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.

Solution 1

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

Solution 2

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

Solution 3

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

Solution 4

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.

Solution 5

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.

View more solutions

Comments

[Bill Karwin]

FWIW, you could also use mydumper. This creates a logical dump like mysqldump, but it outputs separate files per table, and it can do both the dumping and the loading multi-threaded, so it takes less time.

[JCCyC]

If you think you love grep, when you learn awk you'll become its happy sex slave: en.wikipedia.org/wiki/Awk

[JCCyC]

That's even nicer than my answer since it takes care of the CREATE TABLE too, but you should search with the backquotes so as not to get another table called "thisismytabletoo".

[uloBasEI]

True. I was not sure if the table names in all mysql dumps are always surrounded by backquote or if "CREATE TABLE mytable" could also be possible. We can easily adapt the first regexp if we know how the dump looks like. A second problem could be if the table mytable is not unique (one in the database db1, and another one in the database db2). Both will be exported in the file mytable.dump. If the table is not unique, we can use the same sed command, first with CREATE DATABASE in order to extract only the right database. Then, use the sed command with CREATE TABLE.

[Young L.]

Yep, JCCyC. This thing did exactly what I needed with zero monkeybusiness. Thank you and uloBasEl so much!

[Nathan]

Sweet! Remember to add DROP TABLE at the top and remove the DROP TABLE [next table] at the bottom of the file.

[Olexa]

For not adding/removing DROP TABLE, it is more useful to match by Table structure for table comment, instead of matching by CREATE TABLE. This will ensure that next table would not be dropped if one forgets to remove its DROP TABLE statement, and allows piping for single-command table restore (gzip | sed | mysql). However, this solution is dependent on mysqldump comment syntax (I don't know how standard it is).

[deeenes]

With Olexa's modification it's perfect: sed -n -e '/Table structure for.*`mytable/,/Table structure for/p' whole.sql > mytable.sql

[rICh]

this is actually a much better solution than the one chosen as the answer, above. Can't believe it didnt' get more upvotes.

[kmarks2]

Brilliant. Really quite nice.

[bartekbrak]

I suggest adding at least backtick quotes to the table name: `mytable` to avoid matching tables mytable2 and so on as was in my case.

[Elijah Lynn]

Maxim, it didn't work because there was no source file in the example nor stdout and destination file. I edited the example to include these (mydump.sql > tabledump.sql) and it should work now.

[Elijah Lynn]

This answer is better, it includes the DROP TABLE => stackoverflow.com/a/15857815/292408

[user706420]

for me sed give wrong file format, must be utf8 but is ansi. text is damaged.

[Gabriel Alack]

you just saved my ass

[bryn]

@user706420 Hmm, are you certain your terminal wasn't to blame? sed shouldn't interfere with encoding...

[d.sergeiev]

@rICh this answer is a bit more difficult to understand and it requires good sql dump strucutre. Also, if you want add DROP and other additions, you can derive this from uloBasEl's answer easily.

[sjas]

This is what most people need.

[S.L. Barth]

According to this edit suggestion, you should add the "--one-database" parameter, to make sure you restore only one database and don't destroy everything else.

[hamx0r]

For those with SQL files not containing the DROP TABLE (my MySQL dump did not have this) may want to use:sed -n -e '/-- Table structure for table ``<Table Name>/,/UNLOCK TABLES/p' <SQL File> > table.sql This makes use of the table comments provided before each table in a MySQl dump, and ensures lines like /*!40101 SET @saved_cs_client = @@character_set_client */; get included.

[jotrocken]

Remember to add a closing backtick ` after the tablename, otherwise all tables with the same prefix will be matched.

[gunzapper]

It works fine also for postgres $ sed -n -e '/COPY.*mytable/,/COPY/p' pg.dump > mytable.dump - Thanks!

[Enyby]

For really huge db it is can very weird - restore 150 GB of data for one table of 100 MB.

[hamx0r]

Thanks @WestonGanger. t I believe you are correct. The command should be sed -n -e '/-- Table structure for table `<Table Name>`/,/UNLOCK TABLES/p' <SQL File> > table.sql

[Andrey]

Very bad practice! I had few DBs in dump, and trying to restore only one, deleted all others.

[Karl Johan Vallner]

This is actually really easy to do and understandable even by beginners. I do not know, why this is downvoted.

[Kay]

Anone know how i can swap mytable for a variable in bash? ``` export VAR=my_table sed -n -e '/CREATE TABLE.*$VAR/,/Table structure for table/p' backup ```

[CodeMantle]

Except that the OP asked about restoring a single table from a single database dump, not restoring a single database from an 'all databases' dump?

[TheSatinKnight]

that imports everything related to that database from the sql file. This question was about extracting a single table from a full database dump. useful ... but not related to this question