【问题描述】
mysqld.log日志：
2021-07-27T23:49:57.579647Z 4763264 [ERROR] InnoDB: Rec address 0x2add84585017, buf block fix count 1
2021-07-27T23:49:57.579694Z 4763264 [ERROR] InnoDB: Index corruption: rec offs 4119 next offs 0, page no 1476398, index PRIMARY of table datam.t_b_xlog. Run CHECK TABLE. You may need to restore from a backup, or dump + drop + reimport the table.
2021-07-27T23:49:57.579704Z 4763264 [ERROR] InnoDB: We detected index corruption in an InnoDB type table. You have to dump + drop + reimport the table or, in a case of widespread corruption, dump all InnoDB tables and recreate the whole tablespace. If the mysqld server crashes after the startup or when you dump the tables. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.
2021-07-28T05:48:33.675704Z 4787173 [Note] Start binlog_dump to master_thread_id(4787173) slave_server(2887171350), pos(mysql-bin.000032, 348060480)
05:50:19 UTC - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.
key_buffer_size=268435456
read_buffer_size=1048576
max_used_connections=941
max_threads=2000
thread_count=681
connection_count=680
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 6432909 K bytes of memory
Hope that’s ok; if not, decrease some variables in the equation.
Thread pointer: 0x2af78c4f0670
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong…
stack_bottom = 2af4dbba9ea8 thread_stack 0x40000
2021-07-28T07:17:31.568276Z 0 [Note] InnoDB: Index 93592 is PRIMARY in table large.bbd_log / Partition PART_20210729 /
2021-07-28T07:17:31.568285Z 0 [Note] InnoDB: It is also possible that your operating system has corrupted its own file cache and rebooting your computer removes the error. If the corrupt page is an index page. You can also try to fix the corruption by dumping, dropping, and reimporting the corrupt table. You can use CHECK TABLE to scan your table for corruption. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.
【问题分析】
log表，频繁插入删除，数据量大，产生大量碎片，发生主键索引页损坏，造成mysql异常不断重启。。
【问题处理】
1.在my.cnf里面设置innodb_force_recovery=3，启动mysql
innodb_force_recovery的参数说明：
1(SRV_FORCE_IGNORE_CORRUPT):忽略检查到的corrupt页。
2(SRV_FORCE_NO_BACKGROUND):阻止主线程的运行，如主线程需要执行full purge操作，会导致crash。
3(SRV_FORCE_NO_TRX_UNDO):不执行事务回滚操作。
4(SRV_FORCE_NO_IBUF_MERGE):不执行插入缓冲的合并操作。
5(SRV_FORCE_NO_UNDO_LOG_SCAN):不查看重做日志，InnoDB存储引擎会将未提交的事务视为已提交。
6(SRV_FORCE_NO_LOG_REDO):不执行前滚的操作。
2.导出数据
mysqldump --user=root --password=xxx_123 --host=10.2.8.148 --single-transaction --master-data=2 --set-gtid-purged=OFF -R -E --triggers --all-databases > /opt/mysql/backup_0409.sql
3.清除innodb_force_recovery
vi my.cnf
4.初始化一个实例
删除已存在数据和日志
mysqld --defaults-file=/etc/my.cnf --initialize --user=mysql
获取临时密码 cat mysqld.log|grep password
mysql -uroot -p临时密码
alter user ‘root’@‘localhost’ identified by 'xxx_123’;
flush privileges;
5.导入dump的数据
为了加快导入速度，建议关闭双11，甚至关闭binlog日志(my.cnf注释log_bin)
修改前查参数值
show GLOBAL variables like ‘foreign_key_checks’;
show GLOBAL variables like ‘unique_checks’;
show GLOBAL variables like ‘innodb_flush_log_at_trx_commit’;
show GLOBAL variables like ‘sync_binlog’;
执行如下加速操作
SET GLOBAL foreign_key_checks=0;
SET GLOBAL unique_checks=0;
SET GLOBAL innodb_flush_log_at_trx_commit=2;
SET GLOBAL sync_binlog=1000;
6.导入数据
nohup mysql -uroot -pxxx_123 </opt/MySQL/backup_0409.sql &>ron_nohup.log
导入后，记得登录执行flush privileges;
【问题规避】
1.常规巡检。在常规巡检中，发现是否有数据也损坏。命令mysqlcheck -uroot –p -c --all-databases=true (注:若是数据量大，时间会很长，建议业务低估进行)
2.对于频繁插入删除的log大表，建议减少索引个数，保留 1-2个就可以。
3.对于频繁插入删除的数据大表，建议1-2个月手动进行一次碎片整理，空间回收。 命令是alter table table_name engine=innodb;或者OPTIMIZE table table_name.
