Zabbix mysql innodb文件ibdata1损坏导致mysql无法启动

suncool

　　一 故障描述
　　发现Zabbix mysql的磁盘空间满了，之前升级Zabbix到3.2的时候，history_text这个表分区不起作用了，也没有及时处理。导致这个表越来越大，占了磁盘空间140多G。
　　于是使用mv 命令将history_text.ibd这个文件移动到其他目录下，再使用ln -sf软链接的方式连接到原来的目录下。但是启动mysql报错了。
　　还有看ibdata1比较大，我也是用了软连接方式把这个文件移动到其他目录下。然后mysql就无法启动了。
[Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details)　　

　　
[Warning] Buffered warning: option 'table_open_cache': unsigned value 8388608 adjusted to 524288
　　
[Warning] Buffered warning: Changed limits: max_open_files: 102400 (requested 1078586)
　　
[Warning] Buffered warning: Changed limits: table_open_cache: 36195 (requested 524288)
　　

　　
7fdcafc77700  InnoDB: 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 ;
　　二 故障解决

• 　　[Warning] 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.
　　[Warning] Buffered warning: option 'table_open_cache': unsigned value 8388608 adjusted to 524288
　　[Warning] Buffered warning: Changed limits: max_open_files: 102400 (requested 1078586)
　　[Warning] 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.
　　7fdcafc77700  InnoDB: 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/