Bad backup

[LeeS]

I've got a corrupt 2.5gb .sql backup (from mysqldump) which we need to use. I've managed to establish that the coruptness is in the attachment table (which is 1.5gb in size). Is there any (easier) way to remove the attachment table from the .sql file than using vim to delete the 15 million odd lines of that particular table?? :confused: :(

[Steve Machol]

Not that I'm aware of unfortunately.

[eva2000]

I've got a corrupt 2.5gb .sql backup (from mysqldump) which we need to use. I've managed to establish that the coruptness is in the attachment table (which is 1.5gb in size). Is there any (easier) way to remove the attachment table from the .sql file than using vim to delete the 15 million odd lines of that particular table?? :confused: :(
you have no original MYI/MYD/frm data files to work with ?

if not you'd need to manually remove the entries from a copy of this corrupted sql file (leaving the original corrupted file untouched as a safe backup in case you stuff up the editing)

[LeeS]

I've given up on the idea and resided to recovering and upgrading to vB3 a backup from last December. A bit depressing (300,000 posts lost), but on the bright side, 350,000 posts saved!

Remind me to buy some backup space from RackShack and make sure the damn backups work next time ... :rolleyes:

By the way, vB3 is coming along nicely. A few niggling niggles which I will write up when I get a moment, but it's coping quite nicely with our forums.

[eva2000]

I've given up on the idea and resided to recovering and upgrading to vB3 a backup from last December. A bit depressing (300,000 posts lost), but on the bright side, 350,000 posts saved!

Remind me to buy some backup space from RackShack and make sure the damn backups work next time ... :rolleyes:

By the way, vB3 is coming along nicely. A few niggling niggles which I will write up when I get a moment, but it's coping quite nicely with our forums.
a shame there, if you had even corrupted live database there could be a chance at salvaging it through mysql repair process.. would at best mean loosing some attachments or all attachments but posts and threads would be intact

[Dave#]

I've got a corrupt 2.5gb .sql backup (from mysqldump) which we need to use. I've managed to establish that the coruptness is in the attachment table (which is 1.5gb in size). Is there any (easier) way to remove the attachment table from the .sql file than using vim to delete the 15 million odd lines of that particular table?? :confused: :(
It will of course only be a single line begining

INSERT INTO attachment values

you could just vi the sql or use AWK to comment out that line

[rylin]

Using grep & head, grep & tail could've saved everything but the attachments stuff.. didn't notice this thread before :/

[LeeS]

This is all very well, but it takes 8 minutes just to open the file ... I tried a search in it and it hadn't finished it 15 minutes after it had started... it's massive. :(

[rylin]

Well,
here's what I'd do.
MySQLDumps are done in alphabetical ascending order (at least, my mysqldumps are)

From this, we know that eg. attachment comes before post etc.
let's say your backup is called forum.db.sql

here's (more or less) what you'd do:
grep -n -m 1 "CREATE TABLE attachment" forum.db.sql
-n is to show linenumber, -m 1 is just to make sure we only get the first match
this'll give us something like
232:CREATE TABLE attachment (
Now, we take the first 231 lines from the file, and output into a temporary file.
head -n 231 forum.db.sql > workcopy.sql

in vb2, the table after attachment is avatar.
in vb3, the table after attachment is attachmenttype (and attachmentviews), but we'll skip them and go for avatar anyway.

grep -n -m 1 "CREATE TABLE avatar" forum.db.sql
which gives us something like
327:CREATE TABLE avatar (
but now we can't use head, right? (we don't want from the top of the file to line, we want from line to bottom)

also, using tail immediately wouldn't help. we need to know exactly how many lines are in the file.
wc -l forum.db.sql
1806 forum.db.sql
So, 1806 - 327 = 1479.

tail -n 1479 forum.db.sql >> workcopy.sql

this should give you the entire mysqldump backup without the attachment* tables in the file workcopy.sql.

if you have any questions, feel free to PM me or icq me (4592259)

[Dave#]

This is all very well, but it takes 8 minutes just to open the file ... I tried a search in it and it hadn't finished it 15 minutes after it had started... it's massive. :(
don't use an editor use awk

something like

awk '{gsub("
INSERT INTO attachment values ", "#
INSERT INTO attachment values ", $0); print > FILENAME}' backup.sql

the above is untested but you get the general idea

[rylin]

don't use an editor use awk

something like

awk '{gsub("
INSERT INTO attachment values ", "#
INSERT INTO attachment values ", $0); print > FILENAME}' backup.sql

the above is untested but you get the general idea
No!
I already win!
Shoo! Away with you! :P

Anyway.. LeeS, let us know how it goes :)

[Dave#]

No!
I already win!
Shoo! Away with you! :P

Anyway.. LeeS, let us know how it goes :) um whatever

I actually think perl is the best way to do this in a single line

perl -i -pe 's/INSERT INTO attachment value/#INSERT INTO attachment values/g' backup.sql