MySQL 故障分析需要抽取那些信息

原創 CuiHulong 2020-12-03

5235

日常運維MySQL數據庫中會碰到一些莫名其妙的故障，雖然監控存在，但因為當時沒有及時采集信息，導致后續不了了之的情況或等到下次故障發生的時，再采集信息，進行分析問題。

那些故障下，會出現信息少，無法進行分析：

TPS突然下降到 0
連接數直接爆滿
error日志無記錄
突然hang住
數據庫服務不停的重啟
內存曲線是上升，最后OOM

如生產環境中，碰見類似現象，應該采集那些數據，后續分析。

1.基礎信息

基礎信息包含OS信息，mysql版本，高可用，參數設置

mysql -uroot -p -S /opt/data/mysql.sock  -e "\s;show global variables;" > /tmp/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日志抽取

MySQL 所有時間都會基本都會寫到日志文件里,但問題出現的時候需要確認error 日志。
error日志查看

mysql> SHOW VARIABLES LIKE '%log_error%';
+----------------------------+----------------------------------------+
| Variable_name              | Value                                  |
+----------------------------+----------------------------------------+
| binlog_error_action        | ABORT_SERVER                           |
| log_error                  | /opt/data8.0/logs/mysql_err.log        |

除了error日志之外，需要系統日志/var/log/messages

4.慢日志抽取

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

mysql> SHOW VARIABLES LIKE '%slow%';
+---------------------------+----------------------------+
| Variable_name             | Value                      |
+---------------------------+----------------------------+
| log_slow_admin_statements | ON                         |
| log_slow_extra            | OFF                        |
| log_slow_slave_statements | OFF                        |
| slow_launch_time          | 2                          |
| slow_query_log            | ON                         |
| slow_query_log_file       | /opt/data8.0/logs/slow.log |
+---------------------------+----------------------------+

對于慢日志分析提供2種方式：

mysqldumpslow 方式：

1.得到返回記錄最多的20個sql

mysqldumpslow -s r -t 20 sqlslow.log  > /tmp/mysql-slow.20180725.log

2.得到平均訪問次數最多的20條sql

mysqldumpslow -s ar -t 20 sqlslow.log > /tmp/mysql-slow.20180725.log

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

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;

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;