Fix MySQL database corruption – Cloudfanatic

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

Contact US