第100集MySQL数据库运维监控与优化实战

1. MySQL数据库运维监控概述

MySQL作为最流行的关系型数据库，在生产环境中需要专业的运维监控和管理。本文将详细介绍MySQL集群监控、性能调优、主从复制、索引优化的完整解决方案，帮助运维人员有效管理MySQL集群。

1.1 核心挑战

集群监控: 实时监控MySQL集群和实例状态
性能调优: 优化MySQL性能和查询效率
主从复制: 管理主从复制和故障切换
索引优化: 优化索引设计和查询性能
故障诊断: 快速定位MySQL相关问题

1.2 技术架构

MySQL监控 → 数据采集 → 性能分析 → 告警通知 → 自动优化
   ↓        ↓         ↓         ↓         ↓
集群指标 → 监控代理 → 数据存储 → 告警引擎 → 调优脚本
   ↓        ↓         ↓         ↓         ↓
主从管理 → 索引优化 → 性能调优 → 自动修复 → 运维记录

2. MySQL监控系统

2.1 Maven依赖配置

<!-- pom.xml -->
<dependencies>
    <!-- Spring Boot Web -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    
    <!-- Spring Boot Data Redis -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-redis</artifactId>
    </dependency>
    
    <!-- MySQL JDBC Driver -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.33</version>
    </dependency>
    
    <!-- Micrometer监控 -->
    <dependency>
        <groupId>io.micrometer</groupId>
        <artifactId>micrometer-registry-prometheus</artifactId>
    </dependency>
    
    <!-- MyBatis Plus -->
    <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-boot-starter</artifactId>
        <version>3.5.2</version>
    </dependency>
</dependencies>

2.2 应用配置

# application.yml
server:
  port: 8080

spring:
  redis:
    host: localhost
    port: 6379
    database: 0

# MySQL监控配置
mysql-monitor:
  master-url: "jdbc:mysql://localhost:3306/monitoring?useSSL=false&serverTimezone=UTC"
  slave-url: "jdbc:mysql://localhost:3307/monitoring?useSSL=false&serverTimezone=UTC"
  username: "monitor"
  password: "password"
  cluster-name: "production-cluster"
  collection-interval: 10000
  query-timeout: 30000
  connection-threshold: 80
  slow-query-threshold: 1000

3. MySQL监控服务

3.1 MySQL监控实体类

/**
 * MySQL集群监控数据实体类
 */
@Data
@TableName("mysql_cluster_monitor")
public class MySQLClusterMonitor {
    
    @TableId(type = IdType.AUTO)
    private Long id;                    // 主键ID
    
    private String clusterName;          // 集群名称
    
    private String hostname;             // 主机名
    
    private String ip;                   // IP地址
    
    private Integer instanceCount;       // 实例数量
    
    private Integer databaseCount;       // 数据库数量
    
    private Integer tableCount;          // 表数量
    
    private Long totalConnections;       // 总连接数
    
    private Long activeConnections;      // 活跃连接数
    
    private Long maxConnections;         // 最大连接数
    
    private Double connectionUsage;      // 连接使用率
    
    private Long queriesPerSecond;       // 每秒查询数
    
    private Long slowQueries;            // 慢查询数
    
    private Double avgQueryTime;         // 平均查询时间(毫秒)
    
    private Long innodbBufferPoolHit;    // InnoDB缓冲池命中率
    
    private Long innodbBufferPoolMiss;   // InnoDB缓冲池未命中数
    
    private Double bufferPoolHitRate;     // 缓冲池命中率
    
    private Long tableLocksWaited;       // 表锁等待数
    
    private Long deadlocks;              // 死锁数
    
    private String clusterStatus;        // 集群状态
    
    private Date collectTime;            // 采集时间
    
    private Date createTime;             // 创建时间
}

/**
 * MySQL实例监控数据实体类
 */
@Data
@TableName("mysql_instance_monitor")
public class MySQLInstanceMonitor {
    
    @TableId(type = IdType.AUTO)
    private Long id;                    // 主键ID
    
    private String clusterName;          // 集群名称
    
    private String instanceId;           // 实例ID
    
    private String host;                 // 主机地址
    
    private Integer port;                // 端口
    
    private String role;                 // 角色(master/slave)
    
    private String status;               // 状态
    
    private String version;              // MySQL版本
    
    private Long uptime;                 // 运行时间(秒)
    
    private Long connections;            // 连接数
    
    private Long threadsConnected;       // 已连接线程数
    
    private Long threadsRunning;         // 运行中线程数
    
    private Long queries;                // 查询总数
    
    private Long slowQueries;            // 慢查询数
    
    private Double qps;                  // 每秒查询数
    
    private Double tps;                  // 每秒事务数
    
    private Long bytesReceived;          // 接收字节数
    
    private Long bytesSent;              // 发送字节数
    
    private Long innodbBufferPoolSize;   // InnoDB缓冲池大小
    
    private Long innodbBufferPoolUsed;   // InnoDB缓冲池已使用
    
    private Double innodbBufferPoolUsage; // InnoDB缓冲池使用率
    
