MySQL 主从架构（基于Binlog）-171主机测评

MySQL 主从架构（基于Binlog）

一、核心原理

MySQL主从架构的核心是基于Binlog（二进制日志）实现数据同步：

主库（Master）将所有数据修改操作（增删改）记录到Binlog；

从库（Slave）通过IO线程连接主库，读取主库的Binlog并写入本地的中继日志（Relay Log）；

从库的SQL线程读取中继日志，重放其中的操作，最终实现从库数据与主库一致。

二、典型架构及架构图

1. 核心架构类型

架构类型适用场景核心特点

一主一从	中小规模业务、数据备份	架构简单，成本低，主库故障可切换到从库
一主多从	读多写少、高并发查询	分散读压力，多个从库分担查询请求
多主一从	多业务中心写入、数据汇总	多个主库写入，一个从库汇总数据
多主多从	大型分布式系统、异地多活	高可用、高扩展，支持异地部署

2. 架构图

（1）一主一从架构图

#mermaid-svg-7wL7n7wyZgzFAVae{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-7wL7n7wyZgzFAVae .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-7wL7n7wyZgzFAVae .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-7wL7n7wyZgzFAVae .error-icon{fill:#552222;}#mermaid-svg-7wL7n7wyZgzFAVae .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-7wL7n7wyZgzFAVae .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-7wL7n7wyZgzFAVae .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-7wL7n7wyZgzFAVae .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-7wL7n7wyZgzFAVae .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-7wL7n7wyZgzFAVae .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-7wL7n7wyZgzFAVae .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-7wL7n7wyZgzFAVae .marker{fill:#333333;stroke:#333333;}#mermaid-svg-7wL7n7wyZgzFAVae .marker.cross{stroke:#333333;}#mermaid-svg-7wL7n7wyZgzFAVae svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-7wL7n7wyZgzFAVae p{margin:0;}#mermaid-svg-7wL7n7wyZgzFAVae .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-7wL7n7wyZgzFAVae .cluster-label text{fill:#333;}#mermaid-svg-7wL7n7wyZgzFAVae .cluster-label span{color:#333;}#mermaid-svg-7wL7n7wyZgzFAVae .cluster-label span p{background-color:transparent;}#mermaid-svg-7wL7n7wyZgzFAVae .label text,#mermaid-svg-7wL7n7wyZgzFAVae span{fill:#333;color:#333;}#mermaid-svg-7wL7n7wyZgzFAVae .node rect,#mermaid-svg-7wL7n7wyZgzFAVae .node circle,#mermaid-svg-7wL7n7wyZgzFAVae .node ellipse,#mermaid-svg-7wL7n7wyZgzFAVae .node polygon,#mermaid-svg-7wL7n7wyZgzFAVae .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-7wL7n7wyZgzFAVae .rough-node .label text,#mermaid-svg-7wL7n7wyZgzFAVae .node .label text,#mermaid-svg-7wL7n7wyZgzFAVae .image-shape .label,#mermaid-svg-7wL7n7wyZgzFAVae .icon-shape .label{text-anchor:middle;}#mermaid-svg-7wL7n7wyZgzFAVae .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-7wL7n7wyZgzFAVae .rough-node .label,#mermaid-svg-7wL7n7wyZgzFAVae .node .label,#mermaid-svg-7wL7n7wyZgzFAVae .image-shape .label,#mermaid-svg-7wL7n7wyZgzFAVae .icon-shape .label{text-align:center;}#mermaid-svg-7wL7n7wyZgzFAVae .node.clickable{cursor:pointer;}#mermaid-svg-7wL7n7wyZgzFAVae .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-7wL7n7wyZgzFAVae .arrowheadPath{fill:#333333;}#mermaid-svg-7wL7n7wyZgzFAVae .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-7wL7n7wyZgzFAVae .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-7wL7n7wyZgzFAVae .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-7wL7n7wyZgzFAVae .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-7wL7n7wyZgzFAVae .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-7wL7n7wyZgzFAVae .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-7wL7n7wyZgzFAVae .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-7wL7n7wyZgzFAVae .cluster text{fill:#333;}#mermaid-svg-7wL7n7wyZgzFAVae .cluster span{color:#333;}#mermaid-svg-7wL7n7wyZgzFAVae div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-7wL7n7wyZgzFAVae .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-7wL7n7wyZgzFAVae rect.text{fill:none;stroke-width:0;}#mermaid-svg-7wL7n7wyZgzFAVae .icon-shape,#mermaid-svg-7wL7n7wyZgzFAVae .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-7wL7n7wyZgzFAVae .icon-shape p,#mermaid-svg-7wL7n7wyZgzFAVae .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-7wL7n7wyZgzFAVae .icon-shape rect,#mermaid-svg-7wL7n7wyZgzFAVae .image-shape rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-7wL7n7wyZgzFAVae .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-7wL7n7wyZgzFAVae .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-7wL7n7wyZgzFAVae :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

