跳至主要內容

MySQL 高可用方案

SunnyFan大约 13 分钟约 3928 字

MySQL 高可用方案

简介

MySQL 高可用方案是保障数据库服务持续稳定运行的关键技术体系。在生产环境中，单点故障可能导致整个业务系统不可用，因此需要通过主从复制、自动故障转移、读写分离等机制来构建高可用的 MySQL 架构。本文将详细介绍常见的 MySQL 高可用方案，包括主从复制、MHA、MySQL InnoDB Cluster 以及读写分离的配置与实践。

特点

主从复制

MySQL 主从复制是高可用架构的基础，通过 binlog 将主库的数据变更同步到从库。

配置主库

# my.cnf 主库配置
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW
binlog-cache-size = 1M
max-binlog-size = 500M
expire-logs-days = 7
sync-binlog = 1
innodb-flush-log-at-trx-commit = 1

# 半同步复制插件
plugin-load = "rpl_semi_sync_master=semisync_master.so"
rpl-semi-sync-master-enabled = 1
rpl-semi-sync-master-timeout = 3000

配置从库

# my.cnf 从库配置
[mysqld]
server-id = 2
relay-log = relay-bin
read-only = 1
log-slave-updates = 1
slave-parallel-workers = 4
slave-parallel-type = LOGICAL_CLOCK
slave-preserve-commit-order = 1

# 半同步复制从库插件
plugin-load = "rpl_semi_sync_slave=semisync_slave.so"
rpl-semi-sync-slave-enabled = 1

创建复制账号并启动复制

-- 在主库上创建复制用户
CREATE USER 'repl_user'@'%' IDENTIFIED BY 'StrongPassword123!';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
FLUSH PRIVILEGES;

-- 在从库上配置主库连接信息
CHANGE MASTER TO
    MASTER_HOST = '192.168.1.100',
    MASTER_PORT = 3306,
    MASTER_USER = 'repl_user',
    MASTER_PASSWORD = 'StrongPassword123!',
    MASTER_AUTO_POSITION = 1;

-- 启动复制并检查状态
START SLAVE;
SHOW SLAVE STATUS\G

常用复制状态监控

状态变量说明期望值
Slave_IO_RunningIO 线程状态Yes
Slave_SQL_RunningSQL 线程状态Yes
Seconds_Behind_Master复制延迟（秒）0 或接近 0
Last_Error最近错误信息空
Retrieved_Gtid_Set已接收的 GTID 集合持续增长

MHA 高可用方案

MHA（Master High Availability）是一套优秀的 MySQL 故障切换和主从提升软件，能在 10-30 秒内完成故障转移。

安装与配置 MHA Manager

# /etc/mha/app1.cnf MHA 配置文件
[server default]
manager_workdir=/var/log/mha/app1
manager_log=/var/log/mha/app1/manager.log
user=mha_monitor
password=MhaPassword123!
repl_user=repl_user
repl_password=StrongPassword123!
ssh_user=root
ping_interval=3
secondary_check_script=/usr/local/bin/masterha_secondary_check -s slave1 -s slave2
master_ip_failover_script=/usr/local/bin/master_ip_failover
shutdown_script=/usr/local/bin/power_manager
report_script=/usr/local/bin/send_report

[server1]
hostname=192.168.1.100
port=3306
candidate_master=1

[server2]
hostname=192.168.1.101
port=3306
candidate_master=1
check_repl_delay=0

[server3]
hostname=192.168.1.102
port=3306
no_master=1

MHA 管理命令

# 检查 SSH 连接
masterha_check_ssh --conf=/etc/mha/app1.cnf

# 检查复制状态
masterha_check_repl --conf=/etc/mha/app1.cnf

# 启动 MHA Manager
nohup masterha_manager --conf=/etc/mha/app1.cnf &

# 检查 MHA 状态
masterha_check_status --conf=/etc/mha/app1.cnk

# 手动故障切换
masterha_master_switch --conf=/etc/mha/app1.cnf --master_state=dead --interactive=0

MySQL InnoDB Cluster

MySQL InnoDB Cluster 是 MySQL 官方提供的高可用方案，基于 Group Replication、MySQL Router 和 MySQL Shell 构建。

使用 MySQL Shell 创建集群

// 连接到 MySQL 实例
shell.connect('root@192.168.1.100:3306')

