innodb 引擎数据恢复

今天遇到一个问题 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)

走运地修复好表及数据了