目录
2. sync_binlog 与 innodb_flush_log_at_trx_commit
可以在任意个主从库之间建立复杂的复制拓扑结构，如普通的一主一（多）从、双（多）主复制、级联复制，MySQL 5.7.2 后新增的多源复制，特殊场景下使用的 Blackhole 引擎与日志服务器等等。复制中的 MySQL 服务器须要遵循以下基本原则：
- 拓扑中的每个服务器必须有一个唯一的 server_id 和 server_uuid。
- 一个主库可以有多个从库（或者说一个从库可以有多个兄弟从库）。
- 如果打开了 log_slave_updates 选项，一个从库可以把其对应主库上的数据变化传播到它的从库。
一、复制拓扑
1. 一主一（多）从
（1）一级主从
一主一从的基本配置是最简单的拓扑结构，而一主多从的结构和基本配置差不多简单，因为从库之间根本没有交互，它们仅仅是连接到同一个主库。图1 显示了这种结构。
尽管这是非常简单的拓扑结构，但它非常灵活，能满足多种需求。为满足数据一致性和主从切换需求，从库原则上应为只读，下面是从库的一些用途：
- 为不同的角色使用不同的从库，例如为满足读写分离需求，在从库添加不同于主库的适合读操作的索引（不要忘记在从库执行任何写操作前先 set sql_log_bin=0）。
- 把一台从库只当做备用主库，除了复制没有其它数据传输。
- 将一台从库放到远程数据中心，用作灾难恢复。
- 延迟复制一个从库，以备恢复用户错误。
- 使用其中一个从库，作为备份或测试使用。
这种结构流行的原因是它避免了很多其它拓扑的复杂性。例如，可以方便地比较不同从库重放的事件在主库二进制日志中的位置，当然如果启用 GTID 就更跟简单了，支持自动定位。换句话说，如果在同一个逻辑点停止所有从库的复制，它们正在读取的是主库上同一个日志文件的相同物理位置。这是个很好的特性，可以减轻管理员许多工作，例如把从库提升为主库。
这种特性只存在于兄弟从库之间。在没有直接的主从或者兄弟关系的服务器上去比较日志文件的位置要复杂很多。例如树形复制或分布式主库，很难计算出复制事件的逻辑顺序。
（2）级联主从
如果正在将主库复制到大量从库中，不管是把数据分发到不同的地方，还是提供更高的读性能，使用级联复制都能更好地管理，如图2 所示。
这种设计的好处是减轻了主库的负担，将读负载分发到多个从库。缺点是中间层出现的任何错误都会影响到多个服务器。如果每个从库和主库直接相连就不会存在这样的问题。同时中间层次越多，处理故障就会越复杂和困难。
2. 双（多）主复制
（1）主动-主动模式下的双主复制
双主复制包含两台 MySQL 服务器，每一个都被配置为对方的主库和从库，换句话说，它们是一对主库。图3 显示了该结构。
主动-主动模式指的是两台服务器对于应用均可读写，通常用于特殊目的。例如一个可能的应用场景是两个处于不同地理位置的办公室，并且都需要一份可写的数据拷贝。这种配置最大的问题时如何解决冲突，两个可写的互主服务器导致的问题非常多。这通常发生在两台服务器同时修改一行记录，或同时在两台服务器上向一个包含 auto_increment 列的表里插入数据。这些问题会经常发生，而且不易解决，因此并不推荐这种模式。下面演示两个常见的问题。
- 在两台机器更新顺序不同导致数据不一致或 1032 错误。
-- 主1
mysql> select * from t1;
+---+
| a |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
-- 主2
mysql> select * from t1;
+---+
| a |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
-- 主2延迟复制，模拟两个主库不同的执行顺序
stop slave;
change master to master_delay = 10;
start slave;
-- 主1
set binlog_format='statement';
update t1 set a=a+1;
-- 主2在复制之前（10秒之内）执行
set binlog_format='statement';
update t1 set a=a*2;
-- 10秒之后查询
-- 主1
mysql> select * from t1;
+------+
| a |
+------+
| 4 |
+------+
1 row in set (0.00 sec)
-- 主2
mysql> select * from t1;
+------+
| a |
+------+
| 3 |
+------+
1 row in set (0.00 sec)复制没有报告任何错误，但两个库的数据已经不一致。主1 上先执行的 a=a+1，紧接着由于复制执行 a=a*2，最终结果是 4。主2 上由于延迟复制，先执行 a=a*2，10 秒后执行复制的 a=a+1，最终结果是 3。此实验是在 binlog_format='statement' 下进行的，如果设置 binlog_format='row'，则两个库（首先是主1，10 秒后是主2）的都会报 1032 错误，show slave status 中显示的错误如下：
Last_SQL_Errno: 1032
Last_SQL_Error: Could not execute Update_rows event on table test.t1; Can't find record in 't1', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log binlog.000001, end_log_pos 2534报 1032 的原因是应用复制时记录已经发生改变，找不到更新时间点的数据行。
- 由 auto_increment 引起的 1062 错误。
-- 主1
use test;
create table t1(a int auto_increment primary key);
delimiter //
create procedure p1(a int)
begin
declare i int default 1;
while i<=a do
insert into t1(a) select null;
set i=i+1;
end while;
end;
//
delimiter ;
call p1(1000);
-- 主2，在主1执行过程期间同时在主2执行
call p1(1000);show slave status 中显示如下错误：
Last_SQL_Errno: 1062
Last_SQL_Error: Could not execute Write_rows event on table test.t1; Duplicate entry '366' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log binlog.000001, end_log_pos 101521因为本机插入的数据与复制插入的数据产生冲突而报 1062 错误。通过在两个服务器设置不同的 auto_increment_offset 和 auto_increment_increment，可以让 MySQL 自动为 insert 语句选择不互相冲突的值，稍微增加了点安全性。
-- 主1
set auto_increment_offset=1;
set auto_increment_increment=2;
call p1(1000);
-- 主2，在主1执行过程期间同时在主2执行
set auto_increment_offset=2;
set auto_increment_increment=2;
call p1(1000);主1 上插入单数，主2 插入双数，复制与本机数据不冲突。过程执行完后，两个库都插入了2000条数据，但缺省配置 innodb_autoinc_lock_mode=2 会造成序列值不连续。
-- 主1
mysql> select count(*),min(a),max(a) from t1;
+----------+--------+--------+
| count(*) | min(a) | max(a) |
+----------+--------+--------+
| 2000 | 1 | 2414 |
+----------+--------+--------+
1 row in set (0.00 sec)
-- 主2
mysql> select count(*),min(a),max(a) from t1;
+----------+--------+--------+
| count(*) | min(a) | max(a) |
+----------+--------+--------+
| 2000 | 1 | 2414 |
+----------+--------+--------+
1 row in set (0.00 sec)可以看到复制正常，两个服务器数据是一致。但这只极端理想的场景：从空表开始插入数据，配置复制时没有联机访问。如果在配置双主复制时已经有数据，情况将复杂的多。同时允许向两台主库写入很危险，极易造成复制错误或数据不一致。数据不同步还仅仅是开始。当正常的复制发生错误停止了，但应用仍然在同时向两台服务器写入数据，这时候会发生什么呢？不能简单地把数据从一台服务器复制到另外一台，因为这两台机器上需要复制的数据都可能发生了变化。解决这个问题将非常困难。总的来说，允许向两个服务器上同时写入所带来的麻烦远远大于其带来的好处。只要作为从库可写，就存在主从数据不一致的风险。
（2）主动-被动模式下的双主复制
这是前面描述的双主结构的变体，主要区别在于其中的一台服务器是只读的被动服务器。这种拓扑结构能够避免之前讨论的问题，也是构建容错性和高可用性系统的强大方式。两个服务器从硬件到操作系统再到 MySQL 配置都应该完全相同。为便于故障转移，只读最好由客户端应用保证，通过设置以下系统变量强制只读仅作为可选项。
set global read_only=1;
set global super_read_only=1;这种方式使得反复切换主动和被动服务器非常方便，因为服务器的配置是对称的。这使得故障转移和故障恢复相对容易。它也允许用户在不关闭服务器的情况下执行维护、优化、升级操作系统（或者应用程序、硬件等）或其它任务。
例如，执行 alter table 操作可能会锁住整个表，阻塞对表的读写，这可能会花费很长时间并导致服务中断。
use test;
create table t1(a bigint auto_increment primary key);
insert into t1 select -1;
commit;
-- session 1
set autocommit=0;
insert into t1 select null;
-- session 2
alter table t1 add column (b int);
-- session 3
update t1 set a=-2 where a=-1;
-- session 4
show processlist;
+-----+-------------+-----------------+------+-------------+------+-----------------------------------------------------------------------------+-----------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+-------------+-----------------+------+-------------+------+-----------------------------------------------------------------------------+-----------------------------------+
| 1 | root | localhost:33309 | NULL | Binlog Dump | 6159 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 3 | system user | | NULL | Connect | 6104 | Waiting for master to send event | NULL |
| 4 | system user | | NULL | Connect | 340 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL |
| 170 | root | localhost:33981 | test | Query | 17 | Waiting for table metadata lock | alter table t1 add column (b int) |
| 171 | root | localhost:33982 | test | Query | 9 | Waiting for table metadata lock | update t1 set a=-2 where a=-1 |
| 172 | root | localhost:33983 | test | Query | 0 | init | show processlist |
| 173 | root | localhost:33986 | test | Sleep | 25 | | NULL |
+-----+-------------+-----------------+------+-------------+------+-----------------------------------------------------------------------------+-----------------------------------+
7 rows in set (0.00 sec)可以看到，如果在执行 alter table 时，表上有未提交的事务，alter table 本身和其后的所有 DML 都会等待 table metadata lock，而不论这些后续的 DML 操作的是哪些行，因为 metadata lock 是一个表级锁。当 session 1 的事务提交或回滚，session 2 才能得以执行。高并发场景下，在线 DDL 极有可能造成灾难性的后果。一种暴力的解决方案是，先 kill 掉所有 sleep 的 MySQL 线程，紧接着执行 alter table，这样不会因为 metadata lock 而卡住后面的 DML。在这个例子中，如果 session 2 可以先得到执行，即使操作需要很长时间，也不会对后面的 DML 造成等待。脚本文件的内容可能为：
#!/bin/bash
source ~/.bashrc
rm -rf /tmp/kill.sql
mysql -u root -p123456 -P3306 -h127.0.0.1 -e "select * into outfile '/tmp/kill.sql' from (select concat('kill ',id,';') from information_schema.processlist where command='sleep' union all select 'set sql_log_bin=0;' union all select 'alter table test.t1 add column (b int);') t;"
mysql -u root -p123456 -P3306 -h127.0.0.1 < /tmp/kill.sql注意，将所有 sleep 的线程都杀掉这个操作会导致没有提交的事务回滚，是有风险的，需要根据业务场景进行操作。主库上大表的 DDL 操作（如 drop、truncate 等等）可能引起从库的复制延时变大。在不影响数据一致性的前提下，一种可能的解决方案是执行 DDL 前先 set sql_log_bin=0，让大的 DDL 操作不写入 binlog，从而不会复制到从库，之后再在从库手动执行一遍。
然而在主动-被动模式的双主配置下，在线 DDL 变得更具可操作性。可以先停止主动服务器上的复制线程，这样就不会复制被动服务器上执行的任何更新。然后在被动服务器上执行 alter table 操作，交换角色，最后在先前的主动服务器上启动复制线程。这个服务器将会读取中继日志并执行相同的 alter 语句。这可能花费很长时间，但不要紧，因为该服务器没有为任何活跃查询提供服务。假设 A、B 库配置了双主复制，A 为主动库提供服务，B 为被动库进行复制。如果需要在一个大表上增加字段，可能的操作步骤如下：
- A 库 stop slave，此时 A 不会复制 B 的更新。
- B 库执行 alter table，B 此时仍然复制来自 A 的更新。
- 交互角色，B 变为主动提供读写服务，A 变为被动，这意味着应用连接需要切换到 B。
- A 库 start slave，此时 A 将重放 B 上的 alter table 语句和其它更新。
整个过程不停库，只需修改应用连接的数据库即可。如果使用虚 IP 技术，则应用不需要做任何修改，原本可能导致服务中断的 DDL 操作将对应用完全透明。下面的过程用于验证第 2 步操作中，B 上的 alter table 不会阻塞它对 A 的复制。
-- 1. A停止复制
stop slave;
-- 2. B上执行一个长时间的alter table操作
alter table t1 add column (b int);
-- 3. 在上一步执行过程中，A上操作同一个表
call p1(1000000);
-- 4. B确认复制状态和线程状态
show slave status\G
show processlist;
select max(a) from t1;
-- 5. 前面的步骤都执行完后，A开启复制
start slave;show slave status 的 Read_Master_Log_Pos 和 Exec_Master_Log_Pos 不停改变，show processlist 中的 State 没有任何锁，t1 表的数据一直处于更新状态，说明 B 对 A 的复制不会被其上的 alter table 阻塞。注意，如果在第 2 步执行前从库被修改表上有未提交的事务（从主库复制过来），依然会阻塞第 2 步执行。但情况要比在主库上缓解很多，其一是因为从库缺省为单线程复制，没有并发，事务应该很快被提交。其次是从库可以设置成 autocommit=on，这也会缩短 alter table 语句被阻塞的时间。调换以上步骤 2 和 3 的执行顺序，可以验证 B 对 A 的复制同样也不会阻塞其上的 alter table 语句执行。
上面的步骤并非无懈可击，成立的前提是 alter table 与复制的语句兼容，否则会导致复制错误。但通常来说都是先修改数据库表结构，再升级应用程序，这样看来此前提成立是自然而然的，问题并不大。下面的过程只是演示一种出错的情况。
-- 1. A停止复制
stop slave;
-- 2. A上执行一个长时间的操作
call p1(1000000);
-- 3. 在上一步执行过程中，B上alter table同一个表
alter table t1 add column b int,drop column a;
-- 4. B确认复制状态和线程状态
show slave status\G由于 t1.a 列被删除，添加了一列 b，而列 a 与列 b 的数据类型不兼容，导致 B 库上的复制报错：
Last_Errno: 1677
Last_Error: Column 0 of table 'test.t1' cannot be converted from type 'bigint' to type 'int(11)'执行下面的修复后复制继续：
alter table t1 change b a bigint auto_increment primary key;
stop slave;
start slave;让我们看看主动服务器上更新时会发生什么事情。更新被记录到二进制日志中，通过复制传递给被动服务器的中继日志中。被动服务器重放中继日志里的查询，如果开启了 log_slave_updates 选项，它还会将复制事件记录到自己的二进制日志中。由于复制事件的服务器 ID 与主动服务器相同，因此主动服务器将忽略这些事件，通过这种方式避复制免死循环。设置主动-被动的双主拓扑结构在某种意义上类似于创建一个热备份，但可以使用这个“备份”来提高性能，例如，用它来执行读操作、备份、轮换维护以及升级等。
（3）拥有从库的双主结构
另外一种相关的配置是为每个主库增加一个从库，如图4 所示。
这种配置的优点是增加了冗余，对于不同地理位置的复制拓扑，能够消除站点单点失效的问题。也可以像平常一样，将读查询分配到从库上。如果在本地为了故障转移使用双主结构，这种配置同样有用。当主库失效时，有两种不同的处理方式，一是用从库代替主库，二是把从库指向另一个不同的主库。以图4 为例，假设主库 1 失效，采用第一种方式，需要将从库 1 提升为新的主库 1，修改主库 2 的复制配置，指向新的主库 1，并将新主库指向主库 2，保持双主配置。如果采用第二种方式，只需要将从库 1 指向主库 2，但这样拓扑已从双主变为一主两从。
（4）环形复制
如图5 所示，双主结构实际上是环形结构的一种特例。环形结构可以有三个或更多的主库。每个服务器都是在它之前的服务器的从库，是在它之后的服务器的主库。这种结构也称为环形复制（circular replication）。
环形结构没有双主结构的一些优点，例如对称配置和简单的故障转移，并且完全依赖于环上的每一个可用节点，这大大增加了整个系统失效的几率。如果从环中移除一个节点，这个节点发起的事件就会陷入无限循环：它将永远绕着服务器循环。因为唯一可以根据服务器 ID 将其过滤的服务器是创建这个事件的服务器。下面的步骤可以模拟这种场景，M1、M2、M3 构成的三个主库的环形复制，M1 复制 M3、M3 复制 M2、M2 复制 M1。
-- 1. M1停止sql_thread线程
stop slave sql_thread;
-- 2. M2停止sql_thread线程
stop slave sql_thread;
-- 3. M3做更新
insert into test.t1 values (1);
commit;
-- 4. M3停库
mysqladmin -uroot -p123456 shutdown
-- 5. M1启动sql_thread线程，此时M3的更新复制到M1
start slave sql_thread;
-- 6. M1复制M2，此时原环形复制中移除了M3，其中master_log_file和master_log_pos从M2的show master status的输出得到。
stop slave;
change master to
master_host = '172.16.1.126',
master_port = 3306,
master_user = 'repl',
master_password = '123456',
master_auto_position = 0,
master_log_file='binlog.000002',
master_log_pos=664210;
start slave;
-- 7. M2启动sql_thread线程，此时M2复制了来自M3的更新，并继续传递给M1，复制陷入死循环。在M1、M2上查询test.t1，可以看到记录不停增长。
start slave sql_thread;如果三个主库都启用 GTID 复制，以上过程不会陷入死循环，因为复制不再通过 server_id 过滤本地事件，而是通过 server_uuid 复制事务。总的来说，环形结构非常脆弱，应该尽量避免。可以通过为每个节点增加从库的方式来减少环形复制的风险，如图6 所示。但这仅仅防范了服务器失效的风险，断电或者其它一些影响到网络连接的问题都可能破坏整个环。
4. 多源复制
MySQL 5.7.6 开始支持多源复制（Multi-Source Replication）。多源复制不同于多主复制，多主复制指的是在复制拓扑中存在多个主库，而多源复制是指一个从库可以同时从多个主库进行复制。图7 所示为两主一从的多源复制结构。
多源复制可用于将来自多个服务器的数据合并或备份到单个服务器，如合并表分片。应用事务时，多源复制不会检测或解决任何冲突，如果需要，这些任务将留给应用程序实现。在多源复制拓扑中，从库为每个接收其事务的主库创建复制通道。
（1）复制通道
复制通道是一个字符串，表示从主库到从库的复制路径。为提供与先前版本的兼容性，MySQL 服务器在启动时自动创建一个默认通道，其名称为空字符串（""）。这个通道始终存在，不能被用户创建或销毁。如果没有创建其它通道（具有非空名称），则复制语句仅作用于缺省通道，以便旧版从库的所有复制语句按预期运行。多源复制中，从库打开多个命名通道，每个通道都有自己的中继日志和复制线程。一旦复制通道的 IO 线程接收到事务，它们就会被添加到通道对应的中继日志文件中并传递给 SQL 线程，这使得每个通道能够独立运行。复制通道还与主机名和端口关联，可以将多个通道分配给主机名和端口的相同组合。在 MySQL 8.0 中，添加到一个从库的最大通道数为 256。每个复制通道独立配置，必须具有唯一非空名称。
（2）配置
多源复制拓扑至少需要配置两个主库和一个从库。可以将多源复制中的主库配置为使用基于全局事务标识符（GTID）的复制或基于二进制日志位置的复制。配置多源复制的步骤如下。
1. 将从库的 master_info_repository、relay_log_info_repository 系统变量设置为TABLE。
stop slave;
set global master_info_repository = 'table';
set global relay_log_info_repository = 'table';这是 MySQL 8.0 的默认值。多源复制拓扑中的从库需要使用表存储主库二进制日志和本身中继日志的信息，多源复制与基于文件（file）的存储库不兼容。现在不推荐将这两个参数设置为'file'。
2. 将主库添加到从库
change master to
master_host = '172.16.1.125',
master_port = 3306,
master_user = 'repl',
master_password = '123456',
master_auto_position = 1
for channel 'master-125';
change master to
master_host = '172.16.1.126',
master_port = 3306,
master_user = 'repl',
master_password = '123456',
master_auto_position = 1
for channel 'master-126';这里使用 GTID 复制，设置两主一从的多源复制。CHANGE MASTER TO 语句通过使用 FOR CHANNEL 子句将新主库添加到复制通道。多源复制与自动定位兼容。
3. 启动从库复制
-- 启动所有线程所有通道的复制
start slave;
-- 启动所有通道的io_thread线程
start slave io_thread;
-- 启动所有通道的sql_thread线程
start slave sql_thread;
-- 启用单个通道
start slave for channel 'master_125';
start slave io_thread for channel 'master_125';
start slave sql_thread for channel 'master_125';停止复制命令也启动复制类似，只是把 start 换成 stop。同样重置也可以选择重置所有和重置单一通道：
reset slave;
reset slave for channel 'master_125';（3）监控
监控可以使用 performance_schema.replication* 表，这些表的第一列都是 Channel_Name。注意 SHOW VARIABLES 语句不适用于多个复制通道。这些变量的信息已迁移到复制性能表。在具有多个通道的拓扑中使用 SHOW VARIABLES 语句仅显示默认通道的状态。
-- 查询特定通道的连接状态
mysql> select * from replication_connection_status where channel_name='master-125'\G
*************************** 1. row ***************************
CHANNEL_NAME: master-125
GROUP_NAME:
SOURCE_UUID: 8eed0f5b-6f9b-11e9-94a9-005056a57a4e
THREAD_ID: 10421
SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 41
LAST_HEARTBEAT_TIMESTAMP: 2019-06-24 16:21:31.583443
RECEIVED_TRANSACTION_SET:
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_QUEUED_TRANSACTION:
LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000
QUEUEING_TRANSACTION:
QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
QUEUEING_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000
1 row in set (0.00 sec)使用 SHOW SLAVE STATUS FOR CHANNEL 监控特定通道的状态，如果不加 FOR CHANNEL 子句，则返回所有复制通道的状态，每个通道一行。
（4）测试
-- 主库1
mysql> insert into test.t1 values(125);
Query OK, 1 row affected (0.01 sec)
-- 主库2
mysql> insert into test.t1 values(126);
Query OK, 1 row affected (0.01 sec)
-- 从库
mysql> select * from test.t1;
+------+
| a |
+------+
| 1 |
| 125 |
| 126 |
+------+
3 rows in set (0.00 sec)
-- 主库1
mysql> truncate table test.t1;
Query OK, 0 rows affected (0.01 sec)
-- 从库
mysql> select * from test.t1;
Empty set (0.00 sec)两个主库新增的数据都复制到从库，但只在一个主库清空表，从库表所有数据全部被清空。因此使用多源复制最好避免多个主库具有同名的数据库。
-- 主库1
mysql> create user 'u1'@'%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
-- 主库2
mysql> create user 'u1'@'%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
-- 从库
mysql> show slave status\G通道 master-125 复制状态正常，但 master-126 报错：
Last_SQL_Errno: 1396
Last_SQL_Error: Error 'Operation CREATE USER failed for 'u1'@'%'' on query. Default database: 'test'. Query: 'CREATE USER 'u1'@'%' IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$*B_B^@}R;15egC4\nYdRPGtaEXbF.jB36e2UpAZEoXEPck87oeMl4j8rO6iu5''建用户的时候报告 1396 错误，原因是 mysql 库中已经有了这个用户。恢复复制的过程如下：
1. 停止从库通道 master-126 的复制
stop slave for channel 'master-126';2. 在从库上确认出错的事务 ID
show slave status for channel 'master-126'\G
...
Retrieved_Gtid_Set: 53442434-8bfa-11e9-bc15-005056a50f77:1008-1009
Executed_Gtid_Set: 53442434-8bfa-11e9-bc15-005056a50f77:1-1008,
6a739bf0-961d-11e9-8dd8-005056a5497f:1-1885,
8eed0f5b-6f9b-11e9-94a9-005056a57a4e:1-24240
...可以看到，从库从 53442434-8bfa-11e9-bc15-005056a50f77 接收到事务 1009，但只执行到 1008，所以确定报错的事务为：53442434-8bfa-11e9-bc15-005056a50f77:1009。
3. 在从库上注入一个空事务跳过错误
set gtid_next='53442434-8bfa-11e9-bc15-005056a50f77:1009';
begin;commit;
set gtid_next=automatic;
start slave for channel 'master-126';对于 mysql 库，建议使用 REPLICATE_IGNORE_DB 将其屏蔽掉：
stop slave;
change replication filter replicate_ignore_db = (mysql);
start slave;在主库上对 mysql 库进行操作时，需要加 use mysql，否则不会进行过滤。
5. Blackhole 引擎与日志服务器
（1）Blackhole 存储引擎与复制
MySQL 8 中 show engines 命令返回的存储引擎如下：
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)像 MyISAM、InnoDB 一样，BlackHole 是另一种 MySQL 引擎。该引擎的功能可谓名副其实，任何写入到此引擎的数据均会被丢弃掉，不做实际存储，和 Linux 中的 /dev/null 文件所起的作用类似。创建一个 blackhole 的表时，MySQL 服务器在数据库目录创建一个 .frm 表定义文件，没有其它文件关联到这个表。虽然 blackhole 表不存储任何数据，但它却能够接收并重放二进制日志，如果开启了 log_slave_updates，它也能把复制向下传播，如同普通的级联复制拓扑一样。
当从库足够多时，会对主库造成很大的负载。每个从库会在主库上创建一个线程执行 binlog dump 命令。该命令读取二进制文件中的数据并将其发送给从库。每个从库都会重复这样的工作，它们不会共享 binlog dump 的资源。如果从库很多，并且有大的事件时，例如 binlog_format 为 statement 时一次很大的 load data infile 操作，主库的负载会显著上升，甚至可能由于从库同时请求同样的事件而耗尽内存并崩溃。另一方面，如果从库请求的数据不在文件系统的缓存中，可能会导致大量的磁盘检索，这同样会影响主库的性能并增加锁的竞争。
因此，如果需要多个从库，一个好办法是从主库移除负载并利用 blackhole 进行分发，即所谓的分发主库。分发主库实际上也是一个从库，它唯一的目的就是提取和提供主库的二进制日志。多个从库连接到分发主库，这使原来的主库摆脱了负担，如图8 所示。
很难说当主库数据达到多少时需要一个分发主库。按照通用准则，如果主库接近满负载，不应该为其建立 10 个以上的从库。如果只有少量写操作，或者只复制其中一部分表，则主库可以提供更多的复制。如果需要，可以使用多个分发主库向大量从库进行复制，或者使用级联的分发主库。对于跨数据中心的复制，从库设置 slave_compressed_protocol 能节约一些主库带宽。该变量是全局系统变量，缺省值为 off，可以动态设置。
还可以通过分发主库实现其它目的，如对二进制日志事件执行过滤和重放规则。这比在每个从库上重复进行日志记录、重放和过滤要高效得多。使用 blackhole 存储引擎可以支持更多的从库。虽然会在分发主库执行查询，但代价极小，因为 blackhole 表中没有任何数据。
一个常见的问题是如何确保分发服务器上的每个表都是 blackhole 存储引擎。如果在主库创建了一个表并指定了不同的存储引擎呢？确实，不管何时在从库上使用不同的存储引擎总会导致同样的问题。通常的解决方案是设置服务器的缺省存储引擎：
default_storage_engine=blackhole这只会影响那些没有指定存储引擎的 create table 的语句。如果有一个无法控制的应用，这种拓扑结构可能会非常脆弱。可以设置 disabled_storage_engines 禁用多个存储引擎。该系统变量为只读，只能通过配置文件修改，并重启 MySQL 服务器使之生效。下面演示如何联机搭建一个 blackhole 的分发主库。
- 服务器角色分配：
172.16.1.125：主库。假设为生产主库，可以在以下整个过程中存在负载。
172.16.1.126：blackhole 分发主库。一个初始化的 MySQL 服务器。
172.16.1.127：从库。
- MySQL 服务器配置：
172.16.1.125：
[mysqld]
server_id=1125
gtid_mode=ON
enforce-gtid-consistency=true
172.16.1.126：
[mysqld]
server_id=1126
gtid_mode=ON
enforce-gtid-consistency=true
default_storage_engine=blackhole
default_tmp_storage_engine=blackhole
disabled_storage_engines='innodb'
secure_file_priv='/tmp'
172.16.1.127:
server_id=1127
gtid_mode=ON
enforce-gtid-consistency=true
其它配置使用 MySQL 8 的缺省值。启用 GTID 复制，三台 MySQL 服务器均已创建复制账号。
（1）初始化 blackhole 分发主库
在 126 执行执行内容如下的脚本文件 init_blackhole.sh
source ~/.bashrc
# 全量导入主库，无数据
mysqldump --single-transaction --all-databases --host=172.16.1.125 -d --user=wxy --password=123456 | mysql -uroot -p123456
# 修改所有表的存储引擎为blackhole
rm -rf /tmp/black.sql
mysql -uroot -p123456 -e "
select concat('alter table ', table_schema, '.', table_name, ' engine=''blackhole''', ';')
from information_schema.tables
where table_schema not in ('information_schema','mysql','performance_schema','sys')
and table_type='BASE TABLE' into outfile '/tmp/black.sql';"
# 在执行的SQL文件第一行加入sql_log_bin=0，否则下级从库也会执行
sed -i '1i\set sql_log_bin=0;' /tmp/black.sql
mysql -uroot -p123456 < /tmp/black.sql（2）初始化从库
因为是联机配置复制，使用 xtrabackup 初始化从库。
# 将主库备份到从库，在125执行
xtrabackup -uroot -p123456 --socket=/tmp/mysql.sock --no-lock --backup --compress --stream=xbstream --parallel=4 --target-dir=./ | ssh mysql@172.16.1.127 "xbstream -x -C /usr/local/mysql/data/ --decompress"
# 从库执行应用日志，在127执行
xtrabackup --prepare --target-dir=/usr/local/mysql/data/
# 启动从库，在127执行
mysqld_safe --defaults-file=/etc/my.cnf &（3）启动复制
-- 在126执行
change master to
master_host = '172.16.1.125',
master_port = 3306,
master_user = 'repl',
master_password = '123456',
master_auto_position = 1;
start slave;
show slave status\G
-- 在127执行
change master to
master_host = '172.16.1.126',
master_port = 3306,
master_user = 'repl',
master_password = '123456',
master_auto_position = 1;
start slave;
show slave status\G至此完成了分发主库的复制拓扑结构的搭建。
（2）日志服务器
使用 MySQL 复制的另一种用途是创建没有数据的日志服务器。它唯一的目的就是更加容易重放或过滤二进制日志事件。假设有一组二进制日志或中继日志，可能从备份或者一台崩溃的服务器上获取，希望能够重放这些日志中的事件。最容易想到的是通过 mysqlbinlog 命令行工具从其中提取出事件，但更加方便和高效得方法是配置一个没有任何应用数据的 MySQL 实例并使其认为这些二进制日志是它拥有的。因为无须执行二进制日志，日志服务器也就不需要任何数据，它的目的仅仅是将复制事件提供给别的服务器。
我们来看看该策略是如何工作的。假设日志被命名为 binlog.000001、binlog.000002 等等，将这些日志放到日志服务器的日志文件夹中，假设为 /usr/local/mysql/data。然后在启动服务器前编辑 my.cnf 文件：
log_bin = /usr/local/mysql/data/binlog
log_bin_index = /usr/local/mysql/data/binlog.index服务器不会自动发现日志文件，因此还需要更新日志的索引文件。Linux 上可用下面的命令完成。
/bin/ls -1 /usr/local/mysql/data/binlog.[0-9]* > /usr/local/mysql/data/binlog.index确保运行 MySQL 的账户能够读写日志索引文件。现在可以启动日志服务器并通过 show master logs 命令来确保其找到日志文件。当主库失效但二进制日志尚存，可以设置一个日志服务器，把从库指向它，然后让所有从库赶上主库的失效点。
相比于使用 mysqlbinlog 来实现恢复，日志服务器有优势主要体现在：
- 速度快，因为无须将语句从日志导出来并传给 MySQL。
- 可以观察到复制过程。
- 容易处理错误，如跳过执行失败的语句。
- 便于过滤复制事件。
二、复制性能
我们可以将复制的时间分为两部分：一是事件从主库到从库的传输时间，二是事件在从库上的执行时间。事件在主库上记录二进制日志后到传递到从库的时间理论上非常快，因为它只取决于网络速度。MySQL 二进制日志的 dump 线程不是通过轮询方式请求事件，而是由主库来通知从库新的事件，因为前者低效且缓慢。从主库读取一个二进制日志事件是一个阻塞型网络调用，当主库记录事件后，马上就开始发送。因此可以说，只要 IO 线程被唤醒并且能够通过网络传输数据，事件就会很快到达从库。但如果网络很慢并且二进制日志事件很大，记录二进制日志和在从库上执行的延迟可能会非常明显。如果查询需要执行很长时间而网络很快，通常可以认为重放时间占据了更多的复制时间开销。
本节主要从日志持久化、组提交与多线程复制，以及新增的 WRITESET 特性三个方面，讨论对复制性能产生的影响。我们先简要介绍每种特性的基础知识，然后针对不同情况进行测试，最后由测试结果得出结论。所有测试均基于 GTID 的标准主从异步复制。
1. 测试规划
这里使用的思路是：记录主库加压前后的 GTID，得到从库需要执行的事务数。然后在从库上执行复制，记录执行时间，得到从库的每秒执行事务数（TPS）作为衡量复制性能的指标。测试目的在于对比不同情况下复制的性能，而不是针对测量绝对值进行优化。主库加压使用 tpcc-mysql 基准测试工具。
（1）测试环境
测试环境如下，已经配置好 GTID 异步复制。
主库：172.16.1.125
从库：172.16.1.126
MySQL版本：8.0.16
测试通用参数：
主库：
server_id=1125
gtid_mode=ON
enforce-gtid-consistency=true
innodb_buffer_pool_size=4G
从库：
server_id=1126
gtid_mode=ON
enforce-gtid-consistency=true
innodb_buffer_pool_size=4G
（2）tpcc-mysql 测试前准备
TPC-C 是专门针对联机交易处理系统（OLTP 系统）的规范，tpcc-mysql 则是 percona 公司基于 TPC-C 衍生出来的产品，专用于 MySQL 基准测试，下载地址为“GitHub - Percona-Lab/tpcc-mysql”。这里使用 tpcc-mysql 只是为了给主库加压。使用 tpcc-mysql 开始测试前完成以下准备工作，所有步骤均在主库上执行：
1. 安装
cd tpcc-mysql-master/src
make2. 建立测试库
mysql -uroot -p123456 -e "create database tpcc_test;"3. 建表和索引
cd tpcc-mysql-master
mysql -uroot -p123456 -Dtpcc_test < create_table.sql
mysql -uroot -p123456 -Dtpcc_test < add_fkey_idx.sql4. 生成数据
tpcc_load -h127.0.0.1 -d tpcc_test -u root -p "123456" -w 10-w 参数指定建立的仓库数。
5. 备份测试库
mysqldump --databases tpcc_test -uroot -p123456 --set-gtid-purged=off > tpcc_test.sql为在同等环境下进行比较，每次测试前都要重新生成测试库中的表、索引和数据，因此这里做一个测试库的逻辑备份。一定要加 --set-gtid-purged=off，因为将备份导入主库时，需要在从库通过复制同时生成。
下面是每次测试在从库执行的自动化脚本：
# 初始化tpcc数据
mysql -uwxy -p123456 -h172.16.1.125 < tpcc_test.sql
# 读取主库的二进制坐标
read master_file master_pos < <(mysql -uwxy -p123456 -h172.16.1.125 -e "show master status;" --skip-column-names | awk '{print $1,$2}')
# 从库初始化tcpp数据结束后停止复制
mysql -uwxy -p123456 -e "select master_pos_wait('$master_file',$master_pos);stop slave;"
# 取得从库开始GTID
read start_gtid < <(mysql -uwxy -p123456 -e "show variables like 'gtid_executed';" --skip-column-names | awk '{print $2}' | sed "s/\\\n//g")
# 主库执行压测，10个仓库，32个并发线程，预热1分钟，压测5分钟
tpcc_start -h172.16.1.125 -d tpcc_test -u wxy -p "123456" -w 10 -c 32 -r 60 -l 300 > tpcc_test.log 2>&1
# 读取主库的二进制坐标
read master_file master_pos < <(mysql -uwxy -p123456 -h172.16.1.125 -e "show master status;" --skip-column-names | awk '{print $1,$2}')
# 从库复制开始时间
start_time=`date '+%s'`
# 从库执行复制
mysql -uwxy -p123456 -e "start slave;select master_pos_wait('$master_file',$master_pos);"
# 从库复制结束时间
end_time=`date '+%s'`
# 复制执行时长
elapsed=$(($end_time - $start_time))
# 取得从库结束GTID
read end_gtid < <(mysql -uwxy -p123456 -e "show variables like 'gtid_executed';" --skip-column-names | awk '{print $2}' | sed "s/\\\n//g")
# 取得从库执行的事务数
read start end < <(mysql -uwxy -p123456 -e "select gtid_subtract('$end_gtid','$start_gtid');" --skip-column-names | awk -F: '{print $2}' | awk -F- '{print $1,$2}')
trx=$(($end - $start + 1))
# 计算从库、主库的TPS
Slave_TPS=`expr $trx / $elapsed`
Master_TPS=`expr $trx / 360`
# 打印输出
echo "TRX: $trx" "Elapsed: $elapsed" "Slave TPS: $Slave_TPS" "Master TPS: $Master_TPS"2. sync_binlog 与 innodb_flush_log_at_trx_commit
sync_binlog 控制 MySQL 服务器将二进制日志同步到磁盘的频率，可取值 0、1、N，MySQL 8 的缺省值为 1。innodb_flush_log_at_trx_commit 控制提交时是否将 innodb 日志同步到磁盘，可取值 0、1、2，MySQL 8 的缺省值为 1。关于这两个参数已经在“MySQL 8 复制（一）——异步复制”中详细讨论，这里不再赘述。简单说，对于复制来讲，主库上的 sync_binlog 为 0 可能造成从库丢失事务，innodb_flush_log_at_trx_commit 为 0 可能造成从库比主库事务多。而从性能角度看，双 1 的性能最差，双 0 的性能最好。权衡数据安全与性能，一般建议主库都设置为双 1，根据场景从库可以设置成其它组合来提升性能。
下表所示为从库上 sync_binlog、innodb_flush_log_at_trx_commit 四种设置的测试结果：
| sync_binlog | innodb_flush_log_at_trx_commit | 事务数 | 复制执行时间（秒） | 从库TPS | 主库TPS |
| 0 | 0 | 183675 | 330 | 556 | 510 |
| 0 | 1 | 184177 | 498 | 369 | 511 |
| 1 | 0 | 183579 | 603 | 304 | 509 |
| 1 | 1 | 183020 | 683 | 267 | 508 |
测试中主库执行了一共 360 秒（预热+压测），TPS 为 510。从表中可以明显看到这两个参数的不同组合对复制性能的影响。当从库仅为单线程复制时，只有双 0 的设置在执行时间和 TPS 上优于主库，其它组合会造成复制延迟。
3. 组提交与多线程复制
MySQL 5.6 支持多线程复制（multi-threaded slave，MTS），但太过局限。它只实现了基于 schema 的多线程复制，使不同数据库下的 DML 操作可以在从库并行重放，这样设计的复制效率并不高。如果用户实例仅有一个库，那么就无法实现并行重放，甚至性能会比原来的单线程更差，而单库多表是比多库多表更为常见的一种情形。
MySQL 5.7 的多线程复制基于组提交实现，不再有基于 schema 的多线程复制限制。
（1）组提交
从 MySQL 5.6 开始同时支持 Innodb redo log 和 binlog 组提交，并且默认开启，大大提高了 MySQL 的事务处理性能。和很多 RDBMS 一样，MySQL 为了保证事务处理的一致性和持久性，使用了 WAL（Write Ahead Log）机制，即对数据文件进行修改前，必须将修改先记录日志。Redo log 就是一种 WAL 的应用，每次事务提交时，不用同步刷新磁盘数据文件，只需要同步刷新 redo log 就够了。相比写数据文件时的随机 I/O，写 Redo log 时的顺序 I/O 能够提高事务提交速度。Redo log 的刷盘操作将会是最终影响 MySQL TPS 的瓶颈所在。为了缓解这一问题的影响，MySQL 使用了 redo log 组提交，将多个 redo log 刷盘操作合并成一个。
为了保证 redo log 和 binlog 的数据一致性，MySQL 使用了两阶段提交（prepare 阶段和 commit 阶段），由 binlog 作为事务的协调者。而引入两阶段提交使得 binlog 又成为了性能瓶颈，于是 MySQL 5.6 增加了 binlog 的组提交，目的同样是将 binlog 的多个刷盘操作合并成一个。结合 redo log 本身已经实现的组提交，将提交过程分成 Flush stage、Sync stage、Commit stage 三个阶段完成组提交，最大化每次刷盘的收益，弱化磁盘瓶颈。每个阶段都有各自的队列，使每个会话的事务进行排队，提高并发性能。
Flush 阶段：
- 首先获取队列中的事务组，将 redo log 中 prepare 阶段的数据刷盘。
- 将 binlog 数据写入文件系统缓冲，此时并不能保证数据库崩溃时 binlog 不丢失。
- Flush 阶段队列的作用是提供了 redo log 的组提交。
- 如果在这一步完成后数据库崩溃，由于协调者 binlog 中不保证有该组事务的记录，所以 MySQL 可能会在重启后回滚该组事务。
Sync 阶段：
- 将 binlog 缓存 sync 到磁盘，sync_binlog = 1 时该队列中所有事务的 binlog 将永久写入磁盘。
- 为了增加一组事务中的事务数量，提高刷盘收益，MySQL 使用两个参数控制获取队列事务组的时机：
binlog_group_commit_sync_delay = N：在等待 N 微秒后，开始事务刷盘。
binlog_group_commit_sync_no_delay_count = N：如果队列中的事务数达到 N 个，就忽视 binlog_group_commit_sync_delay 的设置，直接开始刷盘。
- Sync 阶段队列的作用是支持 binlog 的组提交。
- 如果在这一步完成后数据库崩溃，由于协调者 binlog 中已经有了事务记录，MySQL 会在重启后通过 Flush 阶段中 Redo log 刷盘的数据继续进行事务的提交。
Commit 阶段：
- 首先获取队列中的事务组。
- 依次将 redo log 中已经 prepare 的事务在存储引擎层提交，清除回滚信息，向 redo log 中写入 COMMIT 标记。
- Commit 阶段不用刷盘，如上所述，Flush 阶段中的 redo log 刷盘已经足够保证数据库崩溃时的数据安全了。
- Commit 阶段队列的作用是承接 Sync 阶段的事务，完成最后的引擎提交，使得 Sync 可以尽早的处理下一组事务，最大化组提交的效率。
Commit 阶段会受到参数 binlog_order_commits 的影响，当该参数为 OFF 时，不保证 binlog和事务提交的顺序一致，因为此时允许多个线程发出事务提交指令。也正是基于同样的原因，可以防止逐个事务提交成为吞吐量瓶颈，性能会有少许提升。但存储引擎的提交指令与 binlog 不同序可能造成主从数据不一致。看一个明显的例子，假设 a=1，主库上两个事务执行顺序是：
update t set a=a+1;
update t set a=a*10;结果主库 a = 20。如果 binlog 按相反顺序记录，则从库的 a=11。
另外还有一些其它的副作用，例如会影响 XtraBackup 工具的备份。XtraBackup 会从 innodb page 中获取最后提交事务的 binlog 位置信息，binlog_order_commits=0 时事务提交顺序和 binlog 顺序可能不一致，这样此位置前可能存在部分 prepare 状态的事务，这些事务在备份恢复后会因回滚而丢失。
binlog_order_commits 的缺省值为 ON，此时存储引擎的事务提交指令将在单个线程上串行化，以致事务始终以与写入二进制日志相同的顺序提交。
这里有一篇 MySQL 组提交的图解说明：[图解MySQL]MySQL组提交(group commit)。
（2）多线程复制
MySQL 5.6 开始出现基于 schema 的多线程复制，简单说就是主库上不同数据库上的 DML 可以在从库上并行重放。因为大多数生产环境依然习惯于单库多表的架构，这种情况下 MTS 依然还是单线程的效果。MySQL 5.7 实现了基于组提交的多线程复制，其思想简单易懂：主库上同一个组提交的事务可以在从库并行重放，因为这些事务之间没有任何冲突，由存储引擎的 ACID 所保证。为了与 5.6 版本兼容，5.7 引入了新的变量 slave_parallel_type，可以配置为下面两个值之一：
- DATABASE：缺省值，基于 schema 的多线程复制方式。
- LOGICAL_CLOCK：基于组提交的多线程复制方式。
那么从库如何知道事务是否在一组中呢？MySQL 5.7 的设计方式是将组提交信息存放在二进制日志的 GTID_EVENT 中。
[mysql@hdp2/usr/local/mysql/data]$mysqlbinlog binlog.000064 | grep last_committed | awk '{print $11, $12}' | head -10
last_committed=0 sequence_number=1
last_committed=0 sequence_number=2
last_committed=0 sequence_number=3
last_committed=0 sequence_number=4
last_committed=0 sequence_number=5
last_committed=0 sequence_number=6
last_committed=0 sequence_number=7
last_committed=0 sequence_number=8
last_committed=0 sequence_number=9
last_committed=0 sequence_number=10
[mysql@hdp2/usr/local/mysql/data]$last_committed 表示事务提交的时候，上次事务提交的编号。事务在 perpare 阶段获取相同的 last_committed 而且相互不影响，最终会作为一组进行提交。如果事务具有相同的 last_committed，表示这些事务都在一组内，可以进行并行重放。例如上述 last_committed 为 0 的 10 个事务在从库是可以进行并行重放的。这个机制是 Commit-Parent-Based Scheme 的实现方式。
sequence_number 是事务计数器。记录在 GTID_EVENT 中的 sequence_number 和 last_committed 使用的是相对当前二进制日志文件的值。即每个二进制日志文件中事务的起始值 last_commited 为 0，sequence_number 为 1。由于二进制日志文件切换时，需要等待上一个文件的事务执行完，所以这里记录相对值并不会导致冲突事务并行执行。
由于在 MySQL 中写入是基于锁的并发控制，所以所有在主库同时处于 prepare 阶段且未提交的事务就不会存在锁冲突，从库就可以并行执行。Commit-Parent-Based Scheme 使用的就是这个原理，简单描述如下：
- 主库上有一个全局计数器（global counter）。每一次存储引擎提交之前，计数器值就会增加。
- 主库上，事务进入 prepare 阶段之前，全局计数器的当前值会被储存在事务中，这个值称为此事务的 commit-parent。
- 主库上，commit-parent 会在事务的开头被储存在 binlog 中。
- 从库上，如果两个事务有同一个 commit-parent，它们就可以并行被执行。
此 commit-parent 就是在 binlog 中看到的 last_committed。如果 commit-parent 相同，即 last_committed 相同，则被视为同一组，可以并行重放。
Commit-Parent-Based Scheme 的问题在于会降低复制的并行程度，如图9 所示（引自“WL#7165”）。
每一个水平线代表一个事务,时间从左到右。P 表示事务在进入 prepare 阶段之前读到的 commit-parent 值的那个时间点，可以简单视为加锁时间点。C 表示事务增加了全局计数器值的那个时间点，可以简单视为释放锁的时间点。P 对应的 commit-parent 是取自所有已经执行完的事务的最大的 C 对应的 sequence_number，举例来说：Trx4 的 P 对应的 commit-parent 是 Trx1 的 C 对应的 sequence_number。因为这个时候 Trx1 已经执行完，但是 Trx2 还未执行完。Trx5 的 P 对应的 commit-parent 是 Trx2 的 C 对应的 sequence_number。Trx6 的 P 对应的 commit-parent 是 Trx2 的 C 对应的 sequence_number。
Trx5 和 Trx6 具有相同的 commit-parent，在进行重放的时候，Trx5 和 Trx6 可以并行执行。Trx4 和 Trx5 不能并行执行，Trx6 和 Trx7 也不能并行执行，因为它们的 commit-parent 不同。但注意到，在同一时段，Trx4 和 Trx5、Trx6 和 Trx7 分别持有它们各自的锁，事务互不冲突，所以在从库上并行执行是不会有问题的。针对这种情况，为了进一步增加并行度，MySQL 对并行复制的机制做了改进，提出了一种新的并行复制的方式：Lock-Based Scheme，使同时持有各自锁的事务可以在从库并行执行。
Lock-Based Scheme 定义了一个称为 lock interval 的概念，表示一个事务持有锁的时间间隔。假设有两个事务 Trx1、Trx2，Trx1 先于 Trx2。那么，当且仅当 Trx1、Trx2 的 lock interval 有重叠，则可以并行执行。换言之，若 Trx1 结束自己的 lock interval 早于 Trx2 开始自己的 lock interval，则不能并行执行。如图10 所示，L 表示 lock interval 的开始点，C 表示 lock interval 的结束。
对于 C（lock interval 的结束点），MySQL 会给每个事务分配一个逻辑时间戳（logical timestamp），命名为 transaction.sequence_number。此外，MySQL 会获取全局变量 global.max_committed_transaction，表示所有已经结束 lock interval 的事务的最大的 sequence_number。对于 L（lock interval 的开始点），MySQL 会把 global.max_committed_transaction 分配给一个变量，并取名叫 transaction.last_committed。transaction.sequence_number 和 transaction.last_committed 这两个时间戳都会存放在 binlog 中，就是前面看到的 last_committed 和 sequence_number。
根据以上分析得出，只要事务和当前执行事务的 Lock Interval 都存在重叠，就可以在从库并行执行。图9 中，Trx3、Trx4、Trx5、Trx6 四个事务可以并行执行，因为 Trx3 的 sequence_number 大于 Trx4、Trx5、Trx6 的 last_committed，即它们的 Lock Interval 存在重叠。当 Trx3、Trx4、Trx5 执行完成之后，Trx6 和 Trx7 可以并发执行，因为 Trx6 的 sequence_number 大于 Trx7 的 last_committed，即两者的 lock interval 存在重叠。Trx5 和 Trx7 不能并发执行，因为 Trx5 的 sequence_number 小于 Trx7 的 last_committed，即两者的 lock interval不存在重叠。
可以通过以下命令粗略查看并发度：
[mysql@hdp2/usr/local/mysql/data]$mysqlbinlog binlog.000064 | grep -o 'last_committed.*' | sed 's/=/ /g' | awk '{print $4-$2-1}' | sort -g | uniq -c
1693 0
4795 1
8174 2
11378 3
13879 4
15407 5
15979 6
15300 7
13762 8
11471 9
9061 10
6625 11
4533 12
3006 13
1778 14
1021 15
521 16
243 17
135 18
61 19
31 20
23 21
18 22
7 23
5 24
7 25
3 26
3 27
6 28
1 29
1 30
2 31
1 32
3 33
3 34
1 37
1 39
1 40
1 42
1 44
1 46
1 49
1 50
1 56
1 120第一列为事务数量，第二列表示这些事务能与它们之前的多少个事务并行执行。例如有 1693 个事务不能与之前的事务并发，必须等到所有前面的事务完成之后才能开始，但并不表示不能和后面的事务并行执行。当前事务无法判断能否和后面的事务并行执行，只能与前面事务的 sequence_number 比较，得出自己是否可以并发执行。
仅仅设置为 LOGICAL_CLOCK 还会存在问题，因为此时在从库上应用事务是无序的，和 relay log 中记录的事务顺序可能不一样。在这种情况下，从库的 GTID 会产生间隙，事务可能在某个时刻主从是不一致的，但是最终会一致，满足最终一致性。相同记录的修改，会按照顺序执行，这由事务隔离级保证。不同记录的修改，可以产生并行，并无数据一致性风险。这大概也是 slave_preserve_commit_order 参数缺省为 0 的原因之一。
如果要保证事务是按照 relay log 中记录的顺序来重放，需要设置参数slave_preserve_commit_order = 1，这要求从库开启 log_bin 和 log_slave_updates，并且 slave_parallel_type 设置为 LOGICAL_CLOCK。
启用 slave_preserve_commit_order 后，正在执行的 worker 线程将等待，直到所有先前的事务提交后再提交。当复制线程正在等待其它 worker 线程提交其事务时，它会将其状态报告为等待提交前一个事务。使用此模式，多线程复制的重放顺序与主库的提交顺序保持一致。
slave_parallel_workers 参数控制并行复制 worker 线程的数量。若将 slave_parallel_workers设置为 0，则退化为单线程复制。如果 slave_parallel_workers=N（N>0），则单线程复制中的 SQL 线程将转为 1 个 coordinator 线程和 N 个 worker 线程，coordinator 线程负责选择 worker 线程执行事务的二进制日志。例如将 slave_parallel_workers 设置为 1，则 SQL 线程转化为 1 个 coordinator 线程和 1 个 worker 线程，也是单线程复制。然而，与 slave_parallel_workers=0 相比，多了一次 coordinator 线程的转发，因此 slave_parallel_workers=1 的性能反而比 0 还要差。MySQL 8 中 slave_parallel_workers 参数可以动态设置，但需要重启复制才能生效。
LOGICAL_CLOCK 多线程复制为了准确性和实现的需要，其 lock interval 实际获得的区间比理论值窄，会导致原本一些可以并发行行的事务在从库上没有并行执行。当使用级联复制时，LOGICAL_CLOCK 可能会使离主库越远的从库并行度越小。
（3）性能测试
从库增加以下配置参数：
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
slave_preserve_commit_order = 1
slave_parallel_type = LOGICAL_CLOCK下表所示为从库上 slave_parallel_workers 分别设置为 2、4、8、16 的测试结果：
| slave_parallel_workers | 事务数 | 复制执行时间（秒） | 从库TPS | 主库TPS |
| 2 | 183717 | 460 | 399 | 510 |
| 4 | 183248 | 396 | 462 | 509 |
| 8 | 182580 | 334 | 546 | 507 |
| 16 | 183290 | 342 | 535 | 509 |
测试中主库执行了一共 360 秒（预热+压测），TPS 为 509。从表中可以看到，在实验负载场景下，多线程复制性能明显高于单线程复制。slave_parallel_workers=8 时性能最好，当 worker 数量增加到 16 时，性能反而比 8 时差。太多线程会增加线程间同步的开销，因此 slave_parallel_workers 值并非越大越好，需要根据实际负载进行测试来确定其最佳值，通常建议建议 4-8 个 worker 线程。
4. 基于 WriteSet 的多线程复制
基于组提交 LOGICAL_CLOCK 多线程复制机制在每组提交事务足够多，即业务量足够大时表现较好。但很多实际业务中，虽然事务没有 Lock Interval 重叠，但这些事务操作的往往是不同的数据行，也不会有锁冲突，是可以并行执行的，但 LOGICAL_CLOCK 的实现无法使这部分事务得到并行重放。为了解决这个问题，MySQL 在 5.7.22 版本推出了基于 WriteSet 的并行复制。简单来说，WriteSet 并行复制的思想是：不同事务的记录不重叠，则都可在从库上并行重放。可以看到并行的力度从组提交细化为记录级。
（1）WriteSet 对象
MySQL 中用 WriteSet 对象来记录每行记录，从源码来看 WriteSet 就是每条记录 hash 后的值（必须开启 ROW 格式的二进制日志），具体算法如下：
WriteSet=hash(index_name | db_name | db_name_length | table_name | table_name_length | value | value_length)上述公式中的 index_name 只记录唯一索引，主键也是唯一索引。如果有多个唯一索引，则每条记录会产生对应多个 WriteSet 值。另外，value 这里会分别计算原始值和带有字符集排序规则（Collation）值的两种 WriteSet。所以一条记录可能有多个 WriteSet 对象。
新产生的 WriteSet 对象会插入到 WriteSet 哈希表，哈希表的大小由参数 binlog_transaction_dependency_history_size 设置，默认 25000。内存中保留的哈希行数达到此值后，将清除历史记录。
（2）实现原理
基于 WriteSet 的复制优化了主库组提交的实现，主要体现主库端 last_committed 的定义变了。原来一组事务是指拥有同一个 parent_commit 的事务，在二进制日志中记录为同一个 last_committed。在基于 WriteSet 的方式中，last_committed 的含义是保证冲突事务（更新相同记录的事务）不能拥有同样的 last_committed 值，事务执行的并行度进一步提高。
当事务每次提交时，会计算修改的每个行记录的 WriteSet 值，然后查找哈希表中是否已经存在有同样的 WriteSet，若无，WriteSet 插入到哈希表，写入二进制日志的 last_committed 值不变。上一个事务跟当前事务的 last_committed 相等，意味着它们可以最为一组提交。若有，更新哈希表对应的 WriteSet 值为 sequence_number，并且写入到二进制日志的 last_committed 值也更新为 sequnce_number。上一个事务跟当前事务的 last_committed 必然不同，表示事务冲突，必须等待之前的事务提交后才能执行。
从库端的逻辑跟以前一样没有变化，last_committed 相同的事务可以并行执行。
要使用 WriteSet 方式组提交，需要设置 binlog_transaction_dependency_tracking 参数为 WRITESET。binlog_transaction_dependency_tracking 参数指定主库确定哪些事务可以作为一组提交的方法，有三个可选值：
- COMMIT_ORDER：依赖事务提交的逻辑时间戳，是默认值。如果事务更新的表上没有主键和唯一索引，也使用该值。这是 MySQL 5.7 所使用使用的方式。
- WRITESET：更新不同记录的事务（不冲突）都可以并行化。
- WRITESET_SESSION：与 WRITESET 的区别是 WRITESET_SESSION 需要保证同一个会话内的事务的先后顺序。消除了从库中某一时刻可能看到主库从未出现过的数据库状态的问题。
从下面这个简单的实验可以直观看到 COMMIT_ORDER 与 WRITESET 的区别。
drop table if exists t1;
create table t1 (a int primary key);
insert into t1 values (1), (2);
flush logs;
set global binlog_transaction_dependency_tracking = WRITESET;
update t1 set a=10 where a=1;
update t1 set a=20 where a=2;
set global binlog_transaction_dependency_tracking = COMMIT_ORDER;
update t1 set a=1 where a=10;
update t1 set a=2 where a=20;查看二进制日志：
[mysql@hdp2/usr/local/mysql/data]$mysqlbinlog binlog.000002 --base64-output=decode-rows -v | grep -e 'last_committed' -A4 -e 'UPDATE' | grep -v "# original\|# immediate\|/*!" | awk '{if ($1!="###") {print $11, $12} else {print $0}}'
last_committed=0 sequence_number=1
### UPDATE `test`.`t1`
### WHERE
### @1=1
### SET
### @1=10
last_committed=0 sequence_number=2
### UPDATE `test`.`t1`
### WHERE
### @1=2
### SET
### @1=20
last_committed=2 sequence_number=3
### UPDATE `test`.`t1`
### WHERE
### @1=10
### SET
### @1=1
last_committed=3 sequence_number=4
### UPDATE `test`.`t1`
### WHERE
### @1=20
### SET
### @1=2
[mysql@hdp2/usr/local/mysql/data]$第一、二个事务的 last_committed 都是0。虽然这两个事务的 lock_interval 没有重叠，但它们修改的是不同的数据行，不存在事务冲突，因此它们的 last_committed 相同，可以作为一组并行提交。
当设置 global binlog_transaction_dependency_tracking 为 COMMIT_ORDER 时，第三、四个事务的 last_committed 分别为 2 和 3。这两个事务的 lock_interval 没有重叠，即使更新的行不冲突，它们的 last_committed 也不相同，不能作为同一组并行提交。
与 WriteSet 相关的另一个参数是 transaction_write_set_extraction。该参数定义计算 WriteSet 使用的哈希算法。如果用于多线程复制，必须将此变量设置为 XXHASH64，这也是缺省值。如果设置为 OFF，则 binlog_transaction_dependency_tracking 只能设置为 COMMIT_ORDER。如果 binlog_transaction_dependency_tracking 的当前值为 WRITESET 或 WRITESET_SESSION，则无法更改 transaction_write_set_extraction 的值。
（3）WriteSet 性能测试
主库增加以下配置参数：
binlog_transaction_dependency_tracking = WRITESET
transaction_write_set_extraction = XXHASH64从库增加以下配置参数：
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
slave_preserve_commit_order = 1
slave_parallel_type = LOGICAL_CLOCK下表所示为从库上 slave_parallel_workers 分别设置为 2、4、8、16、32 的测试结果：
| slave_parallel_workers | 事务数 | 复制执行时间（秒） | 从库TPS | 主库TPS |
| 2 | 209237 | 515 | 406 | 581 |
| 4 | 207083 | 438 | 472 | 575 |
| 8 | 207292 | 364 | 569 | 575 |
| 16 | 205060 | 331 | 619 | 569 |
| 32 | 201488 | 340 | 592 | 559 |
测试中主库执行了一共 360 秒（预热+压测），TPS 平均为 572，同等场景下的比 COMMIT_ORDER 高出 12%。当 16 个复制线程时从库 TPS 达到峰值 619，比 COMMIT_ORDER 下性能最好的 8 复制线程高出 13%。
MySQL 的复制延迟是一直被诟病的问题之一，从以上三组测试得出了目前解决延迟最普遍的三种方法：
- 如果负载和数据一致性要求都不是太高，可以采用单线程复制 + 安全参数双 0。这种模式同样拥有不错的表现，一般压力均可应付。
- 如果主库的并发量很高，那么基于 order-commit 的模式的多线程复制可以有很好的表现。
- 基于 WriteSet 的模式是目前并发度最高的多线程复制，基本可以满足大部分场景。如果并发量非常高，或是要求从库与主库的延迟降至最低，可以采取这种方式。
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
