Corrupt MySQL Database, Only Accessible With innodb_force_recovery=6

Question

MySQL 5.7 on Ubuntu 16.04 LTS

Database was corrupted after what I assume was an improper system shutdown, having the worst time trying to recover it. Previous system admin had no recent backups, somewhat necessary that I try everything I can to repair/recover the database.

MySQL server only runs with: innodb_force_recovery=6

mysqldump gives this error:

Couldn't execute 'SHOW VARIABLES LIKE 'gtid_mode'': Table 'performance_schema.session_variables' doesn't exist (1146)

Can't run mysql_upgrade with innodb_force_recovery=6

If you have any relevant questions, please ask.

Answer 1

At this point the only option is to extract data and re-create InnoDB database from the dump.

Quite often MySQL crashes when it hits a corruption in any of pages, so it's better to dump tables one by one.

Here's a script to dump tables individually that you can tweak for your specific case. It will restart MySQL if it crashes, and save a list of "bad" tables in failed.txt.

set -eux

edir=export

function ensure_mysqld() {
        mysql -e 'select 1' && return

        service mysql start 

        timeout=300
        while [[ $timeout -gt 0 ]]
        do
                mysql -e 'select 1' && return
                sleep 1
                timeout=$(( $timeout - 1 ))
        done
        echo "failed to start MySQL"
        exit 1
}

for d in $(cat databases)
do
        mkdir -p $edir/$d
        set +e
        ensure_mysqld
        set -e
        for t in $(mysql -S $socket -NBe "select TABLE_NAME from information_schema.TABLES WHERE TABLE_SCHEMA='$d' AND ENGINE = 'InnoDB' AND TABLE_TYPE = 'BASE TABLE'")
        do
                ensure_mysqld
                mysqldump --skip-lock-tables $d $t > $edir/$d/$t.sql || echo "$d.$t" >> failed.txt
        done
done