Damaged Kopano DB, partial restore with errors

olabre

Hi, thanks for taking care. The support request was sent by company IT25 in Leipzig already. The innodb repair was used as link indicates. I had to set to level 4 - after some fixing level 2 works. In the end myql lost connection when dumping. Ibdata1 file has 10 GB of size.
On dumping mysql connections dies on specific rows. I tried to exclude that rows when dumping using --where in mysqldump command. As there are more than one rows affected it is a puzzle games as I have to find out the matching hierachyID for affected rows. This request dies in phpmaydmin for some rows so I cannot get full dump. But in the end this way might work when I am able to use more than one hierachyID in a single mysqldump.
mysql.log and mysql.err is empty

Olaf

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