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