    private String instanceStatus;       // 实例状态
    
    private Date collectTime;            // 采集时间
    
    private Date createTime;             // 创建时间
}

3.2 MySQL监控服务

/**
 * MySQL监控服务
 * 负责MySQL集群和实例数据的采集、存储和分析
 */
@Service
public class MySQLMonitorService {

    @Autowired
    private MySQLClusterMonitorMapper mysqlClusterMonitorMapper;
    
    @Autowired
    private MySQLInstanceMonitorMapper mysqlInstanceMonitorMapper;
    
    @Autowired
    private RedisTemplate<String, Object> redisTemplate;
    
    @Autowired
    private AlertService alertService;
    
    private DataSource masterDataSource;
    private DataSource slaveDataSource;

    /**
     * 初始化MySQL数据源
     */
    @PostConstruct
    public void initMySQLDataSources() {
        try {
            // 创建主库数据源
            masterDataSource = createDataSource("jdbc:mysql://localhost:3306/monitoring?useSSL=false&serverTimezone=UTC", 
                    "monitor", "password");
            
            // 创建从库数据源
            slaveDataSource = createDataSource("jdbc:mysql://localhost:3307/monitoring?useSSL=false&serverTimezone=UTC", 
                    "monitor", "password");
            
            log.info("MySQL数据源初始化成功");
            
        } catch (Exception e) {
            log.error("初始化MySQL数据源失败: {}", e.getMessage(), e);
        }
    }

    /**
     * 创建数据源
     */
    private DataSource createDataSource(String url, String username, String password) {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(url);
        config.setUsername(username);
        config.setPassword(password);
        config.setMaximumPoolSize(10);
        config.setMinimumIdle(5);
        config.setConnectionTimeout(30000);
        config.setIdleTimeout(600000);
        config.setMaxLifetime(1800000);
        
        return new HikariDataSource(config);
    }

    /**
     * 采集MySQL集群数据
     * 定期采集MySQL集群和实例信息
     */
    @Scheduled(fixedRate = 10000) // 每10秒执行一次
    public void collectMySQLData() {
        try {
            // 1. 采集集群信息
            collectClusterInfo();
            
            // 2. 采集实例信息
            collectInstanceInfo();
            
        } catch (Exception e) {
            log.error("采集MySQL数据失败: {}", e.getMessage(), e);
        }
    }

    /**
     * 采集集群信息
     */
    private void collectClusterInfo() {
        try {
            // 1. 获取集群信息
            MySQLClusterInfo clusterInfo = getClusterInfo();
            
            // 2. 创建集群监控数据
            MySQLClusterMonitor monitorData = createClusterMonitorData(clusterInfo);
            
            // 3. 保存到数据库
            mysqlClusterMonitorMapper.insert(monitorData);
            
            // 4. 更新缓存
            updateClusterCache(monitorData);
            
            // 5. 检查集群告警
            checkClusterAlert(monitorData);
            
            log.debug("采集集群信息: instanceCount={}, databaseCount={}", 
                    monitorData.getInstanceCount(), monitorData.getDatabaseCount());
                    
        } catch (Exception e) {
            log.error("采集集群信息失败: {}", e.getMessage(), e);
        }
    }

    /**
     * 获取集群信息
     */
    private MySQLClusterInfo getClusterInfo() {
        MySQLClusterInfo clusterInfo = new MySQLClusterInfo();
        
        try {
            // 获取主库信息
            setMasterInfo(clusterInfo);
            
            // 获取从库信息
            setSlaveInfo(clusterInfo);
            
            // 计算集群统计信息
            calculateClusterStatistics(clusterInfo);
            
            // 设置集群状态
            clusterInfo.setClusterStatus(determineClusterStatus(clusterInfo));
            
        } catch (Exception e) {
            log.error("获取集群信息失败: {}", e.getMessage(), e);
        }
        
        return clusterInfo;
    }

    /**
     * 设置主库信息
     */
    private void setMasterInfo(MySQLClusterInfo clusterInfo) {
        try {
            Connection connection = masterDataSource.getConnection();
            
            // 获取数据库数量
            String sql = "SELECT COUNT(*) as db_count FROM information_schema.SCHEMATA WHERE SCHEMA_NAME NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')";
            PreparedStatement statement = connection.prepareStatement(sql);
            ResultSet resultSet = statement.executeQuery();
            
            if (resultSet.next()) {
                clusterInfo.setDatabaseCount(resultSet.getInt("db_count"));
            }
            
            resultSet.close();
            statement.close();
            
            // 获取表数量
            sql = "SELECT COUNT(*) as table_count FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')";
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();
            
            if (resultSet.next()) {
                clusterInfo.setTableCount(resultSet.getInt("table_count"));
            }
            
            resultSet.close();
            statement.close();
            
            // 获取连接信息
            sql = "SHOW STATUS LIKE 'Connections'";
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();
            
            if (resultSet.next()) {
                clusterInfo.setTotalConnections(resultSet.getLong("Value"));
            }
            
            resultSet.close();
            statement.close();
            
            // 获取活跃连接数
            sql = "SHOW STATUS LIKE 'Threads_connected'";
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();
            
            if (resultSet.next()) {
                clusterInfo.setActiveConnections(resultSet.getLong("Value"));
            }
            
            resultSet.close();
            statement.close();
            
            // 获取最大连接数
            sql = "SHOW VARIABLES LIKE 'max_connections'";
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();
            
            if (resultSet.next()) {
                clusterInfo.setMaxConnections(resultSet.getLong("Value"));
            }
            
            resultSet.close();
            statement.close();
            
            connection.close();
            
        } catch (Exception e) {
            log.error("设置主库信息失败: {}", e.getMessage(), e);
        }
    }

