python3 监控mysql5.7(3)

1.脚本

#!/usr/bin/env python3
import pymysql
import time
import texttable
from datetime import datetime
from collections import OrderedDict

class MySQLMonitor:
    def __init__(self):
        self.conn_config = {
            'host': 'localhost',
            'port': 3306,
            'user': 'root',
            'password': 'Root@1234',
            'database': 'mysql',
            'connect_timeout': 3
        }
        self.refresh_interval = 5  # seconds
        self.alert_thresholds = {
            'threads_connected_pct': 0.8,
            'threads_running': 20,
            'slow_queries': 10,
            'innodb_row_lock_waits': 50,
            'aborted_connects': 10
        }

    def connect(self):
        """建立带重试机制的MySQL连接"""
        try:
            return pymysql.connect(**self.conn_config)
        except pymysql.Error as e:
            print(f"\033[31m✖ Connection failed: {e}\033[0m")
            return None

    def safe_int(self, value, default=0):
        """安全转换为整数"""
        try:
            return int(value)
        except (ValueError, TypeError):
            return default

    def get_metrics(self, conn):
        """获取全面的监控指标"""
        metrics = OrderedDict()
        
        try:
            with conn.cursor() as cursor:
                # 获取全局状态和变量
                cursor.execute("SHOW GLOBAL STATUS")
                metrics['status'] = OrderedDict((k.lower(), self.safe_int(v)) for k, v in cursor.fetchall())
                
                cursor.execute("SHOW GLOBAL VARIABLES")
                metrics['variables'] = OrderedDict((k.lower(), v) for k, v in cursor.fetchall())
                
                cursor.execute("SHOW ENGINE INNODB STATUS")
                metrics['innodb_status'] = cursor.fetchone()[2]
                
                # 获取复制状态（如果有）
                cursor.execute("SHOW SLAVE STATUS")
                metrics['replication'] = cursor.fetchone()
                
                # 获取进程列表
                cursor.execute("SHOW FULL PROCESSLIST")
                metrics['processlist'] = cursor.fetchall()
                
                # 获取数据库大小（修正后的查询）
                cursor.execute("""
                    SELECT table_schema as schema_name, 
                    ROUND(SUM(data_length+index_length)/1024/1024,2) as size_mb 
                    FROM information_schema.tables 
                    GROUP BY table_schema
                """)
                # 将结果转换为列表形式
                metrics['db_sizes'] = [list(row) for row in cursor.fetchall()]
                
            return metrics
            
        except pymysql.Error as e:
            print(f"\033[31m✖ Query error: {e}\033[0m")
            return None

    def analyze_metrics(self, metrics):
        """分析指标并生成报告"""
        if not metrics:
            return None
            
        report = OrderedDict()
        s = metrics['status']
        v = metrics['variables']
        
        # 基础健康状态
        report['uptime'] = {
            'value': f"{s.get('uptime', 0)//3600}h {s.get('uptime', 0)%3600//60}m",
            'alert': False
        }
        
        # 连接状态
        max_conn = self.safe_int(v.get('max_connections', 0))
        threads_conn = s.get('threads_connected', 0)
        report['connections'] = {
            'used': threads_conn,
            'max': max_conn,
            'pct': threads_conn/max_conn if max_conn > 0 else 0,
            'alert': threads_conn/max_conn > self.alert_thresholds['threads_connected_pct'] if max_conn > 0 else False
        }
        
        # 性能指标
        report['performance'] = {
            'qps': s.get('questions', 0),
            'tps': s.get('com_commit', 0) + s.get('com_rollback', 0),
            'slow_queries': s.get('slow_queries', 0),
            'alert': s.get('slow_queries', 0) > self.alert_thresholds['slow_queries']
        }
        
        # InnoDB状态
        report['innodb'] = {
            'bp_hit_rate': self.calculate_bp_hit_rate(s),
            'row_lock_waits': s.get('innodb_row_lock_waits', 0),
            'alert': s.get('innodb_row_lock_waits', 0) > self.alert_thresholds['innodb_row_lock_waits']
        }
        
        # 复制状态
        if metrics.get('replication'):
            report['replication'] = {
                'io_thread': metrics['replication'].get('Slave_IO_Running', 'Unknown'),
                'sql_thread': metrics['replication'].get('Slave_SQL_Running', 'Unknown'),
                'delay': metrics['replication'].get('Seconds_Behind_Master', 'Unknown'),
                'alert': metrics['replication'].get('Slave_IO_Running') != 'Yes' or 
                         metrics['replication'].get('Slave_SQL_Running') != 'Yes'
            }
        
        return report

    def calculate_bp_hit_rate(self, status):
        """计算缓冲池命中率"""
        reads = status.get('innodb_buffer_pool_reads', 1)
        requests = status.get('innodb_buffer_pool_read_requests', 1)
        if requests == 0:
            return "0.0%"
        return f"{100*(1-reads/requests):.1f}%"

    def display_report(self, report, metrics):
        """专业格式显示监控报告"""
        # 清屏
        print("\033[H\033[J", end="")
        
        # 打印标题
        print(f"\033[1;36mMySQL Advanced Monitor - {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}\033[0m")
        print("-" * 100)
        
        if not report:
            print("\033[31mUnable to generate report due to connection/query errors\033[0m")
            return
        
        # 1. 基础健康状态
        print("\033[1;34mBASIC HEALTH\033[0m")
        print(f"  Uptime: {report['uptime']['value']}")
        
        # 2. 连接状态
        conn_status = "NORMAL" if not report['connections']['alert'] else "\033[31mWARNING\033[0m"
        print(f"\n\033[1;34mCONNECTIONS [{conn_status}]\033[0m")
        print(f"  Connections: {report['connections']['used']}/{report['connections']['max']} "
              f"({report['connections']['pct']*100:.1f}%)")
        print(f"  Running threads: {metrics['status'].get('threads_running', 'N/A')}")
        
        # 3. 性能指标
        perf_status = "NORMAL" if not report['performance']['alert'] else "\033[31mWARNING\033[0m"
        print(f"\n\033[1;34mPERFORMANCE [{perf_status}]\033[0m")
        print(f"  QPS: {report['performance']['qps']} | TPS: {report['performance']['tps']}")
        print(f"  Slow queries: {report['performance']['slow_queries']}")
        
        # 4. InnoDB状态
        innodb_status = "NORMAL" if not report['innodb']['alert'] else "\033[31mWARNING\033[0m"
        print(f"\n\033[1;34mINNODB [{innodb_status}]\033[0m")
        print(f"  Buffer Pool Hit Rate: {report['innodb']['bp_hit_rate']}")
        print(f"  Row Lock Waits: {report['innodb']['row_lock_waits']}")
        
        # 5. 复制状态
        if 'replication' in report:
            repl_status = "NORMAL" if not report['replication']['alert'] else "\033[31mWARNING\033[0m"
            print(f"\n\033[1;34mREPLICATION [{repl_status}]\033[0m")
            print(f"  IO Thread: {report['replication']['io_thread']} | "
                  f"SQL Thread: {report['replication']['sql_thread']}")
            print(f"  Replication Delay: {report['replication']['delay']} sec")
        
        # 6. 数据库大小（使用texttable）
        print("\n\033[1;34mDATABASE SIZES\033[0m")
        table = texttable.Texttable()
        table.set_deco(texttable.Texttable.HEADER)
        table.set_cols_align(["l", "r"])
        table.set_cols_width([30, 15])
        db_sizes = metrics.get('db_sizes', [])
        if not db_sizes:
            db_sizes = [["No databases found", "0"]]
        table.add_rows([["Database", "Size(MB)"]] + db_sizes)
        print(table.draw())
        
        # 7. 关键进程（使用texttable）
        print("\n\033[1;34mTOP PROCESSES\033[0m")
        table = texttable.Texttable()
        table.set_deco(texttable.Texttable.HEADER)
        table.set_cols_align(["r", "l", "l", "l", "r", "l"])
        table.set_cols_width([8, 15, 20, 15, 8, 30])
        processes = metrics.get('processlist', [])[:10]  # 显示前10个进程
        rows = [["ID", "User", "Host", "DB", "Time", "State"]]
        rows.extend([[p[0], p[1], p[2], p[3], p[5], p[6] or ''] for p in processes])
        table.add_rows(rows)
        print(table.draw())

    def run(self):
        """主监控循环"""
        print("\033[1;32mMySQL Advanced Monitor Started (Ctrl+C to exit)\033[0m")
        
        try:
            while True:
                conn = self.connect()
                if not conn:
                    time.sleep(self.refresh_interval)
                    continue
                
                metrics = self.get_metrics(conn)
                if metrics:
                    report = self.analyze_metrics(metrics)
                    self.display_report(report, metrics)
                
                if conn:
                    conn.close()
                time.sleep(self.refresh_interval)
                
        except KeyboardInterrupt:
            print("\n\033[1;32mMonitoring stopped\033[0m")

