脚本如何监控数据库连接数

本文目录导读：

1. MySQL 连接数监控
2. PostgreSQL 连接数监控
3. Redis 连接数监控
4. 通用监控脚本(支持多种数据库)
5. 使用系统工具监控
6. Prometheus + Grafana 监控方案
7. 监控脚本的部署建议

我来介绍几种监控数据库连接数的脚本方法:

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

监控脚本的部署建议

1. 定时执行：

   # crontab 配置，每5分钟执行一次
   */5 * * * * /path/to/monitor_script.sh

2. 日志记录：

   # 输出到日志文件
   ./monitor.sh >> /var/log/db_monitor.log 2>&1

3. 告警通知：

• 邮件通知
• 钉钉/微信机器人
• Slack
• Prometheus AlertManager

4. 指标记录：

• 保存到时序数据库(如InfluxDB)
• 可视化到Grafana

这些脚本可以根据具体需求进行修改和扩展,建议结合使用多种监控方式来确保数据库稳定性。