Damaged Kopano DB, partial restore with errors

mcostan

Have you tried a higher level than 4?

olabre

I am not really sure but I think I had to use 5 once to get it started in the first step.

mcostan

I experienced database crashes and corruptions in the past and I always was able to resolve it by using the lowest innodb recovery setting that would let me have a full dump.

Do you still have the original corrupted database? If yes, you could increase the innodb recovery settings till you are able to get a full dump without mysql to stop.

olabre

This is what I did starting with 1 - none worked except 5 - after this I was able to get back to 2

mcostan

and if you let it at 5 and get a mysqldump would you have been able to get a full dump?

olabre

this is the result:
mysqldump --user=root -p"$(cat /etc/mysql.secret)" kopano --skip-extended-insert | gzip > dumpfilename_kopano_170907.sql.gz

mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table properties at row: 2505854

then I use aquire the hierachyID for that row using SELECT * FROM properties LIMIT 2505854, 1 and exclude this in the dump query as follows, hierarchyid is 230503

mysqldump --user=root -p"$(cat /etc/mysql.secret)" kopano properties --where “hierarchyid!=230503” | gzip > dumpfilename_kopano_prop_170907_1.sql.gz

Result: mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table properties at row: 3796811

I query: SELECT * FROM properties LIMIT 3796811, 1
this call will come back with connection lost

mcostan

it looks like a bad crash… I am sorry if you tried the possible highest innodb recovery options and you still get the corruption then I am afraid I am out of ideas.

In general (once this is resolved) I would use a script (I think I found it previously on the zarafa forums or guides or something) that dumps the database every night automatically and keeps it for a number of days, you can run this twice a day (which I do).

I also go one extreme further and turn off the whole thing during the night and take a dump of what’s in /var/lib/msyql + a copy of the entire VM that runs the database.

The system is therefore down for like 30 minutes overnight.

Perhaps a bit extreme, but the worse that can happen if everything is corrupted is that I lose 1/5 day of emails or at worse a day.

fbartels

@mcostan you should have a look at kopano-backup to complement your backup strategy.

mcostan

@fbartels said in Damaged Kopano DB, partial restore with errors:

kopano-backup

thanks, I’ll have a look.

olabre

well yes, the backup was next on our task list - the way is clear either VM snapshot or full backup or mysql dump - but we did not get that far