監控MySQL的同時收集表格資訊代碼詳解（圖文）

1. Story

也許你常常會被問到，庫裡某個表最近一年的內每個月的資料量成長情況。當然如果你有按月分錶比較好辦，挨個show table status，如果只有一個大表，那估計要在大家都休息的時候，寂寞的夜裡去跑sql統計了，因為你只能取得目前的表格信息，歷史資訊追查不到了。

除此之外，作為DBA本身也要對資料庫空間成長情況進行預估，用以規劃容量。我們說的表格資訊主要包括：

SELECT IFNULL(@@hostname, @@server_id) SERVER_NAME, %s as HOST, t.TABLE_SCHEMA, t.TABLE_NAME, t.TABLE_ROWS, t.DATA_LENGTH, t.INDEX_LENGTH, t.AUTO_INCREMENT, c.COLUMN_NAME, c.DATA_TYPE, LOCATE('unsigned', c.COLUMN_TYPE) COL_UNSIGNED # CONCAT(c.DATA_TYPE, IF(LOCATE('unsigned', c.COLUMN_TYPE)=0, '', '_unsigned')) FROM information_schema.`TABLES` t LEFT JOIN information_schema.`COLUMNS` c ON t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME AND c.EXTRA = 'auto_increment' WHERE t.TABLE_SCHEMA NOT IN ( 'mysql', 'information_schema', 'performance_schema', 'sys' ) AND t.TABLE_TYPE = 'BASE TABLE'
關於

auto_increment，我們除了專注於目前成長到哪了，還會在意相比int / bigint 的最大值，還有多少可用空間。於是計算了 autoIncrUsage 這一列，用於保存目前已使用的比例。

然後使用 InfluxDB 的python客戶端，大量存入influxdb。如果沒有InfluxDB，結果會列印出

json —— 這是Zabbix、Open-Falcon這些監控工具普遍支援的格式。 最後就是使用 Grafana 從 influxdb 資料來源畫圖。

3. Usage
1. 環境

   在 python 2.7 環境下寫的，2.6，3.x沒測。
   

運行需要

MySQLdb、influxdb兩個函式庫：

$ sudo pip install mysql-python influxdb

1. 設定

   
   #settings_dbs.py 設定檔

2. 
   

• DBLIST_INFO：列表存放需要擷取的哪些MySQL實例表訊息，元組內分別是連接位址、連接埠、使用者名稱、密碼使用者需要select表的權限，否則看不到對應的資訊.
  

• InfluxDB_INFO：influxdb的連接訊息，注意提前建立好資料庫名稱mysql_info設定為
  None 可輸出結果為json.

• 建立influxdb上的資料庫與儲存策略

  存放2年，1個複製集：（按需調整）
  

  CREATE DATABASE "mysql_info"
  CREATE RETENTION POLICY "mysql_info_schema" ON "mysql_info" DURATION 730d REPLICATION 1 DEFAULT

看大的信息類似：

1. 放crontab跑

   可以單獨放在用於監控的伺服器上，不過建議在生產環境可以運行在mysql實例所在主機上，安全起見。
   

一般程式庫在晚上會有資料遷移的動作，可以在遷移前後分別執行

mysql_schema_info.py 來收集一次。不建議太頻繁。

40 23,5,12,18 * * * /opt/DBschema_info/mysql_schema_info.py >> /tmp/collect_DBschema_info.log 2>&1

1. 產生圖表

匯入專案下的

grafana_table_stats.json 到 Grafana面板中。效果如下：

表格資料大小與行數

#每天行數變更增量,auto_increment使用率

4. More

1. 分庫分錶情況下，全域唯一ID在表裡無法計算autoIncrUsage

2. 實作其實很簡單，更主要的是喚醒收集這些資訊的意識

3. 可以增加Graphite 輸出格式

以上是監控MySQL的同時收集表格資訊代碼詳解（圖文）的詳細內容。更多資訊請關注PHP中文網其他相關文章！