Author: Josh Amata
Last Updated: Fri, Sep 16, 2022MySQL is a Relational Database Management System (RDBMS). An RDBMS is a database management system (DBMS) that stores related data in tables. These tables form rows called records and columns called attributes, with a unique key identifying each row.
RDMSes such as MySQL include functionality to preserve data integrity, consistency, and accuracy. CRUD (Create, Read, Update, Delete) transactions execute on RDBMS using SQL, following the ACID (Atomicity, Consistency, Isolation, Durability) mechanism.
This helps the system achieve consistency and stability.
This guide covers how to fix a corrupted MySQL table.
Table corruption leads to data held within them being unreadable, with attempts to read data leading to the MySQL server crashing. Common causes of MySQL table corruption include:
Before attempting to fix or troubleshoot problems, create a backup of your data directory to reduce the risk of data loss.
$ sudo systemctl stop mysql
On Ubuntu, the default data directory is /var/lib/mysql.
$ cp -r /var/lib/mysql /var/lib/mysql_backup
As of MySQL version >= 5.5, InnoDB is the default storage engine and has a couple of features, including automated corruption checking and repair operations. Prior MySQL versions use MyISAM as the default storage engine, which is more susceptible to table corruption.
To check the MySQL engine type, enter the following query in the MySQL console:
mysql> SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = 'database_name';
Note: Replace 'database_name' with the name of your database.
This will display an output like this:
+--------------------+-------------+-----------+---
| Table_Name | Engine | Version | ...
+--------------------+-------------+-----------+---
| customers | MyISAM | 10 | ...
+--------------------+-------------+-----------+---
| staff | InnoDB | 10 | ...
+--------------------+-------------+-----------+---
Check for corruption on tables running the MyISAM storage engine, such as the customers table above, using the following MySQL prompt:
mysql> CHECK TABLE customers;
Corruption errors on MyISAM tables are usually repaired with the REPAIR TABLE table_name
prompt. E.g
mysql> REPAIR TABLE customers;
This will return an output telling the repair was successful. This method repairs most table errors on MyISAM storage engines.
Note: REPAIR TABLE requires SELECT and INSERT privileges.
If the repair does not result in an OK value, use the myisamchk utility command within the database directory:
$ myisamchk --safe-recover
This will most likely fix all corruption errors if the previous prompt fails to.
For more options with the myisamchk utility, check the documentation.
InnoDB performs checksums on each page it reads. When it finds a checksum inconsistency - it automatically stops the MySQL server. The InnoDB storage engine is quite stable; thus, repairs are rarely needed. It features a crash recovery mechanism that can resolve most issues by restarting the server.
If a server restart doesn't fix the issue, use an alternative method called a DUMP and RELOAD to rebuild the table. This method involves creating a logical backup of the table, which preserves the table structure and data within, and finally reloading the table back into the database.
Enable InnoDB's force_recovery
option by editing the configuration file:
$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
â¦
[mysqld]
â¦
innodb_force_recovery = 1
The default value of innodb_force_recovery
is 0. Change this to a value between one and six to start the InnoDB engine and dump the MySQL table.
Note: A
innodb_force_recovery
value greater than four increases the risk of further data corruption. If need be, increase incrementally from 1.
$ sudo systemctl restart mysql.service
If the MySQL service restarts successfully and you can access the corrupted table, the next step is to dump the table data to a new file using the mysqldump
utility.
$ mysqldump database_name table_name > output.sql
This dumps the table data into a new file named output.sql.
The next step is to drop the table from the database:
$ mysql -u user -p --execute="DROP TABLE database_name.table_name"
The above command will drop the given table name from the database.
The final step in the DUMP and RELOAD
method is to restore the table to the database:
$ mysql -u user -p < output.sql
This guide covered repairing a corrupted MySQL table running the MyISAM or InnoDB storage engines. For more information, visit the MySQL reference documentation.