Check database for corruption
Last updated on: 2021-05-24
Authored by: David Fonseca
This article explains how to check your database for corruption in MySQL®. Why does corruption occur in a database? It might happen because of hardware, specifically disk-based failures or when a disk is full.
The principal symptom: You try to log in and get an error message in the console: Session Replace: Table ‘./DB_NAME/mdl_sessions2’ is marked as crashed and should be repaired.
You can check and repair this issue by using the
mysqlcheck command with the
--auto-repair DBNAME flag. When you add the
--auto-repair flag, MySQL tries to
repair the corruption in your database.
# mysqlcheck -u USER_NAME -p --auto-repair DB_NAME Enter password: db_test.adodb_logsql OK db_test.mdl_assignment OK db_test.mdl_assignment_submissions OK ... db_test.mdl_log error : Table './db_test/mdl_log' is marked as crashed and should be repaired ... db_test.mdl_sessions2 error : Table './db_test/mdl_sessions2' is marked as crashed and should be repaired Repairing tables db_test_18_latest.mdl_log OK db_test_18_latest.mdl_sessions2 OK
If you just want to check if your database is or not corrupted, run the following command:
# mysqlcheck -c DATABASE_NAME -u USER_NAME -p
If you want to check all databases and tables in your server, add
--all-databases and omit the name of the database,
as shown in the following command:
# mysqlcheck -c -u USER_NAME -p --all-databases
If you just want to check a table inside a database, run the following command:
# mysqlcheck -a DB_NAME TABLE_NAME -u USER_NAME -p
With the commands presented in this article, you can now check your MySQL database or table for corruption.