1. 性能指标
– QPS (Queries Per Second) – 每秒查询数
– TPS (Transactions Per Second) – 每秒事务数
– 慢查询数量 – 执行时间超过阈值的查询
– 连接数 – 当前活跃连接数

2. 资源指标
– CPU使用率 – MySQL进程CPU占用
– 内存使用率 – 缓冲池、连接内存等
– 磁盘I/O – 读写操作和延迟
– 磁盘空间 – 数据文件、日志文件大小

3. 复制指标
– 复制延迟 – 主从复制的时间差
– 复制状态 – IO线程和SQL线程状态
– 主从数据一致性

4. 错误指标
– 错误日志数量
– 连接失败次数
– 锁等待超时次数

1. 下载Prometheus
wget https://github.com/prometheus/prometheus/releases/download/v2.47.0/prometheus-2.47.0.linux-amd64.tar.gz

2. 解压安装
tar -xzf prometheus-2.47.0.linux-amd64.tar.gz
mv prometheus-2.47.0.linux-amd64 /usr/local/prometheus

3. 创建配置文件
vim /usr/local/prometheus/prometheus.yml

global:
scrape_interval: 15s
evaluation_interval: 15s

alerting:
alertmanagers:
– static_configs:
– targets: [‘localhost:9093’]

rule_files:
– /usr/local/prometheus/rules/*.yml

scrape_configs:
– job_name: ‘prometheus’
static_configs:
– targets: [‘localhost:9090’]

– job_name: ‘mysql’
static_configs:
– targets: [‘localhost:9104’]
params:
auth_module: [client]

4. 创建系统服务
vim /etc/systemd/system/prometheus.service

[Unit]
Description=Prometheus Monitoring System
After=network.target

[Service]
Type=simple
ExecStart=/usr/local/prometheus/prometheus –config.file=/usr/local/prometheus/prometheus.yml
Restart=always

[Install]
WantedBy=multi-user.target

5. 启动服务
systemctl daemon-reload
systemctl start prometheus
systemctl enable prometheus

6. 验证安装
curl http://localhost:9090/api/v1/status/targets

输出示例：
{
“status”: “success”,
“data”: {
“activeTargets”: [
{
“discoveredLabels”: {
“__address__”: “localhost:9090”,
“__metrics_path__”: “/metrics”,
“__scheme__”: “http”,
“job”: “prometheus”
},
“labels”: {
“instance”: “localhost:9090”,
“job”: “prometheus”
},
“scrapePool”: “prometheus”,
“scrapeUrl”: “http://localhost:9090/metrics”,
“globalUrl”: “http://localhost:9090/metrics”,
“lastError”: “”,
“lastScrape”: “2026-04-04T12:00:00Z”,
“lastScrapeDuration”: 0.001,
“health”: “up”
}
]
}
}

1. 下载MySQL Exporter
wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.15.0/mysqld_exporter-0.15.0.linux-amd64.tar.gz

2. 解压安装
tar -xzf mysqld_exporter-0.15.0.linux-amd64.tar.gz
mv mysqld_exporter-0.15.0.linux-amd64/mysqld_exporter /usr/local/bin/

3. 创建配置文件
vim /usr/local/prometheus/.my.cnf

[client]
user=exporter
password=exporter_password
host=localhost
port=3306

4. 创建监控用户
mysql> CREATE USER ‘exporter’@’localhost’ IDENTIFIED BY ‘exporter_password’;
mysql> GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO ‘exporter’@’localhost’;
mysql> FLUSH PRIVILEGES;

输出示例：
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

5. 创建系统服务
vim /etc/systemd/system/mysqld_exporter.service

[Unit]
Description=MySQL Exporter for Prometheus
After=network.target

[Service]
Type=simple
ExecStart=/usr/local/bin/mysqld_exporter –config.my-cnf=/usr/local/prometheus/.my.cnf
Restart=always

[Install]
WantedBy=multi-user.target

6. 启动服务
systemctl daemon-reload
systemctl start mysqld_exporter
systemctl enable mysqld_exporter

7. 验证指标采集
curl http://localhost:9104/metrics | head -20

输出示例：
# HELP mysql_global_status_aborted_clients MySQL global status variable aborted_clients
# TYPE mysql_global_status_aborted_clients counter
mysql_global_status_aborted_clients 0
# HELP mysql_global_status_aborted_connects MySQL global status variable aborted_connects
# TYPE mysql_global_status_aborted_connects counter
mysql_global_status_aborted_connects 0
# HELP mysql_global_status_binlog_cache_disk_use MySQL global status variable binlog_cache_disk_use
# TYPE mysql_global_status_binlog_cache_disk_use counter
mysql_global_status_binlog_cache_disk_use 0
# HELP mysql_global_status_binlog_cache_use MySQL global status variable binlog_cache_use
# TYPE mysql_global_status_binlog_cache_use counter
mysql_global_status_binlog_cache_use 0

1. 安装Grafana
yum install -y https://dl.grafana.com/oss/release/grafana-10.1.2-1.x86_64.rpm

输出示例：
Loaded plugins: fastestmirror
grafana-10.1.2-1.x86_64.rpm | 87 MB 00:00:05
Examining /var/tmp/yum-root-abc123/grafana-10.1.2-1.x86_64.rpm: grafana-10.1.2-1.x86_64
Marking /var/tmp/yum-root-abc123/grafana-10.1.2-1.x86_64.rpm to be installed
Resolving Dependencies
–> Running transaction check
—> Package grafana.x86_64 0:10.1.2-1 will be installed
–> Finished Dependency Resolution

Installed:
grafana.x86_64 0:10.1.2-1

Complete!

2. 启动Grafana服务
systemctl start grafana-server
systemctl enable grafana-server

3. 验证安装
curl http://localhost:3000/api/health

输出示例：
{
“commit”: “abcdef123”,
“database”: “ok”,
“version”: “10.1.2”
}

4. 访问Grafana界面
浏览器访问: http://localhost:3000
默认用户名: admin
默认密码: admin

5. 添加Prometheus数据源
Configuration -> Data Sources -> Add data source
– Name: Prometheus
– Type: Prometheus
– URL: http://localhost:9090
– Save & Test

6. 导入MySQL监控仪表盘
Create -> Import
– Import via grafana.com: 7362 (MySQL Overview)
– 选择Prometheus数据源
– Import

1. 创建告警规则文件
vim /usr/local/prometheus/rules/mysql_alerts.yml

groups:
– name: mysql_alerts
rules:
– alert: MySQLDown
expr: mysql_up == 0
for: 1m
labels:
severity: critical
annotations:
summary: “MySQL instance {{ $labels.instance }} is down”
description: “MySQL instance {{ $labels.instance }} has been down for more than 1 minute.”

– alert: MySQLHighConnections
expr: (mysql_global_status_threads_connected / mysql_global_variables_max_connections) * 100 > 80
for: 5m
labels:
severity: warning
annotations:
summary: “MySQL high connection usage on {{ $labels.instance }}”
description: “MySQL connection usage is above 80% (current value: {{ $value }}%)”

– alert: MySQLSlowQueries
expr: rate(mysql_global_status_slow_queries[5m]) > 10
for: 5m
labels:
severity: warning
annotations:
summary: “MySQL slow queries on {{ $labels.instance }}”
description: “MySQL slow query rate is above 10 per second (current value: {{ $value }})”

– alert: MySQLReplicationLag
expr: mysql_slave_lag_seconds > 300
for: 5m
labels:
severity: warning
annotations:
summary: “MySQL replication lag on {{ $labels.instance }}”
description: “MySQL replication lag is above 5 minutes (current value: {{ $value }} seconds)”

– alert: MySQLDiskSpace
expr: (mysql_global_status_innodb_data_pending_fsyncs / mysql_global_status_innodb_data_fsyncs) * 100 > 90
for: 5m
labels:
severity: critical
annotations:
summary: “MySQL disk space issue on {{ $labels.instance }}”
description: “MySQL disk space usage is critical (current value: {{ $value }}%)”

2. 重启Prometheus
systemctl restart prometheus

3. 查看告警状态
curl http://localhost:9090/api/v1/alerts

输出示例：
{
“status”: “success”,
“data”: {
“alerts”: [
{
“labels”: {
“alertname”: “MySQLHighConnections”,
“instance”: “localhost:9104”,
“severity”: “warning”
},
“annotations”: {
“description”: “MySQL connection usage is above 80% (current value: 85.5%)”,
“summary”: “MySQL high connection usage on localhost:9104”
},
“state”: “firing”,
“activeAt”: “2026-04-04T12:00:00Z”,
“value”: “85.5”
}
]
}
}

1. 下载Alertmanager
wget https://github.com/prometheus/alertmanager/releases/download/v0.26.0/alertmanager-0.26.0.linux-amd64.tar.gz

2. 解压安装
tar -xzf alertmanager-0.26.0.linux-amd64.tar.gz
mv alertmanager-0.26.0.linux-amd64 /usr/local/alertmanager

3. 创建配置文件
vim /usr/local/alertmanager/alertmanager.yml

global:
smtp_smarthost: ‘smtp.fgedu.net.cn:587’
smtp_from: ‘alert@fgedu.net.cn’
smtp_auth_username: ‘alert@fgedu.net.cn’
smtp_auth_password: ‘your_password’

route:
group_by: [‘alertname’]
group_wait: 30s
group_interval: 5m
repeat_interval: 4h
receiver: ’email-notifications’

receivers:
– name: ’email-notifications’
email_configs:
– to: ‘dba@fgedu.net.cn’
subject: ‘MySQL Alert: {{ .GroupLabels.alertname }}’
body: |
{{ range .Alerts }}
Alert: {{ .Annotations.summary }}
Description: {{ .Annotations.description }}
Severity: {{ .Labels.severity }}
Time: {{ .StartsAt }}
{{ end }}

inhibit_rules:
– source_match:
severity: ‘critical’
target_match:
severity: ‘warning’
equal: [‘alertname’, ‘instance’]

4. 创建系统服务
vim /etc/systemd/system/alertmanager.service

[Unit]
Description=Alertmanager for Prometheus
After=network.target

[Service]
Type=simple
ExecStart=/usr/local/alertmanager/alertmanager –config.file=/usr/local/alertmanager/alertmanager.yml
Restart=always

[Install]
WantedBy=multi-user.target

5. 启动服务
systemctl daemon-reload
systemctl start alertmanager
systemctl enable alertmanager

6. 验证安装
curl http://localhost:9093/api/v1/status

输出示例：
{
“status”: “success”,
“data”: {
“clusterStatus”: {
“status”: “ready”
},
“configJSON”: {
“route”: {
“receiver”: “email-notifications”
}
}
}
}

1. 告警分级
– Critical: 数据库宕机、磁盘满、复制中断
– Warning: 连接数高、慢查询多、复制延迟
– Info: 日常统计信息

2. 告警阈值设置
– 连接数使用率: > 80% 警告, > 95% 严重
– 复制延迟: > 5分钟 警告, > 30分钟 严重
– 慢查询: > 10/秒 警告, > 50/秒 严重
– 磁盘空间: > 85% 警告, > 95% 严重

3. 告警通知渠道
– 邮件通知 – 常规告警
– 短信/电话 – 严重告警
– 钉钉/企业微信 – 团队通知
– PagerDuty – 值班告警

4. 告警处理流程
– 告警触发 -> 通知发送
– 值班人员接收 -> 问题诊断
– 问题解决 -> 告警恢复
– 事后复盘 -> 优化改进

5. 监控数据保留
– 原始数据: 15天
– 聚合数据: 1年
– 趋势数据: 长期保留