MySQL复制(六)复制拓扑介绍

欢迎关注公众号：一介IT
本站博文抢先发布在公众号。

摘自个人网站，文章原文地址 https://l080l.com/mysql/ha/chapter08.html

可以在任意个主从库之间建立复杂的复制拓扑结构，如普通的一主一(多)从、双(多)主复制、级联复制，MySQL 5.7.2 后新增的多源复制，特殊场景下使用的 Blackhole 引擎与日志服务器等等。复制中的 MySQL 服务器须要遵循以下基本原则：

• 拓扑中的每个服务器必须有一个唯一的 server_id 和 server_uuid 。
• 一个主库可以有多个从库 (或者说一个从库可以有多个兄弟从库)。
• 如果打开了log_slave_updates选项，一个从库可以把其对应主库上的数据变化传播到它的从库。

1. 一主一(多)从

一级主从
一主一从的基本配置是最简单的拓扑结构，而一主多从的结构和基本配置差不多简单，因为从库之间根本没有交互，它们仅仅是连接到同一个主库。下图显示了这种结构。

尽管这是非常简单的拓扑结构，但它非常灵活，能满足多种需求。为满足数据一致性和主从切换需求，从库原则上应为只读，下面是从库的一些用途：

• 为不同的角色使用不同的从库，例如为满足读写分离需求，在从库添加不同于主库的适合读操作的索引 (不要忘记在从库执行任何写操作前 set sql_log_bin=0)。
• 把一台从库只当做备用主库，除了复制没有其它数据传输。
• 将一台从库放到远程数据中心，用作灾难恢复。
• 延迟复制一个从库，以备恢复用户错误。
• 使用其中一个从库，作为备份或测试使用。

这种结构流行原因是它避免了很多其它拓扑的复杂性。例如，可以方便地比较不同从库重放的事件在主库二进制日志中的位置，当然如果启用 GTID 就更跟简单了，支持自动定位。换句话说，如果在同一个逻辑点停止所有从库的复制，它们正在读取的是主库上同一个日志文件的相同物理位置。这是个很好的特性，可以减轻管理员许多工作，例如把从库提升为主库。这种特性只存在于兄弟从库之间。在没有直接的主从或者兄弟关系的服务器上去比较日志文件的位置要复杂很多。例如树形复制或分布式主库，很难计算出复制事件的逻辑顺序。

2. 级联主从

如果正在将主库复制到大量从库中，不管是把数据分发到不同的地方，还是提供更高的读性能，使用级联复制都能更好地管理，如下图。

这种设计的好处是减轻了主库的负担，将读负载分发到多个从库。缺点是中间层出现的任何错误都会影响到多个服务器。如果每个从库和主库直接相连就不会存在这样的问题。同时中间层次越多，处理故障就会越复杂和困难。

3. 双(多)主复制

3.1 主动-主动模式下的双主复制

双主复制包含两台 MySQL 服务器，每一个都被配置为对方的主库和从库，换句话说，它们是一对主库。下图显示了该结构。

主动-主动模式指的是两台服务器对于应用均可读写，通常用于特殊目的。例如一个可能的应用场景是两个处于不同地理位置的办公室，并且都需要一份可写的数据拷贝。这种配置最大的问题时如何解决冲突，两个可写的互主服务器导致的问题非常多。这通常发生在两台服务器同时修改一行记录，或同时在两台服务器上向一个包含 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)

可以看到复制正常，两个服务器数据是一致。但这只极端理想的场景：从空表开始插入数据，配置复制时没有联机访问。如果在配置双主复制时已经有数据，情况将复杂的多。同时允许向两台主库写入很危险，极易造成复制错误或数据不一致。数据不同步还仅仅是开始。当正常的复制发生错误停止了，但应用仍然在同时向两台服务器写入数据，这时候会发生什么呢？不能简单地把数据从一台服务器复制到另外一台，因为这两台机器上需要复制的数据都可能发生了变化。解决这个问题将非常困难。总的来说，允许向两个服务器上同时写入所带来的麻烦远远大于其带来的好处。只要作为从库可写，就存在主从数据不一致的风险。

3.2 主动-被动模式下的双主复制

这是前面描述的双主结构的变体，主要区别在于其中的一台服务器是只读的被动服务器。这种拓扑结构能够避免之前讨论的问题，也是构建容错性和高可用性系统的强大方式。两个服务器从硬件到操作系统再到 MySQL 配置都应该完全相同。为便于故障转移，只读最好由客户端应用保证，通过设置以下系统变量强制只读仅作为可选项。

set global read_only=1;
set global super_read_only=1;

这种方式使得反复切换主动和被动服务器非常方便，因为服务器的配置是对称的。这使得故障转移和故障恢复相对容易。它也允许用户在不关闭服务器的情况下执行维护、优化、升级操作系统 (或者应用程序、硬件等) 或其它任务。例如，执行 alter table 操作可能会锁住整个表，阻塞对表的读写，这可能会花费很长时间并导致服务中断。