    /**
     * 设置从库信息
     */
    private void setSlaveInfo(MySQLClusterInfo clusterInfo) {
        try {
            Connection connection = slaveDataSource.getConnection();
            
            // 获取从库状态
            String sql = "SHOW SLAVE STATUS";
            PreparedStatement statement = connection.prepareStatement(sql);
            ResultSet resultSet = statement.executeQuery();
            
            if (resultSet.next()) {
                String slaveIoRunning = resultSet.getString("Slave_IO_Running");
                String slaveSqlRunning = resultSet.getString("Slave_SQL_Running");
                
                if ("Yes".equals(slaveIoRunning) && "Yes".equals(slaveSqlRunning)) {
                    clusterInfo.setSlaveStatus("Running");
                } else {
                    clusterInfo.setSlaveStatus("Error");
                }
            }
            
            resultSet.close();
            statement.close();
            connection.close();
            
        } catch (Exception e) {
            log.error("设置从库信息失败: {}", e.getMessage(), e);
        }
    }

    /**
     * 计算集群统计信息
     */
    private void calculateClusterStatistics(MySQLClusterInfo clusterInfo) {
        try {
            Connection connection = masterDataSource.getConnection();
            
            // 获取查询统计
            String sql = "SHOW STATUS LIKE 'Queries'";
            PreparedStatement statement = connection.prepareStatement(sql);
            ResultSet resultSet = statement.executeQuery();
            
            if (resultSet.next()) {
                clusterInfo.setQueriesPerSecond(resultSet.getLong("Value"));
            }
            
            resultSet.close();
            statement.close();
            
            // 获取慢查询数
            sql = "SHOW STATUS LIKE 'Slow_queries'";
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();
            
            if (resultSet.next()) {
                clusterInfo.setSlowQueries(resultSet.getLong("Value"));
            }
            
            resultSet.close();
            statement.close();
            
            // 获取InnoDB缓冲池信息
            sql = "SHOW STATUS LIKE 'Innodb_buffer_pool_read_requests'";
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();
            
            if (resultSet.next()) {
                clusterInfo.setInnodbBufferPoolHit(resultSet.getLong("Value"));
            }
            
            resultSet.close();
            statement.close();
            
            sql = "SHOW STATUS LIKE 'Innodb_buffer_pool_reads'";
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();
            
            if (resultSet.next()) {
                clusterInfo.setInnodbBufferPoolMiss(resultSet.getLong("Value"));
            }
            
            resultSet.close();
            statement.close();
            
            // 计算缓冲池命中率
            long hits = clusterInfo.getInnodbBufferPoolHit();
            long misses = clusterInfo.getInnodbBufferPoolMiss();
            if (hits + misses > 0) {
                clusterInfo.setBufferPoolHitRate((double) hits / (hits + misses) * 100);
            }
            
            // 获取表锁等待数
            sql = "SHOW STATUS LIKE 'Table_locks_waited'";
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();
            
            if (resultSet.next()) {
                clusterInfo.setTableLocksWaited(resultSet.getLong("Value"));
            }
            
            resultSet.close();
            statement.close();
            
            // 获取死锁数
            sql = "SHOW STATUS LIKE 'Innodb_deadlocks'";
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();
            
            if (resultSet.next()) {
                clusterInfo.setDeadlocks(resultSet.getLong("Value"));
            }
            
            resultSet.close();
            statement.close();
            connection.close();
            
        } catch (Exception e) {
            log.error("计算集群统计信息失败: {}", e.getMessage(), e);
        }
    }

    /**
     * 确定集群状态
     */
    private String determineClusterStatus(MySQLClusterInfo clusterInfo) {
        String status = "Healthy";
        
        if (clusterInfo.getConnectionUsage() > 90) {
            status = "HighConnectionUsage";
        } else if (clusterInfo.getSlowQueries() > 100) {
            status = "HighSlowQueries";
        } else if (clusterInfo.getBufferPoolHitRate() < 95) {
            status = "LowBufferPoolHitRate";
        } else if (clusterInfo.getDeadlocks() > 10) {
            status = "HighDeadlocks";
        }
        
        return status;
    }

