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.

Leave a Reply

Your email address will not be published. Required fields are marked *