编写 prometheus exporter监控 mysql group replication

用 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 = row
                replication_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: 6106

    mysql_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