    /**
     * 创建集群监控数据
     */
    private MySQLClusterMonitor createClusterMonitorData(MySQLClusterInfo clusterInfo) {
        MySQLClusterMonitor monitorData = new MySQLClusterMonitor();
        
        // 设置基本信息
        monitorData.setClusterName(clusterInfo.getClusterName());
        monitorData.setHostname(getHostname());
        monitorData.setIp(getLocalIpAddress());
        monitorData.setCollectTime(new Date());
        monitorData.setCreateTime(new Date());
        
        // 设置集群信息
        monitorData.setInstanceCount(clusterInfo.getInstanceCount());
        monitorData.setDatabaseCount(clusterInfo.getDatabaseCount());
        monitorData.setTableCount(clusterInfo.getTableCount());
        
        // 设置连接信息
        monitorData.setTotalConnections(clusterInfo.getTotalConnections());
        monitorData.setActiveConnections(clusterInfo.getActiveConnections());
        monitorData.setMaxConnections(clusterInfo.getMaxConnections());
        monitorData.setConnectionUsage(clusterInfo.getConnectionUsage());
        
        // 设置性能信息
        monitorData.setQueriesPerSecond(clusterInfo.getQueriesPerSecond());
        monitorData.setSlowQueries(clusterInfo.getSlowQueries());
        monitorData.setAvgQueryTime(clusterInfo.getAvgQueryTime());
        
        // 设置缓冲池信息
        monitorData.setInnodbBufferPoolHit(clusterInfo.getInnodbBufferPoolHit());
        monitorData.setInnodbBufferPoolMiss(clusterInfo.getInnodbBufferPoolMiss());
        monitorData.setBufferPoolHitRate(clusterInfo.getBufferPoolHitRate());
        
        // 设置锁信息
        monitorData.setTableLocksWaited(clusterInfo.getTableLocksWaited());
        monitorData.setDeadlocks(clusterInfo.getDeadlocks());
        
        // 设置状态信息
        monitorData.setClusterStatus(clusterInfo.getClusterStatus());
        
        return monitorData;
    }

    /**
     * 采集实例信息
     */
    private void collectInstanceInfo() {
        try {
            // 采集主库实例信息
            collectMasterInstanceInfo();
            
            // 采集从库实例信息
            collectSlaveInstanceInfo();
            
        } catch (Exception e) {
            log.error("采集实例信息失败: {}", e.getMessage(), e);
        }
    }

    /**
     * 采集主库实例信息
     */
    private void collectMasterInstanceInfo() {
        try {
            Connection connection = masterDataSource.getConnection();
            
            // 创建实例监控数据
            MySQLInstanceMonitor instanceMonitor = createInstanceMonitorData(connection, "master");
            
            // 保存到数据库
            mysqlInstanceMonitorMapper.insert(instanceMonitor);
            
            // 更新缓存
            updateInstanceCache(instanceMonitor);
            
            // 检查实例告警
            checkInstanceAlert(instanceMonitor);
            
            connection.close();
            
        } catch (Exception e) {
            log.error("采集主库实例信息失败: {}", e.getMessage(), e);
        }
    }

    /**
     * 采集从库实例信息
     */
    private void collectSlaveInstanceInfo() {
        try {
            Connection connection = slaveDataSource.getConnection();
            
            // 创建实例监控数据
            MySQLInstanceMonitor instanceMonitor = createInstanceMonitorData(connection, "slave");
            
            // 保存到数据库
            mysqlInstanceMonitorMapper.insert(instanceMonitor);
            
            // 更新缓存
            updateInstanceCache(instanceMonitor);
            
            // 检查实例告警
            checkInstanceAlert(instanceMonitor);
            
            connection.close();
            
        } catch (Exception e) {
            log.error("采集从库实例信息失败: {}", e.getMessage(), e);
        }
    }

    /**
     * 创建实例监控数据
     */
    private MySQLInstanceMonitor createInstanceMonitorData(Connection connection, String role) throws SQLException {
        MySQLInstanceMonitor instanceMonitor = new MySQLInstanceMonitor();
        
        // 设置基本信息
        instanceMonitor.setClusterName("production-cluster");
        instanceMonitor.setInstanceId(role + "-" + System.currentTimeMillis());
        instanceMonitor.setRole(role);
        instanceMonitor.setCollectTime(new Date());
        instanceMonitor.setCreateTime(new Date());
        
        // 获取实例信息
        setInstanceInfo(instanceMonitor, connection);
        
        return instanceMonitor;
    }

