日常運維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;

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 查看。
總結
DBA碰到故障的時候 需要頭腦冷靜思路清晰。已盡快解決故障,給業務盡快提供服務為基準,盡量 合理的收集信息。
MySQL也在盡量完善這方面的體系。
如碰見MySQL直接hang住 或 不可用的是時候,建議直接使用pstack抓堆信息。
盡努力做好一切