// 检查实例配置
dba.checkInstanceConfiguration('root@192.168.1.100:3306')
dba.checkInstanceConfiguration('root@192.168.1.101:3306')
dba.checkInstanceConfiguration('root@168.1.102:3306')

// 创建集群
var cluster = dba.createCluster('prodCluster')

// 添加实例到集群
cluster.addInstance('root@192.168.1.101:3306')
cluster.addInstance('root@192.168.1.102:3306')

// 查看集群状态
cluster.status()

MySQL Router 配置

# mysqlrouter.conf
[DEFAULT]
logging_folder = /var/log/mysqlrouter/
runtime_folder = /var/run/mysqlrouter/
config_folder = /etc/mysqlrouter/

[routing:prod_rw]
bind_address = 0.0.0.0
bind_port = 6446
destinations = metadata-cache://prodCluster?role=PRIMARY
routing_strategy = first-available
protocol = classic

[routing:prod_ro]
bind_address = 0.0.0.0
bind_port = 6447
destinations = metadata-cache://prodCluster?role=SECONDARY
routing_strategy = round-robin-with-fallback
protocol = classic

[routing:prod_x_rw]
bind_address = 0.0.0.0
bind_port = 64460
destinations = metadata-cache://prodCluster?role=PRIMARY
routing_strategy = first-available
protocol = x

[metadata_cache:prodCluster]
cluster_type = gr
ttl = 0.5

读写分离

读写分离通过将读请求分发到从库，写请求路由到主库，有效提升系统整体吞吐量。

基于 ProxySQL 实现读写分离

-- 配置 MySQL 后端服务器
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight)
VALUES (10, '192.168.1.100', 3306, 1);

INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight)
VALUES (20, '192.168.1.101', 3306, 1),
       (20, '192.168.1.102', 3306, 1);

-- 配置读写分离规则
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (1, 1, '^SELECT.*FOR UPDATE$', 10, 1),
       (2, 1, '^SELECT', 20, 1);

-- 配置后端服务器健康检测
INSERT INTO mysql_replication_hostgroups (writer_hostgroup, reader_hostgroup, check_type)
VALUES (10, 20, 'read_only');

-- 加载配置到运行时
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

高可用方案对比

特性主从复制 + MHAInnoDB Cluster主从复制 + ProxySQL
自动故障转移支持支持支持
多主写入不支持可配置不支持
数据一致性半同步较好强一致（单主模式）半同步较好
运维复杂度中等较高中等
性能损耗低中等低
官方支持社区项目MySQL 官方社区项目
最小节点数2 主 + 1 管理节点3 节点2 节点

GTID 复制

GTID 原理与配置

-- GTID（Global Transaction Identifier）全局事务标识
-- 格式：source_id:transaction_id
-- 例如：3E11FA47-71CA-11E1-9E33-C80AA9429562:23

-- 主库配置
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW
gtid-mode = ON
enforce-gtid-consistency = ON
log-slave-updates = ON

-- 从库配置
[mysqld]
server-id = 2
relay-log = relay-bin
gtid-mode = ON
enforce-gtid-consistency = ON
log-slave-updates = ON
read-only = 1

-- 启动 GTID 复制
CHANGE MASTER TO
    MASTER_HOST = '192.168.1.100',
    MASTER_PORT = 3306,
    MASTER_USER = 'repl_user',
    MASTER_PASSWORD = 'StrongPassword123!',
    MASTER_AUTO_POSITION = 1;

START SLAVE;

-- 查看 GTID 执行状态
SHOW MASTER STATUS\G
-- Executed_Gtid_Set: 3E11FA47-71CA-11E1-9E33-C80AA9429562:1-1000

-- GTID 优势：
-- 1. 不需要指定 binlog 文件名和位置
-- 2. 搭建主从更简单
-- 3. 故障切换时不会漏事务
-- 4. 便于定位事务执行的源头

GTID 跳过错误事务

-- 当从库复制出错时，可以跳过特定 GTID
STOP SLAVE;

-- 查看出错的事务
SHOW SLAVE STATUS\G
-- Last_Error: Error ... on query. Default database: 'mydb'.

-- 注入空事务跳过
SET GTID_NEXT = '3E11FA47-71CA-11E1-9E33-C80AA9429562:1001';
BEGIN;
COMMIT;
SET GTID_NEXT = 'AUTOMATIC';

START SLAVE;

-- 批量跳过多个错误事务
-- 先记录需要跳过的 GTID 范围，然后逐个注入空事务

延迟复制与数据恢复

