Oct 22, 2019
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