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.

I want to import 3 specific tables from a dump file that contains 270 tables.

The dump file, naveen_ifs_db.sql, is 20 GB.

How do I restore 3 tables from that dump-file using the command line in windows?

share|improve this question
    
You could restore the dump into a new database, drop all the tables you don't need from the new database, then dump the new database and restore the new dump against the original database. –  Max Vernon Sep 27 '12 at 5:05
add comment

1 Answer

Unless some unusual options are used when it is invoked, the files produced by mysqldump contain the raw sql statements needed to drop and recreate your tables as they existed at the time the dump was done, uncompressed, and human-readable. They can generally be manipulated with a text editor... although finding a text editor that works well with 20GB files is another matter.

But restoring an individual table essentially requires extracting the lines you want, and piping those statements into the server via the mysql CLI. Of course, I would not recommend that you do this on your production server without testing elsewhere, first.

It is also possible that there are dependencies in your schema that may make restoring a single table much more difficult... such as foreign keys referencing the table. You might have to modify the resulting SQL file to get the data back in, but this situation would exist even if you had a mysqldump file of the individual tables you're needing to restore.

But for the task at hand, the first two tools that came to mind are Perl and sed, both of which are free and available for Windows. Either one should do what you need.

We can find the range of lines we're interested in by displaying the line numbers of strategically-placed SQL comments mysqldump adds to the file:

K:\backup>perl -n -e "print \"$. $_\" if (/^--\s+.*for\stable\s`/ || /^--.*Database/)" backup_file.sql 

Output:

3 -- Host: localhost    Database:
19 -- Current Database: `mediawiki`
27 -- Table structure for table `archive`
56 -- Dumping data for table `archive`
       ...
272 -- Table structure for table `image`
302 -- Dumping data for table `image`
312 -- Table structure for table `imagelinks`
327 -- Dumping data for table `imagelinks`
       ...

The numbers at the left are line numbers in the dump file (we just now calculated them). So, if we wanted to restore mediawiki.image we need lines 272 through 301 to drop and re-create the table ... and 302 through 311 to restore the data.

K:\backup>perl -n -e "print $_ if ($. >= 272 && $. <= 311)" backup_file.sql > my_new_file.sql

Examine my_new_file.sql's content for sanity:

You should find, in order: DROP TABLE, CREATE TABLE, LOCK TABLES your_table WRITE, INSERT INTO your_table VALUES (all_your_data), and UNLOCK TABLES.

Once you're satisfied with the content of the file, you can pipe it to the server via the command line interface:

K:\backup>mysql [normal CLI options here] target_database < my_new_file.sql

If you wanted to use sed instead of Perl, count the lines with:

K:\backup>sed = backup_file.sql | sed "N;s/\n/\t/" | sed -n "/-- Table structure for/p;/-- Dumping data for/p;/-- Current Database/p" 

(My sed skills are a little soft. This could probably be written better and/or to run faster, but it does work as written.)

Then extract the lines you want with:

K:\backup>sed -n 272,311p backup_file.sql > my_new_file.sql

Of course, these work on Linux, too, but the outer double-quotes would be single quotes, and the inner double-quotes in the first Perl example wouldn't be backslash-escaped.

If you have sed or Perl installed, you may also need to add them to your path, or replace "sed" or "perl" in the above examples with fully-qualified pathnames (with the quotes as shown).

"C:\Program Files (x86)\GnuWin32\bin\sed.exe"
"C:\Perl64\bin\perl.exe"
share|improve this answer
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.