# 查看错误日志位置
mysql> SHOW GLOBAL VARIABLES LIKE ‘log_error’;
+—————+——————————+
| Variable_name | Value |
+—————+——————————+
| log_error | /var/lib/mysql/mysql-error.log |
+—————+——————————+

# 监控错误日志的脚本示例
#!/bin/bash
# monitor_error_log.sh

ERROR_LOG=”/var/lib/mysql/mysql-error.log”
ALERT_EMAIL=”dba@fgedu.net.cn”

# 监控最新的错误日志条目
NEW_ERRORS=$(tail -n 100 $ERROR_LOG | grep -i “error\|warning\|critical”)

if [ -n “$NEW_ERRORS” ]; then
echo “MySQL错误日志中发现新的错误/警告：” >> /tmp/mysql_error_alert.txt
echo “$NEW_ERRORS” >> /tmp/mysql_error_alert.txt
mail -s “MySQL错误日志告警” $ALERT_EMAIL < /tmp/mysql_error_alert.txt rm -f /tmp/mysql_error_alert.txt fi # 使用crontab定时执行 echo "* * * * * /mysql/scripts/monitor_error_log.sh" >> /var/spool/cron/root

# 查看慢查询日志配置
mysql> SHOW GLOBAL VARIABLES LIKE ‘slow_query_log’;
+—————-+——-+
| Variable_name | Value |
+—————-+——-+
| slow_query_log | ON |
+—————-+——-+

mysql> SHOW GLOBAL VARIABLES LIKE ‘slow_query_log_file’;
+———————+—————————+
| Variable_name | Value |
+———————+—————————+
| slow_query_log_file | /var/lib/mysql/slow.log |
+———————+—————————+

mysql> SHOW GLOBAL VARIABLES LIKE ‘long_query_time’;
+—————–+———-+
| Variable_name | Value |
+—————–+———-+
| long_query_time | 10.000000 |
+—————–+———-+

# 监控慢查询日志的脚本示例
#!/bin/bash
# monitor_slow_query.sh

SLOW_LOG=”/var/lib/mysql/slow.log”
ALERT_EMAIL=”dba@fgedu.net.cn”
THRESHOLD=5 # 每分钟慢查询数量阈值

# 获取当前时间和5分钟前的时间
CURRENT_TIME=$(date +”%s”)
FIVE_MINUTES_AGO=$(($CURRENT_TIME – 300))

