Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

What is the best way to restore a single table from a large (~5GB) database mysqldump file?


update: I have found solutions (posted below) using command line tools to parse the table, but is there a way to do this with mysqlimport?

share|improve this question
    
Create a new user with access to only that table and then restore it using the newly created user with --force parameter. –  shantanuo Mar 13 '12 at 1:42

2 Answers 2

up vote 6 down vote accepted

I have found two solutions, one using

grep -n "Table Structure" mydump.sql
# identify the first and last line numbers (n1 and n2) of desired table
sed -n n1,n2p mydump.sql > mytable.sql # (e.g. sed -n 48,112p)

and one using awk

awk '/Table Structure for table .table1./, /Table structure for table .cultivars./{print}' mydump.sql > mytable.sql
share|improve this answer
    
Many sysadmins that have to manhandle mysql thrive on stuff like this. +1 !!! –  RolandoMySQLDBA Mar 9 '12 at 22:34
    
Neither of these work. The sed solution yields an error: sed: -e expression #1, char 6: unknown command: ',', and the awk solution yields an empty file. –  CaptSaltyJack Jun 19 '13 at 16:11
    
Correct sed command is: sed -n n1,n2p mydump.sql > mytable.sql (e.g. sed -n 48,112p) –  CaptSaltyJack Jun 19 '13 at 16:19
    
@CaptSaltyJack thanks for the clarification I have updated my answer –  David Jun 19 '13 at 16:22
    
FYI -- at least with mysql Ver 14.14 Distrib 5.6.14 -- the correct grep string is actually "Table structure" (lowercase s), also fwiw, I used ack instead of grep (requires you to create an empty file so you can ack 2 separate files in order to get the line numbers displayed), and it took 69 seconds vs 866 with grep on a 52gb database dump (I had to benchmark it after I realized it was much faster). –  Kem Mason 10 mins ago

This approach does not get around the issue of having to import a large mysqldumpfile, but from within Mysql you can do the following:

  1. restore entire dump

     mysql fakedb < mydump.sql
    
  2. delete contents of current table

     mysql
     delete from production.target_table;
    
  3. insert from backup table

    insert into production.target_table select * from fakedb.targettable;
    
share|improve this answer
1  
For step1, make sure DROP DATABASE is not included. Otherwise, your way works for smaller databases. +1 !!! –  RolandoMySQLDBA Mar 9 '12 at 22:34

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.