Before you attempt to repair corrupted tables, you must first back up the database files. Yes, it is already broken, but this is to minimize the risk of possible additional damage that may be caused by a recovery operation. There is no guarantee that any action you take will not damage intact data blocks. Force recovery of InnoDB with values greater than 4 can corrupt data files, so be sure to do it with a previous backup and ideally on a separate physical copy of the database.
To back up
all files in all databases, follow these steps:
Stop the
mysqld server
service stop Type
the following command for your datadir
. cp -r /var/lib/mysql /var/lib/mysql_bkp
Once we have a backup of the data directory, we are ready to start troubleshooting
.
Identifying
data corruption The error log is your best friend. Generally, when data corruption occurs, you will find relevant information (including links to documentation) in the error log. If you don’t know where it is, see my.cnf and the log_error variable, for details, see this article https://dev.mysql.com/doc/refman/8.0/en/error-log-destination-configuration.html. What you also need to know is your type of storage engine. 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 for diagnosing problems 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 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.department OKemployees.dept_emp OKemployees.dept_manager OKemployees.employees OKEmployees.salariesWarning : Missing table space for table ’employees/wages’Error : Table ’employees.salaries’ does not exist in enginestatus : Operation failed
Data corruption issues can also be related to permission issues. In some cases, the operating system may change the mount point to read-only mode due to R/W issues or this may be due to a user accidentally changing ownership of data files. In such cases, you will find relevant information 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.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 salary count(*); ERROR 1932 (42S02): Table ’employees.salaries’ does not exist in engine
Error log entry
2018-05-10 9:15:38 140703666226944 [ERROR] InnoDB: Error finding tablespace for table ’employees’.’ wages’ in the cache. Trying to load tablespace with space id 92018-05-10 9:15:38 140703666226944 [ERROR] InnoDB: Operating System Error Number 13 in File Operation.2018-05-10 9:15:38 140703666226944 [ERROR] InnoDB: Error means mysqld does not have directory access rights.2018-05-10 9:15:38 140703666226944 [ERROR] InnoDB: Unable to Open Data File for Read Only: ‘./employees/salaries.ibd’ OS Error: 812018-05-10 9:15:38 140703666226944 [ERROR] InnoDB: OS Error Number 13 in a File Operation.2018-05-10 9:15:38 140703666226944 [ERROR] InnoDB: Error means mysqld does not have directory access rights.2018-05-10 9:15:38 140703666226944 [ERROR] InnoDB: Could not find a valid tablespace file for ’employees/wages’. See http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html to learn how to resolve the issue.
InnoDB
table recovery If you are using the InnoDB storage engine
for a database table, you can run the InnoDB recovery process. To enable automatic recovery, MySQL needs innodb_force_recovery option to be enabled. Innodb_force_recovery forces InnoDB to start while preventing background operations from running, so it can dump its tables.
To do this, open my.cnf and add the following line to the [mysqld] section
: [mysqld
]innodb_force_recovery=1service mysql restart
You must start from innodb_force_recovery=1, save the changes to the my.cnf file, and then restart the MySQL server using the appropriate command for your operating system. If you can dump your boards with a 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 know that can corrupt the data.
[mysqld]innodb_force_recovery=1service mysql restart
If it is necessary to change to the highest value, six is the maximum and the most dangerous
.
Once you can start
the database, type the following command to export all databases to the .sql file: mysqldump -all-databases -add-drop-database -add-drop-table > dump.sql
Start mysql, and then try to remove the affected database or databases by using the DROP DATABASE command. If MySQL cannot delete a database, you can delete it manually by following the steps below after stopping the MySQL server.
Mysqld Stop Service
If you were unable to remove a database, type the following commands to delete it manually
. cd /var/lib/mysqlrm -rf <DATABASE> Make
sure that you do not delete the internal directories of the database. Once you’re done, comment out the following line in [mysqld] to disable InnoDB recovery mode.
#innodb_force_recovery=… save the changes to the
my.cnf file and then start
the mysql server service Starting mysqld Type the following command to
restore the databases from the backup file that you created in step 5:
mysql> tee import_database.logmysql> source dump.sql
Repair MyISAM Yes mysqlcheck
Report an error for a table, type the command mysqlcheck with the -repair flag to correct it. The mysqlcheck repair option works while the server is running.
mysqlcheck -uroot -p -r <DATABASE> <TABLE>
If the server is down and for some reason mysqlcheck cannot repair your table, you still have an option to perform recovery directly on the 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 tables in a database, type the following command:
myisamchk *. MYI
If the above command doesn’t work, you can try deleting temporary files that may be preventing myisamchk from running properly. To do this, change back to the directory directory of data directory, and then run the following command:
ls */*. TMD
If there is any . TMD files listed, delete them:
rm */*. TMD Then run myisamchk again.
To attempt to repair a table, run the following command, replacing TABLE with the name of the table you want to repair:
myisamchk
-recover <TABLE>
Restart the MySQL Server service
Starting MySQL