当前位置：首页 > news >正文

编写 prometheus exporter监控 mysql group replication

news 来源：原创 2024/9/20 17:59:28

用 prometheus 监控 mysql，之前用 mysqld_exporter 收集mysql 的监控指标，发现并没有 mysql 组复制状态的指标。只能自己收集了，编写脚本收集指标推送到 pushgateway，这个办法更简单但是扩缩容不是很方便。下面用 python 编写的一个 exporter，比较简单只抓取了一个指标

需要修改的是，mysql_instances_config.yml 实例配置文件的路径，按需暴露的端口和抓取间隔

from prometheus_client import start_http_server, Gauge
import mysql.connector  # 这里的 `mysql` 包是 `mysql-connector-python`
import time
import yaml  # 包是PYyaml# 读取配置文件
def load_config(file_path):with open(file_path, 'r') as file:config = yaml.safe_load(file)return config# 从配置文件加载 MySQL 实例和凭据
config = load_config('/app/mysql_instances_config.yml')  #抓取目标的配置绝对路径
mysql_instances = [(item['host'], item['port']) for item in config['mysql_instances']]
mysql_user = config['mysql_credentials']['user']
mysql_password = config['mysql_credentials']['password']# 自定义指标
replication_group_status = Gauge('replication_group_status','Status of MySQL Group Replication',labelnames=['member_host', 'member_state']
)def collect_metrics():for host, port in mysql_instances:config = {'user': mysql_user,'password': mysql_password,'host': host,'port': port,'database': 'performance_schema',}try:conn = mysql.connector.connect(**config)  # 使用 `mysql.connector.connect`cursor = conn.cursor()cursor.execute("SELECT MEMBER_HOST, MEMBER_STATE, CASE WHEN MEMBER_STATE = 'ONLINE' THEN 1 ELSE 0 END AS state_value FROM performance_schema.replication_group_members")rows = cursor.fetchall()# 更新 Prometheus 指标for row in rows:member_host, member_state, state_value = rowreplication_group_status.labels(member_host=member_host, member_state=member_state).set(state_value)  #指标和标签cursor.close()conn.close()except mysql.connector.Error as err:print(f"Error connecting to MySQL instance {host}:{port} - {err}")if __name__ == '__main__':start_http_server(9104)  # 启动 HTTP 服务，以及暴露的端口while True:collect_metrics()  # 定期收集指标time.sleep(30)  # 每 30 秒收集一次

抓取目标的配置，用的同一套用户名密码，抓取目标自己按需扩减

    mysql_instances:- host: '10.1.11.16'port: 5407- host: '10.1.11.81'port: 5407- host: '10.4.1.1'port: 5407- host: '10.1.11.80'port: 6106- host: '10.1.11.81'port: 6106mysql_credentials:user: 'exporter'password: 'password'

创建数据库用户以及授权

CREATE USER 'exporter'@'%' IDENTIFIED BY 'XXXXXXXX' WITH MAX_USER_CONNECTIONS 3;
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'%';
FLUSH PRIVILEGES;

访问抓取指标的 url，指标名字为 replication_group_status，当状态为 ONLINE 时 value 为1，否则为 0