Home Can I restore a single table from a full mysql mysqldump file?
Reply: 19

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

Mobius
1#
Mobius Published in 2009-06-18 16:50:32Z

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.

sjas
2#
sjas Reply to 2016-08-10 09:55:09Z

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

sjas
3#
sjas Reply to 2016-08-10 09:55:33Z

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
Chris Forrence
4#
Chris Forrence Reply to 2013-04-25 12:58:25Z

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

Brom558
5#
Brom558 Reply to 2013-08-08 09:34:24Z

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.

JCCyC
6#
JCCyC Reply to 2009-06-18 16:57:00Z

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.

Maxime Biette
7#
Maxime Biette Reply to 2013-09-18 12:40:00Z

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
vicvicvic
8#
vicvicvic Reply to 2015-02-23 11:10:33Z
  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
    
Tim Hoolihan
9#
Tim Hoolihan Reply to 2009-06-18 16:57:48Z

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.

user1097790
10#
user1097790 Reply to 2011-12-14 12:41:20Z

This tool may be is what you want: tbdba-restore-mysqldump.pl

https://github.com/orczhou/dba-tool/blob/master/tbdba-restore-mysqldump.pl

e.g. Restore a table from database dump file:

tbdba-restore-mysqldump.pl -t yourtable -s yourdb -f backup.sql

peter
11#
peter Reply to 2014-01-02 14:45:26Z

This may help too.

# mysqldump -u root -p database0 > /tmp/database0.sql
# mysql -u root -p -e 'create database database0_bkp'
# mysql -u root -p database0_bkp < /tmp/database0.sql
# mysql -u root -p database0 -e 'insert into database0.table_you_want select * from database0_bkp.table_you_want'
Bryan Migliorisi
12#
Bryan Migliorisi Reply to 2009-06-18 16:58:53Z

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.

mqqn
13#
mqqn Reply to 2010-02-28 15:59:07Z

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?

mtoloo
14#
mtoloo Reply to 2015-02-02 07:28:34Z

You can use vi editor. Type:

vi -o mysql.dump mytable.dump

to open both whole dump mysql.dump and a new file mytable.dump. Find the appropriate insert into line by pressing / and then type a phrase, for example: "insert into `mytable`", then copy that line using yy. Switch to next file by ctrl+w then down arrow key, paste the copied line with pp. Finally save the new file by typing :wq and quite vi editor by :q.

Note that if you have dumped the data using multiple inserts you can copy (yank) all of them at once using Nyy in which N is the number of lines to be copied.

I have done it with a file of 920 MB size.

Cameron
15#
Cameron Reply to 2009-06-18 17:04:42Z

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.

Kuyenda
16#
Kuyenda Reply to 2009-06-18 18:24:02Z

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.

JaredC
17#
JaredC Reply to 2012-11-09 16:11:50Z

Back in '08 I had a need to do this too. I wrote a Perl script that'll do it, and it's now my method of choice. Also summarized how to do it in awk or how to restore elsewhere and extract. Recently I added this sed method to the list as well. You can find the script and the other methods here: http://blog.tsheets.com/2008/tips-tricks/extract-a-single-table-from-a-mysqldump-file.html

Community
18#
Community Reply to 2017-05-23 12:18:23Z

I tried a few options, which were incredibly slow. This split a 360GB dump into its tables in a few minutes:

How do I split the output from mysqldump into smaller files?

Weston Ganger
19#
Weston Ganger Reply to 2016-07-12 16:59:22Z
sed -n -e '/-- Table structure for table `my_table_name`/,/UNLOCK TABLES/p' database_file.sql > table_file.sql

This is a better solution than some of the others above because not all SQL dumps contain a DROP TABLE statement. This one will work will all kinds of dumps.

Jad Chahine
20#
Jad Chahine Reply to 2017-01-17 12:24:04Z

The 'sed' solutions mentioned earlier are nice but as mentioned not 100% secure

  • You may have INSERT commands with data containing: ... CREATE TABLE...(whatever)...mytable...

  • or even the exact string "CREATE TABLE `mytable`;" if you are storing DML commands for instance!

(and if the table is huge you don't want to check that manually)

I would verify the exact syntax of the dump version used, and have a more restrictive pattern search:

Avoid ".*" and use "^" to ensure we start at the begining of the line. And I'd prefer to grab the initial 'DROP'

All in all, this works better for me:

sed -n -e '/^DROP TABLE IF EXISTS \`mytable\`;/,/^UNLOCK TABLES;/p' mysql.dump > mytable.dump
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.406066 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO