How to Repair MySQL Database Tables through Linux Servers?

While handling MySQL databases on Linux OS, it is pretty common to encounter database corruption. Though MySQL DB corruption can happen due to various unexpected causes, like:

• Sudden system shutdown
• Server hardware failure
• Incorrect data schema
• Virus attack, etc.

Irrespective of the reason, if you have a valid backup of your DB, then you can perform MySQL database recovery from the available backup.

Nevertheless, in the absence of a backup, you can repair MySQL database by using an advanced MySQL Repair Software mentioned in this post. Also, you can go for the manual approach discussed here to get your database fixed.

Read further to know more in detail…

Solutions to Repair MySQL Database Table through Linux Server

Follow the below step-by-step methods to fix MySQL tables on a Linux system with ease.

Solution 1- Check & Fix MySQL Database Tables Using Commands

Important Note– The database repairing process using the manual method may depend on the storage engine (MyISAM or InnoDB) that you are currently using.

Step 1- Backing Up MySQL Database

Before you proceed with the repairing process on your Linux, ensure to create a backup of your DB tables to avoid losing them. Follow the below instructions to back up your database files:

• First, sign in to your MySQL server through SSH (Secure Shell Host) and stop the server by running the below commands:

For CentOS & Fedora, use the syntax:

service mysqld stop

For Ubuntu & Debian, use the syntax:

service mysql stop

• For backing up your DB file, use this command:

cp -r /var/lib/mysql /var/lib/mysql_backup

• Once you have backed up your database file(s), run the below command to restart the server in your Linux device:

service mysqld start

Now, proceed to the next step to check & repair the database and tables.

Step 2- Checking & Fixing MySQL Databases with mysqlcheck Command

Next, you have to follow the below steps to run mysqlcheck and repair database tables (MyISAM or InnoDB):

• Enter the beneath command as a root user:

cd /var/lib/mysql

• Now, run the below command to check the DB and tables for corruption:

mysqlcheck database_name

If you want to check a particular table for an issue, type this command:

mysqlcheck database_name table_name

• In case, if the table isn’t corrupted, you will see an OK message on your screen. But, if the DB table shows any errors, simply run the below command and repair it:

mysqlcheck –r database_name table_name

Well, if the above steps failed to solve it, proceed with the following phase.

Step 3- Executing Diagnostics Specific to Storage Engine

In this stage, you have to repair the database.

Repair MyISAM Tables using myisamchk Command

If you’re using MyISAM storage engine for MySQL, then you can run myisamchk commands for fixing the table. But do remember that the myisamchk command can only work for MyISAM engine not work for InnoDB engine.

Now, follow the below instructions carefully to repair it:

• First, stop the server by running the below commands:

For Debian & Ubuntu, run:

service mysql stop

For CentOS & Fedora, run:

service mysqld stop

• Then, type the below command:

cd /var/lib/mysql

Here, replace the overhead directory with your corrupted DB directory (location).

• Now, you have to run the below command to check the tables for corruption:

myisamchk table_name

In order to check all the DB tables, use this syntax:

myisamchk *.MYI

• As soon as you identify the corrupted DB tables, run the below command for repairing them:

myisamchk –recover table

• Start the server again:

For Debian & Ubuntu, run this:

service mysql start

For CentOS & Fedora, run this:

service mysqld start

Repair Database Running On the InnoDB Engine

Follow the below steps:

• Initially, open the configuration file of MySQL “my.cnf”.
• The my.cnf file is actually located:

On Debian & Ubuntu– ‘/etc/mysql’ directory.

On CentOS & Fedora- ‘/etc’ directory.

• After locating the file, find a [mysqld] section.
• Under that section, you have to add the below line:

innodb_force_recovery=4

• Now, save all the changes that you have made in configuration file >> restart the server:

For Debian & Ubuntu, use this:

service mysql restart

For CentOS & Fedora, use this:

service mysqld restart

• After this, you need to export all databases to databasesbkp.sql file by running the below command:

mysqldump –all-databases > databasesbkp.sql

• Begin MySQL service and use the command “DROP DATABASE” to drop the affected DB. In case, if you are unable to do this, then you have to stop the server and delete it manually.
• Simply stop the server and run the below command for manually deleting the database:

cd /var/lib/mysql rm -rf database

(Here, change ‘database’ word with your corrupted database name)

• At this time, under my.cnf file, comment out the below line to disable the recovery mode. Next, save the modifications & close the file.

#innodb_force_recovery=4

• Now, run the below command to restore the DB from the created backup:

mysql < databasesbkp.sql

And you are done!

Solution 2- Use Professional MySQL Repair Software

Even after trying the above manual method, if the issue persists, you should try the recommended Stellar Repair for MySQL Tool to repair MySQL database on your Linux machine. Besides, it can even restore its objects such as tables, views, triggers, primary keys, etc. without modifying the original database.

The best thing about this MySQL repair tool is- it’s compatible with both OS: Windows & Linux respectively.

So, all you need to do is to download & install this utility on your Linux system. For installation of this MySQL database recovery tool, launch a Linux Terminal & type this command:

$ sudo yum install app_name

Once you have installed it successfully, follow the below steps to repair MySQL DB tables on a Linux device:

Steps To Be Followed:

Step 1: Open the Stellar Repair for MySQL software. Click on the OK option in the Instruction window.

Step 2: Under Select Data Folder popup, choose your MySQL installation version.

Step 3: In the same window, click on browse to select a folder that contains the corrupt DB file.

Step 4- At this time, you will get the databases list, select the particular DB or select all databases you need to repair. Click Repair.

Step 5- Once the repairing process gets over, you will get ‘Repair Complete’ message box on your screen >> Click OK button.

Step 6- Next, the program will display the preview of all recoverable MySQL DB components.

Step 7- Then, go to the File menu & click on Save option to save the repaired file successfully.

Step 8- Under ‘Save Database’ popup box, you have to choose MySQL option and enter the mandatory details for connecting to MySQL server. Now, click on Save to save the fixed file in a MySQL Server.

Step 9- As soon as the file-saving procedure gets over, you will get the ‘Save Complete’ popup on your screen.

Step 10- At last, you will get your MySQL database fixed.

Bottom Line

Above, I have explained 2 workable methods to repair MySQL database in the Linux-based system effortlessly. You can use the mysqlcheck command or myisamchk command to check & fix corruption in the database manually.

However, the manual process is lengthy and time-consuming. A quick & easy way is to go for Stellar Repair for MySQL software. It can eventually repair your corrupt database without any data loss.

Thanks for reading!