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 form 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 1

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.

Answer 2

One possible way to deal with this is to restore to a temporary database, and dump just that table from the temporary database. Then use the new script.

Answer 3

Most modern text editors should be able to handle a text file that size, if your system is up to it.

Anyway, I had to do that once very quickly and i didnt have time to find any tools. I set up a new MySQL instance, imported the whole backup and then spit out just the table I wanted.

Then I imported that table into the main database.

It was tedious but rather easy. Good luck.

Answer 4

Get a decent text editor like Notepad++ or Vim (if you're already proficient with it). Search for the table name and you should be able to highlight just the CREATE, ALTER, and INSERT commands for that table. It may be easier to navigate with your keyboard rather than a mouse. And I would make sure you're on a machine with plenty or RAM so that it will not have a problem loading the entire file at once. Once you've highlighted and copied the rows you need, it would be a good idea to back up just the copied part into it's own backup file and then import it into MySQL.

Answer 5

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/,/CREATE 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).

Answer 6

The chunks of SQL are blocked off with "Table structure for table my_table" and "Dumping data for table my_table."

You can use a Windows command line as follows to get the line numbers for the various sections. Adjust the searched string as needed.

find /n "for table `" sql.txt

The following will be returned:

---------- SQL.TXT

[4384]-- Table structure for table my_table

[4500]-- Dumping data for table my_table

[4514]-- Table structure for table some_other_table

... etc.

That gets you the line numbers you need... now, if I only knew how to use them... investigating.

Answer 7

wouldn't it be much easier to just create a test database, restore the whole database, then dump the single table to a file - then restore the data to your "production" database?

Answer 8

My database's size is 96GB so it is not possible to load whole dump to temp database and export one table from it.

pls give me other solution if possible

really need help

thanks in advance

Answer 9

I assume this will help: http://tinyurl.com/KzDumpSplitter