import mysql.connector
from prometheus_client import CollectorRegistry, Gauge, write_to_textfile
# 连接 MySQL 数据库并获取指标信息
def get_mysql_perf_indicators(host, port, user, password):
cnx = mysql.connector.connect(user=user, password=password,
host=host,
port=port)
cursor = cnx.cursor()
cursor.execute("SHOW GLOBAL STATUS")
indicators = {}
for row in cursor:
indicators[row[0]] = int(row[1])
cnx.close()
return indicators
# 指标名称映射字典,用于将 MySQL 指标转换为 Prometheus Metrics 格式的指标名和帮助文本
mysql_metrics_mapping = {
'Bytes_received': ('mysql_network_bytes_received_total', 'Total number of bytes received from all clients'),
'Bytes_sent': ('mysql_network_bytes_sent_total', 'Total number of bytes sent to all clients'),
'Questions': ('mysql_queries_total', 'Total number of questions (queries) from clients'),
}
# 创建 Prometheus Metrics 格式的指标对象,并初始化为 0
metrics = {}
for metric_name, _ in mysql_metrics_mapping.values():
metrics[metric_name] = 0
# 获取 MySQL 指标信息,并更新 Prometheus Metrics 指标对象的值
indicators = get_mysql_perf_indicators('localhost', 3306, 'root', 'password')
for mysql_metric_name, (prom_metric_name, _) in mysql_metrics_mapping.items():
metrics[prom_metric_name] = indicators.get(mysql_metric_name, 0)
# 将 Prometheus Metrics 指标对象保存到文件中
registry = CollectorRegistry()
for prom_metric_name, help_text in mysql_metrics_mapping.values():
g = Gauge(prom_metric_name, help_text, registry=registry)
g.set(metrics[prom_metric_name])
write_to_textfile("mysql_metrics.prom", registry)
# 输出保存的文件内容
with open("mysql_metrics.prom", "r") as f:
print(f.read())
在上述代码中,首先定义了 get_mysql_perf_indicators()
函数用于连接 MySQL 数据库并获取关键性能指标信息。然后,定义了 mysql_metrics_mapping
字典,用于将 MySQL 指标转换为 Prometheus Metrics 格式的指标名和帮助文本。接下来,创建了 Prometheus Metrics 格式的指标对象,并初始化为 0。然后,获取 MySQL 指标信息,并更新 Prometheus Metrics 指标对象的值。最后,将 Prometheus Metrics 指标对象保存到文件中,并输出文件内容。
示例输出结果:
# HELP mysql_network_bytes_received_total Total number of bytes received from all clients
# TYPE mysql_network_bytes_received_total gauge
mysql_network_bytes_received_total 12345.0
# HELP mysql_network_bytes_sent_total Total number of bytes sent to all clients
# TYPE mysql_network_bytes_sent_total gauge
mysql_network_bytes_sent_total 67890.0
# HELP mysql_queries_total Total number of questions (queries) from clients
# TYPE mysql_queries_total gauge
mysql_queries_total 100000.0