Friday, September 30, 2011

mysql crashed

Issue : mysql crashed after server reboot, mysql start failing every 10-15 minutes with following error:



…..
InnoDB: Doing recovery: scanned up to log sequence number 22 1183672832
InnoDB: Doing recovery: scanned up to log sequence number 22 1188915712
InnoDB: Doing recovery: scanned up to log sequence number 22 1194158592
InnoDB: Doing recovery: scanned up to log sequence number 22 1199401472
InnoDB: Doing recovery: scanned up to log sequence number 22 1204644352
InnoDB: Doing recovery: scanned up to log sequence number 22 1209887232

Here is the solution  work out for me:


Enable #innodb_force_recovery=1 in my.cnf

Again failed with same error

Set innodb_force_recovery=2 failed

innodb_force_recovery=3 failed

innodb_force_recovery=4 successful

mysql started successfuly however few tables got corrupted, see the error logs:


090505 14:07:34 [ERROR] /usr/sbin/mysqld: Table './db_2_1/userclients' is marked as crashed and should be repaired
090505 14:07:34 [Warning] Checking table:   './db_2_1/userclients'
090505 14:07:34 [ERROR] 1 client is using or hasn't closed the table properly
090505 14:08:47 [ERROR] /usr/sbin/mysqld: Table './db_2_1/smsrecord' is marked as crashed and should be repaired
090505 14:08:47 [Warning] Checking table:   './db_2_1/smsrecord'
090505 14:08:47 [ERROR] 1 client is using or hasn't closed the table properly
090505 14:12:26 [Note] Slave I/O thread killed while reading event



now repair the tables



root#mysql>repair table table_name