How do MySQL tables get corrupted? There are many ways to spoil data files. Often, corruption is due to defects in the underlying platform, which MySQL relies on to store and retrieve data – disk subsystem, controllers, communication channels, drivers, firmware or other hardware faults. Data corruption can also occur if the MySQL server daemon restarts suddenly, or your server reboots due to a crash of other OS components. If the database instance was in the middle of writing data to disk, it could write the data partially which may end up with a page checksum that is different than expected. There have also been bugs in MySQL so even if the server hardware is ok, MySQL itself can cause corruption. Show
Usually when MySQL data gets corrupted the recommendation is to restore it from the last backup, switch to DR server or take down the affected node if you have Galera cluster to serve data immediately from other nodes. In some cases you can’t – if the backup is not there, the cluster was never set up, your replication is down for a very long time, or the DR procedure was never tested. Even if you have a backup, you may still want to take some actions to attempt recovery as it may take less time get back online. MyISAM, the bad and uglyInnoDB is more fault-tolerant than MyISAM. InnoDB has auto_recovery features and is much safer as compared to the older MyISAM engine. MyISAM tables can easily get corrupted when lots of writes happen and a lot of locks happen on that table. The storage engine “writes” data to the filesystem cache, which may take some time before it is flushed to disk. Therefore if your server restarts suddenly, some unknown amount of data in the cache is lost. That’s a usual way for MyISAM data to be corrupted. The recommendation is to migrate from MyISAM to InnoDB, but there may be cases where this is not possible. Primum non nocere, the backupBefore 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 IdentificationThe 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.
MySQL Client
Error log entry
Recovering InnoDB tableIf you are using the InnoDB storage engine for a database table, you can run the InnoDB recovery process. 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.
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:
Repairing MyISAMIf 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
How to avoid data lossThere are several things you can do to minimize the risk of unrecoverable data. First of all backups. The problem with backups is that sometimes they can be overlooked. For cron scheduled backups, usually we write wrapper scripts that detect problems in the backup log, but that does not include cases when the backup didn’t start at all. Cron can sometimes hang and often there is no monitoring set on it. Another potential issue could be the case when the backup was never set up. The good practice is to run reports from a separate tool that will analyze the backup status and inform you about missing backups schedules. You can use ClusterControl for that or write your own programs.
ClusterControl operational backup report To reduce the impact of the possible data corruption you should always consider clustered systems. It’s just a matter of time when the database will crash or get corrupted, so it’s good to have a copy which you can switch to. It could be Master / Slave replication. The important aspect here is to have safe automatic recovery to minimize the complexity of the switchover and minimize the recovery time (RTO). ClusterControl auto recovery features Subscribe to get our best and freshest contentWhy we repair a table & How can we repair a MySQL table?MySQL Repair Table allows us to repair or fix the corrupted table. The repair table in MySQL provides support only for selected storage engines, not for all. It is to ensure that we have a few privileges like SELECT and INSERT to use this statement.
How check MySQL table is corrupted?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.. # mysqlcheck -c -u USER_NAME -p --all-databases.. # mysqlcheck -a DB_NAME TABLE_NAME -u USER_NAME -p.. How do I fix a corrupted InnoDB table?How to Repair Corrupt InnoDB Table in MySQL?. Fix Corrupted InnoDB Table Manually. ... . Step 1: Restart the MySQL Service. ... . Step 2: Force InnoDB Recovery. ... . Step 3: Drop the Corrupt Table. ... . Step 4: Restore the Table. ... . Step 5: Restart MySQL in Normal Mode.. What is MySQL repair?mysqlcheck is a maintenance tool that allows you to check, repair, analyze and optimize multiple tables from the command line. It is essentially a commandline interface to the CHECK TABLE, REPAIR TABLE, ANALYZE TABLE and OPTIMIZE TABLE commands, and so, unlike myisamchk and aria_chk, requires the server to be running.
|