467
MySQL巡檢怎麼做
馬上要迎來長假，想想是不是有點小激動，但激動的同時也要了解一下MySQL服務器的狀態，以免在外旅遊時，沒準正和妹子...，突然來個報警，那內心的草泥馬恐怕要無限奔騰......
一、操作係統巡檢
如果有zabbix或者其他監控類型的工具，就方便很多。
首先看 CPU內存、硬盤io的消耗程度，其中重點是硬盤使用率，要為長假做好準備，避免廠家期間業務寫入增長，磁盤占滿。
每家業務不一樣，所以參考標準不一樣。 如果沒有zabbix，建議使用sar這個小工具，能夠收集曆史的信息，它的曆史數據在/var/log/sa下麵，通過 -f 來指定文件。
舉例：
1.1 cpu監控
[root@zst data]# sar -u 10 3
Linux 2.6.32-642.el6.x86_64 (zst) 09/22/2017 _x86_64_ (8 CPU)
10:26:44 AM CPU %user %nice %system %iowait %steal %idle
10:26:54 AM all 0.55 0.00 0.41 5.61 0.03 93.40
1.2 內存監控
[root@zst data]# sar -r 10 3
Linux 2.6.32-642.el6.x86_64 (zst) 09/22/2017 _x86_64_ (8 CPU)
10:28:36 AM kbmemfree kbmemused %memused kbbuffers kbcached kbcommit %commit
10:28:46 AM 223084 32658252 99.32 143468 16549080 18774068 37.81
1.3 I/O監控
[root@zst data]# sar -b 10 3
Linux 2.6.32-642.el6.x86_64 (zst) 09/22/2017 _x86_64_ (8 CPU)
10:30:25 AM tps rtps wtps bread/s bwrtn/s
10:30:35 AM 67.17 61.63 5.54 16169.99 86.20
1.4 係統SWAP監控
[root@zst data]# sar -w 10 3
Linux 2.6.32-642.el6.x86_64 (zst) 09/22/2017 _x86_64_
10:31:56 AM proc/s cswch/s
10:32:06 AM 0.00 2234.44
當然，查看當前的磁盤和內存使用情況df -h，free -m，是否使用numa和swap，或是否頻繁交互信息等。當然，還有其他的監控項目，這裏就不一一贅述了。
除此之外，還需要關注日誌類信息，例如：
/var/log/messages
/var/log/dmesg
二、MySQL本身巡檢
MySQL本身的監控應該包含重點參數的檢查，MySQL狀態的檢查，除此以外還應該包含自增id的使用情況（小心因為自增id使用滿了 不能insert寫入從而引發報警哦），及主從健康狀態的巡檢。
2.1 重點參數
"innodb_buffer_pool_size"
"sync_binlog"
'binlog_format'
'innodb_flush_log_at_trx_commit'
'read_only':
'log_slave_updates'
'innodb_io_capacity'
'query_cache_type'
'query_cache_size'
'max_connections'
'max_connect_errors'
'server_id'
2.2 MySQL的狀態
例如：每秒的tps、qps，提交了多少事務、回滾了多少事務、打開文件數、打開表數、連接數、innodb buffer使用率，及鎖等待等等。
首先，查看mysql狀態
mysql> show full processlis;
mysql> show global status;
mysql> show engine innodb status\G
show status中的一些狀態信息
1、wait事件
Innodb_buffer_pool_wait_free
Innodb_log_waits
2、MySQL鎖監控
表鎖
Table_locks_waited
Table_locks_immediate
行鎖
Innodb_row_lock_current_waits，當前等待鎖的行鎖數量
Innodb_row_lock_time，請求行鎖總耗時
Innodb_row_lock_time_avg，請求行鎖平均耗時
Innodb_row_lock_time_max，請求行鎖最久耗時
Innodb_row_lock_waits，行鎖發生次數
還可以定時收集INFORMATION_SCHEMA裏麵的信息：
INFORMATION_SCHEMA.INNODB_LOCKS;
INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
臨時表/臨時文件
Created_tmp_disk_tables/Created_tmp_files
打開表/文件數
Open_files/Open_table_definitions/Open_tables
並發連接數
Threads_running /Threads_created/Threads_cached
Aborted_clients
客戶端沒有正確關閉連接導致客戶端終止而中斷的連接數
Aborted_connects
試圖連接到mysql服務器而失敗的連接數
Binlog
Binlog_cache_disk_use
使用臨時二進製日誌緩衝但超過 binlog_cache_size 值並使用臨時文件
Binlog_cache_use
使用臨時二進製日誌緩衝的事務數量
Binlog_stmt_cache_disk_use
當非事務語句使用二進製日誌緩存
Binlog_stmt_cache_use
使用二進製日誌緩衝非事務語句數量
鏈接數:
Connections
試圖連接到（不管成不成功）mysql服務器的鏈接數
臨時表：
Created_tmp_disk_tables
服務器執行語句時,在硬盤上自動創建的臨時表的數量,是指在排序時,內存不夠用(tmp_table_size小於需要排序的結果集)，所以需要創建基於磁盤的臨時表進行排序
Created_tmp_files
服務器執行語句時自動創建的內存中的臨時表的數量
索引:
Handler_commit 內部交語句
Handler_rollback 內部 rollback語句數量
Handler_read_first 索引第一條記錄被讀的次數,如果高,則它表明服務器正執行大量全索引掃描
Handler_read_key 根據索引讀一行的請求數，如果較高，說明查詢和表的索引正確
Handler_read_last 查詢讀索引最後一個索引鍵請求數
Handler_read_next 按照索引順序讀下一行的請求數
Handler_read_prev 按照索引順序讀前一行的請求數
Handler_read_rnd 根據固定位置讀一行的請求數，如果值較高，說明可能使用了大量需要mysql掃整個表的查詢或沒有正確使用索引
Handler_read_rnd_next 在數據文件中讀下一行的請求數，如果你正進行大量的表掃，該值會較高
Open_table_definitions
被緩存的.frm文件數量
Opened_tables
已經打開的表的數量,如果較大,table_open_cache值可能太小
Open_tables
當前打開的表的數量
Queries
已經發送給服務器的查詢個數
Select_full_join
沒有使用索引的聯接的數量,如果該值不為0,你應該仔細檢查表的所有
Select_scan
對第一個表進行完全掃的聯接的數量
Slow_queries
查詢時間超過long_query_time秒的查詢個數
Sort_merge_passes
排序算法已經執行的合並的數量,如果值較大,增加sort_buffer_size大小
線程:
Threads_cached 線程緩存內的線程數量
Threads_connected 當前打開的連接數量
Threads_created 創建用來處理連接的線程數
Threads_running 激活的（非睡眠狀態）線程數
我寫了一個不成熟的小巡檢程序，僅巡檢MySQL的狀態和參數配置（因為客戶的環境不能直連linux但可以直連MySQL），有興趣的小夥伴可以看看。詳見：
https://github.com/enmotplinux/On-Site-Inspection
2.4 MySQL自增id的使用情況
mysql> SELECT table_schema,table_name,engine, Auto_increment
FROM information_schema.tables where
INFORMATION_SCHEMA.TABLE_SCHEMA
not in ("INFORMATION_SCHEMA" ,"PERFORMANCE_SCHEMA", "MYSQL", "SYS")
2.5 存儲引擎是否為innodb
mysql> SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE FROM
INFORMATION_SCHEMA.TABLES WHERE
ENGINE != 'innodb' AND
TABLE_SCHEMA NOT IN
("INFORMATION_SCHEMA" ,"PERFORMANCE_SCHEMA", "MYSQL", "SYS");
2.6 MySQL主從檢測
mysql> show slave status\G
2.6.1 主從狀態
主從狀態是否雙yes？
2.6.2 主從是否延遲
Master_Log_File == Relay_Master_Log_File
&& Read_Master_Log_Pos == Exec_Master_Log_Pos
最後，同樣要檢查MySQL的日誌，提前發現潛在風險：
- MySQL error log
- MySQL 慢查詢日誌
三、高可用巡檢
3.1 MHA && keepalived
觀察日誌看是否有頻繁主從切換，如果有的話就分析一下是什麼原因導致頻繁切換？
3.2 中間件的巡檢 mycat && pproxysql
這些中間件的巡檢，首先參考係統巡檢，再看一下中間件本身的日誌類和狀態類信息，網絡延遲或丟包的檢查，也是必須要做工作。
四、總結
關於巡檢來說，每個環境都是不一樣的，所以巡檢的側重點也是不一樣的，但基本的巡檢步驟是避免不了的，如果有其他的巡檢姿勢也歡迎一起討論。
原文發布時間為：2017-09-27
本文作者：田帥萌
本文來自雲棲社區合作夥伴“老葉茶館”，了解相關信息可以關注“老葉茶館”微信公眾號
最後更新：2017-09-30 08:03:43