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
add comment

2 Answers

up vote 5 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
add comment

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
add comment

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.