How to Recover a Corrupted MySQL InnoDB Database from Files on CentOS 7

Sometimes the MySQL InnoDB storage engine may be damaged due to an unexpected shutdown of the database server or for some other reasons. As a result, the MySQL service will not start. Here’s a way to resolve this problem and fix a corrupted MySQL database.

Note that we’ll explore a method to recover a MySQL database from files on Linux machines (CentOS 7).

1) Install the necessary software

cd /root/
yum install mysql-utilities-1.6.5-1.el7.noarch.rpm mysql-connector-python-2.1.7-1.el7.x86_64.rp

2) Copy the entire MySQL directory, make sure you have enough free disk space

mkdir /root/corrupt_mysql
cp -r /var/lib/mysql /root/corrupt_mysql/

3) Start MySQL

systemctl start mysql

If the database does not start, then gradually increase the innodb_force_recovery parameter from 2 and higher in /etc/my.cnf

Grant rights

mysql -e'grant all on . to frm@localhost identified by "MysqlFRMSecret678";'

4) Specify the name of the damaged database and restore the scheme

for file in $(ls /root/corrupt_mysql/mysql/${DATABASE}/*.frm); do mysqlfrm --server=frm:MysqlFRMSecret678@localhost:/var/lib/mysql/mysql.sock --diagnostic $file | grep -v "WARNING" >> /root/corrupt_mysql/${DATABASE}_create.sql ; done
sed -i 's##utf8#g' /root/corrupt_mysql/${DATABASE}_create.sql

5) Delete a damaged database

mysql -e'drop database ${DATABASE};'
rm -rf /var/lib/mysql/${DATABASE}/

6) Let’s comment innodb_force_recovery in the config and restart MySQL

systemctl restart mysql

7) Create a database and load the scheme

mysql -e'create database ${DATABASE};'
mysql --force ${DATABASE} < /root/corrupt_mysql/${DATABASE}_create.sql

8) Copy the table files from the previously copied directory with the damaged database and restore the data of each table, assign rights

for table in $(mysql -e 'show tables from ${DATABASE};' | grep -v "Tables_in"); do mysql ${DATABASE} -e"ALTER TABLE $table DISCARD TABLESPACE;"; done
for table in $(mysql -e 'show tables from ${DATABASE};' | grep -v "Tables_in"); do rsync -avzP /root/corrupt_mysql/mysql/${DATABASE}/$table.ibd /var/lib/mysql/${DATABASE}/ ; done
chown -R mysql:mysql /var/lib/mysql/
for table in $(mysql -e 'show tables from ${DATABASE};' | grep -v "Tables_in"); do mysql ${DATABASE} -e"ALTER TABLE $table IMPORT TABLESPACE;"; done

Restart MySQL

systemctl restart mysql

Check the data in the tables

Whether your MySQL database is corrupted during an upgrade or MySQL has determined that there are corrupted database tables, recovering mySQL database from files on CentOS 7 won’t be painstaking.

There are no comments yet. Be first.
Write a comment