It is not easy to restore database if the database corrupt. Recently one of my client have problem with the database corruption in Cpanel. The website trowing error related with database. We check our daily MySQL backup and found there is no data in the client MySQL database. To make thing worst our weekly and monthly backup also doesn’t have the database. According to client the last access to the website is in March.
Our daily MySQL backup does not contain data in the SQL query. This indicate data corruption.
We try to repair it using below command, but none helps.
mysqlcheck --auto-repair --optimize databasename find /var/lib/mysql/databasename -name "*.MYI" -exec myisamchk -r -f {} \; find /var/lib/mysql/databasename -name "*.ibd" -exec innochecksum {} \; mysqlcheck --repair --use-frm databasename
After researching in the Internet we found that we can reconstruct the database from *.frm files using MySQL utilities called mysqlfrm
First you need to install mysql-utilities.
yum install mysql-utilities
Then cd to /var/lib/mysql and using below command to dump the database.
mysqlfrm --diagnostic databasename > /root/databasename.sql
The sql will give you tables but not the data but it is sufficient to restore database.
Backup /var/lib/mysql/databasename to somewhere or just rename it to something.
Restore database.
mysql databasename < /root/databasename.sql
Restore the permission because the ownership is root.
chown -R mysql:mysql /var/lib/mysql/databasename
Stop the MySQL
Copy the data from databasename folder that you just rename or resync using command. Example;
rsync -arvh --progress /root/databasename/ /var/lib/mysql/databasename/
Start the MySQL
Supposed you have a working database and website now.