How To Repair A Corrupt Mysql Database

How do MySQL tables get corrupted? There are many ways to spoil data files. Frequently, corruption is due to defects in the underlying platform, which MySQL relies on to shop 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 example was in the middle of writing information to deejay, it could write the data partially which may end up with a page checksum that is different than expected. There have as well been bugs in MySQL and so even if the server hardware is ok, MySQL itself tin can cause corruption.

Normally when MySQL data gets corrupted the recommendation is to restore it from the last backup, switch to DR server or accept downwardly the affected node if you have Galera cluster to serve data immediately from other nodes. In some cases you tin't - if the backup is not at that place, the cluster was never set up, your replication is downwardly for a very long time, or the DR process was never tested. Fifty-fifty if you have a fill-in, you may still desire to have some actions to endeavor recovery as it may take less time go back online.

MyISAM, the bad and ugly

InnoDB 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 fourth dimension 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 information to be corrupted. The recommendation is to migrate from MyISAM to InnoDB, merely there may be cases where this is not possible.

Primum not nocere, the backup

Before you effort to repair corrupted tables, you should back your database files starting time. Yeah, information technology'due south already broken merely this is to minimize the take chances 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 tin corrupt data files, and so make certain you will do it with prior backup and ideally on a divide physical copy of the database.

To dorsum up all of the files from all of your databases, follow these steps:

End the MySQL server

          service mysqld stop

Blazon the following control for your datadir.

          cp -r /var/lib/mysql /var/lib/mysql_bkp

After we take a backup copy of the data directory, nosotros are set up to get-go 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 fault log. If you don't know where it's located, bank check my.cnf and variable log_error, for more details cheque this commodity https://dev.mysql.com/doc/refman/eight.0/en/mistake-log-destination-configuration.html. What you should too know is your storage engine blazon. You lot can detect this information in the error log or in information_schema.

          mysql> select table_name,engine from information_schema.tables where table_name = '<Table>' and table_schema = '<DATABASE>';

The main tools/commands to diagnose issues with data abuse are Cheque Table, REPAIR Tabular array, and myisamchk. The mysqlcheck client performs table maintenance: Information technology checks, repairs (MyISAM), optimizes or analyzes tables while MySQL is running.

          mysqlcheck -uroot -p <DATABASE>

Supersede DATABASE with the name of the database, and replace TABLE with the name of the tabular array that you desire to cheque:

          mysqlcheck -uroot -p <DATABASE> <TABLE>

Mysqlcheck checks the specified database and tables. If a table passes the check, mysqlcheck displays OK for the table.

          employees.departments                              OK employees.dept_emp                                 OK employees.dept_manager                             OK employees.employees                                OK Employees.salaries Alert  : Tablespace is missing for table 'employees/salaries' Error    : Table 'employees.salaries' doesn't be in engine condition   : Operation failed employees.titles                                   OK

Data corruption issues may exist also related to permission issues. In some cases, Os can switch mount indicate to read-only fashion due to R/W issues or this can be caused by a user who accidentally changed ownership of the information files. In such cases, you lot will find relevant data in the error log.

          [[email protected] employees]# ls -rtla ... -rw-rw----. 1 mysql mysql  28311552 05-10 06:24 titles.ibd -rw-r-----. 1 root  root  109051904 05-10 07:09 salaries.ibd drwxr-xr-x. 7 mysql mysql      4096 05-10 07:12 .. drwx------. 2 mysql mysql      4096 05-ten 07:17 .

MySQL Client

          MariaDB [employees]> select count(*) from salaries; Fault 1932 (42S02): Table 'employees.salaries' doesn't exist in engine

Fault log entry

          2018-05-10  9:15:38 140703666226944 [Fault] InnoDB: Failed to observe tablespace for table `employees`.`salaries` in the cache. Attempting to load the tablespace with space id 9 2022-05-10  9:15:38 140703666226944 [Error] InnoDB: Operating system fault number 13 in a file functioning. 2022-05-10  ix:15:38 140703666226944 [ERROR] InnoDB: The error means mysqld does not accept the access rights to the directory. 2022-05-10  9:15:38 140703666226944 [Fault] InnoDB: Cannot open datafile for read-only: './employees/salaries.ibd' OS error: 81 2022-05-10  9:15:38 140703666226944 [ERROR] InnoDB: Operating organization error number 13 in a file operation. 2022-05-10  nine:15:38 140703666226944 [Mistake] InnoDB: The error means mysqld does not have the admission rights to the directory. 2022-05-x  nine:fifteen:38 140703666226944 [Error] InnoDB: Could not find a valid tablespace file for `employees/salaries`. Please refer to http://dev.mysql.com/doc/refman/five.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.

