Zabbix mysql innodb文件ibdata1损坏导致mysql无法启动
一 故障描述发现Zabbix mysql的磁盘空间满了，之前升级Zabbix到3.2的时候，history_text这个表分区不起作用了，也没有及时处理。导致这个表越来越大，占了磁盘空间140多G。
于是使用mv 命令将history_text.ibd这个文件移动到其他目录下，再使用ln -sf软链接的方式连接到原来的目录下。但是启动mysql报错了。
还有看ibdata1比较大，我也是用了软连接方式把这个文件移动到其他目录下。然后mysql就无法启动了。
TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details)
Buffered warning: option 'table_open_cache': unsigned value 8388608 adjusted to 524288
Buffered warning: Changed limits: max_open_files: 102400 (requested 1078586)
Buffered warning: Changed limits: table_open_cache: 36195 (requested 524288)
7fdcafc77700InnoDB: Warning: purge reached the head of the history list,
InnoDB: but its length is still reported as 42949673139! Make a detailed bug
InnoDB: report, and submit it to http://bugs.mysql.com
InnoDB: Assertion failure in thread 140585818617600 in file trx0purge.cc line 699
InnoDB: Failing assertion: purge_sys->iter.trx_no rseg->last_trx_no
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
14:14:05 UTC - mysqld got signal 6 ;
二 故障解决
[*] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details)
解决办法：
在/etc/my.cnf中添加
explicit_defaults_for_timestamp=1
2.
Buffered warning: option 'table_open_cache': unsigned value 8388608 adjusted to 524288
Buffered warning: Changed limits: max_open_files: 102400 (requested 1078586)
Buffered warning: Changed limits: table_open_cache: 36195 (requested 524288)
解决办法：
这个3个警告信息都和系统文件打开数有关
ulimit -n 1024000
/etc/security/limits.conf
/etc/security/limits.d/90-nproc.conf
两个文件中的nofile调大些
3.
7fdcafc77700InnoDB: Warning: purge reached the head of the history list,
InnoDB: but its length is still reported as 42949673139! Make a detailed bug
InnoDB: report, and submit it to http://bugs.mysql.com
InnoDB: Assertion failure in thread 140585818617600 in file trx0purge.cc line 699
InnoDB: Failing assertion: purge_sys->iter.trx_no rseg->last_trx_no
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
14:14:05 UTC - mysqld got signal 6 ;
从日志中可以看出MySQL的InnoDB崩溃了。
这时候可以设置MySQL的InnoDB为恢复模式，恢复模式会使用数据库只读，用于不能更新、插入或者操作其他改变数据的操作。
innodb_force_recovery有0~6个级别
设置
innodb_force_recovery=2
innodb_purge_threads=0
然后检查有哪些表损坏
mysqlcheck -A -uroot -p
然后将需要的数据备份下来，重新导入到新的mysql库中，
/opt/app/mysql/bin/mysqldump -uroot -p zabbix --ignore-table=zabbix.history --ignore-table=zabbix.history_uint--ignore-table=zabbix.history_str--ignore-table=zabbix.history_str > zabbixbackup20161215.sql
/opt/app/mysql/bin/mysql -uroot -p zabbix < /mnt/sdb1/zabbixbackup20161215.sql
参考文档：
https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp
http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
http://blackbird.si/mysql-corrupted-innodb-tables-recovery-step-by-step-guide/
页:
[1]