innodb 引擎数据恢复

Terry Tsang

今天遇到一个问题 mysql-5.5

数据库保障

部分错误日志如下

InnoDB: stored checksum 808812544, prior-to-4.0.14-form stored checksum 959328563  InnoDB: Page lsn 791621944 858666297, low 4 bytes of lsn at page end 841888053  InnoDB: Page number (if stored to page already) 307162691,  InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 841958736  InnoDB: Database page corruption on disk or a failed  InnoDB: file read of page 44864.  InnoDB: You may have to recover from a backup.InnoDB: It is also possible that your operating  InnoDB: system has corrupted its own file cache  InnoDB: and rebooting your computer removes the  InnoDB: error.  130826  9:59:12 [ERROR] Invalid (old?) table or database name 'zs20121215-21'  

检测数据库时候发生下面错误

mysql> check table activity.enterGiveApp;  ERROR 2013 (HY000): Lost connection to MySQL server during query

表结构存在

mysql> desc  activity.goldidea;  +-------------+--------------+------+-----+---------+----------------+  | Field       | Type         | Null | Key | Default | Extra          |  +-------------+--------------+------+-----+---------+----------------+  | id          | int(10)      | NO   | PRI | NULL    | auto_increment |  | userId      | varchar(50)  | YES  |     | NULL    |                |  | localIp     | varchar(100) | YES  |     | NULL    |                |  | create_time | datetime     | YES  |     | NULL    |                |  +-------------+--------------+------+-----+---------+----------------+  4 rows in set (0.06 sec) 

查询数据发生下面错误

mysql> select ID from activity.goldidea where ID < 50;  ERROR 2013 (HY000): Lost connection to MySQL server during query

非 MyISAM 表，不支持 repair 修复

mysql> repair table activity.goldidea;  +-----------------------+--------+----------+---------------------------------------------------------+  | Table                 | Op     | Msg_type | Msg_text                                                |  +-----------------------+--------+----------+---------------------------------------------------------+  | activity.funitv_click | repair | note     | The storage engine for the table doesn't support repair |  +-----------------------+--------+----------+---------------------------------------------------------+  1 row in set (0.01 sec)  

查询存储

mysql> select engine from information_schema.tables where table_schema='activity' and table_name=goldidea';  +--------+  | engine |  +--------+  | InnoDB |  +--------+  1 row in set (0.00 sec)  

尝试修复

my.cnf 启动参数添加

innodb_force_recovery = 6  

重启 mysql

检测表

mysql> check table activity.goldidea;  ERROR 2013 (HY000): Lost connection to MySQL server during query  

mysql> desc  activity.goldidea;  +-------------+--------------+------+-----+---------+----------------+  | Field       | Type         | Null | Key | Default | Extra          |  +-------------+--------------+------+-----+---------+----------------+  | id          | int(10)      | NO   | PRI | NULL    | auto_increment |  | userId      | varchar(50)  | YES  |     | NULL    |                |  | localIp     | varchar(100) | YES  |     | NULL    |                |  | create_time | datetime     | YES  |     | NULL    |                |  +-------------+--------------+------+-----+---------+----------------+  4 rows in set (0.06 sec)

mysql> select count(*) from activity.goldidea;  +----------+  | count(*) |  +----------+  |    60827 |  +----------+  1 row in set (0.13 sec)    mysql> check table activity.goldidea;  +-------------------+-------+----------+----------+  | Table             | Op    | Msg_type | Msg_text |  +-------------------+-------+----------+----------+  | activity.goldidea | check | status   | OK       |  +-------------------+-------+----------+----------+  1 row in set (0.22 sec)

走运地修复好表及数据了