MySQL 教程：主从复制原理与 binlog 三种格式

前言

主从复制是读写分离、备份和故障切换的地基。它的实现思路其实很朴素：主库把自己执行过的变更写进二进制日志，从库把日志拿过来重放一遍。

一条链路，三个线程

主库把数据变更写入 binlog
从库的 IO 线程连上主库（复制线程身份是 Slave_IO_Running），读取 binlog 内容，追加写入本地的 relay log
从库的 SQL 线程解析 relay log 并在自己身上重放，Slave_SQL_Running 就是它

主库端还有一个 dump 线程负责发送 binlog。判断复制状态最常用的就是这句：

SHOW SLAVE STATUS\G

重点看 Seconds_Behind_Master、Slave_IO_Running、Slave_SQL_Running 三项，以及 Last_IO_Error、Last_SQL_Error。

定位同步到哪了有两种方式：传统的 binlog 文件名加位点（SHOW MASTER STATUS 给出 File 和 Position），以及 5.6 之后推荐的 GTID（gtid_mode 在 5.7 默认 OFF，打开后配 enforce_gtid_consistency = ON，搭从库时用 MASTER_AUTO_POSITION = 1，切换和补同步不用再手工算位点）。

最小可用的搭建流程：

# 主库
[mysqld]
server_id   = 1
log_bin     = mysqlbin
binlog_format = ROW

# 从库
[mysqld]
server_id   = 2
relay_log   = relay-bin
read_only   = 1

-- 主库：授权复制账号
CREATE USER 'repl'@'10.0.0.%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.0.%';

-- 从库
CHANGE MASTER TO
  MASTER_HOST = '10.0.0.1', MASTER_USER = 'repl', MASTER_PASSWORD = 'password',
  MASTER_LOG_FILE = 'mysqlbin.000001', MASTER_LOG_POS = 154;
START SLAVE;

binlog 三种格式

格式	记录内容	优点	问题
STATEMENT	原始 SQL 语句	日志小，网络传输省	非确定性语句会主从不一致
ROW	每行变更前后的值	安全，能精确重放、支持误删恢复	日志大，批量更新产生海量事件
MIXED	默认走语句，遇到不安全语句自动切行格式	兼顾两者	同一语句在不同表上格式可能不同，排查要看清楚

MySQL 5.7 里 binlog_format 的默认值已经是 ROW（更早的版本默认 STATEMENT）。非确定性语句是 STATEMENT 的核心风险：UUID()、RAND()、SYSDATE() 这类函数在主库和从库上求值结果不同（NOW() 取的是语句开始时间，本身还算稳定），不带 ORDER BY 的 LIMIT 更新、涉及自增值分配的语句等同样可能让主从结果分叉——日志里出现 "Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT" 就是这个原因，这也是 MIXED 存在的意义。

看某段 binlog 实际记了什么：

SHOW BINARY LOGS;
SHOW BINLOG EVENTS IN 'mysqlbin.000001' LIMIT 20;

# 行格式的事件要解码后才看得懂
mysqlbinlog --no-defaults -vv --base64-output=decode-rows /data/mysql/mysqlbin.000001

配合 binlog_row_image（默认 FULL，记录整行前后镜像）可以进一步控制行格式日志的体积；设成 MINIMAL 只在有合适主键的表上安全，别乱调。

几个绕不开的配套项

不丢事务：innodb_flush_log_at_trx_commit = 1 与 sync_binlog = 1 组合，也就是常说的"双一"。放松这两个值能提速，代价是崩溃时可能丢事务或出现主从不一致
日志清理：expire_logs_days 控制 binlog 保留天数，配合 FLUSH LOGS 滚动；磁盘被 binlog 打满是很常见的事故
并发重放：5.7 支持多线程复制，slave_parallel_workers 默认 0（单线程），slave_parallel_type 默认 DATABASE，只能按库并行；库少的架构想提并行度得靠 GTID 下的逻辑时钟
只读保护：从库设 read_only，运维账号另有 SUPER 权限时才写得进去；需要防误写就用 super_read_only
延迟从库：CHANGE MASTER TO MASTER_DELAY = 3600 让从库刻意晚一小时重放，误删主库数据时有个时间窗口可以捞
半同步：异步复制的窗口内可能丢已提交事务，官方插件 rpl_semi_sync_master / rpl_semi_sync_slave 让主库等到至少一个从库收到日志再返回，需要单独安装启用

一致性校验不要凭感觉，pt-table-checksum 是常用的第三方工具；binlog 除了复制，还是数据恢复的依据（按时间点重放），这也是为什么它必须定期备份。

小结

复制的本质是 binlog 接力：主库写、IO 线程搬、SQL 线程放
位点难维护，新架构直接用 GTID
STATEMENT 小但有非确定性风险，ROW 可靠但吃 IO 和磁盘，MIXED 是折中
双一、日志保留、并行度、只读这几项要一起配，只调一个没有意义

前言#

一条链路，三个线程#

binlog 三种格式#

几个绕不开的配套项#

小结#

同分类其他笔记

前言

一条链路，三个线程

binlog 三种格式

几个绕不开的配套项

小结