所有写操作

所有读操作

Binlog日志

写入Relay Log

重放操作

客户端

Master主库

Slave从库

IO线程（从库）

Relay Log（中继日志）

SQL线程（从库）

注：主库负责写，从库仅提供读服务

（2）一主多从架构图

#mermaid-svg-VkF1JJnIDjlYFF4w{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-VkF1JJnIDjlYFF4w .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-VkF1JJnIDjlYFF4w .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-VkF1JJnIDjlYFF4w .error-icon{fill:#552222;}#mermaid-svg-VkF1JJnIDjlYFF4w .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-VkF1JJnIDjlYFF4w .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-VkF1JJnIDjlYFF4w .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-VkF1JJnIDjlYFF4w .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-VkF1JJnIDjlYFF4w .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-VkF1JJnIDjlYFF4w .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-VkF1JJnIDjlYFF4w .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-VkF1JJnIDjlYFF4w .marker{fill:#333333;stroke:#333333;}#mermaid-svg-VkF1JJnIDjlYFF4w .marker.cross{stroke:#333333;}#mermaid-svg-VkF1JJnIDjlYFF4w svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-VkF1JJnIDjlYFF4w p{margin:0;}#mermaid-svg-VkF1JJnIDjlYFF4w .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-VkF1JJnIDjlYFF4w .cluster-label text{fill:#333;}#mermaid-svg-VkF1JJnIDjlYFF4w .cluster-label span{color:#333;}#mermaid-svg-VkF1JJnIDjlYFF4w .cluster-label span p{background-color:transparent;}#mermaid-svg-VkF1JJnIDjlYFF4w .label text,#mermaid-svg-VkF1JJnIDjlYFF4w span{fill:#333;color:#333;}#mermaid-svg-VkF1JJnIDjlYFF4w .node rect,#mermaid-svg-VkF1JJnIDjlYFF4w .node circle,#mermaid-svg-VkF1JJnIDjlYFF4w .node ellipse,#mermaid-svg-VkF1JJnIDjlYFF4w .node polygon,#mermaid-svg-VkF1JJnIDjlYFF4w .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-VkF1JJnIDjlYFF4w .rough-node .label text,#mermaid-svg-VkF1JJnIDjlYFF4w .node .label text,#mermaid-svg-VkF1JJnIDjlYFF4w .image-shape .label,#mermaid-svg-VkF1JJnIDjlYFF4w .icon-shape .label{text-anchor:middle;}#mermaid-svg-VkF1JJnIDjlYFF4w .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-VkF1JJnIDjlYFF4w .rough-node .label,#mermaid-svg-VkF1JJnIDjlYFF4w .node .label,#mermaid-svg-VkF1JJnIDjlYFF4w .image-shape .label,#mermaid-svg-VkF1JJnIDjlYFF4w .icon-shape .label{text-align:center;}#mermaid-svg-VkF1JJnIDjlYFF4w .node.clickable{cursor:pointer;}#mermaid-svg-VkF1JJnIDjlYFF4w .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-VkF1JJnIDjlYFF4w .arrowheadPath{fill:#333333;}#mermaid-svg-VkF1JJnIDjlYFF4w .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-VkF1JJnIDjlYFF4w .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-VkF1JJnIDjlYFF4w .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-VkF1JJnIDjlYFF4w .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-VkF1JJnIDjlYFF4w .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-VkF1JJnIDjlYFF4w .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-VkF1JJnIDjlYFF4w .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-VkF1JJnIDjlYFF4w .cluster text{fill:#333;}#mermaid-svg-VkF1JJnIDjlYFF4w .cluster span{color:#333;}#mermaid-svg-VkF1JJnIDjlYFF4w div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-VkF1JJnIDjlYFF4w .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-VkF1JJnIDjlYFF4w rect.text{fill:none;stroke-width:0;}#mermaid-svg-VkF1JJnIDjlYFF4w .icon-shape,#mermaid-svg-VkF1JJnIDjlYFF4w .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-VkF1JJnIDjlYFF4w .icon-shape p,#mermaid-svg-VkF1JJnIDjlYFF4w .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-VkF1JJnIDjlYFF4w .icon-shape rect,#mermaid-svg-VkF1JJnIDjlYFF4w .image-shape rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-VkF1JJnIDjlYFF4w .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-VkF1JJnIDjlYFF4w .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-VkF1JJnIDjlYFF4w :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

