How to Repair MySQL Databases & Tables

As your MySQL database grows, you will find that there are errors that occur sometimes. These are not the errors in SQL query but in database performance. Luckily, MySQL provides several tools to diagnose and repair databases & tables. In this article, we will learn how to repair MySQL databases & tables.

How to Repair MySQL Databases & Tables

Here are the steps to repair MySQL databases & tables.

1. Backup MySQL databases

First, it is important to backup your database, before you proceed with repairing it. Open terminal run the following command to stop MySQL server.

CentOS/RHEL/Fedora/SUSE

# service mysqld stop

Debian/Ubuntu

$ sudo service mysql stop

Run the following command to take backup of all files to folder /var/lib/mysql-backup

$ cp -rfv /var/lib/mysql /var/lib/mysql-backup

Then start MySQL server again.

CentOS/RHEL/Fedora/SUSE

$ service mysqld start

Debian/Ubuntu

$ service mysql start

2. Run MySQLcheck

MySQLcheck is an in-built command to check a database for errors. It works on tables that use MyISAM or InnoDB engines. Go to /var/lib/mysql

$ cd /var/lib/mysql

Run the following command to check a given database. Replace database_name with the name of database that you want to check. In some cases, you may need to add sudo keyword at the beginning, if you get ‘permission denied’ error.

$ mysqlcheck database_name

If you want to check a specific table in database, mention it after the database name in above command.

$ mysqlcheck database_name table_name

After the check, the above command will display an OK message if there are no errors. If there are errors, then run the following command to try to repair it.

$ mysqlcheck -r database_name table_name

The advantage of mysqlcheck is that you can check your database for errors, without stopping them.

3. Repair MyISAM with myisamchk

If your database runs on MyISAM engine, you can use myisamchk command. For this, stop MySQL database first.

CentOS/RHEL/Fedora/SUSE

# service mysqld stop

Debian/Ubuntu

$ sudo service mysql stop

Go to /var/lib/mysql/database_name where database_name is the name of your database.

$ cd /var/lib/mysql/database_name

Once you are in the folder of your database, run the following command to check any specific table.

$ myisamchk table_name

If you want to check all tables in a database, run the following command.

$ myisamchk *.MYI

If you find any errors in the output, run the following command to recover it.

$ myisamchk --recover table_name

After the above command completes, start MySQL database again.

CentOS/RHEL/Fedora/SUSE

$ service mysqld start

Debian/Ubuntu

$ service mysql start

4. Repair InnoDB databases

If your database runs on InnoDB engine, you can follow the steps below. For this, open my.cnf file in a text editor.

CentOS/RHEL/Fedora

$ sudo vi /etc/my.cnf

Debian/Ubuntu

$ sudo vi /etc/mysql/my.cnf

Find [mysqld] block and add the following line in it.

innodb_force_recovery=4

Save and exit the file. Restart the MySQL server with the following command.

CentOS/Fedora/RHEL/SUSE

$ service mysqld restart

Debian/Ubuntu

$ service mysql restart

Run the following command to take a backup of all databases.

$ mysqldump --all-databases --add-drop-database --add-drop-table --routines > databases.sql

Then run the following command to drop the database having errors.

mysql> drop database database_name;

If the above command doesn’t work for you, or given errors, then log out of MySQL shell, and run the following command to delete it. Replace database_name.

$ cd /var/lib/mysql
$ rm -rf database_name

Stop MySQL Server.

CentOS/RHEL/Fedora/SUSE

$ sudo service mysqld stop

Ubuntu/Debian

$ sudo service mysql stop

Open my.cnf again and comment the following line by adding # at its beginning.

innodb_force_recovery=4

#innodb_force_recovery=4

Save and quit the file. Start MySQL Server.

CentOS/RHEL/Fedora/SUSE

$ sudo service mysqld start

Ubuntu/Debian

$ sudo service mysql start

Restore the databases with the following command.

$ mysql < databases.sql

Now test your database.

In this article, we have learnt the different ways to repair a MySQL database. You may use any of the above methods as per your requirement. Having said that, one of the simplest ways to keep your MySQL database healthy is to regularly restart it like once a week or fortnight.

How to Repair MySQL Databases & Tables