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 的结果