    /**
     * 设置实例信息
     */
    private void setInstanceInfo(MySQLInstanceMonitor instanceMonitor, Connection connection) throws SQLException {
        // 获取版本信息
        String sql = "SELECT VERSION() as version";
        PreparedStatement statement = connection.prepareStatement(sql);
        ResultSet resultSet = statement.executeQuery();
        
        if (resultSet.next()) {
            instanceMonitor.setVersion(resultSet.getString("version"));
        }
        
        resultSet.close();
        statement.close();
        
        // 获取运行时间
        sql = "SHOW STATUS LIKE 'Uptime'";
        statement = connection.prepareStatement(sql);
        resultSet = statement.executeQuery();
        
        if (resultSet.next()) {
            instanceMonitor.setUptime(resultSet.getLong("Value"));
        }
        
        resultSet.close();
        statement.close();
        
        // 获取连接信息
        sql = "SHOW STATUS LIKE 'Threads_connected'";
        statement = connection.prepareStatement(sql);
        resultSet = statement.executeQuery();
        
        if (resultSet.next()) {
            instanceMonitor.setThreadsConnected(resultSet.getLong("Value"));
        }
        
        resultSet.close();
        statement.close();
        
        // 获取运行线程数
        sql = "SHOW STATUS LIKE 'Threads_running'";
        statement = connection.prepareStatement(sql);
        resultSet = statement.executeQuery();
        
        if (resultSet.next()) {
            instanceMonitor.setThreadsRunning(resultSet.getLong("Value"));
        }
        
        resultSet.close();
        statement.close();
        
        // 获取查询统计
        sql = "SHOW STATUS LIKE 'Queries'";
        statement = connection.prepareStatement(sql);
        resultSet = statement.executeQuery();
        
        if (resultSet.next()) {
            instanceMonitor.setQueries(resultSet.getLong("Value"));
        }
        
        resultSet.close();
        statement.close();
        
        // 获取慢查询数
        sql = "SHOW STATUS LIKE 'Slow_queries'";
        statement = connection.prepareStatement(sql);
        resultSet = statement.executeQuery();
        
        if (resultSet.next()) {
            instanceMonitor.setSlowQueries(resultSet.getLong("Value"));
        }
        
        resultSet.close();
        statement.close();
        
        // 获取网络统计
        sql = "SHOW STATUS LIKE 'Bytes_received'";
        statement = connection.prepareStatement(sql);
        resultSet = statement.executeQuery();
        
        if (resultSet.next()) {
            instanceMonitor.setBytesReceived(resultSet.getLong("Value"));
        }
        
        resultSet.close();
        statement.close();
        
        sql = "SHOW STATUS LIKE 'Bytes_sent'";
        statement = connection.prepareStatement(sql);
        resultSet = statement.executeQuery();
        
        if (resultSet.next()) {
            instanceMonitor.setBytesSent(resultSet.getLong("Value"));
        }
        
        resultSet.close();
        statement.close();
        
        // 获取InnoDB缓冲池信息
        sql = "SHOW VARIABLES LIKE 'innodb_buffer_pool_size'";
        statement = connection.prepareStatement(sql);
        resultSet = statement.executeQuery();
        
        if (resultSet.next()) {
            instanceMonitor.setInnodbBufferPoolSize(resultSet.getLong("Value"));
        }
        
        resultSet.close();
        statement.close();
        
        // 设置实例状态
        instanceMonitor.setInstanceStatus("Running");
    }

    /**
     * 更新集群缓存
     */
    private void updateClusterCache(MySQLClusterMonitor monitorData) {
        try {
            String cacheKey = "mysql:cluster:" + monitorData.getClusterName();
            redisTemplate.opsForValue().set(cacheKey, monitorData, Duration.ofMinutes(5));
            
        } catch (Exception e) {
            log.warn("更新集群缓存失败: {}", e.getMessage());
        }
    }

    /**
     * 更新实例缓存
     */
    private void updateInstanceCache(MySQLInstanceMonitor instanceMonitor) {
        try {
            String cacheKey = "mysql:instance:" + instanceMonitor.getInstanceId();
            redisTemplate.opsForValue().set(cacheKey, instanceMonitor, Duration.ofMinutes(5));
            
        } catch (Exception e) {
            log.warn("更新实例缓存失败: {}", e.getMessage());
        }
    }

    /**
     * 检查集群告警
     */
    private void checkClusterAlert(MySQLClusterMonitor monitorData) {
        try {
            String alertType = null;
            String alertLevel = null;
            String alertMessage = null;
            
            // 检查连接使用率告警
            if (monitorData.getConnectionUsage() > 90) {
                alertType = "MYSQL_CONNECTION_HIGH";
                alertLevel = "CRITICAL";
                alertMessage = String.format("MySQL连接使用率过高: %.2f%%", monitorData.getConnectionUsage());
            } else if (monitorData.getConnectionUsage() > 80) {
                alertType = "MYSQL_CONNECTION_WARNING";
                alertLevel = "WARNING";
                alertMessage = String.format("MySQL连接使用率较高: %.2f%%", monitorData.getConnectionUsage());
            }
            
            // 检查慢查询告警
            if (monitorData.getSlowQueries() > 100) {
                alertType = "MYSQL_SLOW_QUERIES_HIGH";
                alertLevel = "WARNING";
                alertMessage = String.format("MySQL慢查询过多: %d", monitorData.getSlowQueries());
            }
            
            // 检查缓冲池命中率告警
            if (monitorData.getBufferPoolHitRate() < 95) {
                alertType = "MYSQL_BUFFER_POOL_LOW";
                alertLevel = "WARNING";
                alertMessage = String.format("MySQL缓冲池命中率过低: %.2f%%", monitorData.getBufferPoolHitRate());
            }
            
            // 发送告警
            if (alertType != null) {
                sendClusterAlert(monitorData, alertType, alertLevel, alertMessage);
            }
            
        } catch (Exception e) {
            log.error("检查集群告警失败: {}", e.getMessage(), e);
        }
    }

