Damaged Kopano DB, partial restore with errors



  • 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


  • Translator

    Have you tried a higher level than 4?



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


  • Translator

    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.



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


  • Translator

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



  • 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


  • Translator

    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.


  • Kopano

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


  • Translator

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

    kopano-backup

    thanks, I’ll have a look.



  • 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


Log in to reply
 

Looks like your connection to Kopano Community Forum was lost, please wait while we try to reconnect.