Database page corruption


Database page corruption on disk or a failed file read of page [page id: space=0, page number=290]. You may have to recover from a backup.


  1. Stop the affected MySQL service:
service mysql stop

Back up all the MySQL data storage files. By default, they are located in /var/lib/mysql/

For example:

mkdir /root/mysql_backup
cp -a /var/lib/mysql/* /root/mysql_backup/

Set the innodb_force_recovery value under the [mysqld] section in the MySQL configuration file. This option will allow you to start MySQL service and create all databases dump.

For example:

vi /etc/my.cnf 
[mysqld] innodb_force_recovery = 2

Start the MySQL service.

If the service fails to start, set the parameter innodb_force_recovery to greater value and try starting MySQL again.
Value of the parameter innodb_force_recovery can be from 1 to 6.
Warning: Only set innodb_force_recovery to a value greater than 0 in an emergency situation, so that you can start InnoDB and dump your tables. Values of 4 or greater can permanently corrupt data files. Therefore, increase this value incrementally, as necessary. Please see more details in the official MySQL Documentation.
If the service fails to start with an error like:
InnoDB: Waiting for the background threads to start
Add directive innodb_purge_threads according to the following article: Unable to start MySQL service: InnoDB: Waiting for the background threads to start

Try to dump all databases:

mysql -Ns -u***  -Ne"show databases"|grep -v information_schema | grep -v performance_schema > /root/db_list.txt

mkdir /root/db_backup/

cat /root/db_list.txt | while read i; do mysqldump -u*** "$i" --routines --databases > /root/db_backup/"$i".sql; echo $i; sleep 5; done

If the dump fails with an error like:

Incorrect information in file: './psa/APSApplicationItems.frm' when using LOCK TABLES"ยด

then increase innodb_force_recovery value, restart MySQL service, and try to dump the databases again. It is better to dump databases one by one, separately. In that case, there is no need to go through restore of all databases once again if restore failed for some reason. If unable to dump the databases, then try using method II (Copy table content) or III (Restore from the backup) which are described below.

Remove all the MySQL data storage files except the mysql folder. For example:

rm -rf ls -d /var/lib/mysql/* | grep -v "/var/lib/mysql/mysql"

Remove the innodb_force_recovery option from the MySQL configuration file.

Restart the MySQL service:

service mysqld restart

Check the MySQL log file for any errors.

Restore databases from the dumps. For example:

for db in `cat /root/db_list.txt`; do echo -e "Importing $db..."; mysql -u*** < /root/db_backup/$db.sql; done