    /**
     * 检查实例告警
     */
    private void checkInstanceAlert(MySQLInstanceMonitor instanceMonitor) {
        try {
            String alertType = null;
            String alertLevel = null;
            String alertMessage = null;
            
            // 检查实例状态告警
            if (!"Running".equals(instanceMonitor.getInstanceStatus())) {
                alertType = "MYSQL_INSTANCE_DOWN";
                alertLevel = "CRITICAL";
                alertMessage = String.format("MySQL实例状态异常: %s", instanceMonitor.getInstanceId());
            }
            
            // 检查运行线程数告警
            if (instanceMonitor.getThreadsRunning() > 50) {
                alertType = "MYSQL_THREADS_HIGH";
                alertLevel = "WARNING";
                alertMessage = String.format("MySQL运行线程数过多: %d", instanceMonitor.getThreadsRunning());
            }
            
            // 发送告警
            if (alertType != null) {
                sendInstanceAlert(instanceMonitor, alertType, alertLevel, alertMessage);
            }
            
        } catch (Exception e) {
            log.error("检查实例告警失败: {}", e.getMessage(), e);
        }
    }

    /**
     * 发送集群告警
     */
    private void sendClusterAlert(MySQLClusterMonitor monitorData, String alertType, String alertLevel, String alertMessage) {
        try {
            String alertKey = "mysql:cluster:alert:" + monitorData.getClusterName() + ":" + alertType;
            Boolean hasAlert = redisTemplate.hasKey(alertKey);
            
            if (hasAlert == null || !hasAlert) {
                AlertMessage alert = new AlertMessage();
                alert.setType(alertType);
                alert.setLevel(alertLevel);
                alert.setMessage(alertMessage);
                alert.setTimestamp(new Date());
                alert.setHostname(monitorData.getHostname());
                
                alertService.sendAlert(alert);
                
                redisTemplate.opsForValue().set(alertKey, "1", Duration.ofMinutes(5));
                
                log.warn("发送集群告警: clusterName={}, type={}, level={}", 
                        monitorData.getClusterName(), alertType, alertLevel);
            }
            
        } catch (Exception e) {
            log.error("发送集群告警失败: {}", e.getMessage(), e);
        }
    }

    /**
     * 发送实例告警
     */
    private void sendInstanceAlert(MySQLInstanceMonitor instanceMonitor, String alertType, String alertLevel, String alertMessage) {
        try {
            String alertKey = "mysql:instance:alert:" + instanceMonitor.getInstanceId() + ":" + alertType;
            Boolean hasAlert = redisTemplate.hasKey(alertKey);
            
            if (hasAlert == null || !hasAlert) {
                AlertMessage alert = new AlertMessage();
                alert.setType(alertType);
                alert.setLevel(alertLevel);
                alert.setMessage(alertMessage);
                alert.setTimestamp(new Date());
                alert.setHostname(instanceMonitor.getHost());
                
                alertService.sendAlert(alert);
                
                redisTemplate.opsForValue().set(alertKey, "1", Duration.ofMinutes(5));
                
                log.warn("发送实例告警: instanceId={}, type={}, level={}", 
                        instanceMonitor.getInstanceId(), alertType, alertLevel);
            }
            
        } catch (Exception e) {
            log.error("发送实例告警失败: {}", e.getMessage(), e);
        }
    }

    /**
     * 获取实时集群数据
     */
    public MySQLClusterMonitor getRealTimeClusterData(String clusterName) {
        String cacheKey = "mysql:cluster:" + clusterName;
        return (MySQLClusterMonitor) redisTemplate.opsForValue().get(cacheKey);
    }

    /**
     * 获取实时实例数据
     */
    public MySQLInstanceMonitor getRealTimeInstanceData(String instanceId) {
        String cacheKey = "mysql:instance:" + instanceId;
        return (MySQLInstanceMonitor) redisTemplate.opsForValue().get(cacheKey);
    }

    /**
     * 获取主机名
     */
    private String getHostname() {
        try {
            return InetAddress.getLocalHost().getHostName();
        } catch (UnknownHostException e) {
            return "unknown";
        }
    }

    /**
     * 获取本地IP地址
     */
    private String getLocalIpAddress() {
        try {
            return InetAddress.getLocalHost().getHostAddress();
        } catch (UnknownHostException e) {
            return "127.0.0.1";
        }
    }
}

/**
 * MySQL集群信息实体类
 */
@Data
public class MySQLClusterInfo {
    private String clusterName;          // 集群名称
    private Integer instanceCount;       // 实例数量
    private Integer databaseCount;       // 数据库数量
    private Integer tableCount;          // 表数量
    private Long totalConnections;       // 总连接数
    private Long activeConnections;      // 活跃连接数
    private Long maxConnections;         // 最大连接数
    private Double connectionUsage;      // 连接使用率
    private Long queriesPerSecond;       // 每秒查询数
    private Long slowQueries;            // 慢查询数
    private Double avgQueryTime;         // 平均查询时间
    private Long innodbBufferPoolHit;    // InnoDB缓冲池命中数
    private Long innodbBufferPoolMiss;  // InnoDB缓冲池未命中数
    private Double bufferPoolHitRate;    // 缓冲池命中率
    private Long tableLocksWaited;       // 表锁等待数
    private Long deadlocks;              // 死锁数
    private String slaveStatus;          // 从库状态
    private String clusterStatus;         // 集群状态
}

