How to repair databases in MySQL. Sometimes, a MySQL database can crash. This problem can be fixed with cPanel, phpMyAdmin or WHM. This article explains how to repair MySQL databases and tables.
MySQL includes several tools that you can use to check and repair database tables. Follow us in this article to repair MySQL databases.
Table of Contents
Step 1: Backup the databases
Before attempting to repair any database, you must first restore it. To back up all your files from all your databases, follow these steps:
1. Log in to your server using SSH.
2. Stop the MySQL server using the appropriate command for your Linux distribution:
– For CentOS and Fedora, type the following command:
service mysqld stop– For Debian and Ubuntu, type the following command:
service mysql stop3. The following command copies all files from all your databases to a directory name based on the current time (exactly, the number of seconds since January 1, 1970).
This ensures that each database backup is stored in a folder with a unique name. For added protection, you can (and should) separate database files to a location away from the server.
cp -rfv /var/lib/mysql /var/lib/mysql$(date +%s)4. Restart the MySQL server using the appropriate command for your Linux distribution:
– For CentOS and Fedora, type the following command:
service mysqld start– For Debian and Ubuntu, type the following command:
service mysql startStep 2: Check and repair a table with MYSQLCHECK
After backing up your database, you are ready to troubleshoot. The mysqlcheck application enables you to check MySQL databases while the repair is running. This feature is useful when you want to work in a database without stopping the entire MySQL service.
In addition, mysqlcheck is used in tables that use MyISAM or InnoDB database engines.
To use mysqlcheck, follow these steps:
1. As a root user, type the following command:
cd /var/lib/mysql2. To replace the DATABASE with the name of the database you want to check, type the following command:
mysqlcheck DATABASEThe previous command checks all tables in a specified database. Alternatively, type the following command to check a specific table in a database.
Replace DATABASE with the database name, and replace the TaABLE with the table name you want to check:
mysqlcheck DATABASE TABLE3. Mysqlcheck examines specified databases and tables. If a table passes the check, mysqlcheck displays OK for the table. However, if mysqlcheck reports an error for a table, type the following command to try to fix it.
Replace DATABASE with the database name and the table with the table name:
mysqlcheck -r DATABASE TABLE4. If mysqlcheck can not successfully fix tables or tables, go to the following method.
Step 3: Identify the specific engine
If running mysqlcheck does not solve the problem, the next step is to make a specific diagnosis for the engine used in the database table or tables.
For your table database storage engine, follow the appropriate procedure below.
Repair MyISAM tables with myisamchk
If you use a MyISAM storage engine for a table, you can run the myisamchk program to repair it.
To do this, follow these steps:
Note: The myisamchk program only works for tables that run on the MyISAM storage engine and does not work on the InnoDB engine.
1. Stop the MySQL server using the appropriate command for your Linux distribution:
– For CentOS and Fedora:
service mysqld stop– For Debian and Ubuntu:
service mysql stop2. Type the following command:
cd /var/lib/mysql3. Change to the directory where the database is located. For example, if the database is called Customers, type CD Customers.
4. Type the following command, replace the TABLE with the name of the table you want to check:
myisamchk TABLETo check all the tables in the database, type the following command:
myisamchk * .MYIIf the previous command does not work, you can try to delete temporary files that may not run properly from myisamchk.
To do this, go to the /var/lib/mysql folder and then type the following command:
ls * / *. TMDIf there are TMD files, type the following command to delete them:
rm * / *. TMDThen try running myisamchk again.
5. To try to repair a table, type the following command and replace TABLE with the name of the table you want to repair:
myisamchk --recover TABLE6. Restart the MySQL server using the appropriate command for your Linux distribution:
– For CentOS and Fedora:
service mysqld start– For Debian and Ubuntu:
service mysql start7. Test repaired tables or tables.
Execute the InnoDB recovery process
If you use the InnoDB storage engine for a database table, you can run the InnoDB recovery process. To do this, follow these steps:
1. Use a text editor to open the my.cnf file on your server. The location of the my.cnf file depends on your Linux distribution:
– In CentOS and Fedora, the my.cnf file is located in the /etc directory.
– In Debian and Ubuntu, the my.cnf file is located in the /etc/mysql directory.
2. In the my.cnf file, find the [mysqld] section.
3. Add the following line to the [mysqld] section:
innodb_force_recovery=44. Save the changes to my.cnf file.
Then restart the MySQL server using the appropriate command for your Linux distribution:
– For CentOS and Fedora:
service mysqld restart– For Debian and Ubuntu:
service mysql restart5. Type the following command to export all databases to the databases.sql file:
mysqldump --all-databases --add-drop-database --add-drop-table > databases.sql6. Start the mysql program and then try to drop the damaged databases using the DROP DATABASE command.
If MySQL is unable to drop the database, you can manually delete it in step 8 after shutting down the MySQL server.
7. Stop the MySQL server using the appropriate command for your Linux distribution:
– For CentOS and Fedora:
service mysqld stop– For Debian and Ubuntu:
service mysql stop
8. If in step 6 you can not drop the database, type the following commands to delete it manually.
Replace DBNAME with the name of the database you want to delete:
cd /var/lib/mysql rm -rf DBNAMETip: Make sure you do not delete the mysql directory or performance_schema !
9. Use your favorite text editor to open the my.cnf file on your server. Then put the following command in the [mysqld] section:
# innodb_force_recovery = 4This mode disables InnoDB recovery.
10. Save the changes to my.cnf file. Then start the MySQL server using the appropriate command for your Linux distribution:
– For CentOS and Fedora:
service mysqld start– For Debian and Ubuntu:
service mysql start11. Type the following command to restore the database from the backup file created in step 5:
mysql < databases.sql12. Reconstructed database test.
Dear user, we hope you would enjoy this tutorial, you can ask questions about this training in the comments section, or to solve other problems in the field of Eldernode training, refer to the Ask page section and raise your problem in it as soon as possible. Make time for other users and experts to answer your questions.
Goodluck.
Great post and explained in a detailed manner. I found need information. Thanks for sharing such a useful information
by Cognex(https://www.cognextech.com/aws-training-and-certification-course-3)
Great post, really helpful with tons of great insight!
by Cognex(https://www.cognextech.com/aws-training-and-certification-course-3)