MySQLを監視しながらテーブル情報を収集するコードの詳細説明（画像とテキスト）

1. ストーリー

おそらく、過去 1 年間のライブラリ内の特定のテーブルの月ごとのデータ量の増加についてよく質問されると思います。もちろん、テーブルが月ごとに分割されている場合は、show table status を 1 つずつ実行する方が簡単です。大きなテーブルが 1 つしかない場合は、おそらく単独で SQL 統計を実行する必要があります。現在のテーブル情報のみを取得でき、過去の情報を追跡することはできないため、皆が休んでいる夜に。 show table status，如果只有一个大表，那估计要在大家都休息的时候，寂寞的夜里去跑sql统计了，因为你只能获取当前的表信息，历史信息追查不到了。

除此以外，作为DBA本身也要对数据库空间增长情况进行预估，用以规划容量。我们说的表信息主要包括：

1. 表数据大小（DATA_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 两个表：

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. 
   テーブル データ サイズ (DATA_LENGTH)
< li>インデックス サイズ (INDEX_LENGTH)

2. 行数 (ROWS)

3. 
   現在の自動インクリメント値 (AUTO_INCREMENT、存在する場合)
現時点では、このような機能を提供する mysql 監視ツールを見たことがありません。インジケータ。この情報はそれほど頻繁に収集する必要はなく、結果は単なる推定であり正確ではない可能性があるため、これはグローバルかつ長期的な観点からテーブルを監視 (収集) するためです。
🎜この記事で紹介するために私が作成した収集ツールは、グループ内の既存の監視システムに基づいています: 🎜
• 🎜InfluxDB: Timeシリーズデータベース、監視データを保存します🎜
• 🎜Grafana: データ表示パネル🎜
• 🎜Telegraf: 情報を収集するエージェント🎜 telegraf の最新の mysql プラグインを試してみましたが、最初は非常に満足しました。テーブル スキーマの統計情報の収集と情報スキーマの自動増分列がサポートされています。試してみたところデータはありましたが、前述したように、telegraf が収集する頻度が高すぎると意味がありません。IntervalSlow が提供されます。 </ code> オプションはハードコーディングされており、グローバル ステータスの監視の頻度を低下させることしかできません。ただし、他の監視インジケーターから 2 つの<a href="//m.sbmmt.com/code/10545.html" target="_blank">設定ファイル</a>に分離することができ、それぞれが収集間隔を達成します。 🎜</li></ul>🎜最後に、Python を使用して自分でビルドし、influxdb に報告する予定です:)🎜🎜2. コンセプト🎜🎜完全なコードについては、GitHub プロジェクトのアドレスを参照してください: DBschema_g​​ather🎜実装も非常に簡単です。<code> をクエリするだけです。information_schema ライブラリの COLUMNS と TABLES の 2 つのテーブルです。 🎜🎜 rrreee🎜 auto_increment については、現在の増加がどこにあるのかに注意を払うことに加えて、int / bigint の最大値と比較して空き領域がどれだけあるかにも注意します。 。そのため、現在使用されている比率を保存するために autoIncrUsage 列が計算されました。 🎜🎜次に、InfluxDB の Python クライアントを使用して、influxdb にバッチで保存します。 InfluxDB がない場合、結果は json として出力されます。これは Zabbix であり、Open です。 -Falcon など 監視ツールで一般的にサポートされている形式。 🎜🎜最後のステップは、Grafana を使用して influxdb データ ソースから画像を描画することです。 🎜🎜3. 使用法🎜
  1. 🎜環境🎜 Python 2.7 環境で書かれています。2.6 と 3.x はテストされていません。 🎜
  🎜操作には 2 つのライブラリが必要です: MySQLdb と influxdb: 🎜rrreee
  1. 🎜設定🎜settings_dbs.py 設定ファイル🎜
  2. 🎜🎜🎜
• 🎜DBLIST_INFO : リストには、収集する必要がある MySQL インスタンス テーブル情報が保存されます。タプルは、接続アドレス、ポート、ユーザー名、およびパスワードです。ユーザーは、テーブルを選択する権限が必要です。そうでない場合、対応する情報を取得することはできません。 🎜
• 🎜InfluxDB_INFO: Influxdb 接続情報。データベース名 mysql_info🎜結果を json として出力するには、None に設定します。🎜
• 🎜influxdb 上にデータベースとストレージ戦略を作成します🎜ストレージは 2 年間、 1 レプリ​​カ セット: (オンデマンド調整) 🎜
rrreee🎜次のような大きなメッセージを参照してください: 🎜🎜
1. 🎜crontabを実行🎜監視用のサーバーに別途置くこともできますが、セキュリティ上の理由から、mysql インスタンスが配置されているホストの実稼働環境で実行することをお勧めします。 🎜
🎜 通常、ライブラリでは夜間にデータ移行が行われます。移行の前後に mysql_schema_info.py を実行してデータを収集できます。あまり頻繁にはお勧めしません。 🎜rrreee
1. 🎜チャートを生成します🎜
🎜プロジェクトの下の grafana_table_stats.json を Grafana パネルにインポートします。効果は次のとおりです: 🎜🎜🎜🎜テーブルのデータサイズと行数🎜🎜🎜🎜🎜🎜毎日の行番号変更の増分、auto_incrementの使用🎜🎜

4. 詳細

1. サブデータベースとテーブルの場合、テーブル autoIncrUsage でグローバル一意の ID を計算することはできません

2. 実装は非常に簡単で、より重要なことはこの情報を収集する意識

3. を高めることができます グラファイト出力フォーマット

以上がMySQLを監視しながらテーブル情報を収集するコードの詳細説明（画像とテキスト）の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。