if __name__ == '__main__':
    # 检查依赖是否安装
    try:
        import pymysql
        import texttable
    except ImportError as e:
        print(f"\033[31mError: Required module not found - {e}\033[0m")
        print("Please install dependencies with:")
        print("pip install pymysql texttable")
        exit(1)
    
    monitor = MySQLMonitor()
    monitor.run()

2.依赖的包

pip3 install --user texttable

[hx@localhost pycode]$ pip3 install --user texttable
Collecting texttable
  Retrying (Retry(total=4, connect=None, read=None, redirect=None, status=None)) after connection broken by 'ReadTimeoutError("HTTPSConnectionPool(host='pypi.org', port=443): Read timed out. (read timeout=15)",)': /simple/texttable/
  Downloading https://files.pythonhosted.org/packages/24/99/4772b8e00a136f3e01236de33b0efda31ee7077203ba5967fcc76da94d65/texttable-1.7.0-py2.py3-none-any.whl
Installing collected packages: texttable
Successfully installed texttable-1.7.0
[hx@localhost pycode]$

3.运行脚本

MySQL Advanced Monitor - 2025-07-30 15:37:36
----------------------------------------------------------------------------------------------------
BASIC HEALTH
  Uptime: 1h 4m

CONNECTIONS [NORMAL]
  Connections: 2/151 (1.3%)
  Running threads: 1

PERFORMANCE [NORMAL]
  QPS: 785 | TPS: 0
  Slow queries: 0

INNODB [NORMAL]
  Buffer Pool Hit Rate: 98.3%
  Row Lock Waits: 0

DATABASE SIZES
           Database                 Size(MB)
================================================
information_schema                         0.160
mysql                                      2.520
performance_schema                             0
sys                                        0.020

TOP PROCESSES
   ID           User                 Host                 DB            Time                 State
===============================================================================================================
       3   root              localhost              None                   845
     122   root              localhost:51326        mysql                    0   starting