在2017年里,项目上的MySQL出了几个问题,记录总结一下,给自己一个教训!

废话不多说了,直接进入问题主题:

问题一:MySQL单表千万级数据量"add"字段异常分析

问题描述:
项目一个新功能,需要对有1200w+的表"tb_bw_mobile"新增2个字段"batch_num"、"data_status";执行SQL月5-6分钟后,查询数据,发现未增加字段;约10-13分钟后limit 1、10、100条数据,均为发现有新增的2个字段;此时查询表结构,发现表结构未变化;约24分钟后,MySQL命令行端抛出异常"ERROR 1034 (HY000): Incorrect key file for table 'tb_ec_bw_mobile'; try to repair it";再此核实表结构、数据,发现均未有任何改变;
当时就蒙蔽了,怎么会提示"try to repair it",表结构怎么会有问题?通知测试人员,将生产的表结构和10w条数据导出来在测试环境进行测试,结果并没有什么问题;此时查看MySQL日志,发现集群出现了异常
MySQL异常故障总结
执行SQL时抛出异常,尝试4次修改当前执行事务未306;修改失败后,当前事务在从节点的状态从SYNCD->OPEN->CLOSED,再看后面的信息,MySQL从节点服务器异常。
此时重启mysql服务,查看mysql进程;基于rsync服务以及4444端口来同步恢复数据;同时查看进程的线程和io,确定是在做数据同步;约30分钟后,查询mysql日志及系统日志,mysql数据同步成功且成功加入集群。集群正常提供服务
MySQL异常故障总结

**此时回过头来查看执行sql抛出的异常"ERROR 1034 (HY000): Incorrect key file for table 'tb_ec_bw_mobile'; try to repair it";在官网中看到解决办法,需创建一张临时表做数据迁移,而不是直接对千万级数据直接add字段;
  • MySQL异常故障总结

问题二:MySQL临时表问题

问题描述:
MySQL应用所在云主机系统磁盘容量不断减小,在tmp目录下不断生成以MAD、MAI为后缀的文件,导致前端web服务mgr在1min对mysql发起请求的连接高于1000+;此时查看mysql的进程,发现有”Copy data to tmp”的相关事务在执行,原因是黑白名单功能把”tb_ec_contact”和”tb_ec_bw_mobile”两张千万级数据表关联起来
MySQL异常故障总结
问题分析:
Mysql临时表分为”内存临时表”和”磁盘临时表”;一般情况下,MySQL会先创建内存临时表,但是内存临时表超过配置指定的值后,MySQL会将内存临时表导出到磁盘临时表;使用临时表的场景:对Innodb表进行alter和create操作或者是sql语句效率低下事务执行过慢,未做优化,以下为网关说明;
MySQL异常故障总结

    需要注意的是:此配置默认路径是/tmp目录,生成以MAD、MAI为后缀的文件,在短时间内很容易将系统磁盘占慢,导致依赖mysql的程序运行异常;文件删除后系统磁盘空间也不会释放,因为mysql的线程正在运行,除非停止mysql服务;(可以使用lsof查看);但是好处是如果线程执行完了,会将生成的文件自动删除,释放系统磁盘空间;

此参数配置不能在线进行全局修改,只能先停止mysql服务,在配置文件中写明相关参数重启服务才行;但是临时表的空间和行数可在线修改;
show global variables like “%tmp%”;
tmpdir ##指明mysql磁盘临时表存储路径
slave_load_tmpdir=/data/mysql/slave_tmpdir ##指明从节磁盘临时表路径
max_tmp_tables=128 ##指明支持多少个内存临时表
tmp_table_size=33554432 ##指明临内存时表的大小
max_heap_table_size=33554432 ##指明内存临时表表支持多少行