4. MySQL管理服务

4.1 MySQL管理服务

/**
 * MySQL管理服务
 * 提供MySQL集群管理功能
 */
@Service
public class MySQLManagementService {

    @Autowired
    private DataSource masterDataSource;
    
    @Autowired
    private DataSource slaveDataSource;
    
    @Autowired
    private AlertService alertService;

    /**
     * 创建数据库
     */
    public void createDatabase(String databaseName) {
        try {
            Connection connection = masterDataSource.getConnection();
            String sql = String.format("CREATE DATABASE IF NOT EXISTS %s", databaseName);
            
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.execute();
            
            statement.close();
            connection.close();
            
            log.info("创建数据库成功: databaseName={}", databaseName);
            
        } catch (Exception e) {
            log.error("创建数据库失败: databaseName={}, error={}", databaseName, e.getMessage(), e);
        }
    }

    /**
     * 删除数据库
     */
    public void dropDatabase(String databaseName) {
        try {
            Connection connection = masterDataSource.getConnection();
            String sql = String.format("DROP DATABASE IF EXISTS %s", databaseName);
            
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.execute();
            
            statement.close();
            connection.close();
            
            log.info("删除数据库成功: databaseName={}", databaseName);
            
        } catch (Exception e) {
            log.error("删除数据库失败: databaseName={}, error={}", databaseName, e.getMessage(), e);
        }
    }

    /**
     * 创建表
     */
    public void createTable(String databaseName, String tableName, String columns) {
        try {
            Connection connection = masterDataSource.getConnection();
            String sql = String.format(
                "CREATE TABLE IF NOT EXISTS %s.%s (%s) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
                databaseName, tableName, columns
            );
            
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.execute();
            
            statement.close();
            connection.close();
            
            log.info("创建表成功: databaseName={}, tableName={}", databaseName, tableName);
            
        } catch (Exception e) {
            log.error("创建表失败: databaseName={}, tableName={}, error={}", 
                    databaseName, tableName, e.getMessage(), e);
        }
    }

    /**
     * 删除表
     */
    public void dropTable(String databaseName, String tableName) {
        try {
            Connection connection = masterDataSource.getConnection();
            String sql = String.format("DROP TABLE IF EXISTS %s.%s", databaseName, tableName);
            
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.execute();
            
            statement.close();
            connection.close();
            
            log.info("删除表成功: databaseName={}, tableName={}", databaseName, tableName);
            
        } catch (Exception e) {
            log.error("删除表失败: databaseName={}, tableName={}, error={}", 
                    databaseName, tableName, e.getMessage(), e);
        }
    }

    /**
     * 创建索引
     */
    public void createIndex(String databaseName, String tableName, String indexName, String columns) {
        try {
            Connection connection = masterDataSource.getConnection();
            String sql = String.format(
                "CREATE INDEX %s ON %s.%s (%s)",
                indexName, databaseName, tableName, columns
            );
            
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.execute();
            
            statement.close();
            connection.close();
            
            log.info("创建索引成功: databaseName={}, tableName={}, indexName={}", 
                    databaseName, tableName, indexName);
            
        } catch (Exception e) {
            log.error("创建索引失败: databaseName={}, tableName={}, indexName={}, error={}", 
                    databaseName, tableName, indexName, e.getMessage(), e);
        }
    }

    /**
     * 删除索引
     */
    public void dropIndex(String databaseName, String tableName, String indexName) {
        try {
            Connection connection = masterDataSource.getConnection();
            String sql = String.format("DROP INDEX %s ON %s.%s", indexName, databaseName, tableName);
            
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.execute();
            
            statement.close();
            connection.close();
            
            log.info("删除索引成功: databaseName={}, tableName={}, indexName={}", 
                    databaseName, tableName, indexName);
            
        } catch (Exception e) {
            log.error("删除索引失败: databaseName={}, tableName={}, indexName={}, error={}", 
                    databaseName, tableName, indexName, e.getMessage(), e);
        }
    }

    /**
     * 优化表
     */
    public void optimizeTable(String databaseName, String tableName) {
        try {
            Connection connection = masterDataSource.getConnection();
            String sql = String.format("OPTIMIZE TABLE %s.%s", databaseName, tableName);
            
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.execute();
            
            statement.close();
            connection.close();
            
            log.info("优化表成功: databaseName={}, tableName={}", databaseName, tableName);
            
        } catch (Exception e) {
            log.error("优化表失败: databaseName={}, tableName={}, error={}", 
                    databaseName, tableName, e.getMessage(), e);
        }
    }

