influxdb+Grafana监控mysql

client = InfluxDBClient(host=‘192.168.124.10’, port=8086, username=‘test’, password=‘123456’, database=‘devops’)

创建influx用户并授权

MariaDB [(none)]> CREATE USER 'influxdb'@'%' IDENTIFIED BY 'influxdb';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'influxdb'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

监控点
当前连接数
每秒钟增、删、改、查的数量
每秒钟的连接量

from influxdb import InfluxDBClient
import pymysql

# 创建 InfluxDB 客户端连接
client = InfluxDBClient('192.168.124.10', 8086, 'test', '123456', 'devops')

# 连接到 MySQL 数据库
conn = pymysql.connect(host="192.168.124.10", user="influxdb", password="influxdb", db="test")
cur = conn.cursor(pymysql.cursors.DictCursor)

# 执行查询以获取全局状态
cur.execute('SHOW GLOBAL STATUS')
fetch_results = cur.fetchall()

# 遍历查询结果
for result in fetch_results:
    # 过滤感兴趣的变量
    if result['Variable_name'] in ['Threads_running', 'Com_select', 'Com_update', 'Com_delete', 'Com_insert',
                                   'Connections']:
        # 准备要写入 InfluxDB 的数据点
        data_point = [{
            'measurement': 'mysql_status',
            'tags': {'item': f"mysql.{result['Variable_name']}"},
            'fields': {'value': int(result['Value'])}
        }]

        # 将数据点写入 InfluxDB
        client.write_points(data_point)

# 关闭数据库连接
cur.close()
conn.close()

执行上面的脚本
查询influxdb

> show measurements
name: measurements
name
----
mysql_status
nginx_status
> select * from mysql_status;
name: mysql_status
time                item                  value
----                ----                  -----
1722845268612605404 mysql.Com_delete      2
1722845268638297877 mysql.Com_insert      0
1722845268682797024 mysql.Com_select      13
1722845268725191672 mysql.Com_update      1
1722845268775473471 mysql.Connections     17
1722845268820819296 mysql.Threads_running 1

influxdb已经采集到了结果

grafana里面已经获取到了mysql.Com_delete 的结果