Home » Cpanel » 23 October 2019 » 68 supporters » No Comment »

Restore database from *.frm for corrupted database

23 October 2019 68 supporters No Comment

Recently on 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.

Backup /var/lib/mysql/databasename to somewhere or just rename it to something.

Restore the 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 your response!

Add your comment below, or trackback from your own site. You can also subscribe to these comments via RSS.

Be nice. Keep it clean. Stay on topic. No spam.

You can use these tags:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This is a Gravatar-enabled weblog. To get your own globally-recognized-avatar, please register at Gravatar.