    /**
     * 分析表
     */
    public void analyzeTable(String databaseName, String tableName) {
        try {
            Connection connection = masterDataSource.getConnection();
            String sql = String.format("ANALYZE TABLE %s.%s", databaseName, tableName);
            
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.execute();
            
            statement.close();
            connection.close();
            
            log.info("分析表成功: databaseName={}, tableName={}", databaseName, tableName);
            
        } catch (Exception e) {
            log.error("分析表失败: databaseName={}, tableName={}, error={}", 
                    databaseName, tableName, e.getMessage(), e);
        }
    }

    /**
     * 获取数据库列表
     */
    public List<String> getDatabases() {
        try {
            Connection connection = masterDataSource.getConnection();
            String sql = "SHOW DATABASES";
            
            PreparedStatement statement = connection.prepareStatement(sql);
            ResultSet resultSet = statement.executeQuery();
            
            List<String> databases = new ArrayList<>();
            while (resultSet.next()) {
                String dbName = resultSet.getString(1);
                if (!dbName.matches("(information_schema|performance_schema|mysql|sys)")) {
                    databases.add(dbName);
                }
            }
            
            resultSet.close();
            statement.close();
            connection.close();
            
            return databases;
            
        } catch (Exception e) {
            log.error("获取数据库列表失败: {}", e.getMessage(), e);
            return new ArrayList<>();
        }
    }

    /**
     * 获取表列表
     */
    public List<String> getTables(String databaseName) {
        try {
            Connection connection = masterDataSource.getConnection();
            String sql = String.format("SHOW TABLES FROM %s", databaseName);
            
            PreparedStatement statement = connection.prepareStatement(sql);
            ResultSet resultSet = statement.executeQuery();
            
            List<String> tables = new ArrayList<>();
            while (resultSet.next()) {
                tables.add(resultSet.getString(1));
            }
            
            resultSet.close();
            statement.close();
            connection.close();
            
            return tables;
            
        } catch (Exception e) {
            log.error("获取表列表失败: databaseName={}, error={}", databaseName, e.getMessage(), e);
            return new ArrayList<>();
        }
    }

    /**
     * 获取慢查询
     */
    public List<Map<String, Object>> getSlowQueries() {
        try {
            Connection connection = masterDataSource.getConnection();
            String sql = "SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 100";
            
            PreparedStatement statement = connection.prepareStatement(sql);
            ResultSet resultSet = statement.executeQuery();
            
            List<Map<String, Object>> slowQueries = new ArrayList<>();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            
            while (resultSet.next()) {
                Map<String, Object> row = new HashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnName(i);
                    Object value = resultSet.getObject(i);
                    row.put(columnName, value);
                }
                slowQueries.add(row);
            }
            
            resultSet.close();
            statement.close();
            connection.close();
            
            return slowQueries;
            
        } catch (Exception e) {
            log.error("获取慢查询失败: {}", e.getMessage(), e);
            return new ArrayList<>();
        }
    }

    /**
     * 执行查询
     */
    public ResultSet executeQuery(String sql) {
        try {
            Connection connection = masterDataSource.getConnection();
            PreparedStatement statement = connection.prepareStatement(sql);
            return statement.executeQuery();
            
        } catch (Exception e) {
            log.error("执行查询失败: sql={}, error={}", sql, e.getMessage(), e);
            return null;
        }
    }

    /**
     * 获取查询执行计划
     */
    public String getQueryPlan(String sql) {
        try {
            Connection connection = masterDataSource.getConnection();
            String explainSql = "EXPLAIN " + sql;
            
            PreparedStatement statement = connection.prepareStatement(explainSql);
            ResultSet resultSet = statement.executeQuery();
            
            StringBuilder plan = new StringBuilder();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            
            // 添加表头
            for (int i = 1; i <= columnCount; i++) {
                plan.append(metaData.getColumnName(i)).append("\t");
            }
            plan.append("\n");
            
            // 添加数据行
            while (resultSet.next()) {
                for (int i = 1; i <= columnCount; i++) {
                    plan.append(resultSet.getString(i)).append("\t");
                }
                plan.append("\n");
            }
            
            resultSet.close();
            statement.close();
            connection.close();
            
            return plan.toString();
            
        } catch (Exception e) {
            log.error("获取查询执行计划失败: sql={}, error={}", sql, e.getMessage(), e);
            return "";
        }
    }
}

5. 总结

本文详细介绍了MySQL数据库运维监控与管理的完整解决方案，包括：

5.1 核心技术点

集群监控: 实时监控MySQL集群和实例状态
性能监控: 监控查询性能、连接数、缓冲池命中率
主从复制: 管理主从复制状态和故障切换
索引优化: 管理索引设计和查询优化
告警通知: 多级告警、智能通知

5.2 架构优势

实时监控: 10秒间隔的实时MySQL数据采集
智能告警: 基于阈值的智能告警机制
自动化管理: 自动化的数据库和表管理
多维度分析: 集群、实例、性能等多维度分析

5.3 最佳实践

监控策略: 设置合理的MySQL监控阈值
管理策略: 根据业务需求执行数据管理
性能优化: 合理配置MySQL参数
预防措施: 提前预防MySQL集群问题

通过以上架构设计，可以构建完善的MySQL运维监控系统，实现MySQL集群的有效管理和优化。