用 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