Introduction:
MySQL tables may encounter corruption, resulting in unreadable data and potential server crashes. Instances where a corrupted table is accessed often lead to server instability.
Common causes of table corruption include:
-
MySQL server interruption during a write operation.
-
Concurrent alteration of a table by both an external program and the server.
-
Unexpected shutdown of the machine.
-
Hardware failure in the computer.
-
Software bugs within the MySQL code.
If you suspect table corruption, it's essential to create a backup of your data directory before attempting any troubleshooting or fixes. This precautionary measure minimizes the risk of data loss.
To initiate the backup process, start by stopping the MySQL service:
sudo systemctl stop mysql
Note: On certain platforms like Rocky Linux, the MySQL service may be named mysqld instead.
Proceed by duplicating all your data into a fresh backup directory. For Ubuntu systems, the default data directory is typically located at /var/lib/mysql/:
cp -r /var/lib/mysql /var/lib/mysql_bkp
After creating the backup, initiate an investigation to confirm whether the table is corrupted. If the table is utilizing the MyISAM storage engine, you can determine its status by executing a CHECK TABLE statement from the MySQL prompt:
mysql> CHECK TABLE table_name;
The output will contain a message indicating whether the table is corrupted. If confirmed, the next step is to attempt a repair:
mysql> REPAIR TABLE table_name;
After a successful repair, the output should include a message confirming the completion of the repair process. For example:
Output
+--------------------------+--------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------------------+--------+----------+----------+
| database_name.table_name | repair | status | OK |
+--------------------------+--------+----------+----------+
On the contrary, if the corrupted table utilizes the InnoDB storage engine, the repair process differs. InnoDB is the default storage engine in MySQL from version 8.0 onward, featuring automated corruption checks and repairs. InnoDB identifies corrupted pages by performing checksums on each page it reads. If a checksum discrepancy is detected, the MySQL server is automatically halted.
Repairing InnoDB tables is rarely necessary due to InnoDB's built-in crash recovery mechanism, which typically resolves most issues upon server restart. However, in situations where rebuilding a corrupted InnoDB table becomes necessary, the MySQL documentation recommends the "Dump and Reload" method. This involves regaining access to the corrupted table, using the mysqldump utility to create a logical backup, preserving both table structure and data. Subsequently, reload the table back into the database.
Considering this, attempt restarting the MySQL service to check if it restores access to the server:
sudo systemctl restart mysql
If the server remains crashed or inaccessible, enabling InnoDB's force_recovery option might provide a solution. Edit the mysqld.cnf file, typically located in /etc/mysql on Ubuntu and Debian systems, or /etc/my.cnf.d on Red Hat and Rocky systems.
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
In the [mysqld] section, incorporate the subsequent line:
/etc/mysql/mysql.conf.d/mysqld.cnf
. . .
[mysqld]
. . .
innodb_force_recovery=1
Save and close the file, then attempt restarting the MySQL service. If access to the corrupted table is successful, use the mysqldump utility to export your table data to a new file, naming it as desired (e.g., out.sql):
mysqldump database_name table_name > out.sql
Subsequently, drop the table from the database. To avoid reopening the MySQL prompt, use the following syntax:
mysql -u user -p --execute="DROP TABLE database_name.table_name"
Following this step, restore the table using the dump file created:
mysql -u user -p < out.sql
This process aims to recover from a crashed or inaccessible MySQL server, utilizing InnoDB's force_recovery option and relevant MySQL commands for dumping, dropping, and restoring tables.