MySQL 主从复制与读写分离
本文合并自：03-MySQL主从复制.md、05-复制技术架构.md、08-代理Amoeba.md
本文系统讲解 MySQL / MariaDB 的复制原理（binlog、relay log、IO 与 SQL 线程）、主从复制搭建实操、GTID 与半同步等进阶复制技术、以及基于 Amoeba 代理的读写分离部署与排错。实操环境覆盖 CentOS 6 与 CentOS 7、MySQL 5.6 / 5.7 / 8.0 与 MariaDB 5.5 / 10.x，差异处均已标注适用版本。
一、复制原理与架构类型
1.1 为什么需要复制
MySQL 支持单向、异步（async）的复制。复制过程中一台服务器充当主服务器（master），一台或多台其他服务器充当从服务器（slave）。
mysql主 --> mysql从
master slave引入复制的核心动机：
| 目标 | 说明 |
|---|---|
| 读扩展（Scale-Out） | 绝大多数业务读多写少。master 负责写，多个 slave 分担读压力，配合代理层做读写分离，可近似线性扩展读能力 |
| 高可用（HA） | 主库故障时，可将某个从库提升为新主库继续提供服务，配合 MHA / Keepalived 可实现自动故障转移 |
| 备份卸载 | 在从库上执行 mysqldump / xtrabackup 全量备份、执行报表类大查询，避免锁表与 IO 争用影响线上主库 |
| 跨机房容灾 | 在异地机房部署从库或双主，机房级故障时可整体切换；也是机房搬迁的常用手段 |
| 数据分析与延时保护 | 延时复制的从库可用于误删数据的抢救与查看数据库历史状态 |
| 升级演练 | 先把从库升级到新版本验证兼容性，再切换主库 |
典型的整体调用链：
客户
|
|
web 应用程序 / 游戏程序（C、PHP、Java……）
|
|
代理层（MySQL Proxy、Amoeba）
读写分离 / 数据切分
|
|
mysql 主 <----> mysql 从1.2 复制原理与三线程模型
复制的本质：从库重放主库的二进制日志。主服务器把二进制日志（binlog）的内容传到从服务器，从服务器写入一个新的日志文件，称为 relay-bin-log（中继日志）。
三个线程各自的职责：
| 线程 | 所在节点 | 职责 |
|---|---|---|
| Binlog Dump 线程 | 主库 | 每个连接上来的从库对应一个 dump 线程，负责读取本地 binlog 并推送给从库的 IO 线程 |
| IO 线程 | 从库 | 连接主库、请求 binlog，把收到的事件顺序写入本地 relay log，并把读到的主库 binlog 文件名与位置记录到 master.info |
| SQL 线程 | 从库 | 读取 relay log 并解析、执行其中的事件（重放），把执行到的位置记录到 relay-log.info |
完整数据流图示：
[ 主库 master ] [ 从库 slave ]
客户端写入
|
v
存储引擎（InnoDB）
|
v
binlog（mysql-bin.00000X）
|
| ①从库 IO 线程发起复制请求（带 binlog 文件名 + 位置）
|<--------------------------------------------- IO 线程
|
| ②Binlog Dump 线程读取 binlog 事件并推送
Dump 线程 -------------------------------------> IO 线程
|
| ③顺序写入
v
relay log（mysql-relay-bin.00000X）
+ master.info（记录主库位点）
|
| ④SQL 线程检测到新内容
v
SQL 线程 解析并执行
|
+--> 从库存储引擎（数据落地）
+--> relay-log.info（记录重放位点）
+--> 若开启 log_slave_updates，
再写入从库自己的 binlog（供级联）文字版复制过程（四步）：
- slave 端的 IO 线程连接上 master 端，发起复制请求。
- master 端返回 binlog 文件名和位置信息给 slave 端。
- IO 线程把 master 端的 binlog 内容依次写入 slave 端的 relay-bin-log，并把 master 端的 binlog 文件名和位置记录到
master.info中。 - slave 端的 SQL 线程检测到 relay-bin-log 中有内容更新，就会解析 relay-log 中更新的内容并执行这些操作，即 slave 执行与 master 一样的操作，从而达到数据同步的目的。
复制方向的单向性：
- 只有 master 写，slave 能看到。
- slave 写，master 看不到（在从库上直接插入数据不会同步回主库）。
思考题（原始文档保留）：
- 主打开二进制日志，从要不要开二进制日志？
- 从库不开 binlog 也能完成复制。但推荐开启：一是便于故障时把从库提升为主库；二是级联复制需要；三是主库故障期间从库承接写入后，需要用从库 binlog 把数据补回主库。
- 主和从的读写情况怎么分开？
- 靠应用层框架（如 Spring 多数据源）或代理层中间件（Amoeba、MyCat、ProxySQL）做读写分离，并在从库上设置
read_only防止误写。
- 靠应用层框架（如 Spring 多数据源）或代理层中间件（Amoeba、MyCat、ProxySQL）做读写分离，并在从库上设置
1.3 binlog / relay log / master.info / relay-log.info
| 文件 | 所在节点 | 作用 |
|---|---|---|
mysql-bin.00000X |
主库（从库可选） | 二进制日志，记录所有引起数据变更的事件，是复制的数据源 |
mysql-bin.index |
主库 | binlog 文件清单索引 |
mysql-relay-bin.00000X / mysqld-relay-bin.00000X |
从库 | 中继日志，IO 线程写入、SQL 线程读取的中间缓冲 |
relay-bin.index |
从库 | relay log 文件清单索引 |
master.info |
从库 | 记录主库连接信息（host、user、password、port）与已读取到的主库 binlog 文件名 + 位置、重连间隔 |
relay-log.info |
从库 | 记录 SQL 线程已执行到的 relay log 位置及对应的主库位置 |
查看这些文件：
cat /var/lib/mysql/master.info
cat /var/lib/mysql/relay-log.info
mysqlbinlog /var/lib/mysql/mysqld-relay-bin.000002
ll /var/lib/mysql/
# 应能看到 mysql-bin.000001、mysql-bin.index...
-rw-rw----. 1 mysql mysql 475 9月 19 20:59 mysql-bin.000001
-rw-rw----. 1 mysql mysql 19 9月 19 20:57 mysql-bin.index
...MySQL 5.6.5+ / 5.7 起可用
master_info_repository=TABLE与relay_log_info_repository=TABLE把这两个 info 从文件改为存储在mysql.slave_master_info/mysql.slave_relay_log_info表中，从而获得崩溃安全（crash-safe）复制。MySQL 8.0 默认即为 TABLE。MariaDB 10.x 不支持该参数，仍使用文件方式，通过relay_log_recovery保证安全。
1.4 binlog 三种格式对复制的影响
[mysqld]
binlog_format = ROW # 可选 STATEMENT / ROW / MIXED| 格式 | 记录内容 | 优点 | 对复制的风险与影响 |
|---|---|---|---|
STATEMENT（SBR） |
记录原始 SQL 语句 | 日志量小、可读性好、便于审计 | 不确定函数导致主从数据不一致：NOW()（部分场景可复制）、UUID()、RAND()、SYSDATE()、USER()、FOUND_ROWS()、带 LIMIT 的无序 UPDATE/DELETE、自增与触发器/存储过程组合等；另外 INSERT ... SELECT 需要更大范围的间隙锁 |
ROW（RBR） |
记录每一行修改前后的镜像 | 主从绝对一致，无不确定函数问题，是 GTID 与半同步的推荐格式 | 日志量大：一条 update t set c=1（百万行）会产生百万行的 before/after image，占用大量磁盘与网络带宽；从库重放也更慢；无主键大表的 ROW 更新在从库会退化为全表扫描（每行都扫），是复制延迟的重要来源 |
MIXED |
默认用 STATEMENT，遇到不确定语句自动切换为 ROW | 兼顾日志量与安全性 | 行为取决于 MySQL 的判定规则，排查时需先确认某条语句实际以哪种格式记录 |
版本差异：
- MySQL 5.6 / 5.7 默认
STATEMENT（5.7.7 起默认ROW）；MySQL 8.0 默认ROW。 - MariaDB 5.5 / 10.0 默认
MIXED；MariaDB 10.2+ 默认MIXED（部分发行版为 ROW）。 - 使用 GTID、多线程并行复制、半同步复制时，一律建议显式设置
binlog_format = ROW。 - 配合
binlog_row_image = minimal可显著减少 ROW 格式日志量（仅记录主键与被修改列），但会削弱 binlog 用于数据恢复的能力。
1.5 异步复制、半同步复制、组复制
异步复制（默认）：主服务器每执行完一条事务命令后写入二进制日志，从服务器通过 IO 线程监控主服务器的二进制日志，每当主服务器 binlog 发生改变，从服务器就启动复制。
- 特点：允许主从之间的数据存在一定延迟，对网络要求不高，保证 master 不受 slave 影响，异步复制使 master 处于性能最优状态。
- 缺点：如果 master 停机，可能存在刚提交的事务 slave 未能及时复制过来的风险（数据丢失）。
半同步复制（Semi-Sync，MySQL 5.5+ 新功能）：master 每操作一个事务，要等待 slave 应用这个事务后返回确认信号，master 才能提交操作成功，保证主从数据的绝对一致。半同步复制等待超时后（默认 10 秒），会自动转换成异步复制。
- MySQL 5.7 引入
rpl_semi_sync_master_wait_point = AFTER_SYNC（无损半同步，默认）与AFTER_COMMIT（5.5/5.6 的行为），AFTER_SYNC 可做到几乎不丢数据。 - MySQL 5.7 还引入
rpl_semi_sync_master_wait_for_slave_count，可要求 N 个从库确认。
组复制（MGR，MySQL 5.7.17+ / 8.0）：基于 Paxos 协议的多副本强一致复制，支持单主模式（Single-Primary）与多主模式（Multi-Primary），内置自动选主与故障检测，无需外部高可用组件。要求所有表必须有主键、只支持 InnoDB、只支持 ROW 格式。MariaDB 无 MGR，对应方案是 Galera Cluster（MariaDB Galera Cluster，多主同步复制）。
选择建议：
| 场景 | 推荐 |
|---|---|
| 读扩展为主、可容忍秒级数据丢失 | 异步复制 |
| 金融/订单类，要求主库故障基本不丢数据 | 半同步复制（AFTER_SYNC）+ 至少 2 个从库 |
| 要求自动选主、强一致、少人工干预 | MGR（MySQL 8.0）或 Galera（MariaDB） |
| 跨机房、网络 RTT 高 | 异步复制或双主（半同步会把网络延迟直接加到主库事务提交耗时上） |
1.6 复制架构类型
1.6.1 一主一从（基础 AB 复制）
master A --> slave B最简单的架构，适合读压力不大、仅需备份与容灾的场景。是所有其他架构的基础。
1.6.2 一主多从
--> slave
master --> slave
--> slave- master 负责写。
- slave 负责读。
适合以读为主的业务，使用多个 slave 分担读压力。但要注意：slave 越多，master 的复制压力就越大（每个从库在主库上占用一个 dump 线程与一份网络带宽）。经验上单主库挂 5~10 个从库为宜，再多应改为级联或分拆集群。
配置方法：有几个从就把从库配置步骤（CHANGE MASTER TO + START SLAVE）做几次即可。
1.6.3 级联复制
-- slave -- slave
master -- slave -- -- slave -- slave
-- slave -- slave- 为了解决一主多从中 master 的复制压力，可以把 slave 作为下一级从机的 master。
- 增加复制级联层次会造成更长的延迟。
- 这种架构并不是很推荐，一般会进行分拆集群。
关键点：中间从库需要开启 binlog，但默认不会把应用自 master 的操作记录到自己的 binlog，需要开启 log-slave-updates=1 参数才能传给第三级从库。
1.6.4 双主互备（Master-Master）
master A <--> master B双主架构最大的优点是两台服务器数据互通，既能写又能读，在任何一台服务器上写数据，另一台都会同步。
思考题：
- 双主 MySQL 复制架构能同时写相同的数据吗？
- 两边可以写不同的数据吗？
答：可以通过业务程序层指定：某些表的写操作全在一端，另一些表的写操作全在另一端，即两边不会同时写相同的表（理想状态，业务复杂时可能存在表联结等情况）。再通过指定 MySQL 复制参数，让一部分表或库从 A 复制到 B，另一部分从 B 复制到 A。也可以用两个库，一个库由 master A 写，另一个库由 master B 写。
双主架构的两种应用
应用一：两边写不关联的数据，互相复制。
应用二：解决一主一从架构里主库挂掉的问题。
问题 1： 一主一从中主挂了，slave 能不能写？如果能写，主修好启动后，slave 写的数据如何传回主？
- 主挂了，slave 应该要写，否则业务只能读不能写。
- 主修好后，slave 在主故障期间写的数据需要想办法传回主。
- 方法一：DBA 人为把 slave 这段时间写的数据找出来导入主（表多、操作多时几乎不可行）。
- 方法二：使用类似 rsync 的同步方法，通过存储层把整个数据目录复制回去。
- 方法三：DBA 人为把 slave 这段时间变更的二进制日志（需确认 slave 开启 binlog）应用到主上。
- 方法四：直接把一主一从改为双主，或反转为一从一主。
问题 2： 把 slave 写的数据传回主需要时间。假设这一小时 slave 创建了 aaa 表，主修好后如果马上接管写，前端立即执行 insert into aaa，而建表操作还没传回来，会出问题。如何解决？
- 方法一：人为控制主暂时不能写，等 slave 数据传回后再写（等待时间长）。
- 方法二：把一主一从改为双主（master A 和 master B）。
master A ---> master B （平时 B 只读，A 挂了才写）
A 挂掉一小时 B 读写一小时
A 修复启动后，把读交给 A，写留给 B问题 3： 改成双主后，B 在 A 故障期间写的数据可以自动回传给 A，但数据量大时回传也需要时间，若在此期间再次出现问题 2 的情况怎么办？
- 方法一：人为控制主不能写，因为是自动回传，等待时间很短，影响不大。
- 方法二：直接把 master B 作为新的写服务器，master A 启动后只做读服务器；等下一次 B 挂了 A 再成为写服务器（前提是两台性能相当）。
双主架构总结
- 应用一：两边写不关联的数据，互相复制。
- 应用二：主库故障切换，解决一主一从中主挂掉后的数据回传和接管问题。
生产建议：双主强烈建议实际只有一端接收写入（另一端只读，通过 VIP 切换），即"逻辑单写双主"，否则极易产生主键冲突与更新丢失。
1.6.5 双主与级联合并（双主多从 + 级联）
master ---- slave -- slave
|
| -- slave
master -- slave -- slave
-- slave优化原则：分。大型结构数据量太大时，需要使用数据切分（水平、垂直），配合 mysql-proxy、amoeba 等代理层。
搭建方法：双主就是把双主架构做一遍，多从就是在新服务器上把从服务做一遍，要加级联就再把级联架构做一遍。
1.6.6 多源复制（Multi-Source Replication）
多源复制仅支持 MySQL 5.7 及以上版本；MariaDB 从 10.0 起支持（语法为
CHANGE MASTER 'name' TO ...，与 MySQL 的FOR CHANNEL写法不同）。
master1（业务库A） --\
>-- slave（汇总备份/报表库）
master2（业务库B） --/- 和主从复制一样，通过 MySQL 的二进制日志（binlog）实现数据同步。
- 适用于有两台不同数据的 MySQL 服务器，需要使用一个 slave 同时进行数据同步备份的场景（多业务库汇总到一个备份库、统一做报表分析）。
- 要求各主库同步的库/表不冲突，否则会互相覆盖。
1.7 复制延迟的产生原因与并行复制
Seconds_Behind_Master 持续增长即为复制延迟。常见原因：
| 原因 | 说明与对策 |
|---|---|
| SQL 线程单线程重放 | 传统复制中主库可以并发几十个连接写入，从库只有一个 SQL 线程串行重放，天生存在吞吐差。对策：开启并行复制 |
| 大事务 | 一条 delete from t where ... 删除百万行，在主库执行 10 分钟，在从库同样要执行且期间无法处理后续事件。对策：拆分为小批次（每次 1000~5000 行）循环删除 |
| 慢查询 / 缺索引 | ROW 格式下从库按行定位，若表无主键或无唯一索引，每一行更新都要全表扫描，延迟呈指数放大。对策：所有表必须有主键 |
| 从库硬件配置弱 | 生产上常把从库配置降级采购，导致重放跟不上。对策：从库配置不低于主库，尤其磁盘 IOPS |
| 从库承担大量读 | 报表大查询占满 IO 和 CPU，SQL 线程被拖慢。对策：读流量分级，报表专用从库 |
| 网络带宽/抖动 | ROW 格式大事务日志量大，跨机房带宽不足会阻塞 IO 线程（此时 Read_Master_Log_Pos 增长慢）。对策：压缩传输 slave_compressed_protocol=1 |
| 从库 double write / 刷盘过严 | 从库 innodb_flush_log_at_trx_commit=1 + sync_binlog=1 时每事务两次 fsync。对策见下文 3.7 |
| DDL 阻塞 | 大表 ALTER TABLE 在从库串行执行，期间全部延迟。对策：用 gh-ost / pt-online-schema-change |
并行复制（多线程复制 MTS）演进：
| 版本 | 能力 |
|---|---|
| MySQL 5.6 | 基于库（DATABASE）的并行：不同 schema 的事务可并行，单库场景无效 |
| MySQL 5.7 | 基于组提交（LOGICAL_CLOCK）的并行：同一组提交的事务在从库可并行，单库也有效 |
| MySQL 8.0 | 基于 WRITESET 的并行（binlog_transaction_dependency_tracking=WRITESET），并行度显著提升，是目前最优选择 |
| MariaDB 10.0+ | slave_parallel_threads + slave_parallel_mode（optimistic / conservative / aggressive / minimal / none） |
配置示例见第三章 3.7 节。
二、主从复制搭建实操
2.1 环境规划与前置准备
本章给出三套源文档的实际环境，可任选一套照做。
环境 A：MySQL 5.6.35 一主两从（CentOS 7.3）
| 项目 | 说明 |
|---|---|
| 操作系统 | CentOS 7.3 |
| MySQL 版本 | 5.6.35-x64.tar.gz |
| 主节点（Master） | 192.168.1.220 |
| 从节点 1（Slave） | 192.168.1.221 |
| 从节点 2（Slave） | 192.168.1.222 |
环境 B：MariaDB 一主一从（yum 安装）
| 角色 | IP |
|---|---|
| master A 主服务器 | 192.168.1.5 |
| slave B 从服务器 | 192.168.1.6 |
环境 C：级联复制三节点
| 角色 | IP | server-id |
|---|---|---|
| master A | 192.168.169.167 | 1 |
| slave B | 192.168.169.168 | 2 |
| slave C | 192.168.169.166 | 3 |
前置准备清单：
- 关闭 iptables / firewalld、selinux、NetworkManager；生产环境不关闭防火墙时，需放行 3306 端口（从库到主库方向）。
- 将主机名和 IP 写入
/etc/hosts，各节点互相绑定。 - 配置静态 IP，配置好 yum 源。
- 时间同步：主从时间必须一致，否则
Seconds_Behind_Master计算不准、排错时日志时间无法对照。详见 08-03-时间同步与Xinetd.md
- 使用 yum rpm 包或 tar 包安装数据库，主从两台机器安装版本一致。复制允许"从库版本高于主库"（低版本主 → 高版本从），但绝不允许高版本主 → 低版本从；跨大版本（如 5.6 → 8.0）复制仅在升级过程中临时使用。
- 主：开启二进制日志，用于记录表的增删改操作并流传给从。
- 主从设置
server-id，集群内唯一（原始文档经验：主的值比从小，便于识别）。 - 主：建立复制账号并授权。
防火墙放行 3306（CentOS 7 firewalld）：
firewall-cmd --permanent --add-port=3306/tcp
firewall-cmd --reloadCentOS 6（iptables）：
iptables -I INPUT -p tcp --dport 3306 -j ACCEPT
service iptables save2.2 安装 MySQL / MariaDB
方式一：二进制包脚本安装（MySQL 5.6.35，三个节点分别执行）
cd mysql-5.6.35-x64/glibc_install/
./mysql_install.sh使 MySQL 环境变量生效：
source /etc/profile.d/mysql方式二：yum 安装 MariaDB（CentOS 7）
yum -y install mariadb*
systemctl start mariadb
mysqladmin -uroot password 123CentOS 6 与 CentOS 7 差异：
- CentOS 6 默认仓库提供的是
mysql-server（MySQL 5.1），服务管理用service mysqld start/chkconfig mysqld on，配置文件同为/etc/my.cnf。- CentOS 7 默认仓库提供的是
mariadb-server（MariaDB 5.5），服务管理用systemctl start mariadb/systemctl enable mariadb。- 二进制包安装的 MySQL 5.6 在两个系统上都可用
service mysql restart（脚本安装会注册/etc/init.d/mysql）。
2.3 主库配置（my.cnf）
编辑 /etc/my.cnf 的 [mysqld] 段。
最小配置（源文档写法）：
[mysqld]
server_id = 1 # 任意自然数，保证每台主机不重复即可
log-bin = mysql-bin # 开启二进制日志MariaDB 环境 主服务器 [mysqld] 段加入：
[mysqld]
log-bin=mysql-bin
server-id=2生产推荐的完整主库配置：
[mysqld]
# ===== 复制身份 =====
server_id = 1 # 集群内唯一，不可重复；重复会导致 IO 线程反复重连报错
log_bin = mysql-bin # 开启 binlog，复制的前提
log_bin_index = mysql-bin.index
binlog_format = ROW # 推荐 ROW；MySQL 8.0 默认即 ROW
# ===== binlog 可靠性与保留 =====
sync_binlog = 1 # 1=每次事务提交都 fsync binlog，最安全（主库必须 1）
# 0=交给 OS 刷盘，性能最好但宕机可能丢 binlog
# N=每 N 个事务 fsync 一次，折中
innodb_flush_log_at_trx_commit = 1 # 与 sync_binlog=1 合称"双 1"，主库标准配置
expire_logs_days = 7 # binlog 保留 7 天后自动清理（MySQL 8.0.11+ 改用
# binlog_expire_logs_seconds = 604800）
max_binlog_size = 512M
binlog_cache_size = 4M
max_allowed_packet = 64M # 单个事件过大时复制会失败，主从都要设置
# ===== 过滤（谨慎使用，见下方警告）=====
# binlog_do_db = aaa
# binlog_ignore_db = mysql
binlog_do_db/binlog_ignore_db的坑（重要）这两个参数在主库上过滤，即"不满足条件的变更根本不写入 binlog"，带来三个严重问题：
- binlog 不完整，无法用于时间点恢复（PITR），一旦误删数据无法通过 binlog 回滚。
- 过滤依据是当前默认库（
USE的库），不是 SQL 中的库名。执行use test; insert into aaa.t1 ...时，若配置了binlog_do_db=aaa，该语句因为当前库是test而不会被记录，从库丢数据；反之use aaa; insert into test.t1 ...会被记录，从库若无 test 库则复制中断。- 无法动态修改，改了必须重启。
*结论：生产环境不要在主库用
binlog_do_db/binlog_ignore_db做过滤，应改用从库侧的 `replicate_` 参数（见 3.6 节）。**
重启数据库使配置生效（三个节点均需执行）：
service mysql restart # 二进制安装的 MySQL
systemctl restart mariadb # CentOS 7 MariaDB
service mysqld restart # CentOS 6 MySQL在主服务器上确认二进制日志已生成：
ll /var/lib/mysql/
# 应能看到 mysql-bin.0000012.4 从库配置（my.cnf）
从节点 192.168.1.221：
server_id = 2
log-bin = mysql-bin
relay_log = mysql-relay-bin从节点 192.168.1.222：
server_id = 3
log-bin = mysql-bin
relay_log = mysql-relay-binMariaDB 环境最小从库配置：
[mysqld]
server-id=3生产推荐的完整从库配置：
[mysqld]
server_id = 2
relay_log = mysql-relay-bin # 中继日志文件名前缀
relay_log_index = mysql-relay-bin.index
log_bin = mysql-bin # 从库也开 binlog，便于提升为主库
log_slave_updates = 1 # 把重放的事件也写入自己的 binlog（级联/切换必需）
read_only = 1 # 非 SUPER 权限用户不可写，防止误写从库
super_read_only = 1 # MySQL 5.7.8+：连 SUPER 用户也不可写（MariaDB 无此参数）
relay_log_recovery = 1 # 从库崩溃重启后自动丢弃损坏的 relay log 并
# 从主库重新拉取，保证 crash-safe
relay_log_purge = 1 # 自动清理已重放完的 relay log
skip_slave_start = 1 # 重启后不自动 START SLAVE，便于人工检查后再启动
master_info_repository = TABLE # MySQL 5.6.5+，crash-safe（8.0 默认）
relay_log_info_repository = TABLE
max_allowed_packet = 64M
binlog_format = ROW版本差异：
super_read_only仅 MySQL 5.7.8 及以上支持，MySQL 5.6 与所有 MariaDB 版本只有read_only。MariaDB 中master_info_repository/relay_log_info_repository不存在，写入会导致启动失败。
2.5 主库创建复制专用账号
配置主从复制必须开启 binlog 二进制日志，数据同步的本质就是从 MySQL 重放主 MySQL 的二进制日志。
写法一（限定来源 IP，最小权限，推荐）：在 192.168.1.220 中创建一个允许从 192.168.1.221 登录的 MySQL 用户，用于执行数据同步复制：
GRANT REPLICATION SLAVE ON *.* TO 'copy'@'192.168.1.221' IDENTIFIED BY 'copy';
FLUSH PRIVILEGES;写法二（源文档 MariaDB 环境写法，建立 aa 账号，密码 111）：
grant super, replication slave on *.* to 'aa'@'%' identified by '111';
flush privileges;写法三（多源复制常用，附带 REPLICATION CLIENT 便于从库查询主库状态）：
grant replication slave, replication client on *.* to aa@'%' identified by 'aaa';权限说明：复制本身只需要
REPLICATION SLAVE。REPLICATION CLIENT用于执行SHOW MASTER STATUS/SHOW SLAVE STATUS，MHA 等工具需要。SUPER权限并非复制必需，生产环境不建议赋予。MySQL 8.0 差异：不支持
GRANT ... IDENTIFIED BY，必须先建用户再授权，且默认认证插件为caching_sha2_password，低版本从库连不上时需改为mysql_native_password：CREATE USER 'copy'@'192.168.1.%' IDENTIFIED WITH mysql_native_password BY 'Copy@123'; GRANT REPLICATION SLAVE ON *.* TO 'copy'@'192.168.1.%'; FLUSH PRIVILEGES;
在从服务器上登录测试账号是否可用：
mysql -u aa -h192.168.1.5 -p111需要能连上，如果连不上则检查授权、防火墙与 bind-address。
2.6 获取主库位点与导出全量数据
第 1 步：主库加读锁，防止新数据写入
flush tables with read lock;
FLUSH TABLES WITH READ LOCK（FTWRL）会阻塞所有写入，必须在业务低峰期执行，且加锁后要尽快完成后续步骤。加锁期间不要退出当前会话，一旦会话断开锁会自动释放，位点与数据就不再一致。
第 2 步：查看主服务器状态，记录 File 与 Position
show master status;+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 475 | | |
+------------------+----------+--------------+------------------+记录返回结果中的 File（如 mysql-bin.000008）与 Position（如 338），下一步从节点配置时需要使用。
相关命令：
show master status;：查看主服务器配置。show slave status;：查看从服务器配置。主服务器上执行此命令结果为空。
对照文件系统中的 binlog 文件名：
ll /var/lib/mysql...
-rw-rw----. 1 mysql mysql 475 9月 19 20:59 mysql-bin.000001
-rw-rw----. 1 mysql mysql 19 9月 19 20:57 mysql-bin.index
...第 3 步：导出全量数据
新建的空库可跳过本步。若主库已有数据（尤其是运行了很久的库），必须先把全量数据导入从库，再从对应位点开始复制。
在另一个终端执行（保持 FTWRL 会话不断开）：
mysqldump -uroot -p \
--all-databases \
--single-transaction \
--master-data=2 \
--routines --triggers --events \
--default-character-set=utf8mb4 \
> /tmp/full_$(date +%F).sql| 参数 | 说明 |
|---|---|
--all-databases |
导出所有库；也可用 --databases db1 db2 只导指定库 |
--single-transaction |
仅对 InnoDB 有效，用一致性快照导出，不锁表。使用该参数时可以不加 FTWRL |
--master-data=2 |
在导出文件中以注释形式写入 CHANGE MASTER TO MASTER_LOG_FILE=..., MASTER_LOG_POS=...，这是获取位点最可靠的方式 |
--master-data=1 |
写入非注释的 CHANGE MASTER 语句，导入时自动执行（不便于人工核对，一般用 2） |
--routines --triggers --events |
一并导出存储过程、函数、触发器、事件（极易遗漏，导致从库缺对象） |
--set-gtid-purged=ON |
GTID 复制时使用（MySQL 5.6+，默认 AUTO） |
查看导出文件中的位点：
head -30 /tmp/full_2026-08-22.sql | grep -i "CHANGE MASTER"
# -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=475;第 4 步：主库解锁
unlock tables;第 5 步：从库导入全量数据
scp /tmp/full_2026-08-22.sql 192.168.1.221:/tmp/
# 从库上
mysql -uroot -p < /tmp/full_2026-08-22.sql导入前从库应保持
skip_slave_start，导入完成后再执行CHANGE MASTER TO。导入mysql库后需flush privileges;或重启，否则账号权限可能不生效；跨版本时建议不导入mysql库。
2.7 CHANGE MASTER TO 完整语法
在从服务器（192.168.1.6 / 192.168.1.221 / 192.168.1.222）上登录数据库：
mysql -p先停止从服务器复制线程（默认即停止状态，显式执行以明确状态）：
stop slave;MySQL 5.5 ~ 5.7 / 8.0（兼容）/ MariaDB 全版本语法：
CHANGE MASTER TO
MASTER_HOST = '192.168.1.220',
MASTER_PORT = 3306,
MASTER_USER = 'copy',
MASTER_PASSWORD = 'copy',
MASTER_LOG_FILE = 'mysql-bin.000008',
MASTER_LOG_POS = 338;源文档等价写法（MariaDB 环境）：
change master to
master_user='aa',
master_password='111',
master_host='192.168.1.5',
master_port=3306,
master_log_file='mysql-bin.000001',
master_log_pos=338;| 参数 | 说明 |
|---|---|
master_host |
主服务器 IP |
master_port |
主服务器端口（不能加引号，是数字） |
master_user |
主服务器上建立的复制账号 |
master_password |
复制账号密码 |
master_log_file |
主上查到的 binlog 文件名（加引号） |
master_log_pos |
主上查到的 binlog 位置号（不加引号） |
master_connect_retry |
重连间隔，默认 60 秒 |
master_retry_count |
重连次数，默认 86400 |
master_delay |
延时复制秒数，见 3.9 节 |
master_auto_position=1 |
GTID 复制专用，见 3.1 节 |
master_ssl=1 |
启用 SSL 加密复制通道，配合 master_ssl_ca / master_ssl_cert / master_ssl_key |
for channel 'name' |
多源复制指定通道，见 3.5 节 |
MySQL 8.0.23+ 新语法（旧语法虽仍可用但已标记为 deprecated）：
CHANGE REPLICATION SOURCE TO
SOURCE_HOST = '192.168.1.220',
SOURCE_PORT = 3306,
SOURCE_USER = 'copy',
SOURCE_PASSWORD = 'Copy@123',
SOURCE_LOG_FILE = 'mysql-bin.000008',
SOURCE_LOG_POS = 338,
GET_SOURCE_PUBLIC_KEY = 1;对应的线程控制命令也有新写法：
| 旧语法（5.x / MariaDB） | MySQL 8.0.22+ 新语法 |
|---|---|
START SLAVE |
START REPLICA |
STOP SLAVE |
STOP REPLICA |
SHOW SLAVE STATUS |
SHOW REPLICA STATUS |
RESET SLAVE ALL |
RESET REPLICA ALL |
slave_parallel_workers |
replica_parallel_workers |
2.8 启动复制并解读 SHOW SLAVE STATUS
启动 slave 线程：
START SLAVE;
SHOW SLAVE STATUS\G也可以单独启停某一个线程（半同步、跳错时常用）：
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;
STOP SLAVE SQL_THREAD;
START SLAVE SQL_THREAD;当输出中同时满足以下两项即为正常：
Slave_IO_Running: YES
Slave_SQL_Running: YES完整输出示例：
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.169.161
Master_User: test
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 475
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 230
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 338
Relay_Log_Space: 293
Seconds_Behind_Master: 0
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Master_Server_Id: 2关键字段逐项解读：
| 字段 | 含义与排错要点 |
|---|---|
Slave_IO_State |
IO 线程当前状态。正常为 Waiting for master to send event；显示 Connecting to master 说明连不上主库（查网络/防火墙/账号） |
Slave_IO_Running |
IO 线程是否运行。Yes 正常；No 表示连接主库或写 relay log 失败，看 Last_IO_Error；Connecting 表示正在重连 |
Slave_SQL_Running |
SQL 线程是否运行。Yes 正常；No 表示重放 SQL 报错中断，看 Last_SQL_Error。两个都为 Yes 才表示主从连接成功 |
Master_Log_File |
IO 线程当前正在读取的主库 binlog 文件名 |
Read_Master_Log_Pos |
IO 线程已读取到的主库 binlog 位置。与主库 SHOW MASTER STATUS 的 Position 对比，差值大说明网络/IO 线程慢 |
Relay_Log_File / Relay_Log_Pos |
SQL 线程正在执行的 relay log 文件与位置 |
Relay_Master_Log_File |
SQL 线程当前执行的事件对应的主库 binlog 文件名 |
Exec_Master_Log_Pos |
SQL 线程已执行完的主库 binlog 位置。Read_Master_Log_Pos - Exec_Master_Log_Pos 差值大说明SQL 线程重放慢（延迟在重放环节，考虑并行复制） |
Seconds_Behind_Master |
延迟秒数。0 表示无延迟，NULL 表示复制已中断（或 IO 线程未运行）。注意它是"当前重放事件的时间戳与从库当前时间之差"，主从时钟不同步或大事务时会失真，精确测量请用 pt-heartbeat |
Last_Errno / Last_Error |
最近一次错误（等同于 Last_SQL_Errno / Last_SQL_Error） |
Last_IO_Errno / Last_IO_Error |
IO 线程错误。常见 1236（binlog 位点在主库已不存在）、1045（账号密码错）、2003（连不上主库）、1593（server_id 重复） |
Last_SQL_Errno / Last_SQL_Error |
SQL 线程错误。常见 1062（主键冲突）、1032（记录找不到）、1146（表不存在）、1007（库已存在） |
Skip_Counter |
当前 sql_slave_skip_counter 的值 |
Relay_Log_Space |
所有 relay log 占用的总字节数，持续增大说明 SQL 线程跟不上 IO 线程 |
Master_Server_Id |
主库的 server_id，可用于确认连的是哪台主库 |
Retrieved_Gtid_Set |
GTID 模式下，IO 线程已接收的 GTID 集合 |
Executed_Gtid_Set |
GTID 模式下，从库已执行的全部 GTID 集合（包含自身产生的） |
Auto_Position |
1 表示使用 GTID 自动定位，0 表示传统位点复制 |
Replicate_Do_DB / Replicate_Ignore_DB 等 |
当前生效的复制过滤规则，见 3.6 节 |
SQL_Delay / SQL_Remaining_Delay |
延时复制设置值与剩余等待秒数 |
Slave_SQL_Running_State |
SQL 线程细粒度状态，如 Slave has read all relay log; waiting for more updates |
Channel_Name |
多源复制的通道名 |
MariaDB 10.x 差异：多源复制下字段名为 Connection_name，且提供 SHOW ALL SLAVES STATUS\G 一次查看所有通道。
2.9 验证复制
在主节点 192.168.1.220 上创建一个数据库：
CREATE DATABASE jwis;在从节点 192.168.1.221（以及 192.168.1.222）上查看是否同步：
SHOW DATABASES;若从节点结果中出现 jwis 数据库，则主从复制配置成功。
更完整的验证：在主服务器上创建库、表并插入数据，在从服务器查看是否同步。
-- 主库
CREATE DATABASE aaa;
USE aaa;
CREATE TABLE a1(id INT PRIMARY KEY, name VARCHAR(20));
INSERT INTO a1 VALUES(1,'master-test');
-- 从库
SELECT * FROM aaa.a1;反向验证（复制方向的单向性）：在从库上插入一条数据，主库查询不到。
2.10 STOP SLAVE / RESET SLAVE 的使用与区别
| 命令 | 作用 | 是否清除位点信息 |
|---|---|---|
STOP SLAVE; |
停止 IO 与 SQL 两个线程，位点信息保留，START SLAVE 可从原位置继续 |
否 |
STOP SLAVE IO_THREAD; |
只停 IO 线程，SQL 线程继续把已有 relay log 重放完（平滑切换主库前的标准动作） | 否 |
RESET SLAVE; |
停止复制并删除所有 relay log、重建新的 relay log，清空 relay-log.info；但保留 master.info 中的连接信息（MySQL 5.6+ 中 MASTER_HOST 等在内存中仍保留到重启） |
部分 |
RESET SLAVE ALL; |
在 RESET SLAVE 基础上，彻底清除所有主库连接信息（master.info / 表记录）与通道定义，从库变回"从未配置过复制"的状态 |
是（完全） |
RESET MASTER; |
在主库执行，删除所有 binlog 并重新从 000001 开始编号，同时清空 GTID 执行集合。危险操作，已有从库会立即报 1236 错误 |
重做复制集群只需在从上重新执行：
stop slave;
change master to ...;
start slave;彻底解除主从关系（例如把从库转为独立实例）：
STOP SLAVE;
RESET SLAVE ALL;
SET GLOBAL read_only = 0;
SET GLOBAL super_read_only = 0; -- MySQL 5.7.8+
-- 同时删除 my.cnf 中的 read_only / relay_log 等配置2.11 复制故障与重试行为
模拟问题：在从上创建一个库，然后在主上也创建同名库，会造成冲突（1007 错误）导致复制中断。
重启行为测试：
- 从库重启后，查看状态仍然连接，无影响（因为位点已持久化到 master.info）。
- 主库重启后，从库状态显示重试时间为 60 秒，等待 60 秒后自动重新连接。
- 若要修改这个 60 秒，可以编辑从服务器
master.info文件中的 60 为 30（rpm 版测试可能有问题）。推荐做法是用 SQL 修改，避免直接编辑文件：
STOP SLAVE;
CHANGE MASTER TO MASTER_CONNECT_RETRY = 30, MASTER_RETRY_COUNT = 86400;
START SLAVE;2.12 级联复制搭建
master A --> slave B --> slave C中间从库 slave B 也要开启二进制日志，但默认不会把应用自 master 的操作记录到自己的 binlog，需要额外开启参数才能传给第三级从库。
在 slave B 的 /etc/my.cnf 中加入：
max_allowed_packet=20M
log-slave-updates=1| 参数 | 说明 |
|---|---|
max_allowed_packet=20M |
应用日志大小 |
log-slave-updates=1 |
允许记录上一级主服务器的应用日志，使其可被下一级从库读取 |
在服务器中查看参数：
show variables like '%max_allowed%';
show variables like '%log_slave%';各节点配置汇总：
master A (192.168.169.167) slave B (192.168.169.168) slave C (192.168.169.166)
server-id=1 server-id=2 server-id=3
log-bin=mysql-bin log-bin=mysql-bin
log-slave-updates=1日志应用流程：
bin-log --> relay-bin-log --> relay-bin-log- master A 创建库时写入 bin-log。
- slave B 读取 master A 的 bin-log，写入本地 relay-bin-log。
- slave C 读取 slave B 的 bin-log 和 relay-bin-log（因为 slave B 配置了
log-slave-updates=1）。
2.13 双主搭建
把一主一从反过来再做一遍：在原 slave 上也开启二进制日志，反向配置主从关系。操作过程中注意保证两边数据一致。
做好的架构中，只要两边不同时操作相同的数据，就不会出现复制不一致问题；也可以在业务程序层将一部分写操作分配到 master A，另一部分分配到 master B。
自增主键冲突的解决见 3.3 节。
三、复制进阶
3.1 基于 GTID 的复制
GTID（Global Transaction Identifier，全局事务标识符）为 MySQL 5.6+ / MariaDB 10.0+ 支持。MySQL 与 MariaDB 的 GTID 实现互不兼容，不能混用。
GTID 概念与格式
GTID 是每个事务在整个复制拓扑中的唯一标识：
GTID = source_id : transaction_id
例：3E11FA47-71CA-11E1-9E33-C80AA9429562:23
└─────── 主库的 server_uuid ───────┘ └第 23 个事务GTID 集合（GTID Set）用区间表示：
3E11FA47-71CA-11E1-9E33-C80AA9429562:1-23:25-30,
a1b2c3d4-....-....:1-100MariaDB 的 GTID 格式不同，为 domain_id-server_id-sequence_number，例如 0-1-1234。
查看相关变量：
SHOW GLOBAL VARIABLES LIKE 'gtid%';
SELECT @@server_uuid;
SHOW MASTER STATUS; -- 输出多一列 Executed_Gtid_Set配置参数（主从都要配置，且必须一致）
master 端：
gtid-mode=on
enforce-gtid-consistency=1
skip_slave_start=1
log-bin=mysql-bin
server-id=1slave 端：
server-id=4
gtid-mode=on
binlog_gtid_simple_recovery=1
enforce_gtid_consistency=1
master_info_repository=TABLE
relay_log_info_repository=TABLE
replicate_ignore_db=mysql
skip_slave_start=1| 参数 | 说明 |
|---|---|
gtid-mode=on |
开启 GTID 模式（取值 OFF / OFF_PERMISSIVE / ON_PERMISSIVE / ON，在线切换需逐级过渡） |
enforce-gtid-consistency=1 |
强制 GTID 一致性，禁止 CREATE TABLE ... SELECT、临时表事务内 DDL 等不安全语句 |
server-id |
主从集群中唯一的 ID |
binlog_gtid_simple_recovery=1 |
崩溃恢复时只扫描首尾两个 binlog 计算 GTID 集合，加快启动（5.7 默认 ON） |
master_info_repository=TABLE |
将 master-info 存储方式从文件改为表 |
relay_log_info_repository=TABLE |
将 relay-info 存储方式从文件改为表 |
replicate_ignore_db=mysql |
忽略 mysql 库的同步 |
log_slave_updates=1 |
MySQL 5.6 GTID 强制要求从库开启；5.7+ 不再强制但仍建议 |
完整配置文件示例（多源复制场景）：
Master1 和 Master2：
#GTID
gtid-mode = on
binlog_gtid_simple_recovery=1
enforce_gtid_consistency=1
binlog_format = row
skip_slave_start = 1
log-bin = /data/mysql/mysql_3307/logs/binlog/mysql-binSlave：
#binlog
binlog_format = row
server-id = 1343307
log-bin = /data/mysql/mysql_3307/logs/binlog/mysql-bin
#GTID
gtid-mode = on
binlog_gtid_simple_recovery=1
enforce_gtid_consistency=1
#修改 MySQL 存储 master-info 和 relay-info 的方式，即从文件存储改为表存储
master_info_repository=TABLE
relay_log_info_repository=TABLE
replicate_ignore_db=mysql
skip_slave_start = 1用 MASTER_AUTO_POSITION=1 搭建
不需要指定 MASTER_LOG_FILE 和 MASTER_LOG_POS：
STOP SLAVE;
CHANGE MASTER TO
MASTER_HOST='192.168.1.220',
MASTER_PORT=3306,
MASTER_USER='copy',
MASTER_PASSWORD='copy',
MASTER_AUTO_POSITION=1;
START SLAVE;
SHOW SLAVE STATUS\GMySQL 8.0.23+ 新语法：
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='192.168.1.220', SOURCE_USER='copy',
SOURCE_PASSWORD='Copy@123', SOURCE_AUTO_POSITION=1;若从库是用带 --set-gtid-purged=ON 的 mysqldump 恢复的，导入文件中已包含 SET @@GLOBAL.GTID_PURGED='...'；手工设置方法：
RESET MASTER; -- 清空从库自身 GTID 执行集合
SET @@GLOBAL.GTID_PURGED='3E11FA47-71CA-11E1-9E33-C80AA9429562:1-1000';GTID 相比位点复制的优势
| 维度 | 位点复制 | GTID 复制 |
|---|---|---|
| 搭建 | 需人工获取并输入 File + Position，易错 | MASTER_AUTO_POSITION=1 自动协商 |
| 主从切换 | 其余从库需要在新主库上换算出等价位点（极易出错，常用 mysqlbinlog 人工比对） | 直接 CHANGE MASTER TO ... AUTO_POSITION=1 指向新主，自动续传 |
| 幂等性 | 从库不知道某事务是否已执行，重复执行会冲突 | 已执行的 GTID 会被自动跳过，天然幂等 |
| 数据一致性判断 | 只能靠位点比对，不同主库位点不可比 | 比较 Executed_Gtid_Set 即可判断哪台从库数据最全（选新主的依据） |
| 跳过错误 | sql_slave_skip_counter |
注入空事务（更精确，见下） |
| 限制 | 少 | 不支持 CREATE TABLE ... SELECT、事务中混用事务与非事务表、临时表 DDL；不能用 sql_slave_skip_counter |
GTID 下跳过错误事务的正确方法：注入空事务
GTID 模式下执行 SET GLOBAL sql_slave_skip_counter=1 会直接报错。正确做法是把出错的那个 GTID 在从库上"执行成一个空事务"，让从库认为它已完成：
-- 1. 从 SHOW SLAVE STATUS\G 的 Last_SQL_Error 中找出出错的 GTID
SHOW SLAVE STATUS\G
-- Last_SQL_Error: Could not execute Write_rows event on table aaa.a1;
-- Duplicate entry '1' for key 'PRIMARY', ...
-- the event's master log mysql-bin.000003, end_log_pos 1234
-- 或用 performance_schema 精确定位（MySQL 5.7+）
SELECT * FROM performance_schema.replication_applier_status_by_worker\G
-- 2. 停止 SQL 线程
STOP SLAVE SQL_THREAD;
-- 3. 注入空事务（GTID 换成实际出错的那个）
SET GTID_NEXT = '3E11FA47-71CA-11E1-9E33-C80AA9429562:1005';
BEGIN; COMMIT;
SET GTID_NEXT = 'AUTOMATIC';
-- 4. 重启 SQL 线程
START SLAVE SQL_THREAD;
SHOW SLAVE STATUS\G若要连续跳过多个事务，需要对每一个 GTID 重复第 3 步。跳过事务意味着主从数据已不一致，事后必须用 pt-table-checksum 校验（见 5.4 节）。
MySQL 8.0 提供了更方便的
SET GLOBAL sql_replica_skip_counter（非 GTID）与直接的错误跳过配置slave_skip_errors=1062,1032（仅测试环境使用）。
3.2 半同步复制配置
环境要求：已搭建好 MySQL 主从异步复制。
3.2.1 master 安装半同步插件
install plugin rpl_semi_sync_master soname 'semisync_master.so';删除插件：
uninstall plugin rpl_semi_sync_master;查看参数：
show global variables like 'rpl_semi_sync%';| 变量 | 默认值 | 说明 |
|---|---|---|
rpl_semi_sync_master_enabled |
OFF | 是否启用 master 半同步复制 |
rpl_semi_sync_master_timeout |
10000 | 主等待从返回确认的超时时间（毫秒，默认 10 秒） |
rpl_semi_sync_master_trace_level |
32 | 监控等级，32 代表等待 |
rpl_semi_sync_master_wait_no_slave |
ON | 是否每个事务提交都等待 slave 信号 |
MySQL 5.7 新增：
| 变量 | 默认值 | 说明 |
|---|---|---|
rpl_semi_sync_master_wait_point |
AFTER_SYNC | AFTER_SYNC=无损半同步（写 binlog 后、引擎提交前等待 ACK，基本不丢数据）；AFTER_COMMIT=5.5/5.6 行为（引擎提交后等待，主库宕机可能出现"幻读已提交但从库无该事务"） |
rpl_semi_sync_master_wait_for_slave_count |
1 | 需要几个从库确认才算成功 |
3.2.2 slave 安装插件
install plugin rpl_semi_sync_slave soname 'semisync_slave.so';删除插件：
uninstall plugin rpl_semi_sync_slave;查看参数：
show global variables like 'rpl_semi_sync%';| 变量 | 默认值 | 说明 |
|---|---|---|
rpl_semi_sync_slave_enabled |
OFF | 是否启用 slave 半同步复制 |
rpl_semi_sync_slave_trace_level |
32 | 监控等级，32 代表等待 |
MySQL 8.0.26+ 插件与变量改名为
rpl_semi_sync_source_*/rpl_semi_sync_replica_*，插件库文件为semisync_source.so/semisync_replica.so。MariaDB 10.3+ 半同步已内置（无需装插件），参数为rpl_semi_sync_master_enabled等，直接 set 即可。
3.2.3 master 开启半同步复制
set global rpl_semi_sync_master_enabled=on;查看 master 状态：
show global status like 'rpl_semi_sync%';| 状态变量 | 说明 |
|---|---|
Rpl_semi_sync_master_clients |
启用半同步复制的从服务器数量 |
Rpl_semi_sync_master_net_avg_wait_time |
master 等待 slave 回复确认的平均等待时间（毫秒） |
Rpl_semi_sync_master_net_wait_time |
master 总等待时间（毫秒） |
Rpl_semi_sync_master_net_waits |
master 等待 slave 回复的总次数 |
Rpl_semi_sync_master_no_times |
master 关闭半同步复制的次数 |
Rpl_semi_sync_master_no_tx |
master 等待超时的次数 |
Rpl_semi_sync_master_status |
标记 master 当前是否为半同步复制状态 |
Rpl_semi_sync_master_tx_avg_wait_time |
master 处理每个事务的平均等待时间 |
Rpl_semi_sync_master_tx_wait_time |
master 处理事务等待的总时间 |
Rpl_semi_sync_master_tx_waits |
master 事务等待次数 |
Rpl_semi_sync_master_yes_tx |
事务成功从 slave 返回确认信号的次数 |
3.2.4 slave 开启半同步复制
set global rpl_semi_sync_slave_enabled=on;
stop slave IO_THREAD;
start slave IO_THREAD;必须重启 IO 线程，因为半同步的协商是在 IO 线程与主库建立连接时完成的。
查看 slave 状态：
show global status like 'rpl_semi_sync%';+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+3.2.5 测试半同步
master 上往 aaa.a1 表插入数据：
insert into aaa.a1 values (3);再次查看半同步状态，Rpl_semi_sync_master_yes_tx 会增加，Rpl_semi_sync_master_net_avg_wait_time 会有值。
3.2.6 模拟 slave 故障与超时降级
停掉 slave 的 MySQL：
/etc/init.d/mysql56 stopmaster 上插入数据：
insert into aaa.a1 values (4);
-- Query OK, 1 row affected (10.00 sec) -- 等待 10 秒超时
insert into aaa.a1 values (5);
-- Query OK, 1 row affected (0.01 sec) -- 自动转为异步模式此时查看状态：
Rpl_semi_sync_master_no_times: 1 -- 半同步停止过一次
Rpl_semi_sync_master_no_tx: 3 -- 3 个同步超时事务
Rpl_semi_sync_master_status: OFF -- 半同步已关闭重新启动 slave 后，半同步不会自动恢复，需要手动重新开启：
set global rpl_semi_sync_slave_enabled=on;
stop slave IO_THREAD;
start slave IO_THREAD;slave 启动后，故障期间的数据仍会自动复制过来，数据恢复一致。
3.2.7 永久生效
一旦 master 或 slave 的 MySQL 服务停止或重启，通过 set global 设置的半同步就会失效。要让其永久生效，需要将相关参数写入配置文件 my.cnf：
master 的 my.cnf：
[mysqld]
plugin_load_add = "rpl_semi_sync_master=semisync_master.so"
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_master_timeout = 3000 # 3 秒，比默认 10 秒更快降级
rpl_semi_sync_master_wait_point = AFTER_SYNC # MySQL 5.7+
rpl_semi_sync_master_wait_no_slave = ONslave 的 my.cnf：
[mysqld]
plugin_load_add = "rpl_semi_sync_slave=semisync_slave.so"
rpl_semi_sync_slave_enabled = 1双主或需要互换角色的场景，两台机器都要同时装 master 和 slave 两个插件、都开启两个 enabled 参数。
3.3 双主复制与自增冲突的解决
双主架构下两端都可能写入同一张自增表，会产生主键冲突。解决方案：让两端产生的自增值天然不重叠。
master A 的 my.cnf：
[mysqld]
server-id = 1
log-bin = mysql-bin
log_slave_updates = 1
auto_increment_increment = 2 # 自增步长 = 主库数量
auto_increment_offset = 1 # 起始偏移，A 生成 1,3,5,7...（奇数）master B 的 my.cnf：
[mysqld]
server-id = 2
log-bin = mysql-bin
log_slave_updates = 1
auto_increment_increment = 2
auto_increment_offset = 2 # B 生成 2,4,6,8...（偶数）验证：
SHOW VARIABLES LIKE 'auto_inc%';三主环形复制时设为 increment=3，offset 分别为 1、2、3。
注意：
- 该方案只解决自增主键冲突，不解决业务唯一键冲突与同一行的更新丢失。
- 双主仍应保证同一时刻只有一端接受写入（VIP 漂移），
auto_increment_offset只是故障切换瞬间的兜底。- 现代方案更推荐用分布式 ID（雪花算法 / UUID / 号段发号器）替代数据库自增。
双主还需注意 replicate-same-server-id=0（默认），否则事件会在两台之间无限循环。
3.4 级联复制与 log_slave_updates
见 2.12 节的搭建步骤。核心原理再强调一次：
- 从库的 SQL 线程重放事件后，默认不写入自己的 binlog。
- 开启
log_slave_updates=1后，重放的事件会以从库自身 server_id 重新写入本地 binlog，这样第三级从库才能读到。 - 代价：中间从库多一次写 binlog 的 IO 开销，且延迟逐级累加。
- 除级联外，以下场景也必须开
log_slave_updates：MySQL 5.6 的 GTID 复制、需要提升为新主库的从库、需要在从库上做 binlog 备份的场景。
3.5 多源复制（FOR CHANNEL 语法）
3.5.1 主库创建复制账号
-- Master1
grant replication slave,replication client on *.* to aa@'%' identified by 'aaa';
-- Master2
grant replication slave,replication client on *.* to bb@'%' identified by 'bbb';3.5.2 从库启动多个通道
change master to master_host='1.1.1.4',master_port=3308,master_user='aa',master_password='aaa',master_auto_position=1 for channel 'Master_1';
change master to master_host='1.1.1.7',master_port=3308,master_user='bb',master_password='bbb',master_auto_position=1 for channel 'Master_2';
start slave;也可以单独启动某个 channel：
start slave for channel 'Master_1';
stop slave for channel 'Master_2';
reset slave all for channel 'Master_2'; -- 删除某个通道3.5.3 查看 slave 状态
查看所有 channel：
show slave status\G查看单个 channel：
show slave status for channel 'Master_1'\G关键字段（两个 channel 均需满足）：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Ignore_DB: mysql
Auto_Position: 1
Channel_Name: master_132通过 performance_schema 查看同步状态：
select * from performance_schema.replication_connection_status\GCHANNEL_NAME: master_132
SERVICE_STATE: ON
LAST_ERROR_NUMBER: 0
CHANNEL_NAME: master_133
SERVICE_STATE: ON
LAST_ERROR_NUMBER: 0其他有用的 performance_schema 表：
select * from performance_schema.replication_applier_status_by_worker\G -- 并行复制各 worker 状态
select * from performance_schema.replication_applier_status\G
select * from performance_schema.replication_connection_configuration\G3.5.4 数据同步检查
Master1 建库建表并插入数据：
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `sampson_132` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
use sampson_132
create table t_132(id int primary key auto_increment,name varchar(20) not null);
insert into t_132(id,name) values(1,'132'),(2,'132'),(3,'132');
select * from t_132;+----+------+
| id | name |
+----+------+
| 1 | 132 |
| 2 | 132 |
| 3 | 132 |
+----+------+Master2 建库建表并插入数据：
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `sampson_133` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
use sampson_133
create table t_133(id int primary key auto_increment,name varchar(20) not null);
insert into t_133(id,name) values(1,'133'),(2,'133'),(3,'133');Slave 查看数据是否同步：
show databases;+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sampson_132 |
| sampson_133 |
| sys |
+--------------------+select * from sampson_132.t_132;
select * from sampson_133.t_133;同步成功后，Retrieved_Gtid_Set 和 Executed_Gtid_Set 会记录已复制的 GTID 事务集合。
MariaDB 10.x 多源复制语法差异：
CHANGE MASTER 'Master_1' TO master_host='1.1.1.4', master_user='aa', master_password='aaa', master_use_gtid=slave_pos; START SLAVE 'Master_1'; SHOW SLAVE 'Master_1' STATUS\G SHOW ALL SLAVES STATUS\G
3.6 过滤复制（基于库表的复制）
复制库、表和表内容的参数，可通过 show slave status\G 查看：
| 参数 | 说明 |
|---|---|
Replicate_Do_DB |
接受哪个库的复制（必须在指定库下操作才能复制，包括表结构和表数据） |
Replicate_Ignore_DB |
忽略哪个库的复制 |
Replicate_Do_Table |
接受哪个表的复制（只能指定某库下的某个表内容更新，如 aaa.a3） |
Replicate_Ignore_Table |
忽略哪个表的复制 |
Replicate_Wild_Do_Table |
通配符复制哪些表（接收某库下所有表内容的修改，如 aaa.%） |
Replicate_Wild_Ignore_Table |
通配符忽略哪些表 |
3.6.1 实例：只复制指定库表
-
先把两台机器做成 AB 复制架构，此时 master 上的创建库、表或插入数据等动作都会复制到 slave。
-
业务需求：把 master 的
aaa库下的表复制到 slave，其他库不要。 -
在 slave B 的
/etc/my.cnf的[mysqld]中加入：
replicate_do_db=aaa- 重启 slave B 并测试：
-- master 上操作
create database aaa;
create table aaa.a1(id int(1));
insert into aaa.a1 values(1);此时 slave 上有 aaa 库和 a1 表，但没有数据。因为 replicate_do_db 需要在指定库下操作才会复制数据：
-- master 上操作
use aaa;
insert into aaa.a1 values(2);这时 slave 上才能查到数据。
- 若要复制该库下所有表的数据，加入：
replicate_wild_do_table=aaa.%- 重启 slave B 并测试：
-- master 上操作
create table aaa.a2(id int(1));
insert into aaa.a2 values(1);
insert into aaa.a1 values(2);slave 上 aaa.a1、aaa.a2 表结构和数据均同步。
3.6.2 在主库过滤 vs 在从库过滤的取舍
| 维度 | 主库过滤（binlog_do_db / binlog_ignore_db） |
从库过滤（replicate_*） |
|---|---|---|
| 过滤位置 | 事件根本不写入 binlog | 事件传到从库后由 SQL 线程丢弃 |
| binlog 完整性 | 破坏，无法做时间点恢复 | 完整，可用于 PITR |
| 网络带宽 | 节省（不传输） | 不节省（照样传输后丢弃） |
| 多个从库需要不同规则 | 做不到（binlog 只有一份） | 每个从库可有各自规则 |
| 动态修改 | 不支持，必须重启 | MySQL 5.7+ 支持 CHANGE REPLICATION FILTER（仍需 STOP SLAVE） |
*_do_db / *_ignore_db 的判定依据 |
当前默认库（USE 的库） |
同样是当前默认库（ROW 格式下则按事件中的实际库名，行为更符合直觉） |
| 生产建议 | 不要用 | 推荐，且优先用 replicate_wild_do_table / replicate_wild_ignore_table（按实际库表名匹配，不受 USE 影响） |
推荐配置（避开 *_do_db 的默认库陷阱）：
[mysqld]
replicate_wild_do_table = aaa.%
replicate_wild_do_table = bbb.%
replicate_wild_ignore_table = mysql.%
replicate_wild_ignore_table = %.tmp_%MySQL 5.7+ 动态修改（无需重启）：
STOP SLAVE SQL_THREAD;
CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('aaa.%','bbb.%');
CHANGE REPLICATION FILTER REPLICATE_IGNORE_DB = (mysql, sys);
START SLAVE SQL_THREAD;
SHOW SLAVE STATUS\G -- 确认 Replicate_Wild_Do_Table 已变化过滤复制 + GTID 组合使用有坑：被过滤掉的事务其 GTID 仍会被记入
Executed_Gtid_Set（视为已执行），因此该从库不能再作为其他从库的完整数据源，也不宜提升为主库。
3.7 复制延迟的监控与优化
3.7.1 用 pt-heartbeat 精确测量延迟
Seconds_Behind_Master 不可靠（网络中断时可能显示 0、大事务时跳变）。生产标准做法是 Percona Toolkit 的 pt-heartbeat：原理是主库每秒往一张心跳表写入当前时间戳，从库读取该时间戳与本地时间做差。
# 安装
yum -y install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
yum -y install percona-toolkit
# 主库：创建心跳表并每秒更新（守护进程）
pt-heartbeat --user=root --password=xxx --host=192.168.1.220 \
--create-table --database=percona --table=heartbeat \
--update --daemonize --interval=1
# 从库：持续监控延迟（单位秒）
pt-heartbeat --user=root --password=xxx --host=192.168.1.221 \
--database=percona --table=heartbeat \
--monitor --print-master-server-id
# 只看一次
pt-heartbeat --database=percona --table=heartbeat --check --host=192.168.1.221配套监控项：Slave_IO_Running、Slave_SQL_Running、Seconds_Behind_Master、Relay_Log_Space、Read_Master_Log_Pos - Exec_Master_Log_Pos 差值、pt-heartbeat 延迟值。任一异常应告警。
3.7.2 开启并行复制
MySQL 5.7 配置（从库 my.cnf）：
[mysqld]
slave_parallel_type = LOGICAL_CLOCK # DATABASE（5.6 按库）/ LOGICAL_CLOCK（按组提交）
slave_parallel_workers = 8 # 一般设为 CPU 核数，4~16
slave_preserve_commit_order = 1 # 保持从库提交顺序与主库一致（要求 log_slave_updates=1）
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = 1MySQL 8.0 追加（并行度更高）：
[mysqld]
replica_parallel_type = LOGICAL_CLOCK
replica_parallel_workers = 16
replica_preserve_commit_order = 1
# 在【主库】上设置，让 binlog 记录 writeset 依赖信息
binlog_transaction_dependency_tracking = WRITESET
transaction_write_set_extraction = XXHASH64主库侧配合参数（增大组提交的事务数，提升从库并行度）：
[mysqld]
binlog_group_commit_sync_delay = 100 # 微秒，人为延迟凑批
binlog_group_commit_sync_no_delay_count = 20MariaDB 10.x 配置：
[mysqld]
slave_parallel_threads = 8
slave_parallel_mode = optimistic # none / minimal / conservative / optimistic / aggressive
slave_domain_parallel_threads = 4生效需重启复制：
STOP SLAVE; START SLAVE;
SHOW PROCESSLIST; -- 可看到多个 "Waiting for an event from Coordinator" 的 worker 线程
SELECT * FROM performance_schema.replication_applier_status_by_worker\G3.7.3 从库刷盘参数的取舍
| 参数 | 主库（数据安全优先） | 从库（复制吞吐优先） | 说明 |
|---|---|---|---|
innodb_flush_log_at_trx_commit |
1 |
2（或 0） |
1=每事务 fsync redo，最安全；2=每事务写 OS 缓存、每秒 fsync，MySQL 进程崩溃不丢、主机断电丢 1 秒；0=每秒写+刷，性能最好风险最高 |
sync_binlog |
1 |
0（或 1000） |
1=每事务 fsync binlog；0=交给 OS |
取舍原则：
- 从库设
innodb_flush_log_at_trx_commit=2+sync_binlog=0，可获得 2~5 倍重放吞吐提升，代价是从库主机断电时数据可能损坏——但从库数据本可从主库重新拉取，因此这个代价通常可接受。 - 前提是开启
relay_log_recovery=1与master_info_repository=TABLE，否则从库崩溃后位点信息不可靠，只能重做整个从库。 - 如果该从库承担了"随时可提升为主库"的职责（半同步的 ACK 提供者、MHA 候选主），则必须与主库一样保持"双 1"，否则切换后可能丢数据。
其他优化参数：
[mysqld]
slave_compressed_protocol = 1 # 压缩复制流量，跨机房带宽不足时开启（消耗 CPU）
slave_net_timeout = 60 # IO 线程多久收不到数据判定为断开
slave_skip_errors = OFF # 生产必须 OFF，绝不能用它掩盖不一致
innodb_buffer_pool_size = 物理内存的 50%~70%3.8 主从切换
3.8.1 计划内切换（主库不宕机，如硬件维护、版本升级）
以 master A（192.168.1.220）切换到 slave B（192.168.1.221）为例：
-- 【1】主库 A：禁止新的写入，等待从库追平
SET GLOBAL read_only = 1;
SET GLOBAL super_read_only = 1; -- MySQL 5.7.8+
-- 若有连接池长连接，需 KILL 掉残留的写连接
SHOW PROCESSLIST;
-- FLUSH TABLES WITH READ LOCK; -- 更严格的做法
-- 【2】主库 A：记录最终位点
SHOW MASTER STATUS; -- 记下 File / Position / Executed_Gtid_Set
-- 【3】从库 B：确认已追平（反复执行直到位点完全一致）
SHOW SLAVE STATUS\G
-- 要求：Read_Master_Log_Pos == Exec_Master_Log_Pos == 主库的 Position
-- Seconds_Behind_Master = 0
-- GTID 模式下 Executed_Gtid_Set 与主库一致
-- 【4】从库 B：停止并解除复制关系，开放写入
STOP SLAVE;
RESET SLAVE ALL;
SET GLOBAL read_only = 0;
SET GLOBAL super_read_only = 0;
-- 【5】从库 B：确认已开启 log_bin 与 log_slave_updates（否则无法作为新主）
SHOW VARIABLES LIKE 'log_bin';
SHOW MASTER STATUS; -- 记下 B 的位点，供其余从库使用
-- 【6】VIP / DNS / 代理配置切到 B，应用重连（Keepalived 场景为 VIP 自动漂移）
-- 【7】原主库 A 变为新从库，反向指向 B
CHANGE MASTER TO MASTER_HOST='192.168.1.221', MASTER_USER='copy',
MASTER_PASSWORD='copy', MASTER_AUTO_POSITION=1; -- GTID 方式
-- 位点方式：MASTER_LOG_FILE / MASTER_LOG_POS 用第 5 步记下的 B 的位点
START SLAVE;
SET GLOBAL read_only = 1;
SHOW SLAVE STATUS\G3.8.2 其余从库重新指向新主
GTID 方式（简单可靠）：所有其余从库直接改指向即可，GTID 会自动协商位点。
STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='192.168.1.221', MASTER_AUTO_POSITION=1;
START SLAVE;
SHOW SLAVE STATUS\G位点方式（麻烦且易错）：从库 C 记录的是"旧主 A 的位点"，无法直接用于新主 B，因为 B 的 binlog 文件名与位置完全不同。做法：
- 切换前让所有从库都追平到与 A 完全一致（同一个位点）。
- 在 B 上
SHOW MASTER STATUS取得 B 的当前位点。 - C 用 B 的这个位点做
CHANGE MASTER TO。 - 若 C 落后于 B，则需要用
mysqlbinlog比对 A 的位点与 B binlog 中对应事务，人工换算出等价位点——这是位点复制最容易出错的环节，也是强烈推荐用 GTID 的最主要原因。
3.8.3 故障切换与数据丢失风险
主库已宕机、无法登录时：
- 选新主：在所有从库执行
SHOW SLAVE STATUS\G，选择Exec_Master_Log_Pos最大（或Executed_Gtid_Set最全）的从库作为新主。 - 补差：其余从库与新主的差异事务，需从新主的 binlog 补齐；若某从库比新主更超前（GTID 模式下会出现
errant transaction），需要用空事务处理或重建。 - 提升新主：
STOP SLAVE; RESET SLAVE ALL; SET GLOBAL read_only=0; - 切流量：VIP / 代理 / DNS 指向新主。
- 重建原主：原主修复后作为从库指向新主，若其 binlog 中有未传出的事务，这部分数据已丢失（异步复制的固有风险），需人工用 mysqlbinlog 抢救。
数据丢失风险量化：
| 复制方式 | 主库宕机的数据丢失量 |
|---|---|
| 异步复制 | 等于当时的复制延迟（可能是 0，也可能是几分钟的数据） |
| 半同步 AFTER_COMMIT | 极端场景下从库可能缺少最后一个已提交事务 |
| 半同步 AFTER_SYNC（5.7+） | 基本不丢（RPO≈0） |
| MGR / Galera | 不丢（多数派确认） |
3.8.4 MHA 与 Keepalived 实现自动故障转移
Keepalived + 双主：两台 MySQL 配置双主复制，Keepalived 通过 VRRP 维护一个 VIP，应用只连 VIP。主库故障时（脚本检测 3306 端口或 mysqladmin ping 失败）VIP 漂移到备机，实现秒级切换。优点是简单；缺点是只能两节点、无法处理"复制延迟未追平就切换"导致的数据不一致，也无法自动补齐其余从库。
详见 12-03-Keepalived高可用.md
MHA（Master High Availability Manager）：Perl 编写的 MySQL 高可用工具，由 Manager 节点 + 各 MySQL 上的 Node 组成。故障转移流程：
- Manager 每隔数秒 ping 主库，连续失败判定宕机（并通过第二条链路 / 其他从库二次确认，避免脑裂）。
- 从宕机主库的残留 binlog 中用
save_binary_logs尽最大可能抢救未传出的事务（这是 MHA 优于 Keepalived 的核心能力，可将数据丢失降到最低）。 - 选出数据最新的从库（比较
Exec_Master_Log_Pos/ GTID）作为新主。 - 用
apply_diff_relay_logs把新主与其他从库的差异 relay log 应用到各从库，使所有节点补齐到同一位点。 - 提升新主（
RESET SLAVE ALL、关闭 read_only），其余从库CHANGE MASTER TO指向新主。 - 调用
master_ip_failover_script漂移 VIP，调用report_script发送告警邮件。
MHA 常用命令：
masterha_check_ssh --conf=/etc/mha/app1.cnf # 检查各节点 SSH 互信
masterha_check_repl --conf=/etc/mha/app1.cnf # 检查复制拓扑是否健康
masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover
masterha_check_status --conf=/etc/mha/app1.cnf # 查看 Manager 运行状态
masterha_master_switch --conf=/etc/mha/app1.cnf --master_state=alive \
--new_master_host=192.168.1.221 --orig_master_is_new_slave # 计划内在线切换
masterha_stop --conf=/etc/mha/app1.cnfMHA 前提条件：所有节点 SSH 免密互通、所有从库开启 log_bin 与 log_slave_updates、relay_log_purge=0（保留 relay log 供补差）、各节点安装 mha4mysql-node。
更现代的替代方案：Orchestrator（GitHub 开源，Web UI + 自动拓扑发现）、MySQL InnoDB Cluster（MGR + MySQL Router + MySQL Shell）、MariaDB MaxScale 的自动 failover。
3.9 延时复制
延时复制的特点：可自行设置复制间隔时间，用于防止主节点数据误删、查看数据库历史状态等。
在 MySQL 主从复制基础上操作，在 slave 上执行：
stop slave;
change master to master_delay=30;
start slave;MASTER_DELAY 单位为秒，表示备节点延迟的时间。生产上常设为 3600（1 小时）或 86400（1 天）。
查看状态：
show slave status\GSQL_Delay: 30
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates测试：在 master 上插入一条数据，马上在 slave 上查询不到，需等待 30 秒后才能查到。
-- master
insert into ccc.aa values(6);
-- slave（立即查询查不到，30 秒后才能查到）
select * from ccc.aa;误删数据的抢救用法：主库发生 DROP TABLE 后，立刻在延时从库上 STOP SLAVE，此时该表数据仍完好，再用 START SLAVE UNTIL MASTER_LOG_POS=<误操作前的位点> 精确重放到误操作前一刻，然后导出该表回灌主库。
STOP SLAVE;
START SLAVE UNTIL MASTER_LOG_FILE='mysql-bin.000010', MASTER_LOG_POS=123456;
-- GTID 方式
START SLAVE UNTIL SQL_BEFORE_GTIDS = '3E11FA47-...:1050';MariaDB 中延时复制参数为
MASTER_DELAY（10.2+ 支持），语法一致。
四、读写分离与 Amoeba 代理
4.1 读写分离的原理与实现层次
原理：把写请求（INSERT / UPDATE / DELETE / DDL）全部发往主库，把读请求（SELECT）分发到一个或多个从库，从而让读能力随从库数量水平扩展。
客户
|
|
web 应用程序 / 游戏程序（C、PHP、Java……）
|
|
代理层（MySQL Proxy、Amoeba）
读写分离 / 数据切分
|
|
mysql 主 <----> mysql 从两种实现层次：
| 层次 | 实现方式 | 优点 | 缺点 |
|---|---|---|---|
| 应用层框架 | 应用内配置多数据源，按方法名/注解/AOP 判断读写，如 Spring AbstractRoutingDataSource、MyBatis 插件、Sharding-JDBC、PHP 的 PDO 双连接 |
无额外网络跳数，性能最好（少一次代理转发的 RTT）；无单点；可精细控制"强一致读走主库" | 侵入业务代码；每种语言/框架都要各实现一遍；从库上下线需改配置或依赖注册中心 |
| 中间件代理 | 在应用与数据库之间部署独立代理进程，代理解析 SQL 后路由，如 Amoeba、MyCat、ProxySQL、MaxScale、Atlas | 对应用完全透明（连代理就像连普通 MySQL）；集中管理路由、连接池、负载均衡；可做分库分表 | 多一跳网络延迟；代理本身是单点（需 Keepalived 做双活）；SQL 解析可能不支持全部语法；排错链路更长 |
读写分离的共同难点：主从延迟导致"写后立即读"读不到自己刚写的数据。常见对策：
- 写后短时间内（如 1 秒）的读强制走主库（打标记 / 会话粘滞）。
- 关键业务（支付、下单后跳转详情）SQL 加 hint 强制走主库。
- 使用半同步复制降低延迟窗口。
- 从库读前判断
Seconds_Behind_Master，超阈值则摘除该从库（ProxySQL / MaxScale 内置此能力）。
4.2 常见中间件对比
Amoeba 位于 MySQL 之上，作为代理应用与 MySQL 连接的中间件。它可以将连接细化，如将读写分离、分别指向不同数据库，根据不同数据指向不同数据库，还可以做读或写的负载均衡。
原始文档中的代理层对比：
| 代理软件 | 特点 |
|---|---|
| MySQL Proxy | 数据量小时表现尚可，数据量大会暴露较多 BUG，MySQL 官方建议用于轻量级应用 |
| Amoeba | 适用于中大型数据量应用，支持读写分离、数据切分、负载均衡，是企业中应用最广的数据库代理 |
| Cobar | 阿里巴巴开发，通过将表放入不同库实现分布式，本地软件与 Amoeba 类似 |
扩展对比（含现代方案）：
| 中间件 | 开发语言 | 读写分离 | 分库分表 | 延迟感知 | 现状与适用场景 |
|---|---|---|---|---|---|
| Amoeba | Java | 支持 | 支持（rule.xml 水平/垂直切分） | 不支持 | 早期国内使用最广；已停止维护（最后版本 2.2.0，2012 年）。教学与理解代理原理的最佳样本，新项目不建议生产使用 |
| MySQL Proxy | C + Lua | 需写 Lua 脚本 | 需写 Lua | 不支持 | 官方已废弃 |
| Cobar | Java | 支持 | 支持 | 不支持 | 阿里，已停止维护，MyCat 的前身 |
| MyCat | Java | 支持 | 支持（功能最全，含全局序列、ER 分片、跨库 JOIN） | 部分支持 | 社区活跃度较高，适合需要分库分表的中大型项目；资源占用较高（JVM） |
| ProxySQL | C++ | 支持（基于正则/查询规则路由，最灵活） | 有限（不做透明分片） | 支持（max_replication_lag，自动摘除延迟从库） |
当前生产首选。性能高、内存占用小、支持查询缓存/重写/限流、配置全部通过 SQL 在线修改无需重启、支持 MySQL 与 MariaDB |
| MaxScale | C | 支持（readwritesplit 路由） | 支持（schemarouter） | 支持 | MariaDB 官方出品，与 MariaDB 集成最好，含自动 failover；BSL 许可（商用需注意） |
| Atlas | C | 支持 | 支持（分表，不分库） | 不支持 | 360 基于 MySQL Proxy 改写，稳定但已少维护 |
| Sharding-JDBC | Java（客户端） | 支持 | 支持 | — | Apache ShardingSphere 的 JDBC 模式，属应用层方案，无代理跳数 |
4.3 Amoeba 环境准备
| 服务器 | IP | 角色 |
|---|---|---|
| 客户端 | 192.168.169.164 | 应用客户端 |
| Amoeba | 192.168.169.165 | 代理服务器 |
| MySQL 主 | 192.168.169.166 | 写库 |
| MySQL 从 | 192.168.169.167 | 读库 |
注：源文档不同章节的 IP 分配略有出入（部分章节 Amoeba 为 192.168.169.163、MySQL 主为 192.168.169.165、从为 192.168.169.166）。实操时以自己的实际规划为准，配置文件中的 IP 与命令行
-h参数保持一致即可。
所有节点准备工作：
- 主机名设置并互相绑定
/etc/hosts。 - 时间同步。
详见 08-03-时间同步与Xinetd.md
- 关闭 iptables、selinux。
- 配置好 yum 源。
- 配置静态 IP。
4.4 安装 JDK
Amoeba 基于 Java 开发，需要先安装 JDK。
下载 JDK 1.8：
wget http://192.168.2.254/mysql/pack/amoeba/jdk-8u181-linux-x64.tar.gz解压：
tar xf jdk-8u45-linux-x64.tar.gz -C /usr/local/
cd /usr/local
mv ./jdk1.8.0_45 ./java完整目录为 /usr/local/java。
验证 JDK 版本：
/usr/local/java/bin/java -versionjava version "1.8.0_45"
Java(TM) SE Runtime Environment (build 1.8.0_45-b14)
Java HotSpot(TM) 64-Bit Server VM (build 25.45-b02, mixed mode)配置全局 JAVA_HOME（可选，便于其他 Java 程序使用）：
cat > /etc/profile.d/java.sh <<'EOF'
export JAVA_HOME=/usr/local/java
export PATH=$JAVA_HOME/bin:$PATH
export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar
EOF
source /etc/profile.d/java.sh
java -versionAmoeba 2.2.0 只兼容 JDK 1.5 ~ 1.8，不能使用 JDK 9 及以上版本（会因 JVM 参数与类加载机制变化启动失败）。CentOS 自带的
java-1.8.0-openjdk也可以，但需注意JAVA_HOME路径为/usr/lib/jvm/java-1.8.0-openjdk。
4.5 安装 Amoeba
下载：
wget http://192.168.2.254/mysql/pack/amoeba/amoeba-mysql-binary-2.2.0.tar.gz创建安装目录并解压：
mkdir /usr/local/amoeba
tar xf /opt/amoeba/amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba查看目录结构：
ls /usr/local/amoeba/benchmark changelogs.txt lib README.html
bin conf LICENSE.txt重点关注两个目录：
| 目录 | 说明 |
|---|---|
bin/ |
启动脚本目录（amoeba、amoeba.classworlds） |
conf/ |
配置文件目录 |
lib/ |
依赖的 jar 包 |
benchmark/ |
压测工具 |
conf/ 下三个核心配置文件：
| 文件 | 说明 |
|---|---|
amoeba.xml |
Amoeba 全局配置文件（监听端口、客户端认证、读写池路由） |
dbServers.xml |
配置 Amoeba 连接 MySQL 数据库（后端节点定义、连接池、负载均衡） |
rule.xml |
配置数据切分规则 |
functionMap.xml |
SQL 函数映射（一般不改） |
rullFunctionMap.xml |
切分规则中可用的自定义函数（一般不改） |
log4j.xml |
日志级别与输出配置，排错时可把 level 改为 DEBUG |
access_list.conf |
客户端 IP 访问白名单 |
4.6 配置 amoeba.xml 逐项详解
编辑 /usr/local/amoeba/conf/amoeba.xml：
<!-- 第 11 行：修改 Amoeba 监听端口 -->
<property name="port">3307</property>
<!-- 第 30 行：客户端连接 Amoeba 的用户名 -->
<property name="user">test</property>
<!-- 第 32 行：客户端连接 Amoeba 的密码 -->
<property name="password">123321</property>
<!-- 第 117~120 行：去掉注释，配置读写分离池 -->
<property name="writePool">server1</property>
<property name="readPool">server2</property>writePool指定写操作走server1。readPool指定读操作走server2。
需要将第 117 行的 <!-- 和第 120 行的 --> 删除以去掉注释。
完整结构与各段落含义（Amoeba 默认端口为 8066，源文档改为了 3307；生产常用 8066）：
<?xml version="1.0" encoding="gbk"?>
<!DOCTYPE amoeba:configuration SYSTEM "amoeba.dtd">
<amoeba:configuration xmlns:amoeba="http://amoeba.meidusa.com/">
<proxy>
<!-- ============ ① 服务监听段 ============ -->
<service name="Amoeba for Mysql" class="com.meidusa.amoeba.mysql.server.MySQLService">
<!-- Amoeba 对客户端提供服务的端口，客户端就连这个端口，默认 8066 -->
<property name="port">8066</property>
<!-- 监听地址。注释掉表示监听所有网卡 0.0.0.0；
指定后只有该 IP 可被访问，安全但要注意别写成 127.0.0.1 导致远程连不上 -->
<!-- <property name="ipAddress">192.168.169.165</property> -->
<property name="connectionFactory"
class="com.meidusa.amoeba.mysql.net.MysqlClientConnectionFactory">
<property name="sendBufferSize">128</property>
<property name="receiveBufferSize">64</property>
</property>
<!-- ============ ② 客户端认证段 authenticate ============
这里的 user/password 是【客户端连 Amoeba 时使用的账号】，
与后端 MySQL 的账号是两套，可以不同名。 -->
<property name="authenticator"
class="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator">
<property name="user">test</property>
<property name="password">123321</property>
<!-- IP 白名单/黑名单文件，格式为每行一个 IP 或网段 -->
<property name="filter" class="com.meidusa.amoeba.server.IPAccessController">
<property name="ipFile">${amoeba.home}/conf/access_list.conf</property>
</property>
</property>
</service>
<!-- ============ ③ Amoeba 自身运行参数 ============ -->
<runtime class="com.meidusa.amoeba.net.parameter.CommonNetParameter">
<!-- 客户端连接的最大 SQL 队列长度，超过则报 too many connections -->
<property name="clientSideThreadCount">64</property>
<!-- 与后端 MySQL 通信的线程数 -->
<property name="serverSideThreadCount">128</property>
<!-- 定时任务线程数 -->
<property name="queryTimeout">60</property>
</runtime>
</proxy>
<!-- ============ ④ 连接管理器 ============ -->
<connectionManagerList>
<connectionManager name="defaultManager"
class="com.meidusa.amoeba.net.MultiConnectionManagerWrapper">
<property name="subManagerClassName">
com.meidusa.amoeba.net.ConnectionManager
</property>
</connectionManager>
</connectionManagerList>
<!-- ============ ⑤ 引用后端节点定义文件 dbServerList ============ -->
<dbServerList>
<!-- 这里引入 dbServers.xml 中定义的所有 dbServer 节点 -->
<property name="configFile">${amoeba.home}/conf/dbServers.xml</property>
</dbServerList>
<!-- ============ ⑥ 查询路由器 queryRouter（读写分离核心） ============ -->
<queryRouter class="com.meidusa.amoeba.mysql.parser.MysqlQueryRouter">
<!-- 数据切分规则文件 -->
<property name="ruleConfig">${amoeba.home}/conf/rule.xml</property>
<property name="functionConfig">${amoeba.home}/conf/functionMap.xml</property>
<property name="ruleFunctionConfig">${amoeba.home}/conf/ruleFunctionMap.xml</property>
<!-- 是否解析 SQL。true=解析 SQL 以判断读/写并按 rule.xml 切分；
false=不解析，所有语句都发往 defaultPool，读写分离与切分都失效。
做读写分离或数据切分时【必须为 true】 -->
<property name="needParse">true</property>
<!-- 默认池：不满足任何规则的语句（如 show、set、use、事务控制）走这里 -->
<property name="defaultPool">server1</property>
<!-- 写池：INSERT / UPDATE / DELETE / DDL 走这里，必须指向主库 -->
<property name="writePool">server1</property>
<!-- 读池：SELECT 走这里。可以是单个 dbServer，也可以是 virtual 的集群池 -->
<property name="readPool">server2</property>
<!-- LOAD DATA IN FILE 的处理 -->
<property name="LOADDataFileEnable">true</property>
</queryRouter>
</amoeba:configuration>queryRouter 三个池的关系与优先级：
| 属性 | 作用 | 注意事项 |
|---|---|---|
defaultPool |
兜底池 | 必须配置。事务内的语句、SET、SHOW、USE 等会走这里。事务中的 SELECT 也走 defaultPool（即主库），这正是我们想要的强一致行为 |
writePool |
写请求目标 | 只能是单个节点或写集群，指向主库 |
readPool |
读请求目标 | 指向从库或多从库负载均衡的 virtual 池 |
needParse |
是否解析 SQL | 必须 true，否则 writePool / readPool / rule.xml 全部不生效 |
若同时配置了
writePool/readPool与rule.xml中的切分规则，切分规则优先级更高：命中 rule 的表按 rule 走，未命中的才按读写分离走。因此做纯数据切分时需要把writePool/readPool注释掉（见 4.12.1 节）。
4.7 配置 dbServers.xml 逐项详解
编辑 /usr/local/amoeba/conf/dbServers.xml：
<!-- 第 20 行：MySQL 端口 -->
<property name="port">3307</property>
<!-- 第 23 行：默认访问的数据库 -->
<property name="schema">aaa</property>
<!-- 第 26 行：连接 MySQL 的用户名 -->
<property name="user">test</property>
<!-- 第 29 行：连接 MySQL 的密码 -->
<property name="password">123321</property>
<!-- 第 45~50 行：server1，MySQL 主服务器 -->
<dbServer name="server1" parent="abstractServer">
<factoryConfig>
<!-- mysql ip -->
<property name="ipAddress">192.168.169.165</property>
</factoryConfig>
</dbServer>
<!-- 第 52~57 行：server2，MySQL 从服务器 -->
<dbServer name="server2" parent="abstractServer">
<factoryConfig>
<!-- mysql ip -->
<property name="ipAddress">192.168.169.166</property>
</factoryConfig>
</dbServer>完整结构与逐项说明：
<?xml version="1.0" encoding="gbk"?>
<!DOCTYPE amoeba:dbServers SYSTEM "dbserver.dtd">
<amoeba:dbServers xmlns:amoeba="http://amoeba.meidusa.com/">
<!-- ============ ① 抽象 server：所有真实节点的公共配置模板 ============ -->
<dbServer name="abstractServer" abstractive="true">
<!-- factoryConfig：如何创建到后端 MySQL 的连接 -->
<factoryConfig class="com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory">
<property name="manager">${defaultManager}</property>
<property name="sendBufferSize">64</property>
<property name="receiveBufferSize">128</property>
<!-- 后端 MySQL 的端口，所有继承此模板的节点共用；
若某节点端口不同，可在该节点内单独覆盖 -->
<property name="port">3306</property>
<!-- 默认连接的数据库名。Amoeba 连上后端时会先 USE 这个库，
【该库必须在所有后端 MySQL 上都真实存在】，否则启动即报错 -->
<property name="schema">aaa</property>
<!-- 连接后端 MySQL 使用的账号密码，需在所有后端 MySQL 上授权 -->
<property name="user">test</property>
<property name="password">123321</property>
</factoryConfig>
<!-- poolConfig：Amoeba 到该后端的连接池参数 -->
<poolConfig class="com.meidusa.amoeba.net.poolable.PoolableObjectPool">
<property name="maxActive">500</property> <!-- 最大连接数（并发上限） -->
<property name="maxIdle">500</property> <!-- 最大空闲连接数 -->
<property name="minIdle">1</property> <!-- 最小空闲连接数 -->
<property name="minEvictableIdleTimeMillis">600000</property> <!-- 空闲连接存活 10 分钟 -->
<property name="timeBetweenEvictionRunsMillis">600000</property><!-- 回收检查间隔 -->
<property name="testOnBorrow">true</property> <!-- 借出连接前测试可用性（推荐 true） -->
<property name="testOnReturn">true</property> <!-- 归还连接时测试 -->
<property name="testWhileIdle">true</property> <!-- 空闲时定期测试，及时剔除死连接 -->
<property name="numTestsPerEvictionRun">10</property>
</poolConfig>
</dbServer>
<!-- ============ ② 真实节点：主库 ============ -->
<dbServer name="server1" parent="abstractServer">
<factoryConfig>
<property name="ipAddress">192.168.169.165</property>
<!-- 可在此覆盖模板中的任意属性，例如该节点端口/账号不同： -->
<!-- <property name="port">3307</property> -->
<!-- <property name="user">wuser</property> -->
</factoryConfig>
</dbServer>
<!-- ============ ③ 真实节点：从库 ============ -->
<dbServer name="server2" parent="abstractServer">
<factoryConfig>
<property name="ipAddress">192.168.169.166</property>
</factoryConfig>
</dbServer>
<!-- 多个从库时继续增加 server3、server4 ... -->
<dbServer name="server3" parent="abstractServer">
<factoryConfig>
<property name="ipAddress">192.168.169.167</property>
</factoryConfig>
</dbServer>
<!-- ============ ④ 虚拟数据库节点：多从库负载均衡池 ============ -->
<dbServer name="multiPool" virtual="true">
<poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
<!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA -->
<property name="loadbalance">1</property>
<!-- 池中包含的真实节点，逗号分隔。
同一节点重复出现即为"加权"，如 server1,server2,server2 表示 server2 承担 2/3 -->
<property name="poolNames">server2,server3</property>
</poolConfig>
</dbServer>
</amoeba:dbServers>loadbalance 取值：
| 值 | 算法 | 说明 |
|---|---|---|
1 |
ROUNDROBIN | 轮询，每台访问一次（默认） |
2 |
WEIGHTBASED | 加权轮询，性能好的机器承载更多连接（权重通过在 poolNames 中重复列出节点实现） |
3 |
HA | 高可用：只用第一个节点，故障时才切到下一个（主备模式，不做负载分担） |
virtual="true"表示这是一个逻辑池而非真实 MySQL 实例，只能出现在amoeba.xml的readPool/writePool/defaultPool或rule.xml的defaultPools中被引用。
4.8 修改启动脚本与 JVM 参数
启动文件 /usr/local/amoeba/bin/amoeba 需要 JDK 支持。在第 10 行后添加 JDK 环境变量：
# 第 11~13 行
JAVA_HOME=/usr/local/java
PATH=$PATH:$JAVA_HOME/bin
export JAVA_HOME PATH第 62 行将 Xss128k 改为 Xss228k（Amoeba 启动要求最低 228k）：
DEFAULT_OPTS="-server -Xms256m -Xmx256m -Xss228k"JVM 参数说明：
| 参数 | 说明 |
|---|---|
-Xms256m |
分配 256m 物理内存给 Amoeba，连接数据库时初始化内存就要 256m |
-Xmx256m |
Amoeba 最大可用物理内存（32 位 JDK 最大 2G，64 位无限制但不能超过本机物理内存） |
-Xss228k |
启动 Amoeba 需要 228k 内存（默认 128k 不满足，不改会报 The stack size specified is too small） |
-server |
使用 Server 模式 JVM，吞吐优先 |
生产环境建议（内存充足时）：
DEFAULT_OPTS="-server -Xms1024m -Xmx1024m -Xss256k -XX:PermSize=128m -XX:MaxPermSize=256m"
-Xms与-Xmx设为相同值可避免运行期堆伸缩带来的 GC 抖动。JDK 8 中PermSize已被MetaspaceSize取代，写了会有警告但不影响启动。
4.9 启动 Amoeba 与日志查看
推荐使用 nohup 后台启动，启动信息写入 nohup.out 方便排错：
cd /usr/local/amoeba
nohup ./bin/amoeba start &其他启动方式（不推荐）：
# 启动信息打印在终端
./bin/amoeba start &
# 启动信息输出到 /dev/null（丢失启动日志，无法排错）
./bin/amoeba start >/dev/null &停止与重启：
cd /usr/local/amoeba
./bin/amoeba stop
nohup ./bin/amoeba start &验证启动是否成功：
方法一：查看 nohup.out，出现以下内容即启动成功：
2016-09-05 22:24:30,897 INFO net.ServerableConnectionManager - Amoeba for Mysql listening on 0.0.0.0/0.0.0.0:3066.
2016-09-05 22:24:30,934 INFO net.ServerableConnectionManager - Amoeba Monitor Server listening on /127.0.0.1:19659.方法二：查看进程：
ps -ef | grep amoebaroot 12855 12471 0 22:24 pts/3 00:00:09 /usr/local/java/bin/java -server -Xms256m -Xmx256m -Xss228k -Damoeba.home=/usr/local/amoeba -Dclassworlds.conf=/usr/local/amoeba/bin/amoeba.classworlds -classpath /usr/local/amoeba/lib/classworlds-1.0.jar org.codehaus.classworlds.Launcher start方法三：查看端口监听：
netstat -tlnp | grep -E '8066|3307|19659'
ss -tlnp | grep java实时跟踪日志：
tail -f /usr/local/amoeba/nohup.out
# 或在 conf/log4j.xml 中把 level 由 INFO 改为 DEBUG，可看到每条 SQL 被路由到哪个 dbServer4.10 主从库上为 Amoeba 授权与连接验证
为方便验证读写分离效果，可以先关闭两台 MySQL 的主从复制，分别在两台 MySQL 上执行以下操作，制造可区分的数据。
两台数据库都要执行：
create database aaa;
use aaa;
create table emp (id int, ename varchar(20));主服务器插入：
insert into emp values(1,"master");从服务器插入：
insert into emp values(1,"slave");两台 MySQL 都授权（授权 IP 为 Amoeba 服务器）：
grant all on aaa.* to 'test'@'%' identified by '123';
flush privileges;生产环境更严格的授权写法（限定来源为 Amoeba 服务器 IP，且区分读写权限）：
-- 主库（写权限）
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX
ON aaa.* TO 'test'@'192.168.169.165' IDENTIFIED BY '123321';
-- 从库（只读权限）
GRANT SELECT ON aaa.* TO 'test'@'192.168.169.165' IDENTIFIED BY '123321';
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'test'@'192.168.169.165';重要：
dbServers.xml中的user/password必须与这里授权的账号密码一致；amoeba.xml中authenticate段的user/password是客户端连 Amoeba 用的，两者可以不同。源文档中授权密码为123，而配置文件中为123321，实操时必须保持一致，否则 Amoeba 连不上后端 MySQL。若主从复制已开启，只需在主库执行 GRANT，权限会自动复制到从库。
在 Amoeba 服务器上测试直连两台 MySQL（绕过 Amoeba，先确认后端可达）：
mysql56 -utest -p123321 -h192.168.169.165 -P3307
mysql56 -utest -p123321 -h192.168.169.166 -P3307需要能连上，如果连不上则检查上面的授权和配置。
4.11 客户端通过 Amoeba 连接与读写分离验证
客户端连接 Amoeba（端口为 amoeba.xml 中配置的端口，默认 8066，源文档改成了 3307）：
mysql56 -utest -p123321 -h192.168.169.164 -P3307
# 默认端口写法
mysql -utest -p123321 -h192.168.169.165 -P8066验证方法一：在从库 STOP SLAVE 后，写主读从观察差异
use aaa;
-- 插入数据，写操作走 server1（主）
insert into emp(id) values(3);
-- Query OK, 1 row affected (0.01 sec)
-- 查询，读操作走 server2（从），看不到刚插入主库的数据
select * from emp;+------+
| id |
+------+
| 1 |
+------+写操作路由到主库，读操作路由到从库，读写分离生效。
若主从复制处于开启状态，这个验证会因为数据被同步过去而"看不出差别"。此时在从库上执行 STOP SLAVE; 人为造成主从不同步，再通过 Amoeba 写入并查询，就能明确看到"写进去了但查不到"，从而证明读走的是从库：
-- 从库上
STOP SLAVE;
-- 客户端通过 Amoeba
INSERT INTO emp VALUES (99,'only-in-master');
SELECT * FROM emp WHERE id=99; -- 查不到，说明读请求确实落到了从库
-- 直连主库查询
SELECT * FROM emp WHERE id=99; -- 能查到
-- 验证完毕，恢复复制
START SLAVE;
SELECT * FROM emp WHERE id=99; -- 此时通过 Amoeba 也能查到了验证方法二：用 general_log 观察 SQL 落到哪个库
在主库和从库上分别开启通用查询日志，直接看每条 SQL 到达了哪台机器，这是最权威的验证方式：
-- 主库、从库都执行（动态开启，无需重启）
SET GLOBAL general_log = ON;
SET GLOBAL log_output = 'FILE'; -- 也可设为 'TABLE'，日志写入 mysql.general_log 表
SHOW VARIABLES LIKE 'general_log%'; -- 查看日志文件路径# 两台机器上分别实时跟踪
tail -f /var/lib/mysql/$(hostname).log然后通过 Amoeba 依次执行一条 INSERT 和一条 SELECT，观察：
- INSERT 只出现在主库日志中。
- SELECT 只出现在从库日志中。
BEGIN/SET/SHOW/ 事务中的 SELECT 出现在defaultPool指向的库（主库）中。
用 log_output='TABLE' 时的查询方式：
SELECT event_time, user_host, argument
FROM mysql.general_log
WHERE argument LIKE '%emp%'
ORDER BY event_time DESC LIMIT 20;验证完毕后务必关闭（general_log 写盘量极大，会严重影响性能）：
SET GLOBAL general_log = OFF;
TRUNCATE TABLE mysql.general_log;验证方法三：在后端库上执行不同的 SELECT @@server_id 或 SELECT @@hostname
-- 通过 Amoeba 执行，返回的是读库的 server_id
SELECT @@server_id, @@hostname;
-- 反复执行，配置了读集群轮询时会看到 server_id 交替变化4.12 MySQL 数据切分（Amoeba rule.xml）
数据切分是按照不同的数据访问不同的数据库。
4.12.1 关闭读写分离
编辑 /usr/local/amoeba/conf/amoeba.xml，将 117~119 行注释掉：
<!--
<property name="writePool">server1</property>
<property name="readPool">server2</property>
-->原因：切分规则与读写分离同时配置时容易互相干扰，做纯数据切分实验时先关闭读写分离，路由完全由
rule.xml决定。
4.12.2 水平切分（取模规则）
按 aaa.emp 表的 id 列切分：奇数给 server2，偶数给 server1。
编辑 /usr/local/amoeba/conf/rule.xml：
<?xml version="1.0" encoding="gbk"?>
<!DOCTYPE amoeba:rule SYSTEM "rule.dtd">
<amoeba:rule xmlns:amoeba="http://amoeba.meidusa.com/">
<tableRule name="emp" schema="aaa" defaultPools="server1,server2">
<rule name="rule1" ruleResult="POOLNAME">
<parameters>id</parameters>
<expression><![CDATA[
var division = id % 2;
case division when 1 then 'server1';
when 0 then 'server2';
end case;
]]></expression>
</rule>
</tableRule>
</amoeba:rule>| 配置项 | 说明 |
|---|---|
tableRule name |
表名称 |
schema |
数据库名 |
defaultPools |
可支持的服务器列表 |
rule name |
切片动作名称 |
parameters |
用于切片判断的字段 |
expression |
切片规则（CDATA 中的脚本） |
ruleResult="POOLNAME" |
表达式的返回值即为目标池名 |
粘贴时可在 vim 中执行
:set paste后再进入插入模式粘贴，避免缩进错乱。粘贴后需删除中文注释（XML 中含中文且文件声明为 gbk 时容易乱码报错）。
重启 Amoeba：
cd /usr/local/amoeba
./bin/amoeba stop
nohup ./bin/amoeba start &客户端测试：
mysql -h192.168.169.163 -utest -p123321 -P3307Amoeba 根据 SQL 解析进行数据切分，必须在 SQL 中包含切分关键字段（这里是
id），否则切分规则无效，会在 server1 和 server2 都插入数据。
错误写法（两个库都会插入）：
insert into aaa.emp values (1,'aaa');正确写法：
insert into aaa.emp(id,ename) values (1,'aaa');
insert into aaa.emp(id,ename) values (2,'bbb');
insert into aaa.emp(id,ename) values (3,'ccc');
insert into aaa.emp(id,ename) values (4,'ddd');验证结果：
- server1 上只有 1、3 两条数据。
- server2 上只有 2、4 两条数据。
delete 和 update 操作同理（WHERE 中必须带切分字段，否则会广播到所有节点）。
4.12.3 水平切分（范围规则）
只修改 rule.xml，按 id 值范围切分：id <= 100 的操作在 server1，id > 100 的操作在 server2。
<?xml version="1.0" encoding="gbk"?>
<!DOCTYPE amoeba:rule SYSTEM "rule.dtd">
<amoeba:rule xmlns:amoeba="http://amoeba.meidusa.com/">
<tableRule name="emp" schema="aaa" defaultPools="server1,server2">
<rule name="rule1">
<parameters>id</parameters>
<expression><![CDATA[ id <= 100 ]]></expression>
<defaultPools>server1</defaultPools>
<readPools>server1</readPools>
<writePools>server1</writePools>
</rule>
<rule name="rule2">
<parameters>id</parameters>
<expression><![CDATA[ id > 100 ]]></expression>
<defaultPools>server2</defaultPools>
<readPools>server2</readPools>
<writePools>server2</writePools>
</rule>
</tableRule>
</amoeba:rule>4.12.4 垂直切分
架构示意：
client 192.168.169.164
|
|
amoeba 192.168.169.163
|
|
|----------------|
mysqlA mysqlB
192.168.169.165 192.168.169.166
aaa.aaa 表 aaa.bbb 表准备测试环境：
192.168.169.165 上：
create database aaa;
use aaa;
create table aaa.aaa (id int);192.168.169.166 上：
create database aaa;
use aaa;
create table aaa.bbb (id int);修改 rule.xml（清空原有内容）：
<?xml version="1.0" encoding="gbk"?>
<!DOCTYPE amoeba:rule SYSTEM "rule.dtd">
<amoeba:rule xmlns:amoeba="http://amoeba.meidusa.com/">
<tableRule name="aaa" schema="aaa" defaultPools="server1"/>
<tableRule name="bbb" schema="aaa" defaultPools="server2"/>
</amoeba:rule>重启 Amoeba：
cd /usr/local/amoeba
./bin/amoeba stop
nohup ./bin/amoeba start &客户端测试：
mysql -utest -p123321 -h192.168.169.163 -P3307use aaa;
-- 插入到 192.168.169.165 的 aaa.aaa 表
insert into aaa values (1);
-- 插入到 192.168.169.166 的 aaa.bbb 表
insert into bbb values (2);
select * from aaa.aaa;+------+
| id |
+------+
| 1 |
+------+select * from aaa.bbb;+------+
| id |
+------+
| 2 |
+------+4.13 Amoeba 读集群（负载均衡）
集群的好处是让服务器平均负载连接请求。把读写分离改成读集群的条件是至少有 2 台服务器做读。
4.13.1 集群分发算法
| 值 | 算法 | 说明 |
|---|---|---|
1 |
ROUNDROBIN | 轮询，每台访问一次 |
2 |
WEIGHTBASED | 加权轮询，性能好的机器承载更多连接 |
3 |
HA | 高可用 |
4.13.2 配置 dbServers.xml
dbServers.xml 第 59~67 行是集群配置段，关注第 59、62、65 行：
- 第 59 行
name="multiPool"：集群名称，在amoeba.xml中指定读服务器时使用。 - 第 62 行：集群算法，默认
1轮询。 - 第 65 行：服务器列表。
编辑 /usr/local/amoeba/conf/dbServers.xml：
<dbServer name="multiPool" virtual="true">
<poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
<!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA -->
<property name="loadbalance">1</property>
<!-- Separated by commas, such as: server1,server2,server1 -->
<property name="poolNames">server1,server2</property>
</poolConfig>
</dbServer>4.13.3 配置 amoeba.xml
编辑 /usr/local/amoeba/conf/amoeba.xml，修改第 118 行，将读池改为集群名称 multiPool：
<property name="writePool">server1</property>
<property name="readPool">multiPool</property>4.13.4 重启 Amoeba 并验证
cd /usr/local/amoeba/
./bin/amoeba stop
nohup ./bin/amoeba start &验证方式见 4.9 节（查看 nohup.out 或 ps -ef | grep amoeba）。
4.13.5 客户端测试读集群
mysql -utest -p123321 -h192.168.169.163 -P3307插入测试数据：
insert into emp values(10,"test1");
insert into emp values(11,"test11");多次查询，读请求会在 server1 和 server2 之间轮询：
select * from emp;+--------+--------+
| emp_id | ename |
+--------+--------+
| 10 | test1 |
| 11 | test11 |
+--------+--------+再次查询可能返回另一台服务器的数据：
+--------+---------------------+
| emp_id | ename |
+--------+---------------------+
| 1 | ????192.168.169.166 |
| 7 | test |
| 8 | test1 |
| 10 | test1 |
| 11 | test11 |
+--------+---------------------+至此，MySQL 读集群配置完毕。
说明：上面两次查询结果不同，正是因为轮询到了不同的后端节点，而这两台机器数据不一致（实验时手工造成的）。生产环境中主从数据一致，轮询结果应当相同。
4.14 Amoeba 的局限与生产建议
局限：
| 局限 | 说明 |
|---|---|
| 项目已停止维护 | 最后版本 2.2.0（约 2012 年），不再修 BUG、不支持新版 MySQL 协议（连 MySQL 8.0 的 caching_sha2_password 会认证失败） |
| 无延迟感知 | 不检测 Seconds_Behind_Master，从库延迟或宕机时仍会把读请求发过去，返回旧数据或报错 |
| 无自动故障转移 | 主库宕机后需人工改 writePool 并重启 Amoeba |
| 配置需重启生效 | 所有 XML 修改都必须重启 Amoeba，期间业务连接全部中断 |
| SQL 支持不完整 | 复杂 SQL（跨库 JOIN、子查询、存储过程调用、部分函数）解析可能出错；分片场景不支持跨分片聚合与排序 |
| 自身是单点 | 需要 Keepalived + 双 Amoeba 做高可用 |
| JVM 资源开销 | 高并发时需调大堆内存，GC 停顿会造成毛刺 |
| 不支持事务跨分片 | 分片场景下无分布式事务 |
| 字符集问题 | 配置文件为 gbk 编码，与 utf8mb4 业务库配合时易出现乱码 |
生产建议：
- 新项目不要用 Amoeba，改用 ProxySQL（延迟感知 + 在线改配置 + 性能高）或 应用层 Sharding-JDBC / ShardingSphere-Proxy。
- 若必须使用 Amoeba（存量系统），至少做到：
- 部署两台 Amoeba + Keepalived VIP，避免单点。
- 监控 Amoeba 进程、端口、
nohup.out中的 ERROR 关键字、JVM 堆使用率。 - 从库延迟监控独立部署（pt-heartbeat），延迟超阈值时人工把
readPool临时改为主库。 - 定期归档
nohup.out（会无限增长），用logrotate管理。
- 强一致读（写后立即读）必须在业务层控制走主库，代理层无法解决。
- Amoeba 服务器与 MySQL 尽量同机房、内网千兆以上，避免代理成为带宽瓶颈。
- 分库分表是最后手段，先做好索引优化、SQL 优化、读写分离、缓存，再考虑切分。
五、排错清单
5.1 Slave_IO_Running 为 No 的排查
排查顺序（自下而上）：
# ① 网络与端口是否可达
ping 192.168.1.220
telnet 192.168.1.220 3306
nc -zv 192.168.1.220 3306
# ② 防火墙
systemctl status firewalld; firewall-cmd --list-ports # CentOS 7
service iptables status # CentOS 6
# ③ 主库是否只监听本地
grep -E 'bind|skip-networking' /etc/my.cnf # bind-address 应为 0.0.0.0 或内网 IP-- ④ 复制账号能否从从库登录
-- 在从库上：mysql -ucopy -pcopy -h192.168.1.220 -P3306
-- 主库上确认授权
SHOW GRANTS FOR 'copy'@'192.168.1.221';
-- ⑤ 看具体错误
SHOW SLAVE STATUS\G -- 重点看 Last_IO_Errno / Last_IO_Error常见 IO 错误对照表：
| 错误码 | 错误信息关键字 | 原因与解决 |
|---|---|---|
| 2003 | Can't connect to MySQL server |
网络不通 / 端口未放行 / 主库未启动 |
| 1045 | Access denied for user |
账号密码错、授权的主机名/IP 不匹配、FLUSH PRIVILEGES 未执行 |
| 1236 | Could not find first log file name |
位点错误，见 5.2 节 |
| 1593 | Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids |
server_id 重复（常见于克隆虚拟机后忘改）。修改从库 server_id 并重启 |
| 1593 | slave UUID and master UUID are equal |
直接复制了 auto.cnf 文件导致 server_uuid 相同。删除从库 /var/lib/mysql/auto.cnf 后重启 MySQL 自动生成新 UUID |
| 2013 | Lost connection to MySQL server during query |
网络抖动 / max_allowed_packet 太小（主从都要调大）/ 主库 wait_timeout 太短 |
| 1159 | Got timeout reading communication packets |
slave_net_timeout 太小，或网络长期空闲被防火墙断链 |
-- 修正后重启 IO 线程
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;
SHOW SLAVE STATUS\G同时检查主库上是否能看到从库连接：
-- 主库上执行
SHOW SLAVE HOSTS; -- 列出已连接的从库
SHOW PROCESSLIST; -- 应能看到 Command 为 'Binlog Dump' 的线程5.2 1236 错误：binlog 位点丢失
错误信息：
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log:
'Could not find first log file name in binary log index file'原因：从库请求的 binlog 文件在主库上已被删除（expire_logs_days 到期自动清理、有人执行了 PURGE BINARY LOGS 或 RESET MASTER），或位点填错、主库 binlog 被 RESET MASTER 重置。
排查：
-- 主库：查看现存 binlog 列表
SHOW BINARY LOGS;
SHOW MASTER STATUS;
-- 从库：查看请求的是哪个文件与位点
SHOW SLAVE STATUS\G -- Master_Log_File / Read_Master_Log_Pos解决方案：
方案一（数据未丢，仅位点错）：若主库仍保留了从库缺失的所有事务，重新指定正确位点：
STOP SLAVE;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000012', MASTER_LOG_POS=4;
START SLAVE;方案二（GTID 模式）：改用自动定位，若缺失的 GTID 仍在主库 binlog 中会自动续传：
STOP SLAVE;
CHANGE MASTER TO MASTER_AUTO_POSITION=1;
START SLAVE;若报 The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires，说明主库确实已删掉从库需要的日志，只能重建从库（方案三）。
方案三（数据已丢，重建从库）：
# 主库全量导出
mysqldump -uroot -p --all-databases --single-transaction --master-data=2 \
--set-gtid-purged=ON --routines --triggers --events > /tmp/full.sql
# 从库导入
mysql -uroot -p < /tmp/full.sql-- 从库
STOP SLAVE; RESET SLAVE ALL; RESET MASTER;
-- 位点方式：用导出文件头部注释里的 File/Pos
CHANGE MASTER TO MASTER_HOST='...', MASTER_USER='copy', MASTER_PASSWORD='copy',
MASTER_LOG_FILE='mysql-bin.000012', MASTER_LOG_POS=154;
-- 或 GTID 方式
CHANGE MASTER TO MASTER_HOST='...', MASTER_USER='copy', MASTER_PASSWORD='copy',
MASTER_AUTO_POSITION=1;
START SLAVE;预防措施：expire_logs_days 不小于全量备份周期 + 安全余量（建议 7~14 天）；磁盘监控告警；不要在有从库时执行 RESET MASTER；PURGE BINARY LOGS 前先确认所有从库的 Master_Log_File。
5.3 1062 主键冲突与跳过错误事务
错误信息：
Last_SQL_Errno: 1062
Last_SQL_Error: Could not execute Write_rows event on table aaa.a1;
Duplicate entry '1' for key 'PRIMARY'常见原因：从库被误写入了数据（read_only 未生效）、双主同时写同一行、之前跳过过事务导致数据分叉、从库重复应用了 relay log。
位点复制模式的跳过方法：
STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1; -- 跳过 1 个事件（不是 1 个事务！）
START SLAVE;
SHOW SLAVE STATUS\G
sql_slave_skip_counter的单位是 event，不是 transaction。一个事务通常包含BEGIN、若干Table_map、Write_rows、Xid等多个 event。要精确跳过一整个事务，应设为该事务包含的 event 数，或反复执行skip_counter=1直到跳过Xid事件。盲目跳过会造成数据不一致。
GTID 模式的跳过方法（注入空事务）：
STOP SLAVE SQL_THREAD;
SET GTID_NEXT = '3E11FA47-71CA-11E1-9E33-C80AA9429562:1005'; -- 出错的那个 GTID
BEGIN; COMMIT;
SET GTID_NEXT = 'AUTOMATIC';
START SLAVE SQL_THREAD;更好的做法（1062 场景）：不要跳过，而是先判断哪边数据是对的：
-- 从库上找到冲突的行，与主库对比
SELECT * FROM aaa.a1 WHERE id = 1;
-- 若从库这行是脏数据（误写入的），删掉它再重启 SQL 线程，让复制自然应用
STOP SLAVE SQL_THREAD;
DELETE FROM aaa.a1 WHERE id = 1;
START SLAVE SQL_THREAD;1032 错误（Can't find record）：从库缺少要更新/删除的行。处理思路相同：确认差异后手工补齐该行数据，或跳过后用 pt-table-sync 修复。
临时容错参数（仅测试环境使用，生产禁用）：
[mysqld]
slave_skip_errors = 1062,1032 # 自动忽略这两类错误，会静默造成数据不一致MariaDB 10.x 提供 slave_exec_mode = IDEMPOTENT（幂等模式，自动把冲突的 INSERT 当 UPDATE、把找不到的 DELETE 当成功），MySQL 也有该参数，仅建议在 NDB 或明确知晓风险时使用。
5.4 从库数据不一致的校验与修复
校验：pt-table-checksum
原理：在主库上按块（chunk）计算每段数据的 CRC 校验值，写入 percona.checksums 表，该写入通过复制流到达从库并在从库上重新计算，最后比较两边校验值。
# 主库需要一个有 SELECT / PROCESS / SUPER / REPLICATION SLAVE / 建表权限的账号
pt-table-checksum \
--host=192.168.1.220 --port=3306 --user=checksum --password=xxx \
--databases=aaa \
--replicate=percona.checksums \
--recursion-method=processlist \
--chunk-time=0.5 \
--max-lag=5 \
--check-interval=2 \
--nocheck-replication-filters \
--no-check-binlog-format输出解读：
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
08-22T10:00:01 0 0 10000 4 0 1.234 aaa.a1
08-22T10:00:03 0 2 5000 2 0 0.876 aaa.a2DIFFS 列不为 0 即表示该表主从数据不一致。查看具体不一致的块：
SELECT db, tbl, chunk, this_cnt, master_cnt, this_crc, master_crc
FROM percona.checksums
WHERE this_crc <> master_crc OR this_cnt <> master_cnt;要点：
- 必须在主库上运行，且要求所有被校验表有主键或唯一索引。
- 主库
binlog_format建议为 STATEMENT（工具会在会话级自动设置），ROW 格式下需加--no-check-binlog-format。 --max-lag=5表示从库延迟超 5 秒时暂停，避免加重延迟。- 从库不能有复制过滤（
replicate_*），否则校验表本身可能被过滤掉。
修复：pt-table-sync
# 先打印将执行的 SQL，人工确认（强烈建议先用 --print）
pt-table-sync --print \
--replicate=percona.checksums \
--sync-to-master h=192.168.1.221,u=sync,p=xxx
# 确认无误后真正执行（在主库执行修复 SQL，通过复制流下发到从库）
pt-table-sync --execute \
--replicate=percona.checksums \
--sync-to-master h=192.168.1.221,u=sync,p=xxx
# 也可只同步指定表
pt-table-sync --execute --sync-to-master \
h=192.168.1.221,u=sync,p=xxx --databases=aaa --tables=a1
--sync-to-master表示"以主库为准修复从库"，修复语句在主库执行后经复制下发，因此不会破坏复制。差异极大时（如缺几百万行）用 pt-table-sync 会很慢，此时直接重建从库更快。修复前务必备份从库；修复期间从库延迟会上升。
5.5 Seconds_Behind_Master 持续增长
诊断步骤：
-- ① 判断瓶颈在 IO 线程还是 SQL 线程
SHOW SLAVE STATUS\G
-- 主库 Position - Read_Master_Log_Pos 差值大 → 网络/IO 线程瓶颈
-- Read_Master_Log_Pos - Exec_Master_Log_Pos 差值大 → SQL 线程重放瓶颈（最常见）
-- ② 看 SQL 线程当前在干什么
SHOW PROCESSLIST; -- 找 system user 的线程，看 State 和 Info
SELECT * FROM information_schema.PROCESSLIST WHERE USER='system user'\G
-- ③ 并行复制下看各 worker
SELECT * FROM performance_schema.replication_applier_status_by_worker\G
-- ④ 是否有大事务正在重放
SELECT * FROM information_schema.innodb_trx ORDER BY trx_started LIMIT 5\G对应处理：
| 现象 | 处理 |
|---|---|
| SQL 线程卡在某条 UPDATE/DELETE 上很久 | 检查该表是否无主键（ROW 格式下会全表扫描每一行）→ 立即加主键 |
| 主库有大批量 DML | 拆小事务，每批 1000~5000 行，中间 sleep |
| 单线程重放跟不上 | 开启并行复制（见 3.7.2 节） |
从库 IO 打满（iostat -x 1 看 %util） |
从库改 innodb_flush_log_at_trx_commit=2 + sync_binlog=0（见 3.7.3 节）；升级磁盘为 SSD |
| 从库上有大查询占资源 | 把报表查询隔离到专用从库；设置 max_execution_time |
| 跨机房带宽不足 | 开 slave_compressed_protocol=1；改用级联复制减少跨机房流量 |
| 主库有大表 DDL | 用 gh-ost / pt-online-schema-change 替代直接 ALTER |
Seconds_Behind_Master 忽然从 0 跳到很大再回 0 |
大事务在重放，属正常现象；用 pt-heartbeat 得到更平滑的真实延迟 |
延迟期间的应急措施：把读流量临时切回主库（改代理 readPool 或应用配置），等追平后再切回。
5.6 read_only 未生效导致从库被写
现象：从库出现了主库没有的数据，随后引发 1062 / 1032 复制中断。
原因排查：
SHOW VARIABLES LIKE '%read_only%';| 情况 | 说明 |
|---|---|
read_only=OFF |
根本没设。改 my.cnf 并 SET GLOBAL read_only=1 |
read_only=ON 但仍被写入 |
read_only 对拥有 SUPER 权限的用户无效（包括 root）。应用账号若被授予了 SUPER 或 ALL PRIVILEGES ON *.*，就能绕过 |
只设了 read_only 没设 super_read_only |
MySQL 5.7.8+ 应同时设 super_read_only=1，连 SUPER 用户也禁止写 |
只在 my.cnf 里设了但没重启 |
用 SET GLOBAL 立即生效，同时保留配置文件设置 |
用 SET GLOBAL 设了但重启后失效 |
必须同时写入 my.cnf 持久化（MySQL 8.0 可用 SET PERSIST read_only=1） |
| 复制线程本身能写 | 这是正常且必需的，复制 SQL 线程不受 read_only 限制 |
正确配置：
SET GLOBAL read_only = 1;
SET GLOBAL super_read_only = 1; -- MySQL 5.7.8+；MariaDB 与 5.6 无此参数[mysqld]
read_only = 1
super_read_only = 1补充措施：
- 应用账号不要授予 SUPER / ALL ON *.*，只给必要的库级权限。
- 从库上给应用账号只授
SELECT（Amoeba 的 dbServers.xml 中从库账号可单独配置，见 4.10 节）。 - 代理层（ProxySQL / MaxScale）严格路由，禁止写语句发到从库。
- MariaDB 无
super_read_only，可用init_connect或代理层弥补。 - 定期用 pt-table-checksum 校验，尽早发现被写入。
5.7 Amoeba 连不上或认证失败
| 现象 | 原因与解决 |
|---|---|
客户端连 Amoeba 报 Access denied for user 'test'@'...' |
amoeba.xml 的 authenticate 段 user/password 与客户端使用的不一致。注意这是 Amoeba 自己的账号，与 MySQL 账号无关 |
客户端能连上 Amoeba，但执行 SQL 报 Access denied 或 can't connect to MySQL server |
dbServers.xml 的 user/password 与后端 MySQL 上授权的账号不匹配。源文档常见坑：MySQL 上授权密码是 123，而 dbServers.xml 写的是 123321 |
报 Unknown database 'aaa' 或启动失败 |
dbServers.xml 中 schema 指定的库在某个后端 MySQL 上不存在。必须在所有后端节点上都建好该库 |
报 no available connection / NO available datasource |
后端 MySQL 宕机、端口不通、连接池被打满（调大 maxActive）、或授权 IP 不含 Amoeba 服务器 IP |
| 连接被拒绝（Connection refused） | Amoeba 未启动 / 端口写错（默认 8066，源文档改为 3307）/ amoeba.xml 中 ipAddress 写成了 127.0.0.1 |
| 客户端 IP 被拒 | conf/access_list.conf 白名单不含该 IP |
| MySQL 8.0 后端认证失败 | Amoeba 不支持 caching_sha2_password。把账号改为 ALTER USER 'test'@'%' IDENTIFIED WITH mysql_native_password BY '123321'; |
| 中文数据乱码 | 配置文件声明为 gbk；在客户端连接后执行 SET NAMES utf8mb4，或后端库统一 utf8mb4 并检查 Amoeba 的 serverCharset |
| 读写分离不生效，所有 SQL 都走主库 | needParse 不是 true；或 writePool/readPool 的注释未去掉；或语句处于显式事务中（事务内走 defaultPool，属正常设计） |
| 修改了配置没有效果 | Amoeba 所有配置修改都必须重启才生效 |
排错命令：
tail -100 /usr/local/amoeba/nohup.out # 最重要，几乎所有错误都在这里
ps -ef | grep amoeba
netstat -tlnp | grep -E '8066|3307'
telnet 192.168.169.166 3306 # 从 Amoeba 机器测后端连通性
mysql -utest -p123321 -h192.168.169.166 -P3306 # 从 Amoeba 机器直连后端验证账号把日志级别调到 DEBUG 以观察 SQL 路由：
vim /usr/local/amoeba/conf/log4j.xml
# 将 <level value="INFO"/> 改为 <level value="DEBUG"/>，重启后可看到每条 SQL 走了哪个 dbServer5.8 Amoeba 内存不足与 JVM 参数调整
典型报错：
java.lang.OutOfMemoryError: Java heap space
java.lang.OutOfMemoryError: unable to create new native thread
The stack size specified is too small, Specify at least 228k处理：
vim /usr/local/amoeba/bin/amoeba# 栈太小报错 → 至少 228k
DEFAULT_OPTS="-server -Xms256m -Xmx256m -Xss228k"
# 堆溢出 → 调大堆（建议不超过物理内存的 60%），并加 GC 日志与溢出转储
DEFAULT_OPTS="-server -Xms1024m -Xmx1024m -Xss256k \
-XX:+HeapDumpOnOutOfMemoryError -XX:HeapDumpPath=/usr/local/amoeba/logs \
-Xloggc:/usr/local/amoeba/logs/gc.log -XX:+PrintGCDetails -XX:+PrintGCDateStamps"配套调整：
<!-- amoeba.xml：降低线程数与队列，减少内存占用 -->
<property name="clientSideThreadCount">64</property>
<property name="serverSideThreadCount">128</property><!-- dbServers.xml：连接池不要开得过大，每个连接都占内存 -->
<property name="maxActive">200</property>
<property name="maxIdle">200</property>系统层面：
# 提高文件描述符与进程数上限（unable to create new native thread 常因此）
ulimit -n 65535
ulimit -u 65535
cat >> /etc/security/limits.conf <<'EOF'
* soft nofile 65535
* hard nofile 65535
* soft nproc 65535
* hard nproc 65535
EOF
free -m # 确认物理内存充足
jstat -gcutil $(pgrep -f amoeba) 1000 # 观察 GC 与堆使用率
jmap -heap $(pgrep -f amoeba)若并发连接数需求很大（数千），Amoeba 的线程模型会成为瓶颈，应改用 ProxySQL（C++ 编写，单进程可支撑数万连接）。
六、练习与思考题
6.1 原理理解题
1. MySQL 架构中一主多从、双主、级联这三种架构的区别和优缺点。
| 架构 | 区别 | 优点 | 缺点 |
|---|---|---|---|
| 一主多从 | 一台 master 直接带多台 slave | 结构简单；读能力可扩展；任一从库故障不影响其他 | 从库越多 master 的 dump 线程与带宽压力越大；master 是单点，故障后需人工/工具切换 |
| 双主 | 两台互为主从 | 两端都可读写；主库故障时另一端可立即接管写并在恢复后自动回传数据；便于跨机房 | 同时写同一数据会冲突（需 auto_increment_increment/offset 与业务层分流）；数据一致性风险高；不能提升写性能（每台都要承担全部写） |
| 级联 | master → slave → slave | 大幅降低 master 的复制压力；适合从库数量非常多或跨机房场景（跨机房只传一份） | 延迟逐级累加；中间节点故障影响其下所有从库；中间节点必须开 log_slave_updates，多一次写 binlog 开销。一般不推荐，更倾向于分拆集群 |
2. 主打开二进制日志，从要不要开二进制日志？
不开也能复制。但推荐开启，理由：便于把从库提升为主库；级联复制必需（配合 log_slave_updates）；主库故障期间从库承接写入后可用其 binlog 把数据补回；MySQL 5.6 的 GTID 复制强制要求。
3. 主和从的读写情况怎么分开？
应用层多数据源（Sharding-JDBC、Spring AbstractRoutingDataSource）或代理层中间件（Amoeba、ProxySQL、MyCat、MaxScale），并在从库设置 read_only=1 + super_read_only=1 兜底。
4. 双主 MySQL 复制架构能同时写相同的数据吗？两边可以写不同的数据吗？
不能同时写相同的数据（会冲突、更新丢失）。可以写不同的数据：通过业务程序层指定某些表的写全在一端、另一些表的写全在另一端；或用复制过滤参数让一部分库表从 A 复制到 B、另一部分从 B 复制到 A；或用两个库分别由 A、B 写。
6.2 实战题
5. 某公司 MySQL 数据库运行了一年，现在要求搭建 MySQL AB 复制，检查主库后发现这一年没有使用二进制日志，请问如何做复制？
参考答案（步骤）：
- 在主库
my.cnf中加入server_id、log_bin=mysql-bin、binlog_format=ROW、sync_binlog=1、expire_logs_days=7，选业务低峰期重启 MySQL 使 binlog 生效（这一步必须重启，无法动态开启）。 - 从库安装同版本 MySQL，配置
server_id（不同值）、relay_log、read_only=1、log_slave_updates=1。 - 主库创建复制账号：
GRANT REPLICATION SLAVE ON *.* TO 'copy'@'从库IP' IDENTIFIED BY 'copy'; - 主库用
mysqldump --all-databases --single-transaction --master-data=2 --routines --triggers --events做一致性全量导出（InnoDB 表不锁表；若有 MyISAM 表则需FLUSH TABLES WITH READ LOCK后再导，并记录SHOW MASTER STATUS）。 - 全量文件传到从库并导入。
- 从库用导出文件头部注释中的
MASTER_LOG_FILE/MASTER_LOG_POS执行CHANGE MASTER TO，然后START SLAVE。 SHOW SLAVE STATUS\G确认两个Yes、Seconds_Behind_Master=0。- 用 pt-table-checksum 校验数据一致性。
关键点：一年没开 binlog 不影响做复制，因为复制只需要"某个一致性时间点的全量快照 + 从该点之后的 binlog"，历史 binlog 并不需要。
6. 在实际大数据量数据库中，因负载太高或网络问题造成主从复制时有延时，作为 DBA 如何处理？
参考答案：
- 定位瓶颈：比较
主库Position - Read_Master_Log_Pos（网络/IO 线程）与Read_Master_Log_Pos - Exec_Master_Log_Pos（SQL 线程重放），用 pt-heartbeat 测真实延迟。 - SQL 线程瓶颈：开启并行复制（
slave_parallel_type=LOGICAL_CLOCK+slave_parallel_workers=8，8.0 用 WRITESET）；确保所有表有主键；拆分大事务；用 gh-ost 做在线 DDL。 - IO 瓶颈：从库改
innodb_flush_log_at_trx_commit=2+sync_binlog=0；升级 SSD；增大innodb_buffer_pool_size。 - 网络瓶颈：开
slave_compressed_protocol=1；改用级联复制减少跨机房流量；升级带宽。 - 架构层面：读写分离时用支持延迟感知的代理（ProxySQL 的
max_replication_lag）自动摘除延迟从库；把报表大查询隔离到专用从库；对一致性要求高的读走主库；引入 Redis 缓存降低读压力；数据量过大时做分库分表。 - 监控与预案：建立延迟告警（如 >30 秒告警），延迟期间临时把读流量切回主库。
7. 如果一个 LAMP 架构在深圳机房运行，如何尽量无影响地把它迁移到惠州机房？
参考答案（原文档答案）：
- 在惠州机房搭建一套 LAMP，MySQL 做双主架构，Web 做 rsync 远程实时同步。
- 将网站域名对应的 IP 地址 A 记录改为惠州的 IP。
- 当深圳机房服务器无连接时，即可关闭深圳机房的服务器。
补充要点：迁移前把域名 TTL 提前调小（如 60 秒）以加快解析生效；双主搭建时注意 auto_increment_increment/offset 避免自增冲突；切换前用 pt-table-checksum 校验两地数据一致；保留深圳机房只读运行一段时间作为回滚方案；静态文件也可先用 CDN 过渡。
8. 半同步复制如何做到永久生效？
set global 设置在 MySQL 重启后失效。需把 plugin_load_add、rpl_semi_sync_master_enabled=1、rpl_semi_sync_slave_enabled=1、rpl_semi_sync_master_timeout 等写入 my.cnf（详见 3.2.7 节）。
6.3 动手练习
- 用两台虚拟机（CentOS 7 + MariaDB）搭建一主一从复制，验证在主库建库建表后从库自动同步，并在从库上尝试写入，观察数据不会回传主库。
- 在上题基础上改为一主两从，再改造为级联复制（A→B→C），验证不加
log_slave_updates=1时 C 收不到数据、加上后能收到。 - 把一主一从改造为双主，配置
auto_increment_increment=2与不同的auto_increment_offset，在两端各插入 5 条自增记录，验证主键不冲突。 - 用 GTID 方式重新搭建一遍主从，对比
CHANGE MASTER TO语句的差异；然后人为在从库插入一条冲突数据造成 1062 错误，用注入空事务的方式跳过，并用 pt-table-checksum 找出不一致的表。 - 配置半同步复制，用
stop从库 MySQL 的方式观察主库插入耗时变为 10 秒后自动降级为异步，检查Rpl_semi_sync_master_no_tx与Rpl_semi_sync_master_status的变化。 - 配置延时复制
master_delay=60，模拟"误删表"场景，用START SLAVE UNTIL把延时从库恢复到误操作前一刻并把数据回灌主库。 - 在从库上配置
replicate_wild_do_table=aaa.%，验证只有aaa库的变更被复制；再改用replicate_do_db=aaa，用use test; insert into aaa.t1 ...复现"默认库陷阱"导致的数据丢失。 - 部署 Amoeba 实现读写分离：完成 JDK 安装、
amoeba.xml与dbServers.xml配置、后端授权，通过 8066 端口连接并用general_log证明 INSERT 落到主库、SELECT 落到从库。 - 在 Amoeba 上把
readPool改为multiPool虚拟节点实现两台从库轮询，多次执行SELECT @@server_id观察轮询效果；再把loadbalance改为 3（HA）观察行为差异。 - 用 Amoeba 的
rule.xml分别实现取模水平切分、范围水平切分与垂直切分，验证不带切分字段的 SQL 会广播到所有节点。 - 对比实验：在从库上分别设置
innodb_flush_log_at_trx_commit=1/sync_binlog=1与=2/=0，用 sysbench 在主库压测，观察从库Seconds_Behind_Master的差异。 - 用 pt-heartbeat 搭建延迟监控，人为在主库执行一个删除 100 万行的大事务，对比
Seconds_Behind_Master与 pt-heartbeat 报告的延迟值差异。
文档信息
| 项目 | 内容 |
|---|---|
| 编写 | 苏俊全 |
| 版本 | V1 |
| 日期 | 2019-03-13 |
| 邮箱 | junquan.su@jwis.cn |
| 公司 | 深圳杰为软件（系统）深圳有限公司 |
此处评论已关闭