Recovering corrupted mysql innodb database from files on Centos 7

As a result of an unexpected shutdown of the database server, or for other reasons, sometimes the MySQL InnoDB storage engine may be damaged, as a result of which the MySQL service will stop starting. You can solve this problem as follows.

1) Install the necessary software

cd /root/
wget https://dev.mysql.com/get/Downloads/MySQLGUITools/mysql-utilities-1.6.5-1.el7.noarch.rpm
wget https://dev.mysql.com/get/Downloads/Connector-Python/mysql-connector-python-2.1.7-1.el7.x86_64.rpm
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 that 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

DATABASE=brokendbname
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 schema

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