innodb index page损坏修复过程

报错信息：

2017-10-18 10:33:43 7f2063f03700 InnoDB: uncompressed page, stored checksum in field1 4284044754, calculated checksums for field1: crc32 1431673630, innodb 4253233        56, none 3735928559, stored checksum in field2 1782518339, calculated checksums for field2: crc32 1431673630, innodb 545991519, none 3735928559, page LSN 1 1292887        730, low 4 bytes of LSN at page end 1291533045, page number (if stored to page already) 26722, space id (if created with >= MySQL-4.1.1 and stored already) 48
InnoDB: Page may be an index page where index id is 121
InnoDB: Database page corruption on disk or a failed

nnoDB: database modifications by the user. Shut down
InnoDB: mysqld and edit my.cnf so thatInnoDB: innodb_force_... is removed.
InnoDB: innodb_force_recovery is on: we do not allow
InnoDB: database modifications by the user. Shut down
InnoDB: mysqld and edit my.cnf so thatInnoDB: innodb_force_... is removed.
InnoDB: innodb_force_recovery is on: we do not allow
InnoDB: database modifications by the user. Shut down
InnoDB: mysqld and edit my.cnf so thatInnoDB: innodb_force_... is removed.
InnoDB: innodb_force_recovery is on: we do not allow
InnoDB: database modifications by the user. Shut down
InnoDB: mysqld and edit my.cnf so thatInnoDB: innodb_force_... is removed.

查找相关对像信息：

(root@localhost:mysql.sock)[information_schema]select * from INNODB_SYS_INDEXES where index_id = 121 ;
+----------+-----------+----------+------+----------+---------+-------+
| INDEX_ID | NAME      | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE |
+----------+-----------+----------+------+----------+---------+-------+
|      121 | history_1 |       59 |    0 |        2 |       4 |    48 |
+----------+-----------+----------+------+----------+---------+-------+

(root@localhost:mysql.sock)[information_schema]select * from INNODB_SYS_TABLES where table_id = 59 ;
+----------+----------------+------+--------+-------+-------------+------------+---------------+
| TABLE_ID | NAME           | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE |
+----------+----------------+------+--------+-------+-------------+------------+---------------+
|       59 | zabbix/history |    1 |      7 |    48 | Antelope    | Compact    |             0 |
+----------+----------------+------+--------+-------+-------------+------------+---------------+
1 row in set (0.00 sec)

(root@localhost:mysql.sock)[zabbix]drop index history_1 on history ;
ERROR 1881 (HY000): Operation not allowed when innodb_forced_recovery > 0.
(root@localhost:mysql.sock)[zabbix]set innodb_forced_recovery = 0 

innodb表的index不能用repair修复。
innodb_force_recovery 官方相关说明如下：

innodb_force_recovery is 0 by default (normal startup without forced recovery). The permissible
nonzero values for innodb_force_recovery are 1 to 6. A larger value includes the functionality of
lesser values. For example, a value of 3 includes all of the functionality of values 1 and 2
As a safety measure, InnoDB prevents INSERT, UPDATE, or DELETE operations when
innodb_force_recovery is greater than 0. As of MySQL 5.6.15, an innodb_force_recovery setting
of 4 or greater places InnoDB in read-only mode.
• 1 (SRV_FORCE_IGNORE_CORRUPT)
Lets the server run even if it detects a corrupt page. Tries to make SELECT * FROM tbl_name jump
over corrupt index records and pages, which helps in dumping tables.
• 2 (SRV_FORCE_NO_BACKGROUND)
Prevents the master thread and any purge threads from running. If a crash would occur during the purge
operation, this recovery value prevents it.
• 3 (SRV_FORCE_NO_TRX_UNDO)
Does not run transaction rollbacks after crash recovery.
• 4 (SRV_FORCE_NO_IBUF_MERGE)
Prevents insert buffer merge operations. If they would cause a crash, does not do them. Does not
calculate table statistics. This value can permanently corrupt data files. After using this value, be
prepared to drop and recreate all secondary indexes. As of MySQL 5.6.15, sets InnoDB to read-only.
• 5 (SRV_FORCE_NO_UNDO_LOG_SCAN)
Does not look at undo logs when starting the database: InnoDB treats even incomplete transactions as
committed. This value can permanently corrupt data files. As of MySQL 5.6.15, sets InnoDB to readonly.
• 6 (SRV_FORCE_NO_LOG_REDO)
Does not do the redo log roll-forward in connection with recovery. This value can permanently corrupt
data files. Leaves database pages in an obsolete state, which in turn may introduce more corruption into
B-trees and other database structures. As of MySQL 5.6.15, sets InnoDB to read-only

操作步骤：
因为被破坏的地方只在索引的部分，所以当使用innodb_force_recovery = 1运行InnoDB时，操作如下：

执行check，repair table 都无效
alter table history engine =myisam; #也报错了，因为模式是innodb_force_recovery =1。
ERROR 1025 (HY000): Error on rename of ‘…’ to ‘….’ (errno: -1)

建立一张表：
create table history_bak #和原表结构一样，只是把INNODB改成了MYISAM。

把数据导进去

insert into history_bak select * from history;

删除掉原表：

drop table history;

注释掉innodb_force_recovery 之后，重启。
重命名：

rename table history_bak to history;

最后该回存储引擎

alter table history engine = innodb

总结：
这里的一个重要知识点就是 对innodb_force_recovery 参数的理解了，要是遇到数据损坏甚至是其他的损坏。可能上面的方法不行了，需要尝试另一个方法：insert into tb select * from ta limit X；甚至是dump出去，再load回来。