Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

So I have a 100gb mysql database dump. I need to extract a few tables from it so I can import them into an alternate database. At the moment, I have tried importing it into a temporary mysql instance but it fails when trying to import a particular table, saying it doesn't exist. I am not aware of any issues with the database, so the major discrepancy I can think of is the mysql version I am importing to is different to the mysql version running on the primary database (that the dump was taken from). Has anyone heard of a discrepancy like this presenting such symptoms? Could it also be a sign of some database corruption?

I still need the tables so I was considering making some manual changes to the file but vim really struggles when editing a 100gb text file. I was then thinking of just grepping for "CREATE TABLE 'X'", but if the table creation and import process occurs over more than one line, the output of my grep is not going to have all the data.

I suppose I will need to try finding a copy of the exact same mysql version to install and install it from source (i guess it would be possible), to rule out the issue, but then again it's only a stab in the dark. I am still quite perplexed as to how a working database can be dumped and whilst trying to be imported, notes a table doesn't exist. Any advice appreciated.

share|improve this question
1  
Have you tried using mysql_find_rows to extract from the dump only the create and insert you need? –  Guido Vaccarella Oct 31 '13 at 16:34

1 Answer 1

Assuming you're restoring to a newer version of MySQL, your database dump is likely damaged/corrupted/invalid. For the future, this is why you do test restores of all backups on a regular basis - a backup you can't restore from is worthless.
Hopefully this won't be a terribly painful lesson for you, and you can implement restore tests as part of your workflow later.

(If you're trying to restore to an older version of MySQL - well, you can't. There's a substantial chance of incompatibility because the older version may lack features used in the dump.)


For the present situation:

I was then thinking of just grepping for "CREATE TABLE 'X'",

That certainly sounds like a good idea (assuming you're using a text dump) -- determine if the table is actually being created by your dump.

but if the table creation and import process occurs over more than one line, the output of my grep is not going to have all the data.

It will if you tell it to. Read the manual page for grep, and pay particular attention to the -A and -B options (which control how much context grep prints After and Before the matching lines).

share|improve this answer

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.