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.

I have a situation with a particular table that now thinks it contains 4 Petabytes of data. I know that sounds cool, but I assure you, it is only on a 60GB partition.

This table has 9 fields in it. One of them is a domain_id field. It is the best field to identify the rows by, as there are only approximately 6300 of them. The only other field option to match has over 2million records, and thats just more difficult.

I cannot do a straight mysqldump because it will attempt to output all 4PB of data and fill the drive long before it gets close to that, so I need to surgically remove the good stuff, destroy the db, and recreate it.

I believe if I can do a dump for each domain_id record, then I will get most of the usable data out of it. This is what I am trying to use:

mysqldump -u root --skip-opt -q --no-create-info --skip-add-drop-table --max_allowed_packet=1000000000 database table --where="domain_id=10" > domains10.sql

Using this I expect every row with the domain_id 10 to be exported.

However, when I check the export, I am only getting 1 row, when however I look at the db, there are many many rows. It is as though the operator just finds one, then gives up.

I have tried various operators. Using the < or > I am able to get more of the data, but the export stops short at certain rows where the data has been compromised. With over 6000 to go through, I can't narrow down which rows are being affected in the export easily enough.

So, what I need is an operator that will basically do what I thought = would do, simply give me an export of all records that match the specific field.

Also note, the only way I got this DB even accessible is through an innodb force recovery 3. So I need to get this right, because after this is done, I have to drop the db in order to make mysql functional again.

Looking forward to any helpful answers.

share|improve this question
    
I have given up.. the more I tried the worse it got to access the info. I eventually had to drop it all, including the mysql install. I managed to get a dump of the other tables before all this though using an innodb forced recover 3 level. It is helpful to get data out of a db you otherwise couldn't even start. The mysterious 4 Petabyte table will now forever remain a mystery. –  JonathanLIVE Feb 19 '10 at 20:48
add comment

4 Answers 4

From what you write it appears that database has been corrupted (thinking 4PB instead of 60GB is sort of a giveaway).

I doubt you can get any assurance of retrieved information reliability, unless you repair db first. Have you tried this?

Otherwise, what happens if you do "-f" key -- to continue even if errors encountered?

share|improve this answer
    
-f didn't help. Same deal as before. This is an Innodb db, so repair isn't supported. –  JonathanLIVE Feb 19 '10 at 17:56
    
Well, to me it sounds like a case of corrupted database, I am afraid that the data is lost/inconsistent. Check out any database repair tools you might use (eg. databasejournal.com/features/mysql/article.php/10897_3300511_2/… ) –  Gnudiff Mar 31 '10 at 13:04
add comment

How big do you think the table should actually be ?

You could try converting it to myisam:

alter table ggg engine=myisam;

However, it sounds like you have a corrupted database.

Best plan might be to contact the innodb guys for support.

http://www.innodb.com/

share|improve this answer
add comment

I'm not a database admin, so maybe this idea is totally wrongheaded, but is there data in the dump that should be consistent in all the records with a text string? I wondered if it's possible for you to do a dump of your "4 petabyte" database and redirect it through a grep/strings filter so that if the data that's corrupted isn't a valid string, it won't get written to disk. This would depends on whether the corrupted data was just incomprehensible garbage though...

Otherwise someone else here will have to suggest a repair tool to try fixing the database.

share|improve this answer
    
This isn't an option as the db contains blob data, which is binary. Part of my problem is that I get so far and then I have an error like this come up: mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table table at row: 52739 When I start a different domain_id points, I will get different rows.. always consistent. I know this isn't a timeout issue like most would think from this error, as the timeout settings are set very high. –  JonathanLIVE Feb 19 '10 at 11:07
add comment

Try adding --skip-extended-insert. It's possible that things are getting mangled when writing to the file.

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.