mysql异常案例分析

Eric

问题背景

mysql主从同步
1、结构:主库业务数据增删改查,从库是负责报表的查询
2、问题:主从异常导致报表数据不准确,上线期间,出现多次,发现时同步已经中断很长时间。
3、解决:脚本自动监控+短信提醒,及时发现异常,人工干预恢复。
mysql查询效率
问题:系统上线运行后,随着数据的不断增长,可能一些语句执行会越来越慢。
解决方向:借助mysql EXPLAIN 排查sql语句。进行针对性修改。
mysql连接数增长异常
1、问题:4月底多次出现知识库mysql连接被占满,导致系统故障。
不是瞬间,隔几天会出现一次。
2、排查:死锁、连接未关闭(接口平台groovy脚本)、show processlist
3、解决:根据排查,优化代码及jdbc连接数配置,解决故障。
代码执行效率
排查:借助Fiddler(http协议调试代理工具),查找执行慢的代码。
解决:优化代码结构,解决问题。
image.png

mysql主从同步

监控脚本
监测内容:
1、检查主机IP及端口是否一致
2、检查主从mysql-bin文件是否一致
3、检查主从日志文件位置Position是否一致
4、检查slave_IO_running和slave_sql_running两个线程是否启动【重要】
异常短信发送:使用curl -d 参数 请求连接 (发送短信内容及号码)
人工干预
image.png
1、通过sql查询导致同步异常的数据 select * from performance_schema.replication_applier_status_by_worker\G
2、步骤1会查出具体哪张表的问题数据,修改查询出来的异常数据
3、跳过异常步骤 set global sql_slave_skip_counter =1;
4、重新启动同步操作 start slave;
5、查询同步状态是否正常 show slave status\G

mysql连接数异常

监控脚本
监测内容:
1、查询mysql进程,select * from infomation_schema.processlist
Id: 就是这个线程的唯一标识\User: 启动线程的用户\Host: 记录了发送请求的客户端的 IP 和 端口号\DB: 数据库名称\Command: 是指此刻该线程正在执行的命令\Time: 表示该线程处于当前状态的时间(秒)\State: 线程的状态\Info: 记录线程执行的语句。默认只显示前100个字符.
2、查询锁表信息:show open tables where in_use> 0;
3、当连接数超出阈值时,短信提醒,并且抓取1和2的实时结果,定位问题连接。
排查优化
排查:
1、select * from infomation_schema.processlist及锁表信息查询出耗时进程,包含3个:A、sleep进程;
B、km_search_log; C、km_task_sheet 及sys_task_type 。
2、排查A:检查jdbc配置,db.druid.timeBetweenEvictionRunsMillis=60000,db.druid.initialSize=5,60秒关闭空闲连接,B语句耗时长、C语句执行间隔短于执行时长,造成一直在创建新的连接,初始化连接比较小,sleep连接不能回收。
优化--现在正常情况是在30-40之间
1、调整初始化连接至30,减少连接的新建次数,空出回收空闲连接的时间。
2、梳理登录业务,屏蔽km_search_log查询,效果明显
3、反馈信息提醒业务(km_task_sheet 及sys_task_type),原20秒执行一次,调整频率为两分钟,减少语句进程一直叠加。

mysql查询效率-EXPLAIN

EXPLAIN查询结果字段解释
id :编号,id值越大执行优先级越高,id值相同则从上往下执行,id为null最后执行。
select_type :查询类型
table :表名
partitions :分区
type :类型,type常见类型从最优到最差:system > const > eq_ref > ref > range > index > ALL,
possible_keys :预测用到的索引
key :实际使用的索引,如果没有为null。根据这个字段判断是否需要增加索引。
key_len :实际使用索引的长度,
ref :表之间的引用
rows :通过索引查询到的数据量
filtered :指返回结果的行占需要读到的行(rows列的值)的百分比。
Extra :额外的信息
1、Using index
2、Using where
3、Using where Using index
4、NULL
5、Using index condition
6、Using temporary表示MySql需要创建一张临时表来处理查询。一般需要优化

mysql查询效率-案例分析

1、关联表字段类型不一致,导致索引失效
描述:原因是his_phs_send的phs_id和csp_contact的call_id字段类型不一致,导致索引失效
排查过程:
EXPLAIN 查看语句分析结果,d表Extra提示未使用索引,key值使用索引项为空,type为ALL,效率最慢,数据超600W。缩减语句至his_phs_send和csp_contact关联,索引还未执行,定位至d.call_id未走索引,排查原因,phs_id为bigint类型,call_id为varchar类型,修改phs_id为varchar,语句执行时间由9秒多减少至0.3秒。
image.png
2、减少关联出来的数据量
image.png
3、对应字段增加索引
4、减少语句中的子查询
多个子查询拼接的sql语句,索引不会生效

代码执行效率-Fiddler

image.png
image.png
image.png
image.png
image.png

mysql恢复delete数据

1、日志查找delete删除语句
mysqlbinlog --no-defaults --database=cspwcsdb --start-datetime="2019-01-11 08:20:09" --stop-datetime="2019-01-11 10:00:50" --base64-output=decode-rows -v -v mysql-bin.000022 | sed -n '/### DELETE FROM /,/COMMIT/p' > cspwcsdb20190111-3.txt

2、转换delete语句为insert语句
cat cspwcsdb20190111-3.txt | sed -n '/###/p' | sed 's/### //g;s//*./,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g;' | sed -r 's/(@4.),/\1;/g' | sed 's/@[1-9]=//g' > t1.sql

3、替换;和其余@10之后的@序号,生成需要执行的insert语句

备注:此方式只恢复过少量数据,大批量数据没有使用过。当时生成的文件未保存,大家可以测试下,看下生成的文件内容。

总结

本次分享主要涵盖了mysql同步异常监控、恢复、sql性能优化、代码优化工具及delete数据恢复,在现场实施使用率较高。
执行效率的提升,直接提升用户感知。

  • mysql 主从同步的监控
  • show processlist讲解
  • mysql EXPLAIN分析
  • 抓包工具fiddler分享
  • mysql恢复delete数据
阅读 341

许涛,Java后端研发,web端研发经验丰富,主要负责pc端和移动端的企业级业务处理系统定制化开发,如:综合网管、ERP系统、故障处理平台、电子工单系统、辅助监控系统等。

8 声望
2 粉丝
0 条评论
你知道吗?

许涛,Java后端研发,web端研发经验丰富,主要负责pc端和移动端的企业级业务处理系统定制化开发,如:综合网管、ERP系统、故障处理平台、电子工单系统、辅助监控系统等。

8 声望
2 粉丝
宣传栏