配置延迟从库

-- 延迟从库用于误操作恢复
CHANGE MASTER TO
    MASTER_DELAY = 3600;  -- 延迟 1 小时

-- 启动延迟复制
START SLAVE;

-- 查看延迟状态
SHOW SLAVE STATUS\G
-- SQL_Delay: 3600
-- SQL_Remaining_Delay: 2847

-- 延迟复制使用场景：
-- 1. 误 DELETE/UPDATE 后恢复数据
-- 2. 测试升级脚本的影响
-- 3. 历史数据查询

-- 紧急恢复步骤：
-- 1. STOP SLAVE; 停止延迟从库
-- 2. 记录当前 GTID 位置
-- 3. 恢复误操作前的数据
-- 4. 用 mysqlbinlog 提取误操作的反向 SQL
-- 5. 在主库执行恢复

复制拓扑设计

常见复制拓扑

方案一：一主两从（最常用）
  Master ──┬── Slave1（读）
            └── Slave2（读 + 备份）

  优点：简单，延迟低
  缺点：Master 是单点

方案二：级联复制
  Master ── Slave1 ── Slave2 ── Slave3

  优点：减轻 Master 复制压力
  缺点：延迟叠加，故障链长
  配置：log-slave-updates = 1

方案三：双主架构（Active-Passive）
  Master1 ←──→ Master2
  其中一个设为 read-only

  优点：快速故障切换
  缺点：需要处理自增 ID 冲突

方案四：环形复制
  Node1 → Node2 → Node3 → Node1

  优点：所有节点都可读写
  缺点：延迟高，故障难排查
  不推荐用于生产环境

双主自增 ID 配置

-- Master1 配置
[mysqld]
server-id = 1
auto-increment-increment = 2
auto-increment-offset = 1
log-bin = mysql-bin
binlog-format = ROW

-- Master2 配置
[mysqld]
server-id = 2
auto-increment-increment = 2
auto-increment-offset = 2
log-bin = mysql-bin
binlog-format = ROW

-- 结果：Master1 生成 1, 3, 5, 7...
--       Master2 生成 2, 4, 6, 8...
-- 避免 ID 冲突

Group Replication（组复制）

组复制原理

-- MySQL Group Replication 基于 Paxos 协议
-- 所有节点组成一个复制组，事务需组内多数节点同意

-- 配置组复制（所有节点）
[mysqld]
server-id = 1
gtid-mode = ON
enforce-gtid-consistency = ON
log-slave-updates = ON
binlog-format = ROW
binlog-checksum = NONE

# 组复制配置
plugin-load-add = group_replication.so
group-replication-group-name = 'aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee'
group-replication-start-on-boot = OFF
group-replication-local-address = '192.168.1.100:33061'
group-replication-group-seeds = '192.168.1.100:33061,192.168.1.101:33061,192.168.1.102:33061'
group-replication-single-primary-mode = ON
group-replication-enforce-update-everywhere-checks = OFF
group-replication-ssl-mode = REQUIRED

-- 启动组复制（第一个节点）
SET SQL_LOG_BIN=0;
CREATE USER 'repl_user'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

CHANGE MASTER TO MASTER_USER='repl_user', MASTER_PASSWORD='password'
FOR CHANNEL 'group_replication_recovery';
START GROUP_REPLICATION;

-- 查看组复制状态
SELECT * FROM performance_schema.replication_group_members;

-- 组复制模式：
-- 单主模式：只有一个节点可写，自动选主
-- 多主模式：所有节点都可写
-- SET GLOBAL group_replication_single_primary_mode = OFF;

组复制故障处理

-- 查看组复制状态
SELECT * FROM performance_schema.replication_group_members;

-- 节点状态：
-- ONLINE — 正常在线
-- RECOVERING — 正在恢复数据
-- OFFLINE — 离线
-- ERROR — 出错

-- 强制移除故障节点
SELECT group_replication_remove_member('server_uuid');

-- 重新加入组
STOP GROUP_REPLICATION;
START GROUP_REPLICATION;

-- 注意事项：
-- 1. 组复制要求至少 3 个节点（多数派）
-- 2. 网络分区可能导致脑裂
-- 3. 大事务（超过 5 秒）会被拒绝
-- 4. 不支持外键级联约束（单主模式除外）

ProxySQL 高级配置

查询路由与缓存

