一 故障描述
发现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 <= purge_sys->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 <= purge_sys->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
参考文档:
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/
本文出自 “Linux SA John” 博客,请务必保留此出处http://john88wang.blog.51cto.com/2165294/1883064