本文目录导读：
我来介绍几种监控数据库连接数的脚本方法:
MySQL 连接数监控
Shell 脚本
#!/bin/bash
# MySQL 连接数监控脚本
DB_USER="root"
DB_PASS="password"
DB_HOST="localhost"
THRESHOLD=100
# 获取当前连接数
CURRENT_CONN=$(mysql -u$DB_USER -p$DB_PASS -h$DB_HOST -e "SHOW STATUS LIKE 'Threads_connected'" | grep Threads_connected | awk '{print $2}')
# 获取最大连接数
MAX_CONN=$(mysql -u$DB_USER -p$DB_PASS -h$DB_HOST -e "SHOW VARIABLES LIKE 'max_connections'" | grep max_connections | awk '{print $2}')
# 计算使用率
USAGE_RATE=$(echo "scale=2; $CURRENT_CONN/$MAX_CONN*100" | bc)
echo "当前连接数: $CURRENT_CONN"
echo "最大连接数: $MAX_CONN"
echo "使用率: $USAGE_RATE%"
# 告警逻辑
if [ $CURRENT_CONN -gt $THRESHOLD ]; then
echo "WARNING: 连接数超过阈值 $THRESHOLD"
# 可以发送告警邮件等
fi
Python 脚本
#!/usr/bin/env python3
import pymysql
import time
import smtplib
from email.mime.text import MIMEText
class MySQLMonitor:
def __init__(self, host='localhost', user='root', password='password'):
self.host = host
self.user = user
self.password = password
self.threshold = 100
def get_connections(self):
conn = pymysql.connect(
host=self.host,
user=self.user,
password=self.password
)
cursor = conn.cursor()
# 获取当前连接数
cursor.execute("SHOW STATUS LIKE 'Threads_connected'")
current_conn = int(cursor.fetchone()[1])
# 获取最大连接数
cursor.execute("SHOW VARIABLES LIKE 'max_connections'")
max_conn = int(cursor.fetchone()[1])
cursor.close()
conn.close()
return current_conn, max_conn
def check_and_alert(self):
current, max_conn = self.get_connections()
usage_rate = (current / max_conn) * 100
print(f"[{time.strftime('%Y-%m-%d %H:%M:%S')}] "
f"当前连接: {current}/{max_conn} ({usage_rate:.2f}%)")
if current > self.threshold:
self.send_alert(current, max_conn)
def send_alert(self, current, max_conn):
# 发送告警逻辑
subject = f"MySQL连接数告警: {current}/{max_conn}"
print(f"ALERT: {subject}")
if __name__ == "__main__":
monitor = MySQLMonitor()
monitor.check_and_alert()
PostgreSQL 连接数监控
Shell 脚本
#!/bin/bash
# PostgreSQL 连接数监控
PG_USER="postgres"
PG_HOST="localhost"
THRESHOLD=80
# 获取当前活跃连接数
CURRENT_CONN=$(psql -h $PG_HOST -U $PG_USER -t -c "SELECT count(*) FROM pg_stat_activity WHERE state = 'active';" | tr -d ' ')
# 获取最大连接数
MAX_CONN=$(psql -h $PG_HOST -U $PG_USER -t -c "SHOW max_connections;" | tr -d ' ')
echo "当前活跃连接: $CURRENT_CONN"
echo "最大连接数: $MAX_CONN"
if [ $CURRENT_CONN -gt $THRESHOLD ]; then
echo "警告: 连接数过高"
fi
Redis 连接数监控
#!/bin/bash
# Redis 连接数监控
REDIS_HOST="localhost"
REDIS_PORT=6379
THRESHOLD=1000
# 获取连接数
CONNECTED_CLIENTS=$(redis-cli -h $REDIS_HOST -p $REDIS_PORT INFO clients | grep connected_clients | cut -d: -f2 | tr -d '\r')
echo "当前Redis连接数: $CONNECTED_CLIENTS"
if [ $CONNECTED_CLIENTS -gt $THRESHOLD ]; then
echo "警告: Redis连接数超过阈值"
fi
通用监控脚本(支持多种数据库)
#!/usr/bin/env python3
import psutil
import sqlite3
import json
import time
from datetime import datetime
class DatabaseMonitor:
def __init__(self, config_file='db_config.json'):
with open(config_file, 'r') as f:
self.config = json.load(f)
def monitor_mysql(self, db_config):
# MySQL监控逻辑
pass
def monitor_postgresql(self, db_config):
# PostgreSQL监控逻辑
pass
def monitor_mongodb(self, db_config):
# MongoDB监控逻辑
import pymongo
client = pymongo.MongoClient(db_config['host'], db_config['port'])
stats = client.admin.command('serverStatus')
current_conn = stats['connections']['current']
available_conn = stats['connections']['available']
return current_conn, current_conn + available_conn
def save_metrics(self, db_type, metrics):
"""保存监控指标到数据库"""
conn = sqlite3.connect('db_monitor.db')
cursor = conn.cursor()
cursor.execute('''
CREATE TABLE IF NOT EXISTS connection_metrics (
id INTEGER PRIMARY KEY AUTOINCREMENT,
db_type TEXT,
current_connections INTEGER,
max_connections INTEGER,
timestamp DATETIME
)
''')
cursor.execute('''
INSERT INTO connection_metrics
(db_type, current_connections, max_connections, timestamp)
VALUES (?, ?, ?, ?)
''', (db_type, metrics['current'], metrics['max'], datetime.now()))
conn.commit()
conn.close()
def run(self):
while True:
for db in self.config['databases']:
if db['type'] == 'mysql':
metrics = self.monitor_mysql(db)
elif db['type'] == 'postgresql':
metrics = self.monitor_postgresql(db)
elif db['type'] == 'mongodb':
metrics = self.monitor_mongodb(db)
self.save_metrics(db['type'], metrics)
# 检查阈值
if metrics['current'] > db.get('threshold', 100):
print(f"告警: {db['type']} 连接数过高")
time.sleep(60) # 每60秒检查一次
if __name__ == "__main__":
monitor = DatabaseMonitor()
monitor.run()
使用系统工具监控
#!/bin/bash
# 使用 ss 命令监控 MySQL 连接
echo "MySQL连接状态:"
ss -tan | grep :3306 | awk '{print $1}' | sort | uniq -c
# 使用 netstat 命令
echo "数据库连接数统计:"
netstat -an | grep -E ":(3306|5432|6379)" | awk '{print $4}' | awk -F: '{print $2}' | sort | uniq -c
Prometheus + Grafana 监控方案
# prometheus.yml
scrape_configs:
- job_name: 'mysql'
static_configs:
- targets: ['localhost:9104'] # mysqld_exporter
- job_name: 'postgresql'
static_configs:
- targets: ['localhost:9187'] # postgres_exporter
监控脚本的部署建议
-
定时执行：
# crontab 配置，每5分钟执行一次 */5 * * * * /path/to/monitor_script.sh
-
日志记录：
# 输出到日志文件 ./monitor.sh >> /var/log/db_monitor.log 2>&1
-
告警通知：
- 邮件通知
- 钉钉/微信机器人
- Slack
- Prometheus AlertManager
- 指标记录：
- 保存到时序数据库(如InfluxDB)
- 可视化到Grafana
这些脚本可以根据具体需求进行修改和扩展,建议结合使用多种监控方式来确保数据库稳定性。