use test;
create table t1(a int 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 库s top 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.3 拥有从库的双主结构

另外一种相关的配置是为每个主库增加一个从库，如图所示。

这种配置的优点是增加了冗余，对于不同地理位置的复制拓扑，能够消除站点单点失效的问题。也可以像平常一样，将读查询分配到从库上。如果在本地为了故障转移使用双主结构，这种配置同样有用。当主库失效时，有两种不同的处理方式，一是用从库代替主库，二是把从库指向另一个不同的主库。以上图为例，假设主库 1 失效，采用第一种方式，需要将从库1提升为新的主库 1，修改主库2的复制配置，指向新的主库 1，并将新主库指向主库 2，保持双主配置。如果采用第二种方式，只需要将从库1指向主库 2，但这样拓扑已从双主变为一主两从。

3.4 环形复制

如下图所示，双主结构实际上是环形结构的一种特例。环形结构可以有三个或更多的主库。每个服务器都是在它之前的服务器的备库，是在它之后的服务器的主库。这种结构也称为环形复制(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 复制事务。总的来说，环形结构非常脆弱，应该尽量避免。可以通过为每个节点增加从库的方式来减少环形复制的风险，如下图所示。但这仅仅防范了服务器失效的风险，断电或者其它一些影响到网络连接的问题都可能破坏整个环。

4. 多源复制

MySQL 5.7.6 开始支持多源复制(Multi-Source Replication)。多源复制不同于多主复制，多主复制指的是在复制拓扑中存在多个主库，而多源复制是指一个从库可以同时从多个不同的主库进行复制。下图所示为两主一从的多源复制结构。

多源复制可用于将来自多个服务器的数据合并或备份到单个服务器，如合并表分片。应用事务时，多源复制不会检测或解决任何冲突，如果需要，这些任务将留给应用程序实现。在多源复制拓扑中，从库为每个接收其事务的主库创建复制通道。

4.1 复制通道

复制通道是一个字符串，表示从主库到从库的复制路径。为提供与先前版本的兼容性，MySQL 服务器在启动时自动创建一个默认通道，其名称为空字符串""。这个通道始终存在，不能被用户创建或销毁。如果没有创建其它通道(具有非空名称)，则复制语句仅作用于缺省通道，以便旧版从库的所有复制语句按预期运行。多源复制中，从库打开多个命名通道，每个通道都有自己的中继日志和复制线程。一旦复制通道的 I/O 线程接收到事务，它们就会被添加到通道对应的中继日志文件中并传递给 SQL 线程。这使得每个通道能够独立运行。复制通道还与主机名和端口关联。可以将多个通道分配给主机名和端口的相同组合。在 MySQL 8.0 中，添加到一个从库的最大通道数为 256。每个复制通道独立配置，必须具有唯一非空名称。

4.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。

#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; 启动所有通道的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';

4.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.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引擎与日志服务器

5.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 进行分发，即所谓的分发主库。分发主库实际上也是一个从库，它唯一的目的就是提取和提供主库的二进制日志。多个从库连接到分发主库，这使原来的主库摆脱了负担，如图所示。

很难说当主库数据达到多少时需要一个分发主库。按照通用准则，如果主库接近满负载，不应该为其建立 10 个以上的从库。如果只有少量写操作，或者只复制其中一部分表，则主库可以提供更多的复制。如果需要，可以使用多个分发主库向大量从库进行复制，或者使用级联的分发主库。对于跨数据中心的复制，设置slave_compressed_protocol能节约一些主库带宽。该变量是全局系统变量，缺省值为 off，可以动态设置。还可以通过分发主库实现其它目的，如对二进制日志事件执行过滤和重放规则。这比在每个从库上重复进行日志记录、重放和过滤要高效得多。使用 blackhole 存储引擎可以支持更多的从库。虽然会在分发主库执行查询，但代价极小，因为 blackhole 表中没有任何数据。

一个常见的问题是如何确保分发服务器上的每个表都是 blackhole 存储引擎。如果在主库创建了一个表并指定了不同的存储引擎呢？确实，不管何时在从库上使用不同的存储引擎总会导致同样的问题。通常的解决方案是设置服务器的缺省存储引擎：

default_storage_engine=blackhole

这只会影响那些没有指定存储引擎的 create table 的语句。如果有一个无法控制的应用，这种拓扑结构可能会非常脆弱。可以设置disabled_storage_engines禁用多个存储引擎。该系统变量为只读，只能通过配置文件修改，并重启 MySQL 服务器使之生效。下面演示如何联机搭建一个 blackhole 的分发主库。

• 服务器角色分配：

# 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

至此完成了分发主库的复制拓扑结构的搭建。

5.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。
• 可以观察到复制过程。
• 容易处理错误，如跳过执行失败的语句。
• 便于过滤复制事件。

欢迎关注公众号：一介IT
本站博文抢先发布在公众号。