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
service mysqld stop |
Type the following command for your datadir.
cp -r /var/lib/mysql /var/lib/mysql_bkp |
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.
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 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.
mysqlcheck -uroot -p <DATABASE> |
Replace DATABASE with the name of the database, and replace TABLE with the name of the table that you want to check:
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 OKemployees.dept_emp OKemployees.dept_manager OKemployees.employees OKEmployees.salariesWarning : Tablespace is missing for table 'employees/salaries'Error : Table 'employees.salaries' doesn't exist in enginestatus : Operation failed |
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.
[root@node1 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.ibddrwxr-xr-x. 7 mysql mysql 4096 05-10 07:12 ..drwx------. 2 mysql mysql 4096 05-10 07:17 . |
MySQL Client
MariaDB [employees]> select count(*) from salaries;ERROR 1932 (42S02): Table 'employees.salaries' doesn't exist in engine |
Error log entry
2018-05-10 9:15:38 140703666226944 [ERROR] InnoDB: Failed to find tablespace for table `employees`.`salaries` in the cache. Attempting to load the tablespace with space id 92018-05-10 9:15:38 140703666226944 [ERROR] InnoDB: Operating system error number 13 in a file operation.2018-05-10 9:15:38 140703666226944 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory.2018-05-10 9:15:38 140703666226944 [ERROR] InnoDB: Cannot open datafile for read-only: './employees/salaries.ibd' OS error: 812018-05-10 9:15:38 140703666226944 [ERROR] InnoDB: Operating system error number 13 in a file operation.2018-05-10 9:15:38 140703666226944 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory.2018-05-10 9:15:38 140703666226944 [ERROR] InnoDB: Could not find a valid tablespace file for `employees/salaries`. Please refer to http://dev.mysql.com/doc/refman/5.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 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:
[mysqld]innodb_force_recovery=1service mysql restart |
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.
[mysqld]innodb_force_recovery=1service mysql restart |
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:
mysqldump --all-databases --add-drop-database --add-drop-table > dump.sql |
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.
service mysqld stop |
If you were unable to drop a database, type the following commands to delete it manually.
cd /var/lib/mysqlrm -rf <DATABASE> |
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.
#innodb_force_recovery=... |
Save the changes to the my.cnf file, and then start the MySQL server
service mysqld start |
Type the following command to restore the databases from the backup file you created in step 5:
mysql> tee import_database.logmysql> source dump.sql |
Repairing MyISAM
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.
mysqlcheck -uroot -p -r <DATABASE> <TABLE> |
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
service mysqld stopcd /var/lib/mysql |
Change to the directory where the database is located.
cd /var/lib/mysql/employeesmyisamchk <TABLE> |
To check all of the tables in a database, type the following command:
myisamchk *.MYI |
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:
ls */*.TMD |
If there are any .TMD files listed, delete them:
rm */*.TMD |
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:
myisamchk --recover <TABLE> |
Restart the MySQL server
service mysqld start |
