Database page corruption on disk or a failed file read …. Again – Mysql
Again encountered the stopped server in the morning because of page file corruption. Reason was the bad power situation in office during night shifts and which costs us the whole day to repair the testing server.
As from the last time learnings of page file corruption (previous post) we knew how to start the server in recovery mode by changing the /etc/my.cnf file, we started it in recorvery mode 3 (innodb_force_recovery = 3) which is SRV_FORCE_NO_TRX_UNDO i.e. Do not run transaction rollbacks after recovery.
We were able to do few things which are allowed in this recovery mode like checking few databases and integrity of some of the application data. We tried to check mysql tables using mysqlcheck command.
$ mysqlcheck -u root -p --all-databases
Using above command it was not not completing the command and was giving following error
RROR 2013 (HY000): Lost connection TO MySQL server during query
Same error we got when we tried to backup all database schemas in the DB. It took backup of some of the schemas but soon gives above error. We were stuck as not able to take backup of data and also were not able to repair the tables/databases.
tried following thing and backup was successfully taken after that. Even we figured out which tables got corrupted as we could run mysqlcheck successfully also afterwards. We did following simple thing in /etc/my.cnf and restarted the server:
[mysqld] innodb_force_recovery = 4
Finally recovered all the data using the backup and server came back to life after the whole day of effort.
Most Commented Posts
If you enjoyed this post, please consider to leave a comment or subscribe to the feed and get future articles delivered to your feed reader.
diski dance is easy for my friends, but not for me