(基础)MySQL 故障分析需要抽取那些信息

最新推荐文章于 2023-10-26 14:18:40 发布

请给我的爱人一杯mojito

最新推荐文章于 2023-10-26 14:18:40 发布

阅读量106

点赞数

分类专栏： Mysql 文章标签： mysql

本文链接：https://blog.csdn.net/baidu_34007305/article/details/114309603

版权

Mysql 专栏收录该内容

86 篇文章 1 订阅

订阅专栏

背景

数据库故障时的处理步骤

写在前面
（推荐阅读）原文链接https://www.modb.pro/db/41601
link

采集信息，进行分析问题。问题包括：

TPS突然下降到 0
连接数直接爆满
error日志无记录
突然hang住
数据库服务不停的重启
内存曲线是上升，最后OOM

操作

监控，日志优先

1.基础信息包含OS信息，mysql版本，高可用，参数设置
mysql -uroot -p -S /tmp/mysql.sock  -e "\s;show global variables;" > /tools/msyql_baseinfo.txt

2.查看连接信息
SHOW PROCESSLIST;

特别是需要关注State里的状态值。
链接太多的是使用以下语句：

select USER ,HOST,DB ,COMMAND, TIME,STATE , INFO from information_schema.processlist where COMMAND<>'Sleep' limit 10;

3.error日志抽取
SHOW VARIABLES LIKE '%log_error%';
除了error日志之外，需要系统日志/var/log/messages

4.慢日志抽取
慢日志也会导致MySQL 反应慢,所以也需要抽取慢日志
配置文件my.cnf会配置，mysql信息里也可以看到慢日志信息

SHOW VARIABLES LIKE '%slow%';
slow_query_log_file       | /usr/local/mysql5.7.32/data/mysqlupdate01-slow.log

5.锁信息

查看锁等待相关的阻塞线程、被阻塞线程信息及相关用户、IP、PORT

SELECT locked_table,
       locked_index,
       locked_type,
       blocking_pid,
       concat(T2.USER,'@',T2.HOST) AS "blocking(user@ip:port)",
       blocking_lock_mode,
       blocking_trx_rows_modified,
       waiting_pid,
       concat(T3.USER,'@',T3.HOST) AS "waiting(user@ip:port)",
       waiting_lock_mode,
       waiting_trx_rows_modified,
       wait_age_secs,
       waiting_query
FROM sys.x$innodb_lock_waits T1
LEFT JOIN INFORMATION_SCHEMA.processlist T2 ON T1.blocking_pid=T2.ID
LEFT JOIN INFORMATION_SCHEMA.processlist T3 ON T3.ID=T1.waiting_pid;

6.Innodb状态

SHOW ENGINE INNODB STATUS\G;

SHOW ENGINE INNODB MUTEX;(5.7.32)
+--------+-----------------------------+----------+
| Type   | Name                        | Status   |
+--------+-----------------------------+----------+
| InnoDB | rwlock: dict0dict.cc:1228   | waits=15 |
| InnoDB | rwlock: log0log.cc:846      | waits=34 |
| InnoDB | sum rwlock: buf0buf.cc:1460 | waits=7  |
+--------+-----------------------------+----------+

SHOW ENGINE INNODB MUTEX;(8.0.22)
+--------+----------------------------+---------+
| Type   | Name                       | Status  |
+--------+----------------------------+---------+
| InnoDB | sum rwlock: buf0buf.cc:780 | waits=1 |
+--------+----------------------------+---------+
MySQL中latch 与lock都被称为锁，在innodb中lock针对的是事务，latch针对的是线程，latch又可以分为mutex和rw_lock，latch的目的是保证并发的线程操作临界资源的正确性.
Mutex量指的是一种用于保护一些临界资源的使用的信号量。当有线程需要使用这 些临界资源时，会请求获得mutex量，请求成功的线程进入临界区，而请求失败的线程只能等待它释放这个mutex。

7.binlog统计DDL&DML
##统计DML:

mysqlbinlog --no-defaults --start-datetime='2020-05-02 22:36:46' --stop-datetime='2020-05-02 23:25:46' --base64-output=decode-rows -v -v mysql-bin.000007 | awk '/###/{if($0~/UPDATE|INSERT|DELETE/)count[$2" "$NF]++}END{for(i in count)print i,"\t",count[i]}'|column -t|sort -k3nr
##统计DDL：

mysqlbinlog  --start-datetime='2020-05-02 22:36:46' --stop-datetime='2020-05-02 23:25:46' mysql-bin.000007 | awk 'BEGIN{IGNORECASE=1} {if($0~/alter/)count[$1" " $2" " $3" "$NF]++}END{for(i in count)print i,"\t",count[i]}'|column -t|sort -k3nr
binlog并行复制统计

