Before you attempt to repair corrupted tables, you should back your database files first. Yes, it’s already broken but this is to minimize the risk of possible further damage which may be caused by a recovery operation. There is no guarantee that any action you take will not harm untouched data blocks. Forcing InnoDB recovery with values greater than 4 can corrupt data files, so make sure you will do it with prior backup and ideally on a separate physical copy of the database.
To back up all of the files from all of your databases, follow these steps:
Stop the MySQL server
Type the following command for your datadir.
After we have a backup copy of the data directory, we are ready to start troubleshooting.
Data Corruption Identification
The error log is your best friend. Usually, when data corruption happens, you will find relevant information (including links to documentation) in the error log. If you don’t know where it’s located, check my.cnf and variable log_error, for more details check this article https://dev.mysql.com/doc/refman/8.0/en/error-log-destination-configuration.html. What you should also know is your storage engine type. You can find this information in the error log or in information_schema.
The main tools/commands to diagnose issues with data corruption are CHECK TABLE, REPAIR TABLE, and myisamchk. The mysqlcheck client performs table maintenance: It checks, repairs (MyISAM), optimizes or analyzes tables while MySQL is running.
Replace DATABASE with the name of the database, and replace TABLE with the name of the table that you want to check:
Mysqlcheck checks the specified database and tables. If a table passes the check, mysqlcheck displays OK for the table.
Data corruption issues may be also related to permission issues. In some cases, OS can switch mount point to read-only mode due to R/W issues or this can be caused by a user who accidentally changed ownership of the data files. In such cases, you will find relevant information in the error log.
Error log entry
Recovering InnoDB table
If you are using the InnoDB storage engine for a database table, you can run the InnoDB recovery process.
To enable auto recovery MySQL needs innodb_force_recovery option to be enabled. Innodb_force_recovery forces InnoDB to start up while preventing background operations from running, so that you can dump your tables.
To do this open my.cnf and add the following line to the [mysqld] section:
You should start from innodb_force_recovery=1 save the changes to my.cnf file, and then restart the MySQL server using the appropriate command for your operating system. If you are able to dump your tables with an innodb_force_recovery value of 3 or less, then you are relatively safe. In many cases you will have to go up to 4 and as you already know that can corrupt data.
If needed change to the higher value, six is the maximum and most dangerous.
Once you are able to start your database, type the following command to export all of the databases to the databases.sql file:
Start mysql, and then try to drop the affected database or databases using the DROP DATABASE command. If MySQL is unable to drop a database, you can delete it manually using the steps below after you stop the MySQL server.
If you were unable to drop a database, type the following commands to delete it manually.
Make sure you do not delete the internal database directories.
After you are done, comment out the following line in the [mysqld] to disable InnoDB recovery mode.
Save the changes to the my.cnf file, and then start the MySQL server
Type the following command to restore the databases from the backup file you created in step 5:
If mysqlcheck reports an error for a table, type the mysqlcheck command with -repair flag to fix it. The mysqlcheck repair option works while the server is up and running.
If the server is down and for any reason mysqlcheck cannot repair your table, you still have an option to perform recovery directly on files using myisamchk. With myisamchk, you need to make sure that the server doesn’t have the tables open.
Stop the MySQL
Change to the directory where the database is located.
To check all of the tables in a database, type the following command:
If the previous command does not work, you can try deleting temporary files that may be preventing myisamchk from running correctly. To do this, change back to the data dir directory, and then run the following command:
If there are any .TMD files listed, delete them:
Then re-run myisamchk.
To attempt repair a table, execute the following command, replacing TABLE with the name of the table that you want to repair:
Restart the MySQL server
We want to hear your thoughts about this. Is there anything in particular that you love about it? What can we do to improve our services and experience? Leave a comment below or open a ticket on our helpdesk and we’ll personally review all suggestions and feedback.
Since it was founded in 2015, Servercheap has always strived to provide its clients with enterprise-level performance at an unbeatable cost. Servercheap offers a wide range of customizable hybrid and virtual private server hosting services. All Servercheap clients enjoy a 99.9% uptime SLA and 24/7 rapid response support team.
At Servercheap, our core directive has always been to provide our clients with the best services and infrastructure possible, whether you’re hosting a game server, a high-intensity database, a development environment, or anything in-between.
For more information, visit https://www.servercheap.net