Recover corrupted database



  • The MySQL database running in an old Ubuntu 14.04 seems corrupted as successive sequence numbers don't match.

    220127 15:16:11  InnoDB: Error: page 14357 log sequence number 41558915039
    InnoDB: is in the future! Current system log sequence number 41501397566.
    InnoDB: Your database may be corrupt or you may have copied the InnoDB
    InnoDB: tablespace but not the InnoDB log files. See
    InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
    InnoDB: for more information.
    220127 15:16:11  InnoDB: Error: page 15958 log sequence number 41554278818
    InnoDB: is in the future! Current system log sequence number 41501397566.
    InnoDB: Your database may be corrupt or you may have copied the InnoDB
    InnoDB: tablespace but not the InnoDB log files. See
    InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
    InnoDB: for more information.
    220127 15:16:11  InnoDB: Error: page 348 log sequence number 41549579314
    InnoDB: is in the future! Current system log sequence number 41501397566.
    InnoDB: Your database may be corrupt or you may have copied the InnoDB
    InnoDB: tablespace but not the InnoDB log files. See
    InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
    InnoDB: for more information.
    

    Already looked up to the provided link but it's not very useful to solve the problem. Any hints ?



  • Yeah, your DB got corrupted.

    A couple of things might have caused this, 1 is if you moved the tables from one place to another without the innodb log files, as stated in the output, the other reason is a plain corruption due to hardware error = disk.

    Now, the first thing to do here is:

    1. Restore from backup? but I assume you don't have.
    2. Enforce the innodb recovery by adding this to your my.cnf file

    innodb-force-recovery = 6

    1. Then, try to restart the DB, you will see a lot of garbage on the screen.
    2. If it finished correctly, then try to connect to the DB
    3. If you were able to connect, then stop it, remove the enforce parameter from the config file, then move the obdadta*, ib_logdata* and db directories out of the folder:
    cd /var/lib/mysql
    mv ibdata* ib_logfile* dbname ... /backup_location/
    
    1. Start your DB normally, this will generate all the innodb files again.

    If all this doesn't work, then it is seriously damaged, but the sequence is something that the enfoce will usually fix.

    Quick edit here to ensure a better procedure.

    1. Backup the whole data directory before the procedure.
    tar -xzvf mysql.tar.gz /var/lib/mysql
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2