Recovering InnoDB table

If you are using the InnoDB storage engine for a database table, you can run the InnoDB recovery process.
To enable motorcar recovery MySQL needs innodb_force_recovery choice to be enabled. Innodb_force_recovery forces InnoDB to showtime up while preventing background operations from running, then that you lot can dump your tables.

To do this open my.cnf and add together the following line to the [mysqld] department:

          [mysqld] innodb_force_recovery=1 service mysql restart

You should get-go from innodb_force_recovery=one save the changes to my.cnf file, and then restart the MySQL server using the appropriate command for your operating organisation. If you are able to dump your tables with an innodb_force_recovery value of 3 or less, then you are relatively condom. In many cases you will have to go up to four and as yous already know that can corrupt data.

          [mysqld] innodb_force_recovery=i service mysql restart

If needed modify to the higher value, 6 is the maximum and nearly dangerous.

Once you are able to start your database, type the following command to export all of the databases to the databases.sql file:

          mysqldump --all-databases --add-drop-database --add together-drop-tabular array > dump.sql

Commencement mysql, and and then endeavor to driblet the affected database or databases using the Driblet DATABASE control. If MySQL is unable to drop a database, y'all tin delete it manually using the steps below after y'all finish the MySQL server.

          service mysqld cease

If y'all were unable to drop a database, type the post-obit commands to delete it manually.

          cd /var/lib/mysql rm -rf <DATABASE>

Brand sure you lot do not delete the internal database directories.
After you are done, comment out the following line in the [mysqld] to disable InnoDB recovery style.

          #innodb_force_recovery=...

Save the changes to the my.cnf file, and and then starting time the MySQL server

          service mysqld start

Type the post-obit command to restore the databases from the backup file you created in footstep 5:

          mysql> tee import_database.log mysql> source dump.sql

Repairing MyISAM

If mysqlcheck reports an error for a table, type the mysqlcheck command with -repair flag to set it. The mysqlcheck repair option works while the server is upwards and running.

          mysqlcheck -uroot -p -r <DATABASE> <Tabular array>

If the server is down and for any reason mysqlcheck cannot repair your table, yous still have an choice to perform recovery direct on files using myisamchk. With myisamchk, y'all need to brand certain that the server doesn't take the tables open up.

Stop the MySQL

          service mysqld stop cd /var/lib/mysql

Change to the directory where the database is located.

          cd /var/lib/mysql/employees myisamchk <Tabular array>

To cheque all of the tables in a database, type the post-obit control:

          myisamchk *.MYI

If the previous command does not work, y'all tin try deleting temporary files that may exist preventing myisamchk from running correctly. To do this, change back to the data dir directory, and then run the following command:

          ls */*.TMD

If in that location are any .TMD files listed, delete them:

          rm */*.TMD

Then re-run myisamchk.

To attempt repair a table, execute the following command, replacing Tabular array with the name of the tabular array that y'all want to repair:

          myisamchk --recover <TABLE>

Restart the MySQL server

          service mysqld commencement

How to avoid data loss

There are several things you can exercise to minimize the risk of unrecoverable data. Start of all backups. The problem with backups is that sometimes they can be disregarded. For cron scheduled backups, usually we write wrapper scripts that detect problems in the backup log, just that does non include cases when the backup didn't start at all. Cron can sometimes hang and oft in that location is no monitoring set on it. Some other potential issue could be the case when the backup was never prepare. The good practice is to run reports from a split up tool that will analyze the backup status and inform y'all most 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 abuse you lot should always consider clustered systems. Information technology's only a thing of time when the database will crash or become corrupted, and so it's good to take a re-create which you lot can switch to. It could be Master / Slave replication. The important aspect hither is to accept safe automatic recovery to minimize the complication of the switchover and minimize the recovery time (RTO).

ClusterControl auto recovery features