-- 配置查询规则
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES
-- 写操作路由到主库
(1, 1, '^SELECT.*FOR UPDATE', 10, 1),
(2, 1, '^INSERT', 10, 1),
(3, 1, '^UPDATE', 10, 1),
(4, 1, '^DELETE', 10, 1),
(5, 1, '^REPLACE', 10, 1),
(6, 1, '^CALL', 10, 1),
(7, 1, '^SET', 10, 1),
-- 读操作路由到从库
(10, 1, '^SELECT', 20, 1);

-- 配置查询缓存
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, cache_ttl, apply)
VALUES (20, 1, '^SELECT.*FROM config_table', 300000, 1);  -- 缓存 5 分钟

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

-- 连接池配置
UPDATE mysql_servers SET max_connections = 200;
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

-- 慢查询阈值
SET mysql-default_query_timeout = 30000;  -- 30 秒
SET mysql-long_query_time = 1000;         -- 1 秒

ProxySQL 监控

-- 查看后端服务器状态
SELECT hostgroup_id, hostname, port, status, weight, queries, queries_ok, queries_err
FROM stats.stats_mysql_connection_pool;

-- 查看查询统计
SELECT hostgroup, digest, count_star, sum_time, min_time, max_time
FROM stats.stats_mysql_query_digest
ORDER BY sum_time DESC LIMIT 20;

-- 查看当前连接
SELECT * FROM stats.stats_mysql_processlist;

-- ProxySQL 核心表说明：
-- mysql_servers — 后端 MySQL 实例
-- mysql_users — ProxySQL 用户
-- mysql_query_rules — 查询路由规则
-- stats_mysql_query_digest — 查询统计
-- stats_mysql_connection_pool — 连接池统计

备份与灾难恢复

备份策略

# 全量备份（mysqldump）
mysqldump -u root -p --single-transaction --routines --triggers --events \
  --master-data=2 --all-databases > full_backup_$(date +%Y%m%d).sql

# 增量备份（基于 binlog）
mysqlbinlog --start-datetime="2026-04-14 00:00:00" \
  --stop-datetime="2026-04-14 23:59:59" \
  /var/lib/mysql/mysql-bin.000123 > incremental_20260414.sql

# 物理备份（Percona XtraBackup）
xtrabackup --backup --target-dir=/data/backup/full \
  --user=root --password=xxx

# 增量物理备份
xtrabackup --backup --target-dir=/data/backup/inc1 \
  --incremental-basedir=/data/backup/full

# 恢复物理备份
xtrabackup --prepare --target-dir=/data/backup/full
xtrabackup --copy-back --target-dir=/data/backup/full

备份验证与演练

# 定期验证备份可用性（非常重要）
# 1. 在测试环境恢复备份
mysql -u root -p < full_backup_20260414.sql

# 2. 验证数据完整性
mysqlcheck -u root -p --all-databases --check

# 3. 检查表数量和数据量
SELECT table_schema, COUNT(*) AS table_count,
       SUM(data_length + index_length) AS total_size
FROM information_schema.tables
GROUP BY table_schema;

# 备份演练检查清单：
# - [ ] 备份文件完整无损坏
# - [ ] 恢复时间在 RTO 范围内
# - [ ] 数据一致性校验通过
# - [ ] 应用可以正常连接和查询
# - [ ] 增量备份可以正确应用
# - [ ] 备份文件加密存储
# - [ ] 异地备份传输正常

监控告警体系

核心监控指标

-- 1. 复制延迟监控
SELECT
    slave_id,
    TIMESTAMPDIFF(SECOND, last_update, NOW()) AS delay_seconds
FROM (
    SELECT
        SERVER_ID AS slave_id,
        MAX(last_update) AS last_update
    FROM (
        SELECT
            SUBSTRING_INDEX(host, ':', 1) AS host,
            variable_value AS server_id
        FROM information_schema.global_variables
        WHERE variable_name = 'server_id'
    ) s
    CROSS JOIN information_schema.processlist p
    WHERE p.command = 'Binlog Dump'
) t;

-- 2. 连接数监控
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';
SHOW VARIABLES LIKE 'max_connections';
-- 告警阈值：Threads_connected / max_connections > 80%

-- 3. 缓冲池命中率
SELECT
    ROUND(
        (1 - (
            SELECT variable_value FROM information_schema.global_status
            WHERE variable_name = 'Innodb_buffer_pool_reads'
        ) / (
            SELECT variable_value FROM information_schema.global_status
            WHERE variable_name = 'Innodb_buffer_pool_read_requests'
        )) * 100, 2
    ) AS buffer_pool_hit_rate;
