Procedure to reimport MySQL databases -- Core-Admin -- InnoDB hard unrecoverable failure

#1

1. Introduction

  1. In case MySQL does not starts and shows a similar error at your system log /var/log/syslog

    >> tail -f /var/log/syslog | grep mysqld
    Aug 12 02:53:57 node1 mysqld: InnoDB: Dump of the tablespace extent descriptor: len 40; hex 00000000003a744b0000400026beffffffff000000000001ffffffffffffffffffffffffffffffff; asc :tK @ & ;
    Aug 12 02:53:57 node1 mysqld: InnoDB: Serious error! InnoDB is trying to free page 104638
    Aug 12 02:53:57 node1 mysqld: InnoDB: though it is already marked as free in the tablespace!
    Aug 12 02:53:57 node1 mysqld: InnoDB: The tablespace free space info is corrupt.
    Aug 12 02:53:57 node1 mysqld: InnoDB: You may need to dump your InnoDB tables and recreate the whole
    Aug 12 02:53:57 node1 mysqld: InnoDB: database!
    Aug 12 02:53:57 node1 mysqld: InnoDB: Please refer to
    Aug 12 02:53:57 node1 mysqld: InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html

  2. And problem persists after starting MySQL server adjusting innodb_force_recovery = 4, then it is likely you will have no choice but reimport all databases.

For that, see next.

2. MySQL databases reimport with Core-Admin

  1. Do a full database dump after starting MySQL server with innodb_force_recovery = 4:

    >> mysqldump -R --triggers --opt --single-transaction -u root -p --all-databases > /var/backups/mysql-backups/all-databases.txt

    NOTE: save this file because it contains all your data

  2. Cleanup all references to mysql database at /var/backups/mysql-backups/all-databases.txt

    NOTE: with a text editor, search for “CREATE DATABASE mysql”, mark and search next CREATE DATABASE to remove the portion in between.

    IMPORTANT: it is very important to reimport everything but keeping your mysql and performance_schema
    (where all your MySQL users and passwords are located).

  3. Stop MySQL server, core-admin agent (to avoid having it startup up mysql in the middle of the process), postfix and apache2:

    >> /etc/init.d/postfix stop
    >> /etc/init.d/apache2 stop
    >> /etc/init.d/crad-agent stop
    >> /etc/init.d/mysql restart

  4. With MySQL server stopped, remove all databases but “mysql”:

    >> rm -rf `ls -d /var/lib/mysql/* | grep -v ‘/var/lib/mysql/mysql’`

  5. Edit your /etc/mysql/my.cnf and comment out recovery line. Also add indication
    to do database innodb file separation:

    # innodb_force_recovery = 4
    innodb_file_per_table=1

  6. Startup MySQL (it should start without error because it is empty: only mysql and information_schema databases):

    >> /etc/init.d/mysql start

  7. Now connect to MySQL as root:

    >> mysql --defaults-file=/etc/mysql/debian.cnf

  8. Reimport all databases with:

    mysql> . /var/backups/mysql-backups/all-databases.txt

  9. Run upgrade in case log reports:

    Aug 12 10:12:33 node1 mysqld: 190812 10:12:33 [ERROR] Native table ‘performance_schema’.‘events_waits_history’ has the wrong structure
    Aug 12 10:12:33 node1 mysqld: 190812 10:12:33 [ERROR] Native table ‘performance_schema’.‘events_waits_history_long’ has the wrong structure
    Aug 12 10:12:33 node1 mysqld: 190812 10:12:33 [ERROR] Native table ‘performance_schema’.‘setup_consumers’ has the wrong structure

    >> mysql_upgrade -u root -p

  10. Restart stopped services:

    >> /etc/init.d/crad-agent start
    >> /etc/init.d/apache2 start
    >> /etc/init.d/postfix start