wiki:howto/MysqlRepair

Corrupte InnoDB database

Dit type database heeft geen repair functie, daar deze een journal heeft. Repairen is normaal niet nodig.

De database kan echter wel corrupt raken door defecte hardware en harddisken. Let vooral op als u S.M.A.R.T. errors krijgt!

Indien u database niet meer start door een InnoDB corruptie ziet u de volgende meldingen in de logs

# less /home/system/mysql5/darkstar.err
InnoDB: Page checksum 673831230, prior-to-4.0.14-form checksum 3511409578
InnoDB: stored checksum 343549968, prior-to-4.0.14-form stored checksum 2720139525
InnoDB: Page lsn 0 3852576933, low 4 bytes of lsn at page end 67108992
InnoDB: Page number (if stored to page already) 3,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 0
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 3.
InnoDB: You may have to recover from a backup.
InnoDB: It is also possible that your operating
InnoDB: system has corrupted its own file cache
InnoDB: and rebooting your computer removes the
InnoDB: error.
InnoDB: If the corrupt page is an index page
InnoDB: you can also try to fix the corruption
InnoDB: by dumping, dropping, and reimporting
InnoDB: the corrupt table. You can use CHECK
InnoDB: TABLE to scan your table for corruption.
InnoDB: See also InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html
InnoDB: about forcing recovery.
InnoDB: Ending processing because of a corrupt database page.
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 3.
InnoDB: You may have to recover from a backup.
101210 12:40:17  InnoDB: Page dump in ascii and hex (16384 bytes):

Handmatig de database herstellen

We volgen de instructies op http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html.

  • Zet de database uit en alle services die deze gebruiken
# svc -d /service/mysql
# svc -d /service/zarafa-server
  • Voeg innodb_force_recovery = 4 toe en herstart database in recovery mode.
    # mcedit /etc/my.cnf
    # svc -u /sevice/mysql
    # less /home/system/mysql5/darkstar.err
    
    101210 12:40:17  InnoDB: Started; log sequence number 0 4081822057
    InnoDB: !!! innodb_force_recovery is set to 4 !!!
    
  • Nu kunt u de database dumpen zonder dat de mysql server crashed met een error. Indien de server nog steeds crashed, stel dan een hogere innodb_force_recovery in. (Zie instructies op mysql website)
# mysqldump zarafa -p`cat /etc/my.passwd ` > zarafa.sql
# less /home/system/mysql5/darkstar.err
  • Zet de database uit en verplaats de innodb data files. Zorg dat u zeker weet dat er geen andere InnoDB databases zjin dan Zarafa
    # svc -d /sevice/mysql
    # cd /home/system/mysql5
    # mkdir ../old
    # mv ib* ../old
    # mv zarafa ../old
    
  • Zet de recovery optie weer uit, start de database weer op, maak de zarafa database opnieuw aan en herimporteer de data
    # mcedit /etc/my.cnf
    # svc -u /sevice/mysql
    # mysqladmin create zarafa -p`cat /etc/my.passwd `
    # less /home/system/mysql5/darkstar.err
    InnoDB: The first specified data file ./ibdata1 did not exist:
    InnoDB: a new database to be created!
    101210 12:47:20  InnoDB: Setting file ./ibdata1 size to 10 MB
    InnoDB: Database physically writes the file full: wait...
    101210 12:47:20  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
    InnoDB: Setting log file ./ib_logfile0 size to 5 MB
    InnoDB: Database physically writes the file full: wait...
    101210 12:47:20  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
    InnoDB: Setting log file ./ib_logfile1 size to 5 MB
    InnoDB: Database physically writes the file full: wait...
    InnoDB: Doublewrite buffer not found: creating new
    InnoDB: Doublewrite buffer created
    InnoDB: Creating foreign key constraint system tables
    InnoDB: Foreign key constraint system tables created
    101210 12:47:21  InnoDB: Started; log sequence number 0 0
    # cd /home
    # mysql -p`cat /etc/my.passwd ` zarafa <zarafa.sql
    
  • Nu is uw database weer normaal bruikbaar. Start zarafa op en controleer het resultaat.
Last modified 7 years ago Last modified on 12/10/10 13:09:02