# 计算5分钟内的慢查询数量
SLOW_QUERY_COUNT=$(grep -c “^# Time:” $SLOW_LOG | xargs -I {} echo “{}”)

if [ $SLOW_QUERY_COUNT -gt $THRESHOLD ]; then
echo “5分钟内慢查询数量超过阈值：$SLOW_QUERY_COUNT” >> /tmp/mysql_slow_alert.txt
echo “查看慢查询日志：$SLOW_LOG” >> /tmp/mysql_slow_alert.txt
tail -n 100 $SLOW_LOG >> /tmp/mysql_slow_alert.txt
mail -s “MySQL慢查询告警” $ALERT_EMAIL < /tmp/mysql_slow_alert.txt rm -f /tmp/mysql_slow_alert.txt fi # 使用crontab定时执行 echo "*/5 * * * * /mysql/scripts/monitor_slow_query.sh" >> /var/spool/cron/root

# 安装Nagios MySQL插件
# yum install nagios-plugins-mysql

# 配置MySQL监控命令
# vi /etc/nagios/objects/commands.cfg
define command {
command_name check_mysql_health
command_line $USER1$/check_mysql_health –hostname=$HOSTADDRESS$ –username=$ARG1$ –password=$ARG2$ –mode=$ARG3$ $ARG4$
}

# 配置MySQL服务告警
# vi /etc/nagios/objects/services.cfg
define service {
use generic-service
host_name mysql-server
service_description MySQL Connections
check_command check_mysql_health!nagios!password!connections! –warning=80 –critical=90
notification_options w,c,r
notification_interval 60
contact_groups admins
}

define service {
use generic-service
host_name mysql-server
service_description MySQL QPS
check_command check_mysql_health!nagios!password!questions! –warning=1000 –critical=2000
notification_options w,c,r
notification_interval 60
contact_groups admins
}

define service {
use generic-service
host_name mysql-server
service_description MySQL Slow Queries
check_command check_mysql_health!nagios!password!slow-queries! –warning=10 –critical=50
notification_options w,c,r
notification_interval 60
contact_groups admins
}

# 重启Nagios服务
# systemctl restart nagios

# 在Zabbix Web界面中配置MySQL告警

# 1. 配置告警媒介类型
# 管理 > 媒介类型 > 创建媒体类型
# 类型：Email
# SMTP服务器：smtp.fgedu.net.cn
# SMTP服务器端口：25
# SMTP HELO：fgedu.net.cn
# SMTP电邮：zabbix@fgedu.net.cn
# 认证：无
# 点击”测试”按钮测试配置

# 2. 配置用户告警媒介
# 管理 > 用户 > 选择用户 > 媒介 > 添加
# 类型：Email
# 收件人：dba@fgedu.net.cn
# 当启用时：是
# 严重度：选择需要接收的告警级别
# 点击”更新”按钮保存配置

# 3. 配置告警动作
# 配置 > 动作 > 创建动作
# 名称：MySQL告警动作
# 默认接收人：选择用户组
# 默认信息：自定义告警信息
# 恢复信息：自定义恢复信息
# 条件：配置告警触发条件
# 操作：配置告警通知操作
# 点击”添加”按钮保存配置

# 4. 配置告警阈值
# 配置 > 主机 > 选择MySQL主机 > 触发器 > 选择触发器 > 更新
# 表达式：配置告警触发表达式
# 严重性：选择告警级别
# 点击”更新”按钮保存配置

# 配置Prometheus告警规则
# vi /etc/prometheus/rules/mysql_alerts.yml
groups:
– name: mysql_alerts
rules:
– alert: MySQLHighConnections
expr: mysql_global_status_threads_connected / mysql_global_variables_max_connections * 100 > 80
for: 5m
labels:
severity: warning
annotations:
summary: MySQL连接数过高 ({{ $labels.instance }})
description: MySQL连接使用率超过80% (当前: {{ $value | printf “%.2f” }}%)

– alert: MySQLHighConnectionsCritical
expr: mysql_global_status_threads_connected / mysql_global_variables_max_connections * 100 > 90
for: 5m
labels:
severity: critical
annotations:
summary: MySQL连接数严重过高 ({{ $labels.instance }})
description: MySQL连接使用率超过90% (当前: {{ $value | printf “%.2f” }}%)

– alert: MySQLSlowQueries
expr: rate(mysql_global_status_slow_queries[5m]) > 10
for: 5m
labels:
severity: warning
annotations:
summary: MySQL慢查询过多 ({{ $labels.instance }})
description: MySQL慢查询率超过10个/分钟 (当前: {{ $value | printf “%.2f” }}个/分钟)

– alert: MySQLHighBufferPoolUsage
expr: mysql_global_status_innodb_buffer_pool_pages_data / mysql_global_status_innodb_buffer_pool_pages_total * 100 > 90
for: 5m
labels:
severity: warning
annotations:
summary: MySQL缓冲池使用率过高 ({{ $labels.instance }})
description: MySQL缓冲池使用率超过90% (当前: {{ $value | printf “%.2f” }}%)

# 配置Prometheus加载告警规则
# vi /etc/prometheus/prometheus.yml
rule_files:
– “/etc/prometheus/rules/mysql_alerts.yml”

# 配置Alertmanager
# vi /etc/alertmanager/alertmanager.yml
global:
smtp_smarthost: ‘smtp.fgedu.net.cn:25’
smtp_from: ‘alertmanager@fgedu.net.cn’
smtp_auth_username: ”
smtp_auth_password: ”
smtp_require_tls: false

route:
group_by: [‘alertname’, ‘instance’]
group_wait: 30s
group_interval: 5m
repeat_interval: 1h
receiver: ‘dba-emails’

receivers:
– name: ‘dba-emails’
email_configs:
– to: ‘dba@fgedu.net.cn’
send_resolved: true

# 重启Prometheus和Alertmanager服务
# systemctl restart prometheus
# systemctl restart alertmanager

#!/bin/bash
# mysql_alert.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn

# MySQL配置信息
MYSQL_USER=”root”
MYSQL_PASS=”password”
MYSQL_HOST=”localhost”
MYSQL_PORT=”3306″

# 告警接收人
ALERT_EMAIL=”dba@fgedu.net.cn”
ALERT_PHONE=”13800138000″

# 告警阈值
CONNECTION_THRESHOLD=80 # 连接数使用率阈值（%）
QPS_THRESHOLD=1000 # QPS阈值
SLOW_QUERY_THRESHOLD=10 # 慢查询数阈值
BUFFER_POOL_THRESHOLD=90 # 缓冲池使用率阈值（%）
DISK_SPACE_THRESHOLD=90 # 磁盘空间使用率阈值（%）

# 获取当前时间
CURRENT_TIME=$(date ‘+%Y-%m-%d %H:%M:%S’)

# 初始化告警信息
ALERT_INFO=””
ALERT_SEVERITY=”info”

# 检查MySQL连接数
CONNECTION_COUNT=$(mysql -u $MYSQL_USER -p”$MYSQL_PASS” -h $MYSQL_HOST -P $MYSQL_PORT -e “SHOW GLOBAL STATUS LIKE ‘Threads_connected’;” -N | awk ‘{print $2}’)
MAX_CONNECTION=$(mysql -u $MYSQL_USER -p”$MYSQL_PASS” -h $MYSQL_HOST -P $MYSQL_PORT -e “SHOW GLOBAL VARIABLES LIKE ‘max_connections’;” -N | awk ‘{print $2}’)
CONNECTION_USAGE=$(echo “scale=2; $CONNECTION_COUNT / $MAX_CONNECTION * 100” | bc)

if (( $(echo “$CONNECTION_USAGE > $CONNECTION_THRESHOLD” | bc -l) )); then
ALERT_INFO=”$ALERT_INFO\n连接数使用率过高：${CONNECTION_USAGE}% (当前：${CONNECTION_COUNT}，最大：${MAX_CONNECTION})”
ALERT_SEVERITY=”warning”
if (( $(echo “$CONNECTION_USAGE > $CONNECTION_THRESHOLD + 10″ | bc -l) )); then
ALERT_SEVERITY=”critical”
fi
fi

# 检查MySQL QPS
QUERIES=$(mysql -u $MYSQL_USER -p”$MYSQL_PASS” -h $MYSQL_HOST -P $MYSQL_PORT -e “SHOW GLOBAL STATUS LIKE ‘Queries’;” -N | awk ‘{print $2}’)
UPTIME=$(mysql -u $MYSQL_USER -p”$MYSQL_PASS” -h $MYSQL_HOST -P $MYSQL_PORT -e “SHOW GLOBAL STATUS LIKE ‘Uptime’;” -N | awk ‘{print $2}’)
if [ $UPTIME -gt 0 ]; then
QPS=$(echo “scale=2; $QUERIES / $UPTIME” | bc)
if (( $(echo “$QPS > $QPS_THRESHOLD” | bc -l) )); then
ALERT_INFO=”$ALERT_INFO\nQPS过高：${QPS} (阈值：${QPS_THRESHOLD})”
ALERT_SEVERITY=”warning”
fi
fi

# 检查MySQL慢查询数
SLOW_QUERIES=$(mysql -u $MYSQL_USER -p”$MYSQL_PASS” -h $MYSQL_HOST -P $MYSQL_PORT -e “SHOW GLOBAL STATUS LIKE ‘Slow_queries’;” -N | awk ‘{print $2}’)
if (( $SLOW_QUERIES > $SLOW_QUERY_THRESHOLD )); then
ALERT_INFO=”$ALERT_INFO\n慢查询数过多：${SLOW_QUERIES} (阈值：${SLOW_QUERY_THRESHOLD})”
ALERT_SEVERITY=”warning”
fi

# 检查MySQL缓冲池使用率
BUFFER_POOL_DATA=$(mysql -u $MYSQL_USER -p”$MYSQL_PASS” -h $MYSQL_HOST -P $MYSQL_PORT -e “SHOW GLOBAL STATUS LIKE ‘Innodb_buffer_pool_pages_data’;” -N | awk ‘{print $2}’)
BUFFER_POOL_TOTAL=$(mysql -u $MYSQL_USER -p”$MYSQL_PASS” -h $MYSQL_HOST -P $MYSQL_PORT -e “SHOW GLOBAL STATUS LIKE ‘Innodb_buffer_pool_pages_total’;” -N | awk ‘{print $2}’)
if [ $BUFFER_POOL_TOTAL -gt 0 ]; then
BUFFER_POOL_USAGE=$(echo “scale=2; $BUFFER_POOL_DATA / $BUFFER_POOL_TOTAL * 100” | bc)
if (( $(echo “$BUFFER_POOL_USAGE > $BUFFER_POOL_THRESHOLD” | bc -l) )); then
ALERT_INFO=”$ALERT_INFO\n缓冲池使用率过高：${BUFFER_POOL_USAGE}% (阈值：${BUFFER_POOL_THRESHOLD}%)”
ALERT_SEVERITY=”warning”
fi
fi

# 检查磁盘空间使用率
MYSQL_DATA_DIR=$(mysql -u $MYSQL_USER -p”$MYSQL_PASS” -h $MYSQL_HOST -P $MYSQL_PORT -e “SHOW GLOBAL VARIABLES LIKE ‘datadir’;” -N | awk ‘{print $2}’)
DISK_USAGE=$(df -h $MYSQL_DATA_DIR | tail -n 1 | awk ‘{print $5}’ | sed ‘s/%//’)
if (( $DISK_USAGE > $DISK_SPACE_THRESHOLD )); then
ALERT_INFO=”$ALERT_INFO\n磁盘空间使用率过高：${DISK_USAGE}% (目录：${MYSQL_DATA_DIR}，阈值：${DISK_SPACE_THRESHOLD}%)”
ALERT_SEVERITY=”critical”
fi

# 发送告警
if [ -n “$ALERT_INFO” ]; then
# 构建告警标题
ALERT_SUBJECT=”[MySQL告警] ${ALERT_SEVERITY} – ${MYSQL_HOST}:${MYSQL_PORT}”

# 构建告警内容
ALERT_CONTENT=”MySQL告警信息\n”
ALERT_CONTENT=”${ALERT_CONTENT}====================\n”
ALERT_CONTENT=”${ALERT_CONTENT}告警时间：${CURRENT_TIME}\n”
ALERT_CONTENT=”${ALERT_CONTENT}告警级别：${ALERT_SEVERITY}\n”
ALERT_CONTENT=”${ALERT_CONTENT}MySQL实例：${MYSQL_HOST}:${MYSQL_PORT}\n”
ALERT_CONTENT=”${ALERT_CONTENT}告警信息：${ALERT_INFO}\n”
ALERT_CONTENT=”${ALERT_CONTENT}====================\n”
ALERT_CONTENT=”${ALERT_CONTENT}请及时处理！\n”
ALERT_CONTENT=”${ALERT_CONTENT}学习交流加群风哥微信: itpux-com\n”

# 发送邮件告警
echo -e “${ALERT_CONTENT}” | mail -s “${ALERT_SUBJECT}” ${ALERT_EMAIL}

# 如果是紧急告警，发送短信告警
if [ “$ALERT_SEVERITY” = “critical” ]; then
# 这里可以集成短信告警API
echo “发送短信告警：${ALERT_SUBJECT} ${ALERT_INFO}” >> /mysql/logs/alert_sms.log
fi

# 记录告警日志
echo “${CURRENT_TIME} ${ALERT_SUBJECT} ${ALERT_INFO}” >> /mysql/logs/mysql_alert.log
fi

# 使用crontab定时执行告警脚本
# crontab -e
* * * * * /mysql/scripts/mysql_alert.sh

# 查看告警日志
# tail -n 10 /mysql/logs/mysql_alert.log
2026-04-03 22:00:00 [MySQL告警] warning – localhost:3306 连接数使用率过高：85.00% (当前：850，最大：1000)
2026-04-03 22:05:00 [MySQL告警] warning – localhost:3306 连接数使用率过高：86.00% (当前：860，最大：1000)
2026-04-03 22:10:00 [MySQL告警] critical – localhost:3306 连接数使用率过高：95.00% (当前：950，最大：1000)

# 与企业微信集成示例
#!/bin/bash
# mysql_wechat_alert.sh

# 企业微信配置
CORPID=”wx1234567890abcdef”
CORPSECRET=”abcdef1234567890abcdef1234567890abcdef”
AGENTID=”1000001″
TOPARTY=”2″
TOUSER=”@all”

# 获取access_token
ACCESS_TOKEN=$(curl -s “https://qyapi.weixin.qq.com/cgi-bin/gettoken?corpid=$CORPID&corpsecret=$CORPSECRET” | jq -r “.access_token”)

# 告警信息
ALERT_TITLE=”[MySQL告警] 连接数过高”
ALERT_CONTENT=”MySQL实例：localhost:3306\n告警时间：$(date ‘+%Y-%m-%d %H:%M:%S’)\n告警级别：warning\n告警信息：连接数使用率超过80%，当前使用率：85%”

# 发送企业微信消息
curl -s -H “Content-Type: application/json” -X POST “https://qyapi.weixin.qq.com/cgi-bin/message/send?access_token=$ACCESS_TOKEN” -d “{
‘touser’: ‘$TOUSER’,
‘toparty’: ‘$TOPARTY’,
‘msgtype’: ‘text’,
‘agentid’: $AGENTID,
‘text’: {
‘content’: ‘$ALERT_TITLE\n\n$ALERT_CONTENT’
},
‘safe’: 0
}”

# 与钉钉集成示例
#!/bin/bash
# mysql_dingtalk_alert.sh

# 钉钉机器人配置
WEBHOOK=”https://oapi.dingtalk.com/robot/send?access_token=abcdef1234567890abcdef1234567890abcdef”

# 告警信息
ALERT_TITLE=”[MySQL告警] 连接数过高”
ALERT_CONTENT=”MySQL实例：localhost:3306\n告警时间：$(date ‘+%Y-%m-%d %H:%M:%S’)\n告警级别：warning\n告警信息：连接数使用率超过80%，当前使用率：85%”

# 发送钉钉消息
curl -s -H “Content-Type: application/json” -X POST $WEBHOOK -d “{
‘msgtype’: ‘text’,
‘text’: {
‘content’: ‘$ALERT_TITLE\n\n$ALERT_CONTENT’
}
}”