MySQL運維實戰（二）之巧用P_S解決賬號host訪問的榮耀王者之路

背景

• 一個MySQL例項中，如何驗證一個賬號上面是否還有訪問？
• 一個MySQL例項中，如何驗證某個業務ip是否還有訪問？

倔強青銅級別

• 開啟general log

優點： 全量
缺點： 效能差

秩序白銀級別

• 開啟slow log,設定long_query_time = 0

優點： 全量
缺點： 效能比較差

榮耀黃金級別

• tshark | tcpdump | tcpcopy

tshark -i any dst host ${ip} and dst port 3306 -l -d tcp.port==3306,mysql -T fields -e frame.time -e `ip.src`  -e `mysql.query` -e `mysql.user` -e `mysql.schema`

優點：全量*95%
缺點：效能比較差，使用不方便

尊貴鉑金級別

• 使用P_S

* 使用案例


dba:performance_schema> select USER,EVENT_NAME,COUNT_STAR,now() as time from events_statements_summary_by_user_by_event_name where EVENT_NAME in (`statement/sql/select`,`statement/sql/update`,`statement/sql/delete`,`statement/sql/insert`,`statement/sql/replace`) and COUNT_STAR > 0;
+------+----------------------+------------+---------------------+
| USER | EVENT_NAME           | COUNT_STAR | time                |
+------+----------------------+------------+---------------------+
| dba  | statement/sql/select |        143 | 2017-09-04 18:02:33 |
| repl | statement/sql/select |         10 | 2017-09-04 18:02:33 |
+------+----------------------+------------+---------------------+
2 rows in set (0.00 sec)

dba:performance_schema> select HOST,EVENT_NAME,COUNT_STAR,now() as time from events_statements_summary_by_host_by_event_name where EVENT_NAME in (`statement/sql/select`,`statement/sql/update`,`statement/sql/delete`,`statement/sql/insert`,`statement/sql/replace`) and COUNT_STAR > 0;
+-----------+----------------------+------------+---------------------+
| HOST      | EVENT_NAME           | COUNT_STAR | time                |
+-----------+----------------------+------------+---------------------+
| localhost | statement/sql/select |         22 | 2017-09-04 18:02:35 |
+-----------+----------------------+------------+---------------------+
1 row in set (0.00 sec)

• 對比

優點：全量，效能基本無影響
缺點：無法抓到對應的SQL

永恆鑽石級別

• 巧用P_S

將每1分鐘，5分鐘，10分鐘的P_S快照對映到對應的table，永久存下來，進行統計分析

優點：全量，效能基本無影響，且時間更加細粒度化
缺點：無法抓到對應的SQL，需要額外開發成本

最強王者

• 巧用P_S + tshark

1. P_S分段，找到具體有訪問的時間段 $time
2. 在$time時間段內，去用tshark 抓取SQL相關info