所有写操作

读操作负载均衡

Binlog日志

写入Relay Log

重放操作

客户端

Master主库

Slave从库1

Slave从库2

Slave从库3

IO线程（从库1）

IO线程（从库2）

IO线程（从库3）

Relay Log（从库1）

Relay Log（从库2）

Relay Log（从库3）

SQL线程（从库1）

SQL线程（从库2）

SQL线程（从库3）

注：主库仅处理写，多个从库分担读请求

三、关键配置项（以MySQL 8.0为例）

1. 主库（Master）配置（my.cnf/my.ini）

[mysqld]
# 必须开启Binlog，指定日志格式（推荐ROW）
log_bin = /var/lib/mysql/mysql-bin
binlog_format = ROW # 行级日志，同步精度最高，适合主从
server_id = 1 # 主库唯一ID（1-2^32-1），不能与从库重复
binlog_do_db = test # 仅同步test库（可选，不配置则同步所有库）
binlog_ignore_db = mysql # 忽略mysql系统库（可选）
expire_logs_days = 7 # Binlog自动过期清理（避免磁盘占满）
sync_binlog = 1 # 每次事务提交都刷新Binlog到磁盘（高可用）
innodb_flush_log_at_trx_commit = 1 # 事务提交立即刷盘（数据安全）

2. 从库（Slave）配置

[mysqld]
server_id = 2 # 从库唯一ID，与主库不同
relay_log = /var/lib/mysql/relay-bin # 中继日志路径

# ========== 核心：从库只读配置（多层防护） ==========
# 1. 基础只读（super权限用户仍可写，用于运维）
read_only = 1
# 2. 严格只读（所有用户均不可写，MySQL 5.6+支持，推荐生产环境）
super_read_only = 1
# 3. 禁用从库的Binlog（若从库仅作为读节点，无需记录写操作）
skip_log_bin = 1
# 4. 限制从库的写权限（通过系统变量加固）
innodb_read_only = 1

# ========== 其他同步配置 ==========
log_slave_updates = 0 # 从库仅作为读节点，无需开启级联同步
slave_skip_errors = 1062 # 忽略主键冲突等常见同步错误（可选）
# 主库信息存储到表（而非文件，更稳定）
master_info_repository = TABLE
relay_log_info_repository = TABLE

3. 从库只读配置补充说明

配置项作用说明适用场景

