SoFunction
Updated on 2025-04-12

Causes and solutions for the MySQL table data file corruption that causes the database to fail to start

1. Problem phenomenon

When the data file of the MySQL table is corrupted, the following situations usually occur:

  • MySQL service cannot be started: When trying to start the MySQL service, the service will stop immediately.
  • There is related error information in the error log: View MySQL error log (usually located at​/var/log/mysql/​) You can see error messages related to corruption of table data files, such as:

[ERROR] InnoDB: Database page corruption on disk or a failed file read of table ​​database_name​​​.​​table_name​​.

2. Diagnostic steps

2.1 Check the error log

First, check the MySQL error log file to determine the specific error information. This can be done with the following command:

sudo tail -f /var/log/mysql/

2.2 Identify the damaged table

According to the prompts in the error log, you can determine which tables' data files may be corrupted. For example, if the error log mentions​database_name.table_name​, you can preliminarily determine that there are problems with the data file of the table.

2.3 Using the innodb_force_recovery parameter

MySQL provides a parameter​innodb_force_recovery​​, used to try to restore the database when the InnoDB storage engine encounters problems. It can be edited by editing the MySQL configuration file (usually​/etc/​​or​/etc/mysql/​), add or modify the following configuration:

[mysqld]
innodb_force_recovery = 1

Then restart MySQL service:

sudo systemctl restart mysql

The value of innodb_force_recovery can range from 1 to 6. The larger the value, the higher the degree of forced recovery, but it is also more likely to cause data loss. It is recommended to gradually increase it from 1 until the MySQL service can be successfully started.

3. Data recovery

3.1 Backup of existing data

Before performing any recovery operations, it is highly recommended to back up existing database files. This can be done by copying the MySQL data directory:

sudo cp -R /var/lib/mysql /var/lib/mysql_backup

3.2 Try to fix the table

If​innodb_force_recovery​​After the parameter setting, MySQL service can be started, you can try using​REPAIR TABLE​Command to fix corrupted tables:

REPAIR TABLE database_name.table_name;

3.3 Export and import data

If the above method does not solve the problem, consider exporting the data of the corrupted table, then recreating the table and importing the data. First, export the data of the table:

mysqldump -u username -p database_name table_name > table_name.sql

Then, delete the damaged table:

DROP TABLE database_name.table_name;

Finally, recreate the table and import the data:

CREATE TABLE table_name ( ... );
mysql -u username -p database_name < table_name.sql

4. Preventive measures

To avoid similar problems from happening again, the following precautions can be taken:

  • Regular backup: Back up the database regularly to ensure that there are available backups when the data is corrupted.
  • Monitoring system health: Use monitoring tools to monitor the running status of MySQL, and promptly discover and deal with potential problems.
  • Optimize the hardware environment: Ensure the server's hardware environment is stable, especially disk and memory.

MySQL table data file corruption is a serious problem, but through reasonable diagnostic and recovery steps, the problem can be effectively solved and the impact on the business can be reduced. I hope the content of this article can help you better deal with this type of problem.

When the data files of the MySQL table are corrupted, it may cause problems when the database cannot be started properly or accessing specific tables. Here is a sample scenario showing how to deal with this situation. Suppose you have a name called​users​​The data file of the table is corrupted.

1. Simulation data file corruption

First, we need to simulate the situation of data file corruption. In production environments, this is usually caused by hardware failure, unexpected power outages, or other system problems. For demonstration, we can simulate this corruption by manually deleting or modifying the data file.

step:

  1. Backup data: Make sure to back up all important data before doing anything.
  2. Locate data files: Found​users​table data file. Usually, these files are located in the MySQL data directory, with a path similar to​/var/lib/mysql/your_database/​​。
  3. Delete or modify data files: Delete or modify​​Files to simulate corruption.

2. Try to start MySQL

Try starting the MySQL service and see what happens.

sudo systemctl start mysql

If the data file is severely corrupted, MySQL may not start and you will see related error messages in the error log.

3. View the error log

Check out MySQL's error log for more information.

sudo tail -f /var/log/mysql/

You may see error messages similar to the following:

