監控MySQL的一起收集表信息代碼詳細說明（圖文）

1. Story

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

除此以外，作為DBA本身也要對數據庫空間增長情況進行預估，用以規劃容量。我們說的表信息主要包括：

1. 表數據大�。―ATA_LENGTH）

2. 索引大小(INDEX_LENGTH)

3. 行數（ROWS）

4. 當前自增值（AUTO_INCREMENT，如果有）

目前是沒有看到哪個mysql監控工具上提供這樣的指標。這些信息不需要采集的太頻繁，而且結果也只是個預估值，不一定準確，所以這是站在一個全局、長遠的角度去監控(采集)表的。

本文要介紹的自己寫的采集工具，是基于組內現有的一套監控體系：

• InfluxDB：時間序列數據庫，存儲監控數據

• Grafana：數據展示面板

• Telegraf：收集信息的agent
  看了下 telegraf 的最新的 mysql 插件，一開始很欣慰：支持收集 Table schema statistics 和 Info schema auto increment columns。試用了一下，有數據，但是如前面所說，除了自增值外其他都是預估值，telegraf收集頻率過高沒啥意義，也許一天2次就足夠了，它提供的 IntervalSlow選項固定寫死在代碼里，只能是放緩 global status 監控頻率。不過倒是可以與其它監控指標分開成兩份配置文件，各自定義收集間隔來實現。

最后打算自己用python擼一個，上報到influxdb里 :)

2. Concept

完整代碼見 GitHub項目地址：DBschema_gather
實現也特別簡單，就是查詢 information_schema 庫的 COLUMNS、TABLES 兩個表：
<!-- more -->

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中文網其它相關文章！