read_only = 1	普通用户无法写，拥有SUPER权限的用户仍可写（如运维同步账号）	测试/预生产环境，需运维写操作
super_read_only = 1	所有用户（包括SUPER权限）均无法写，彻底禁止从库写入	生产环境（纯读从库）
innodb_read_only = 1	针对InnoDB存储引擎的只读限制，防止通过特殊方式修改InnoDB表数据	生产环境加固
skip_log_bin = 1	关闭从库Binlog，避免从库因误写产生无用日志，同时节省磁盘IO	纯读从库（非级联主库）

4. 主从关联配置（SQL命令）

— 1. 主库创建同步用户
CREATE USER 'repl'@'从库IP' IDENTIFIED BY '同步密码';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'从库IP';
FLUSH PRIVILEGES;

— 2. 主库查看Binlog状态（记录File和Position）
SHOW MASTER STATUS;

— 3. 从库配置连接主库
CHANGE MASTER TO
MASTER_HOST='主库IP',
MASTER_USER='repl',
MASTER_PASSWORD='同步密码',
MASTER_LOG_FILE='主库Binlog文件名（如mysql-bin.000001）',
MASTER_LOG_POS=主库Binlog位置（如156）;

— 4. 启动从库同步
START SLAVE;

— 5. 查看从库同步状态（关键：Slave_IO_Running和Slave_SQL_Running均为Yes）
SHOW SLAVE STATUS\\G;

— 6. 验证从库只读配置（从库执行）
— 查看只读状态（super_read_only应为ON）
SHOW VARIABLES LIKE '%read_only%';
— 测试写操作（应报错：The MySQL server is running with the –super-read-only option so it cannot execute this statement）
INSERT INTO test.t1 (id) VALUES (1);

四、优缺点分析

1. 优点

优势点具体说明

提高读性能	多从库分担查询压力，支持高并发读
数据备份	从库可作为主库的热备，故障时快速切换
高可用	主库故障后，可将从库提升为主库
资源隔离	主库专注写操作，从库专注读操作，资源利用更高效
扩展性好	可按需增加从库数量，水平扩展读能力

2. 缺点

缺点点解决方案

主从延迟	开启半同步复制、优化从库SQL线程、监控延迟指标
架构复杂度提升	标准化配置模板、自动化运维工具（如Ansible）
数据一致性风险	严格限制从库写入、定期校验主从数据（pt-table-checksum）
切换成本	制定切换预案、使用MGR（MySQL Group Replication）简化切换
存储成本增加	按需规划从库数量，采用云数据库弹性扩容

五、应用场景

场景类型适配性核心价值

读多写少的业务（如电商、资讯）	高	分散读压力，提升查询响应速度
数据备份与灾备	高	从库实时备份，主库故障快速恢复
高并发查询场景（如秒杀、报表）	高	多从库分担统计/查询压力
异地多活部署	中	跨地域部署主从，降低地域故障影响
写密集型业务（如金融交易）	低	主从延迟可能导致数据不一致，需谨慎

六、优化建议

性能优化：

Binlog使用ROW格式，减少同步歧义；
从库关闭慢查询日志、不必要的日志记录，降低资源消耗；
主从库均开启索引优化，提升查询/同步效率。

稳定性优化：

开启半同步复制（plugin-load-add = rpl_semi_sync_master.so）；
监控同步状态（Slave_IO_Running、Slave_SQL_Running、Seconds_Behind_Master），异常时及时告警；
定期备份Binlog和中继日志，防止数据丢失。

只读加固优化：

生产环境强制开启super_read_only = 1，彻底禁止从库写入；
限制从库账号权限，仅授予SELECT权限，避免误操作；
定期检查从库只读配置状态，防止配置被篡改。

总结

MySQL主从架构基于Binlog实现数据同步，核心是主库写、从库读，架构图清晰展示了IO线程和SQL线程的同步流程；

从库只读需通过read_only=1（基础）或者super_read_only=1（严格）+innodb_read_only=1（加固）多层配置；

MySQL 主从架构（基于Binlog）