MySQL主从复制与高可用架构

摘要主从复制是 MySQL 实现读写分离和数据冗余的基础高可用架构则是保障业务连续性的关键。本文从 Binlog 的三种格式讲起深入解析主从复制的核心原理与三种复制模式异步/半同步/组复制对比 MHA、MGR、Orchestrator 等高可用方案的优劣并给出完整的搭建命令和故障切换实战。全文包含架构图和配置示例建议收藏。一、为什么需要主从复制单节点 MySQL 面临三大瓶颈瓶颈表现主从复制解决方案读性能瓶颈单机 QPS 有上限读请求过多导致 CPU/IO 满载一主多从读写分离读流量分散到多个从库单点故障主库宕机业务完全不可用从库提升为主库实现故障转移数据安全单节点数据丢失风险高数据实时同步到从库提供冗余备份上图展示了典型的读写分离架构写操作Insert/Update/Delete全部发往主库Master读操作Select分散到多个从库Replica通过负载均衡实现读流量的水平扩展。二、Binlog主从复制的基石2.1 Binlog 的三种格式BinlogBinary Log是 MySQL 的二进制日志记录了所有修改数据的操作。主从复制本质上就是从库读取主库的 Binlog 并重放。格式记录内容优点缺点适用场景STATEMENTSQL 语句原文日志量小某些语句如 UUID、NOW()在主从执行结果不一致简单场景不推荐生产ROW每行数据的变更前后值精确复制无歧义日志量大批量更新会记录所有行生产环境推荐MIXED混合模式MySQL 自动选择兼顾日志量和准确性切换逻辑不透明可能引入不确定性折中方案-- 查看当前 Binlog 格式SHOWVARIABLESLIKEbinlog_format;-- 动态修改建议 ROW 格式SETGLOBALbinlog_formatROW;-- 永久修改my.cnf[mysqld]binlog_formatROW2.2 Binlog 与 Redo Log 的区别特性BinlogRedo Log层级Server 层存储引擎层InnoDB内容逻辑日志SQL 或行数据变更物理日志页数据的物理修改用途主从复制、数据恢复、审计崩溃恢复Crash Recovery写入方式追加写日志文件会轮转循环写固定大小如 2 个 1GB 文件是否必须主从复制场景必须开启InnoDB 必须开启两阶段提交为了保证 Binlog 和 Redo Log 的一致性InnoDB 使用两阶段提交2PCPrepare 阶段事务先将修改写入 Redo Log状态设为 prepareCommit 阶段事务将修改写入 Binlog然后将 Redo Log 状态改为 commit这样即使崩溃也能通过 Redo Log 状态判断事务是否需要回滚或提交。三、主从复制的核心原理3.1 复制流程详解主从复制涉及三个核心线程线程所在节点职责Binlog Dump Thread主库读取 Binlog 文件将日志事件发送给从库的 I/O 线程I/O Thread从库连接主库接收 Binlog 事件写入 Relay Log中继日志SQL Thread从库读取 Relay Log重放 SQL 语句将数据变更应用到从库复制流程主库执行 DML/DDL 操作记录到 Binlog从库的 I/O 线程连接主库请求指定位置之后的 Binlog主库的 Binlog Dump 线程读取 Binlog通过网络发送给从库从库的 I/O 线程将接收到的 Binlog 事件写入 Relay Log从库的 SQL 线程读取 Relay Log按顺序重放事件更新从库数据3.2 两种复制方式基于位置 vs 基于 GTID基于文件位置File Position-- 从库配置CHANGE MASTERTOMASTER_HOST192.168.1.10,MASTER_USERrepl,MASTER_PASSWORDpassword,MASTER_LOG_FILEmysql-bin.000001,MASTER_LOG_POS154;缺点主从切换后新主库的 Binlog 位置与旧主库不同从库需要重新定位多个从库指向同一个主库时位置管理复杂故障恢复时容易出错基于 GTIDGlobal Transaction IdentifierGTID 是 MySQL 5.6 引入的全局事务标识符格式为source_id:transaction_id。-- 主库和从库都开启 GTID[mysqld]gtid_modeONenforce_gtid_consistencyON-- 从库配置CHANGE MASTERTOMASTER_HOST192.168.1.10,MASTER_USERrepl,MASTER_PASSWORDpassword,MASTER_AUTO_POSITION1;-- 自动定位无需指定文件和位置GTID 的优势自动定位从库只需知道已执行到哪个 GTID无需关心文件和位置主从切换简单新主库的 GTID 集合与旧主库一致从库自动追平故障恢复可靠不会遗漏或重复执行事务四、三种复制模式4.1 异步复制Asynchronous Replication默认模式。主库执行完事务后立即返回客户端不等待从库确认。客户端 ← 主库提交成功立即返回 ↓ Binlog Dump Thread 异步发送给从库 ↓ 从库 I/O Thread 接收并写入 Relay Log ↓ 从库 SQL Thread 重放优点主库性能最好无延迟缺点主库宕机时从库可能丢失未同步的数据数据不一致适用读多写少、允许少量数据丢失的场景4.2 半同步复制Semi-Synchronous ReplicationMySQL 5.5 引入。主库执行完事务后至少等待一个从库确认收到 Binlog 后才返回客户端。上图展示了半同步复制的 ACK 机制主库在 commit 前等待从库返回 ACK确保至少一个从库已收到数据。-- 主库安装半同步插件INSTALL PLUGIN rpl_semi_sync_masterSONAMEsemisync_master.so;SETGLOBALrpl_semi_sync_master_enabled1;SETGLOBALrpl_semi_sync_master_timeout10000;-- 10秒超时降级为异步-- 从库安装半同步插件INSTALL PLUGIN rpl_semi_sync_slaveSONAMEsemisync_slave.so;SETGLOBALrpl_semi_sync_slave_enabled1;优点保证至少一个从库有最新数据减少数据丢失风险缺点增加主库延迟等待 ACK 的时间性能略低于异步适用对数据一致性要求较高的场景4.3 组复制Group Replication / MGRMySQL 5.7.17 引入的原生高可用方案基于 Paxos 协议实现多主强一致性复制。核心特点多主模式多个节点可同时写入自动处理冲突自动故障检测节点故障自动踢出集群自动选主主库故障后自动选举新主库数据强一致性基于 Paxos 协议大多数节点确认后才提交-- 组复制配置示例单主模式[mysqld]server_id1gtid_modeONenforce_gtid_consistencyONbinlog_formatROWlog_binmysql-bin plugin_load_addgroup_replication.so group_replication_group_nameaaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeeegroup_replication_local_address192.168.1.10:33061group_replication_group_seeds192.168.1.10:33061,192.168.1.11:33061,192.168.1.12:33061group_replication_single_primary_modeONgroup_replication_enforce_update_everywhere_checksOFF三种复制模式对比特性异步复制半同步复制组复制MGR数据一致性弱可能丢失较强至少一个从库确认强多数节点确认主库延迟无中等等 ACK较高等多数确认故障切换手动手动/脚本自动写入扩展单主单主多主可选复杂度低中高适用场景读多写少数据安全要求高金融级高可用五、高可用架构方案对比5.1 方案总览方案原理自动切换数据一致性适用场景MHA基于 SSH 的脚本化切换是依赖半同步中小型业务MGRMySQL 原生组复制是强一致MySQL 5.7.17 推荐OrchestratorRaft 共识 可视化是可配置大规模集群ProxySQL中间件读写分离否需配合 MHA/MGR依赖后端读写分离场景5.2 MHAMaster High AvailabilityMHA 是由日本开发者用 Perl 编写的开源高可用方案由两部分组成MHA Manager管理节点负责监控主库状态、执行故障切换MHA Node数据节点部署在每个 MySQL 服务器上执行日志解析和差异补全故障切换流程Manager 检测到主库故障通过 ping 和 SELECT 1 检测从多个从库中选择数据最新的一个作为候选主库对比 Relay Log 位置将其他从库的 Relay Log 中的差异事件应用到候选主库提升候选主库为新主库将其他从库指向新主库MHA 配置示例# /etc/mha/app1.cnf[server default]usermhapasswordmha_passwordssh_userrootrepl_userreplrepl_passwordrepl_passwordping_interval1master_binlog_dir/var/lib/mysqlremote_workdir/var/log/masterhamanager_workdir/var/log/masterha/app1manager_log/var/log/masterha/app1/manager.log[server1]hostname192.168.1.10port3306[server2]hostname192.168.1.11port3306candidate_master1# 优先提升为主库[server3]hostname192.168.1.12port3306no_master1# 不参与主库竞选MHA 的优缺点优点缺点故障切换速度快通常 10-30 秒已停止维护2018 年后无更新支持基于 GTID 和基于位置的复制需要 SSH 免密登录存在安全风险切换过程中尽量保证数据一致性只支持单主架构社区使用广泛文档丰富配置复杂依赖 Perl 环境5.3 MGRMySQL Group ReplicationMGR 是 MySQL 官方推出的原生高可用方案基于 Paxos 协议实现。两种模式模式特点适用场景单主模式Single-Primary只有一个节点可写其他只读自动选主大多数业务场景多主模式Multi-Primary所有节点都可写自动处理冲突写密集型、多地域写入MGR 的核心优势原生集成无需第三方工具MySQL 内置自动故障检测与切换节点故障自动踢出新主自动选举数据强一致性基于 Paxos写入需多数节点确认弹性扩展可在线添加/移除节点MGR 的限制只支持 InnoDB 存储引擎表必须有主键不支持 SERIALIZABLE 隔离级别网络延迟要求高建议 10ms5.4 OrchestratorGitHub 开源的 MySQL 高可用管理工具基于 Raft 协议实现 Manager 自身的高可用。核心特性可视化拓扑Web 界面直观展示主从关系智能故障检测不仅检测主库宕机还能检测网络分区、复制延迟等优雅主从切换支持计划内切换如维护窗口零数据丢失Hooks 机制切换前后可执行自定义脚本如切换 VIP、通知告警Orchestrator 配置要点{Debug:true,ListenAddress::3000,MySQLTopologyUser:orchestrator,MySQLTopologyPassword:password,MySQLTopologyMaxPoolConnections:3,RecoveryPeriodBlockSeconds:3600,PreFailoverProcesses:[echo Will recover from {failureType} on {failedHost} /tmp/recovery.log],PostFailoverProcesses:[echo Recovered from {failureType} on {failedHost}, new master is {successorHost} /tmp/recovery.log]}六、实战搭建一主两从 MHA 高可用6.1 环境准备角色IP主机名Master192.168.1.10db-masterSlave1192.168.1.11db-slave1Slave2192.168.1.12db-slave2MHA Manager192.168.1.20mha-manager6.2 主库配置# /etc/mysql/my.cnf [mysqld] server_id 10 log_bin mysql-bin binlog_format ROW gtid_mode ON enforce_gtid_consistency ON binlog_expire_logs_seconds 604800 # 7天 # 半同步复制可选 plugin_load_add semisync_master.so rpl_semi_sync_master_enabled 1 rpl_semi_sync_master_timeout 10000-- 创建复制用户CREATEUSERrepl192.168.1.%IDENTIFIEDBYrepl_password;GRANTREPLICATIONSLAVEON*.*TOrepl192.168.1.%;FLUSHPRIVILEGES;-- 查看主库状态SHOWMASTERSTATUS;-- 或GTID 模式SHOWGLOBALVARIABLESLIKEgtid_executed;6.3 从库配置# /etc/mysql/my.cnf [mysqld] server_id 11 # Slave2 改为 12 log_bin mysql-bin binlog_format ROW gtid_mode ON enforce_gtid_consistency ON read_only ON # 从库只读 # 半同步复制可选 plugin_load_add semisync_slave.so rpl_semi_sync_slave_enabled 1-- 配置主从关系GTID 模式CHANGE MASTERTOMASTER_HOST192.168.1.10,MASTER_USERrepl,MASTER_PASSWORDrepl_password,MASTER_AUTO_POSITION1;STARTSLAVE;-- 检查从库状态SHOWSLAVESTATUS\G-- 确认: Slave_IO_Running Yes, Slave_SQL_Running Yes6.4 复制延迟监控-- 查看从库延迟Seconds_Behind_MasterSHOWSLAVESTATUS\G-- 更精确的延迟监控MySQL 8.0SELECTMAX(TIMESTAMPDIFF(SECOND,LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP,LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP))ASdelay_secondsFROMperformance_schema.replication_applier_status_by_worker;-- 查看 Relay Log 应用进度SHOWPROCESSLIST;-- 查看 SQL Thread 状态6.5 故障切换演练# 1. 模拟主库故障systemctl stop mysql# 在 db-master 上执行# 2. MHA Manager 自动检测并切换约 10-30 秒# 查看切换日志tail-f/var/log/masterha/app1/manager.log# 3. 验证新主库mysql-h192.168.1.11-eSHOW MASTER STATUS;# 4. 将故障节点恢复为从库# 在 db-master 上执行CHANGE MASTER TOMASTER_HOST192.168.1.11,MASTER_USERrepl,MASTER_PASSWORDrepl_password, MASTER_AUTO_POSITION1;START SLAVE;七、读写分离实践7.1 应用层实现// Spring Boot MyBatis 动态数据源ConfigurationpublicclassDataSourceConfig{BeanpublicDataSourceroutingDataSource(){DynamicRoutingDataSourceroutingDataSourcenewDynamicRoutingDataSource();MapObject,ObjecttargetDataSourcesnewHashMap();targetDataSources.put(master,masterDataSource());targetDataSources.put(slave1,slave1DataSource());targetDataSources.put(slave2,slave2DataSource());routingDataSource.setTargetDataSources(targetDataSources);routingDataSource.setDefaultTargetDataSource(masterDataSource());returnroutingDataSource;}}// 注解方式切换数据源Target({ElementType.METHOD,ElementType.TYPE})Retention(RetentionPolicy.RUNTIME)publicinterfaceDataSource{Stringvalue()defaultmaster;}// Service 层使用ServicepublicclassOrderService{DataSource(master)// 写操作走主库publicvoidcreateOrder(Orderorder){orderMapper.insert(order);}DataSource(slave1)// 读操作走从库publicOrdergetOrder(Longid){returnorderMapper.selectById(id);}}7.2 中间件实现ProxySQLProxySQL 是高性能的 MySQL 中间件支持读写分离、连接池、查询缓存等功能。-- 1. 添加后端服务器INSERTINTOmysql_servers(hostgroup_id,hostname,port)VALUES(10,192.168.1.10,3306),-- hostgroup 10: 写组主库(20,192.168.1.11,3306),-- hostgroup 20: 读组从库(20,192.168.1.12,3306);-- 2. 配置用户INSERTINTOmysql_users(username,password,default_hostgroup)VALUES(app_user,password,10);-- 3. 配置读写分离规则INSERTINTOmysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply)VALUES(1,1,^SELECT.*FOR UPDATE,10,1),-- SELECT FOR UPDATE 走主库(2,1,^SELECT,20,1),-- 普通 SELECT 走从库(3,1,.*,10,1);-- 其他INSERT/UPDATE/DELETE走主库-- 4. 加载配置到运行时LOADMYSQL SERVERSTORUNTIME;LOADMYSQL USERSTORUNTIME;LOADMYSQL QUERY RULESTORUNTIME;-- 5. 持久化到磁盘SAVEMYSQL SERVERSTODISK;SAVEMYSQL USERSTODISK;SAVEMYSQL QUERY RULESTODISK;八、面试高频考点速记Q1MySQL 主从复制的原理是什么主库将数据变更记录到 Binlog从库的 I/O 线程连接主库读取 Binlog 并写入 Relay LogSQL 线程读取 Relay Log 重放到从库。涉及三个线程主库的 Binlog Dump 线程、从库的 I/O 线程和 SQL 线程。Q2Binlog 的三种格式有什么区别生产环境用哪种STATEMENT 记录 SQL 原文日志量小但可能有主从不一致ROW 记录每行变更精确复制但日志量大MIXED 自动选择。生产环境推荐 ROW 格式确保数据一致性。Q3GTID 相比传统基于位置的复制有什么优势GTID 为每个事务分配全局唯一标识从库只需追踪已执行的 GTID 集合无需关心文件和位置。主从切换时自动定位不会遗漏或重复事务故障恢复更可靠。Q4半同步复制和异步复制的区别异步复制主库提交后不等待从库确认性能最好但可能丢数据。半同步复制主库至少等待一个从库确认收到 Binlog 后才返回客户端保证至少一个从库有最新数据减少数据丢失风险但增加了主库延迟。Q5MHA 和 MGR 有什么区别MHA 是第三方 Perl 脚本工具基于 SSH 实现故障切换速度快但已停止维护。MGR 是 MySQL 原生方案基于 Paxos 协议实现多主强一致性复制自动故障检测和切换是官方推荐的高可用方案MySQL 5.7.17。Q6主从延迟怎么解决硬件层面从库配置不低于主库使用 SSD2. SQL 层面避免大事务将大事务拆分为小事务3. 架构层面增加从库数量分散读压力或使用缓存Redis分担读请求4. 参数优化开启并行复制slave_parallel_workers调整 sync_binlog 和 innodb_flush_log_at_trx_commit。Q7读写分离后从库数据延迟读不到最新数据怎么办强制走主库对实时性要求高的查询使用DataSource(master)2. 延迟阈值判断如果 Seconds_Behind_Master 阈值自动切到主库3. 业务容忍大部分业务允许秒级延迟4. 使用 MGR单主模式下写入后立即可从主库读取。结语主从复制是 MySQL 水平扩展的基石高可用架构则是业务连续性的保障。选择建议MySQL 5.6/5.7 早期MHA 异步/半同步复制MySQL 5.7.17 / 8.0优先选择MGR原生支持、自动切换、强一致性大规模集群Orchestrator ProxySQL可视化管理和智能切换云环境直接使用云厂商的 RDS 高可用方案如阿里云 PolarDB、AWS Aurora无论选择哪种方案监控和演练都是必不可少的没有监控的高可用等于没有高可用没有演练的故障切换等于没有故障切换。如果本文对你有帮助欢迎点赞收藏