mysqlbinlog mysql-bin.000004 --start-position=20087624 | grep -o 'last_committed.*'  | sed 's/=/ /g' | awk '{print $2"\t"$4}' | awk '{count++;print $0;} END{print "total count is ",count}'


8.监控信息

OS信息：查看CPU TOP ,io iostat状态，网络流量
mysql信息：连接数，active线程数，锁等待，临时表使用情况，tps，qps，网络 input&out信息信息

9.mgr状态

mgr状态查询

Show master status\G;
select * from performance_schema.replication_group_member_stats\G;

MGR成员间的角色和状态信息

SELECT a.member_id, a.member_host, a.member_state, a.member_role, b.channel_name, b.count_transactions_in_queue, b.count_transactions_remote_in_applier_queue FROM performance_schema.replication_group_members a, performance_schema.replication_group_member_stats b WHERE a.member_id=b.member_id order by a.member_role;

10.pstack堆信息抽取

pstack命令 可显示每个进程的栈跟踪。pstack 命令必须由相应进程的属主或 root 运行

pstack $mysql_pid>/tmp/pstack.info
备注：平时不能使用，会卡主mysql，谨慎使用!


11.tcpdump抓包抽取

抓包主要考虑网络相关的部分，也可以通过sql语句找到数据参数 ，原ip信息 丢包等情况
tcpdump -i ens33 tcp port 3410 and host 192.168.244.130 -w ./kafka.pcap
需要配合wireshark 查看。

分析慢日志：

mysqldumpslow

1.得到返回记录最多的20个sql
mysqldumpslow -s r -t 20 mysqlupdate01-slow.log  > /tools/mysql-slow01.log

2.得到平均访问次数最多的20条sql
mysqldumpslow -s ar -t 20 mysqlupdate01-slow.log   > /tools/mysql-slow02.log

(推荐使用)pt-query-digest.可以结合mysqlbinlog命令，对日志进行分析

pt-query-digest是一个perl脚本，只需下载并赋权即可执行。
wget percona.com/get/pt-query-digest
chmod u+x pt-query-digest

例句：
pt-query-digest --limit=100  --since "2016-06-08 00:00:00"  --until "2016-06-08 23:59:59" mysql-slow.log  > /tmp/slow_report.log

直接分析慢查询文件：
pt-query-digest slow.log > slow20170803.log
 
分析指定时间范围内的查询：
pt-query-digest slow.log --since '2017-08-03 15:30:00' --until '2017-08-04 10:30:00' > slow83-84.log
 
分析指含有select语句的慢查询：
pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i' slow.log > slow4.log

分析最近1小时内的查询：
pt-query-digest --since=1h slow.log > 1slow.log


 
针对某个用户的慢查询：
pt-query-digest --filter '($event->{user} || "") =~ m/^admin/i' slow.log > slow5.log
 
查询所有所有的全表扫描或full join的慢查询：
pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes") ||(($event->{Full_join} || "") eq "yes")' slow.log> slow6.log
 
把查询保存到query_review表：
pt-query-digest --user=root –password=abc123 --review h=localhost,D=test,t=query_review --create-review-table slow.log

遇到突发情况，需要批量杀链接时，可以通过拼接 SQL 得到 kill 语句，然后再执行，这样会方便很多，分享几个可能用到的杀链接的 SQL ：
杀掉空闲时间在600秒以上的链接，拼接得到kill语句
select concat('KILL ',id,';') from information_schema.`processlist` where command = 'Sleep' and time > 600; 

杀掉处于某个状态的链接，拼接得到kill语句
select concat('KILL ',id,';') from information_schema.`processlist` where state = 'Sending data';

select concat('KILL ',id,';') from information_schema.`processlist` where state = 'Waiting for table metadata lock';

杀掉某个用户发起的链接，拼接得到kill语句
select concat('KILL ',id,';') from information_schema.`processlist`  where user = 'testuser';

这里提醒下，kill 语句一定要慎用！特别是此链接执行的是更新语句或表结构变动语句时，杀掉链接可能需要比较长时间的回滚操作。

本文说明，主要技术内容来自互联网技术大佬的分享，还有一些自我的加工(仅仅起到注释说明的作用)。如有相关疑问，请留言，将确认之后，执行侵权必删

请给我的爱人一杯mojito

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
(基础)MySQL 故障分析需要抽取那些信息

背景数据库故障时的处理步骤写在前面（推荐阅读）原文链接https://www.modb.pro/db/41601link采集信息，进行分析问题。问题包括：TPS突然下降到 0连接数直接爆满error日志无记录突然hang住数据库服务不停的重启内存曲线是上升，最后OOM操作监控，日志优先1.基础信息包含OS信息，mysql版本，高可用，参数设置mysql -uroot -p -S /tmp/mysql.sock -e "\s;show global variables;
复制链接

扫一扫