1. 为什么MySQL监控与高可用方案如此重要在当今互联网应用中数据库作为核心数据存储组件其稳定性直接影响业务连续性。我曾经历过一次惨痛的线上事故——某电商平台大促期间MySQL主库突然宕机由于缺乏有效监控和容灾方案导致整个交易系统瘫痪近2小时直接损失超过百万。这次教训让我深刻认识到完善的数据库监控体系和高可用架构不是可选项而是保障业务稳定的生命线。MySQL作为最流行的开源关系型数据库在企业级应用中承担着关键角色。但许多团队往往只关注业务功能开发忽视了数据库层面的健壮性建设。实际上一个完整的MySQL高可用方案需要覆盖以下核心维度实时性能监控QPS、连接数、慢查询等异常自动告警宕机、主从延迟、空间不足等故障自动转移主从切换、读写分离应用层容错连接池管理、降级策略2. 构建基础监控体系从Shell脚本到Prometheus2.1 传统Shell监控脚本的实战编写在搭建复杂监控系统前我们先从最基础的Shell脚本监控开始。以下是我在多个生产环境中验证过的核心监控脚本#!/bin/bash # mysql_monitor.sh # 监控指标采集 CONNECTION_COUNT$(mysql -uroot -p$PASSWORD -e SHOW STATUS LIKE Threads_connected | awk NR2{print $2}) SLOW_QUERIES$(mysql -uroot -p$PASSWORD -e SHOW STATUS LIKE Slow_queries | awk NR2{print $2}) QPS$(mysql -uroot -p$PASSWORD -e SHOW GLOBAL STATUS LIKE Questions | awk NR2{print $2}) # 阈值判断 if [ $CONNECTION_COUNT -gt 500 ]; then echo $(date) - 警告数据库连接数过高 ($CONNECTION_COUNT) /var/log/mysql_monitor.log # 可添加邮件/短信告警逻辑 fi # 数据持久化 echo $(date),$CONNECTION_COUNT,$SLOW_QUERIES,$QPS /var/log/mysql_metrics.csv关键改进点说明使用环境变量存储密码避免脚本中硬编码增加时间戳记录便于问题回溯输出标准化CSV格式方便后续处理通过awk精确提取指标值NR2处理标题行实际部署时建议通过crontab每分钟执行* * * * * /path/to/mysql_monitor.sh2.2 现代监控方案PrometheusGrafana集成对于生产环境我推荐使用Prometheus生态体系。以下是具体实施步骤部署MySQL Exporterdocker run -d \ -p 9104:9104 \ -e DATA_SOURCE_NAMEroot:password(mysql-host:3306)/ \ prom/mysqld-exporterPrometheus配置示例scrape_configs: - job_name: mysql static_configs: - targets: [mysql-exporter:9104] metrics_path: /metricsGrafana仪表板关键指标连接数监控mysql_global_status_threads_connectedQPS计算rate(mysql_global_status_questions[1m])慢查询趋势mysql_global_status_slow_queries性能数据采集对比表监控方式部署复杂度实时性历史数据分析告警能力Shell脚本低分钟级需额外处理基础Prometheus中秒级内置强大3. MySQL高可用架构设计与实现3.1 主从复制配置实战基础的主从配置往往隐藏着许多坑以下是我的最佳实践主库配置my.cnf[mysqld] server-id 1 log_bin mysql-bin binlog_format ROW binlog_row_image FULL sync_binlog 1从库配置关键点CHANGE MASTER TO MASTER_HOSTmaster_host, MASTER_USERrepl_user, MASTER_PASSWORDrepl_password, MASTER_AUTO_POSITION 1;常见问题处理经验主从延迟遇到大事务时建议设置slave_parallel_workers4根据CPU核数调整数据不一致定期使用pt-table-checksum工具校验连接闪断配置master_retry_count86400和master_connect_retry603.2 基于MHA的自动故障转移对于关键业务系统我推荐使用MHAMaster High Availability方案典型架构拓扑主库 - 从库1候选主库 - 从库2 - MHA管理节点核心配置步骤安装Perl依赖包yum install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch配置manager节点[server default] manager_workdir/var/log/mha manager_log/var/log/mha/manager.log ssh_userroot repl_userrepl repl_passwordrepl123 ping_interval3 [server1] hostnamemaster_host candidate_master1 [server2] hostnameslave1_host candidate_master1启动故障检测masterha_manager --conf/etc/mha.cnf故障切换过程实测数据场景平均切换时间数据丢失风险手动切换5-10分钟高MHA自动切换10-30秒低配合半同步4. Spring Boot应用层容错设计4.1 连接池优化配置在application.yml中的关键参数配置spring: datasource: hikari: maximum-pool-size: 20 minimum-idle: 5 idle-timeout: 30000 max-lifetime: 1800000 connection-timeout: 30000 leak-detection-threshold: 5000参数调优经验max-lifetime应小于数据库的wait_timeout生产环境leak-detection-threshold建议设置在5-10秒突发流量场景可启用动态扩容Scheduled(fixedRate 5000) public void adjustPool() { int active hikariPool.getActiveConnections(); if (active 15) { // 阈值根据实际调整 hikariPool.setMaximumPoolSize(30); } }4.2 多数据源故障转移实现通过AbstractRoutingDataSource实现自动切换public class MasterSlaveRoutingDataSource extends AbstractRoutingDataSource { private static final ThreadLocalBoolean isReadOnly new ThreadLocal(); Override protected Object determineCurrentLookupKey() { return isReadOnly.get() ? slave : master; } public static void setReadOnly(boolean readOnly) { isReadOnly.set(readOnly); } }结合AOP的自动路由Around(annotation(readOnly)) public Object routeToSlave(ProceedingJoinPoint jp, ReadOnly readOnly) throws Throwable { try { MasterSlaveRoutingDataSource.setReadOnly(true); return jp.proceed(); } finally { MasterSlaveRoutingDataSource.setReadOnly(false); } }4.3 熔断降级策略集成使用Resilience4j实现数据库操作熔断Bean public CircuitBreakerConfig circuitBreakerConfig() { return CircuitBreakerConfig.custom() .failureRateThreshold(50) .waitDurationInOpenState(Duration.ofSeconds(30)) .slidingWindowType(SlidingWindowType.COUNT_BASED) .slidingWindowSize(10) .build(); } CircuitBreaker(name mysqlOperations, fallbackMethod fallbackQuery) public ListUser queryUsers() { // 数据库查询逻辑 }降级策略实测效果请求量正常响应时间熔断后响应时间错误率1000 QPS50ms10ms缓存1%5000 QPS300ms15ms0.5%5. 生产环境中的血泪教训在金融级系统中我们曾遇到过一个隐蔽的故障主从切换后应用出现间歇性连接失败。经过72小时排查最终发现是TCP连接复用导致的问题。解决方案是在连接池配置中添加spring: datasource: hikari: connection-init-sql: SET SESSION wait_timeout28800另一个经典案例是某次大促期间监控系统漏报了磁盘空间告警。现在我们采用多级预警机制80%空间使用率企业微信通知90%空间使用率短信电话告警95%空间使用率自动触发只读模式对于慢查询监控建议不仅要关注执行时间还要监控扫描行数。我们使用以下SQL定期分析SELECT digest_text, count_star, avg_timer_wait/1000000000 as avg_sec, sum_rows_examined/count_star as avg_rows FROM performance_schema.events_statements_summary_by_digest ORDER BY sum_rows_examined DESC LIMIT 10; 
长河君 · 2026/10/28 10:47:13 · 阅读 1.2k MySQL监控与高可用架构实战指南
1. 为什么MySQL监控与高可用方案如此重要在当今互联网应用中数据库作为核心数据存储组件其稳定性直接影响业务连续性。我曾经历过一次惨痛的线上事故——某电商平台大促期间MySQL主库突然宕机由于缺乏有效监控和容灾方案导致整个交易系统瘫痪近2小时直接损失超过百万。这次教训让我深刻认识到完善的数据库监控体系和高可用架构不是可选项而是保障业务稳定的生命线。MySQL作为最流行的开源关系型数据库在企业级应用中承担着关键角色。但许多团队往往只关注业务功能开发忽视了数据库层面的健壮性建设。实际上一个完整的MySQL高可用方案需要覆盖以下核心维度实时性能监控QPS、连接数、慢查询等异常自动告警宕机、主从延迟、空间不足等故障自动转移主从切换、读写分离应用层容错连接池管理、降级策略2. 构建基础监控体系从Shell脚本到Prometheus2.1 传统Shell监控脚本的实战编写在搭建复杂监控系统前我们先从最基础的Shell脚本监控开始。以下是我在多个生产环境中验证过的核心监控脚本#!/bin/bash # mysql_monitor.sh # 监控指标采集 CONNECTION_COUNT$(mysql -uroot -p$PASSWORD -e SHOW STATUS LIKE Threads_connected | awk NR2{print $2}) SLOW_QUERIES$(mysql -uroot -p$PASSWORD -e SHOW STATUS LIKE Slow_queries | awk NR2{print $2}) QPS$(mysql -uroot -p$PASSWORD -e SHOW GLOBAL STATUS LIKE Questions | awk NR2{print $2}) # 阈值判断 if [ $CONNECTION_COUNT -gt 500 ]; then echo $(date) - 警告数据库连接数过高 ($CONNECTION_COUNT) /var/log/mysql_monitor.log # 可添加邮件/短信告警逻辑 fi # 数据持久化 echo $(date),$CONNECTION_COUNT,$SLOW_QUERIES,$QPS /var/log/mysql_metrics.csv关键改进点说明使用环境变量存储密码避免脚本中硬编码增加时间戳记录便于问题回溯输出标准化CSV格式方便后续处理通过awk精确提取指标值NR2处理标题行实际部署时建议通过crontab每分钟执行* * * * * /path/to/mysql_monitor.sh2.2 现代监控方案PrometheusGrafana集成对于生产环境我推荐使用Prometheus生态体系。以下是具体实施步骤部署MySQL Exporterdocker run -d \ -p 9104:9104 \ -e DATA_SOURCE_NAMEroot:password(mysql-host:3306)/ \ prom/mysqld-exporterPrometheus配置示例scrape_configs: - job_name: mysql static_configs: - targets: [mysql-exporter:9104] metrics_path: /metricsGrafana仪表板关键指标连接数监控mysql_global_status_threads_connectedQPS计算rate(mysql_global_status_questions[1m])慢查询趋势mysql_global_status_slow_queries性能数据采集对比表监控方式部署复杂度实时性历史数据分析告警能力Shell脚本低分钟级需额外处理基础Prometheus中秒级内置强大3. MySQL高可用架构设计与实现3.1 主从复制配置实战基础的主从配置往往隐藏着许多坑以下是我的最佳实践主库配置my.cnf[mysqld] server-id 1 log_bin mysql-bin binlog_format ROW binlog_row_image FULL sync_binlog 1从库配置关键点CHANGE MASTER TO MASTER_HOSTmaster_host, MASTER_USERrepl_user, MASTER_PASSWORDrepl_password, MASTER_AUTO_POSITION 1;常见问题处理经验主从延迟遇到大事务时建议设置slave_parallel_workers4根据CPU核数调整数据不一致定期使用pt-table-checksum工具校验连接闪断配置master_retry_count86400和master_connect_retry603.2 基于MHA的自动故障转移对于关键业务系统我推荐使用MHAMaster High Availability方案典型架构拓扑主库 - 从库1候选主库 - 从库2 - MHA管理节点核心配置步骤安装Perl依赖包yum install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch配置manager节点[server default] manager_workdir/var/log/mha manager_log/var/log/mha/manager.log ssh_userroot repl_userrepl repl_passwordrepl123 ping_interval3 [server1] hostnamemaster_host candidate_master1 [server2] hostnameslave1_host candidate_master1启动故障检测masterha_manager --conf/etc/mha.cnf故障切换过程实测数据场景平均切换时间数据丢失风险手动切换5-10分钟高MHA自动切换10-30秒低配合半同步4. Spring Boot应用层容错设计4.1 连接池优化配置在application.yml中的关键参数配置spring: datasource: hikari: maximum-pool-size: 20 minimum-idle: 5 idle-timeout: 30000 max-lifetime: 1800000 connection-timeout: 30000 leak-detection-threshold: 5000参数调优经验max-lifetime应小于数据库的wait_timeout生产环境leak-detection-threshold建议设置在5-10秒突发流量场景可启用动态扩容Scheduled(fixedRate 5000) public void adjustPool() { int active hikariPool.getActiveConnections(); if (active 15) { // 阈值根据实际调整 hikariPool.setMaximumPoolSize(30); } }4.2 多数据源故障转移实现通过AbstractRoutingDataSource实现自动切换public class MasterSlaveRoutingDataSource extends AbstractRoutingDataSource { private static final ThreadLocalBoolean isReadOnly new ThreadLocal(); Override protected Object determineCurrentLookupKey() { return isReadOnly.get() ? slave : master; } public static void setReadOnly(boolean readOnly) { isReadOnly.set(readOnly); } }结合AOP的自动路由Around(annotation(readOnly)) public Object routeToSlave(ProceedingJoinPoint jp, ReadOnly readOnly) throws Throwable { try { MasterSlaveRoutingDataSource.setReadOnly(true); return jp.proceed(); } finally { MasterSlaveRoutingDataSource.setReadOnly(false); } }4.3 熔断降级策略集成使用Resilience4j实现数据库操作熔断Bean public CircuitBreakerConfig circuitBreakerConfig() { return CircuitBreakerConfig.custom() .failureRateThreshold(50) .waitDurationInOpenState(Duration.ofSeconds(30)) .slidingWindowType(SlidingWindowType.COUNT_BASED) .slidingWindowSize(10) .build(); } CircuitBreaker(name mysqlOperations, fallbackMethod fallbackQuery) public ListUser queryUsers() { // 数据库查询逻辑 }降级策略实测效果请求量正常响应时间熔断后响应时间错误率1000 QPS50ms10ms缓存1%5000 QPS300ms15ms0.5%5. 生产环境中的血泪教训在金融级系统中我们曾遇到过一个隐蔽的故障主从切换后应用出现间歇性连接失败。经过72小时排查最终发现是TCP连接复用导致的问题。解决方案是在连接池配置中添加spring: datasource: hikari: connection-init-sql: SET SESSION wait_timeout28800另一个经典案例是某次大促期间监控系统漏报了磁盘空间告警。现在我们采用多级预警机制80%空间使用率企业微信通知90%空间使用率短信电话告警95%空间使用率自动触发只读模式对于慢查询监控建议不仅要关注执行时间还要监控扫描行数。我们使用以下SQL定期分析SELECT digest_text, count_star, avg_timer_wait/1000000000 as avg_sec, sum_rows_examined/count_star as avg_rows FROM performance_schema.events_statements_summary_by_digest ORDER BY sum_rows_examined DESC LIMIT 10;