-- 告警阈值：< 99%

-- 4. 主从一致性校验（pt-table-checksum）
pt-table-checksum h=192.168.1.100,u=checksum_user,p=xxx \
  --replicate=percona.checksums --no-check-binlog-format

-- 5. 主从数据修复（pt-table-sync）
pt-table-sync --execute h=192.168.1.100 h=192.168.1.101

Prometheus + Grafana 监控

# mysqld_exporter 配置
# prometheus.yml
scrape_configs:
  - job_name: 'mysql'
    static_configs:
      - targets: ['192.168.1.100:9104', '192.168.1.101:9104', '192.168.1.102:9104']

# 关键告警规则
# alert_rules.yml
groups:
  - name: mysql_alerts
    rules:
      - alert: MySQLReplicationLag
        expr: mysql_slave_seconds_behind_master > 60
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "MySQL 复制延迟超过 60 秒"

      - alert: MySQLDown
        expr: up{job="mysql"} == 0
        for: 1m
        labels:
          severity: critical
        annotations:
          summary: "MySQL 实例不可用"

      - alert: MySQLTooManyConnections
        expr: mysql_global_status_threads_connected / mysql_global_variables_max_connections > 0.8
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "MySQL 连接数超过 80%"

优点

缺点

总结

MySQL 高可用方案的选择需要根据业务场景的 RPO（恢复点目标）和 RTO（恢复时间目标）来决定。对于中小规模业务，主从复制结合 MHA 是性价比较高的选择；对于对数据一致性要求极高的核心业务，MySQL InnoDB Cluster 提供了更可靠的保障；而读写分离则是几乎所有高可用架构中不可或缺的组成部分。在实际部署中，建议充分测试故障转移流程，建立完善的监控告警体系，并制定详细的应急预案，以确保高可用架构能够真正发挥作用。

关键知识点

  • 数据库主题一定要同时看数据模型、读写模式和执行代价。
  • 很多性能问题不是 SQL 语法问题，而是索引、统计信息、事务和数据分布问题。
  • 高可用、备份、迁移和治理与查询优化同样重要。
  • 先把数据模型、访问模式和执行代价绑定起来理解。

项目落地视角

  • 所有优化前后都保留执行计划、样本 SQL 和关键指标对比。
  • 上线前准备回滚脚本、备份点和校验方案。
  • 把连接池、锁等待、慢查询和容量增长纳入日常巡检。
  • 保留执行计划、样本 SQL、索引定义和优化前后指标。

常见误区

  • 脱离真实数据分布讨论索引或分片。
  • 只看单条 SQL，不看整条业务链路的事务和锁。
  • 把测试环境结论直接等同于生产环境结论。
  • 脱离真实数据分布设计索引。

进阶路线

  • 继续向执行计划、存储引擎、复制机制和数据治理层深入。
  • 把主题与 ORM、缓存、消息队列和归档策略联动起来思考。
  • 沉淀成数据库设计规范、SQL 审核规则和变更流程。
  • 继续深入存储引擎、复制机制、归档与冷热分层治理。

适用场景

  • 当你准备把《MySQL 高可用方案》真正落到项目里时，最适合先在一个独立模块或最小样例里验证关键路径。
  • 适合数据建模、查询优化、事务控制、高可用和迁移治理。
  • 当系统开始遇到慢查询、锁冲突、热点数据或容量增长时，这类主题价值最高。

落地建议

  • 先分析真实查询模式、数据量级和写入特征，再决定索引或分片策略。
  • 所有优化结论都结合执行计划、样本数据和监控指标验证。
  • 高风险操作前准备备份、回滚脚本与校验 SQL。

排错清单

  • 先确认瓶颈在 CPU、I/O、锁等待、网络还是 SQL 本身。
  • 检查执行计划是否走错索引、是否发生排序或全表扫描。
  • 排查长事务、隐式类型转换、统计信息过期和参数嗅探。

复盘问题

  • 如果把《MySQL 高可用方案》放进你的当前项目，最先要验证的输入、输出和失败路径分别是什么？
  • 《MySQL 高可用方案》最容易在什么规模、什么边界条件下暴露问题？你会用什么指标或日志去确认？
  • 相比默认实现或替代方案，采用《MySQL 高可用方案》最大的收益和代价分别是什么？

延伸阅读

Copyright © 2026 SunnyFan