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
to
#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.
Also read:
How to Optimize MySQL Tables
cURL Command to Call REST API
How to Remove WWW from URL in Apache
How to Delete Git Tags
How to Drop One or More Columns in Python Pandas
Related posts:
![](https://fedingo.com/wp-content/uploads/2024/05/db908c05ad732bd197c1eaad1c6675d2.jpeg)
Sreeram has more than 10 years of experience in web development, Python, Linux, SQL and database programming.