2023-10-01T12:34:56.789000Z 0 [ERROR] InnoDB: File ./your_database/: 'read' returned OS error 122. Cannot continue operation
2023-10-01T12:34:56.789000Z 0 [ERROR] InnoDB: Operating system error number 122 in a file operation.
2023-10-01T12:34:56.789000Z 0 [ERROR] InnoDB: Error number 122 means 'Disk quota exceeded'
2023-10-01T12:34:56.789000Z 0 [Note] InnoDB: Some operating system error numbers are described at /doc/refman/5.7/en/
2023-10-01T12:34:56.789000Z 0 [ERROR] InnoDB: Could not open './your_database/'.
2023-10-01T12:34:56.789000Z 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2023-10-01T12:34:56.789000Z 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2023-10-01T12:34:56.789000Z 0 [ERROR] InnoDB: If you are attempting to delete the file, the file cannot be opened so it cannot be deleted.
2023-10-01T12:34:56.789000Z 0 [ERROR] InnoDB: If the file is missing, you can drop the table without the .ibd file present.
2023-10-01T12:34:56.789000Z 0 [ERROR] InnoDB: You may have to recover from a backup.
2023-10-01T12:34:56.789000Z 0 [ERROR] InnoDB: Cannot open datafile for read-only: './your_database/' OS error: 2
2023-10-01T12:34:56.789000Z 0 [ERROR] InnoDB: Could not find a valid tablespace file for `your_database/users`. Please refer to /kb/en/library/innodb-troubleshooting-datadict/ for how to resolve the issue.
2023-10-01T12:34:56.789000Z 0 [ERROR] InnoDB: Table `your_database/users` is missing from the InnoDB data dictionary but exists in MySQL's data directory. You can manually remove the file if you are sure that it should not exist. See /kb/en/library/innodb-troubleshooting-datadict/ for more information.
2023-10-01T12:34:56.789000Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2023-10-01T12:34:56.789000Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2023-10-01T12:34:56.789000Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2023-10-01T12:34:56.789000Z 0 [ERROR] Failed to initialize plugins.
2023-10-01T12:34:56.789000Z 0 [ERROR] Aborting

4. Recover data

Method 1: Use backup to restore

If you have regular backups, you can use the backup file to restore the data.

# Stop MySQL servicesudo systemctl stop mysql
 
# Restore backup filescp /path/to/backup/ /var/lib/mysql/your_database/
 
# Start MySQL servicesudo systemctl start mysql

Method 2: Reconstruct the table

If there is no backup, you can try rebuilding the table and reimporting the data.

  • Delete a corrupted table：
DROP TABLE your_database.users;
  • Create a new table：
CREATE TABLE your_database.users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);
  • Re-import data：
LOAD DATA INFILE '/path/to/your/'
INTO TABLE your_database.users
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

5. Verify recovery

Verify that the data has been successfully restored.

SELECT * FROM your_database.users;

If there is a backup, the recovery process will be easier and more reliable. If there is no backup, you may need to rebuild the table and reimport the data. In production environments, regular backup and monitoring is very important to prevent data loss and system failure. When the data files of the MySQL table are corrupted, it may cause the database to fail to start properly or some specific tables are inaccessible. This situation usually requires diagnosis and repair. Here are some common steps and related SQL commands to deal with the problem of MySQL table data file corruption:

Common steps to deal with MySQL table data file corruption

1. Check the error log

First, look at MySQL's error log file (usually located in /var/log/mysql/) for specific information about the corruption.

2. Try to start MySQL service

If the MySQL service fails to start, you can try to start the service manually and view the output:

sudo systemctl start mysql

or

sudo service mysql start

3. Use the CHECK TABLE command

If the MySQL service can be started but some tables are not accessible, you can use​CHECK TABLE​Command to check the status of the table:

CHECK TABLE your_database.your_table;

This command will return the status information of the table, including whether there is any damage.

4. Use the REPAIR TABLE command

If​CHECK TABLE​The command display table is corrupt, you can try using​REPAIR TABLE​Command to fix the table:

REPAIR TABLE your_database.your_table;

5. Use the myisamchk tool

For MyISAM tables, you can use​myisamchk​Tools to check and repair tables. First, stop the MySQL service:

sudo systemctl stop mysql

Then, navigate to the directory containing the table files (usually ​​/var/lib/mysql/your_database/​（ ） and run （​myisamchk​​：

sudo myisamchk -r /var/lib/mysql/your_database/your_table.MYI

Here the -r​ parameter indicates the repair table.

6. Use the innodb_force_recovery parameter

For InnoDB tables, you can try to enable the innodb_force_recovery parameter to force the MySQL service to start. Edit the MySQL configuration file (usually /etc/mysql/​​ or /etc/mysql//​​), add or modify the following lines:

[mysqld]
innodb_force_recovery = 1

​​innodb_force_recovery​The value of ​ can range from 1 to 6. The larger the value, the higher the degree of forced recovery. It is recommended to try starting from 1 and gradually increase until the problem is solved.

7. Export and import data

If none of the above methods can solve the problem, you can consider exporting the data, deleting the corrupted table, and then re-importing the data:

  • Export data:
mysqldump -u username -p your_database your_table > your_table.sql
  • Delete the corrupted table:
DROP TABLE your_database.your_table;
  • Recreate the table and import the data:
mysql -u username -p your_database < your_table.sql

8. Restore backup

If none of the above methods can solve the problem, the last method is to restore data from the backup. Make sure you have the habit of regular backups and that the backup files are complete and available.

Things to note

  • It is best to back up all data before doing anything.
  • After modifying the configuration file, remember to restart the MySQL service to make the changes take effect.
  • If you are not sure how to do it, it is recommended to consult a professional database administrator or technical support.

Through these steps, you should be able to diagnose and fix the MySQL table data file corruption issue.

The above is the detailed content of the reasons and solutions for the database that cannot be started due to the corruption of MySQL table data file. For more information about the corruption of MySQL table data file and the inability to start, please pay attention to my other related articles!