Check database for corruption

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.

Symptoms

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.

Solutions

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
the flag --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

Conclusion

With the commands presented in this article, you can now check your MySQL
database or table for corruption.