Recovering MySQL with corrupted InnoDB files

Posted on by

InnoDB on MySQL server was corrupted. And it could be solved by removing database files and reload SQL data.

Steps to recover MySQL with corrupted InnoDB files

  1. Stop MySQL server
  2. Backup InnoDB files (i.e. ib*), I suggests backup whole /var/lib/mysql directory.
  3. Adding innodb_force_recovery = 4 to [mysqld] section at MySQL server config file (eg. /etc/mysql/mysql.conf.d/mysqld.cnf in Ubuntu 16.04)
  4. Start MySQL server.
  5. Export all data: # mysqldump -A > mysqldump-A-`date +%s`.sql
  6. Finding databases stored in InnoDB with SQL: SELECT table_schema FROM INFORMATION_SCHEMA.TABLES WHERE engine = ‘innodb’ group by table_schema;
  7. Drop all databases stored in InnoDB. If any database in InnoDB could be dropped (eg. lost connection to server), you need to remove all files at /var/lib/mysql directory according to next section.
  8. Stop MySQL server.
  9. Remove InnoDB files (i.e. ib*).
  10. Remove innodb_force_recovery line from MySQL server config file.
  11. Start MySQL database for normal operation, new InnoDB files should be created. Let’s check whether it is in normal operation.
  12. Restore databases: # mysql -p < mysqldump-A-nnnnnn.sql

Removing ALL database files to recover MySQL

  1. Ensure that you have all backup data and backup copy of database files from /var/lib/mysql.
    1. Backup all database: # mysqldump -A > mysqldump-A-`date +%s`.sql
  2. Stop MySQL server.
  3. Remove all files in /var/lib/mysql
  4. Re-initialize inital database files for MySQL, new unknown random password will be generated for root user account in MySQL, therefore I prepared a
    1. Create /tmp/chmysqlpass SQL file:
      1. ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘sammy.hk’;
        flush privileges;
    2. Re-initialize inital database files: # mysqld –initialize
    3. Stop running MySQL server manually using: killall -9 mysqld
    4. Use initial SQL file to change password at startup# mysqld –init-file=/tmp/chmysqlpass
    5. Try to use mysql client to login with new password (example password: sammy.hk)
    6. Stop running MySQL server manually using: killall -9 mysqld
  5. Start MySQL database for normal operation.
  6. Restore databases: # mysql -p < mysqldump-A-nnnnnn.sql

Reference

  1. https://chepri.com/blog/mysql-innodb-corruption-and-recovery/
  2. https://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html

Related posts

  • No related posts.