MySQL 5.7 Replication 相关新功能说明

MySQL5.7在主从复制上面相对之前版本多了一些新特性，包括多源复制、基于组提交的并行复制、在线修改Replication Filter、GTID增强、半同步复制增强等。因为都是和复制相关，所以本文将针对这些新特性放一起进行说明，篇幅可能稍长，本文使用的MySQL版本是5.7.13。

1，多源复制（多主一从）

MySQL在5.7之后才支持多源复制，之前介绍过MariaDB 多主一从 搭建测试说明，现在介绍如何在MySQL上做多主一从，具体的方法说明可以查看官方文档。

原理：多源复制加入了一个叫做Channel的概念， 每一个Channel都是一个独立的Slave，都有一个IO_THREAD和SQL_THREAD。原理和普通复制一样。我们只需要对每一个Master执行Change Master 语句，只需要在每个语句最后使用For Channel来进行区分。由于复制的原理没有改变，在没有开启GTID的时候Master的版本可以是MySQL5.5、5.6、5.7。并且从库需要master-info-repository、relay-log-info-repository设置为table，否则会报错：

ERROR 3077 (HY000): To have multiple channels, repository cannot be of type FILE; Please check the repository configuration and convert them to TABLE.

① 测试环境：

5台主机（1从4主）：

MySQL5.5 : 10.0.3.202
MySQL5.6 : 10.0.3.162
MySQL5.7 : 10.0.3.141
MySQL5.7 : 10.0.3.219
MySQL5.7 : 10.0.3.251

② 复制账号：

mysql> CREATE USER ‘repl‘@‘10.0.3.%‘ IDENTIFIED BY ‘Repl_123456‘;
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO ‘repl‘@‘10.0.3.%‘;
Query OK, 0 rows affected (0.00 sec)

③ Change：这里先说明通过binlog文件名和position的普通复制，后面会专门介绍GTID的复制。10.0.3.251（MySQL5.7）做从库，这里需要注意：从的版本若是5.7.x～5.7.13，主的版本不能是MySQL5.5，因为MySQL5.5没有server_uuid函数。该问题在MySQL5.7.13里修复(Bug #22748612)。

CHANGE MASTER TO MASTER_HOST=‘10.0.3.141‘,MASTER_USER=‘repl‘,MASTER_PASSWORD=‘Repl_123456‘,MASTER_LOG_FILE=‘mysql-bin-3306.000001‘,MASTER_LOG_POS=154 FOR CHANNEL ‘t22‘;

CHANGE MASTER TO MASTER_HOST=‘10.0.3.162‘,MASTER_USER=‘repl‘,MASTER_PASSWORD=‘Repl_123456‘,MASTER_LOG_FILE=‘mysql-bin.000001‘,MASTER_LOG_POS=120 FOR CHANNEL ‘t21‘;

CHANGE MASTER TO MASTER_HOST=‘10.0.3.202‘,MASTER_USER=‘repl‘,MASTER_PASSWORD=‘Repl_123456‘,MASTER_LOG_FILE=‘mysql-bin-3306.000001‘,MASTER_LOG_POS=107 FOR CHANNEL ‘t10‘;

CHANGE MASTER TO MASTER_HOST=‘10.0.3.219‘,MASTER_USER=‘repl‘,MASTER_PASSWORD=‘Repl_123456‘,MASTER_LOG_FILE=‘mysql-bin-3306.000001‘,MASTER_LOG_POS=154 FOR CHANNEL ‘t23‘;

④ 相关操作：

查看单个channel的状态：

show slave status for channel ‘t10‘\G 

停止单个channel的同步：

stop slave for channel ‘t10‘;

开启单个channel的同步：

start slave for channel ‘t10‘;

重置单个channel：

reset slave all for channel ‘t10‘;

查看所有channel：

show slave status\G

停止所有channel：

stop slave;

开启所有channel：

start slave;

跳过一个channel的报错（类似MariaDB的default_master_connection）：

View Code

处理方法：先停止所有的channel，再执行 sql_slave_skip_counter，接着开启报错的channel，最后开启所有的channel。

一：
#stop all slaves
stop slave;

# set skip counter
set global sql_slave_skip_counter=1;

# start slave that shall skip one entry
start slave for channel ‘t10‘;

set global sql_slave_skip_counter=0;

# start all other slaves
start slave; 

二：
也可以直接停掉错误的channel，再skip：
stop slave for channel ‘t10‘;

set global sql_slave_skip_counter=1;

start slave for channel ‘t10‘;

⑤ 监控：系统库performance_schema增加了一些replication的监控表：

mysql> show tables like ‘replicat%‘;
+-------------------------------------------+
| Tables_in_performance_schema (replicat%)  |
+-------------------------------------------+
| replication_applier_configuration         |###查看各个channel是否配置了复制延迟
| replication_applier_status                |###查看各个channel是否复制正常（service_state）以及事务重连的次数
| replication_applier_status_by_coordinator |###查看各个channel是否复制正常，以及复制错误的code、message和时间
| replication_applier_status_by_worker      |###查看各个channel是否复制正常，以及并行复制work号，复制错误的code、SQL和时间
| replication_connection_configuration      |###查看各个channel的连接配置信息：host、port、user、auto_position等
| replication_connection_status             |###查看各个channel的连接信息
| replication_group_member_stats            |###
| replication_group_members                 |###
+-------------------------------------------+

...

2，在线调整Replication Filter

在上面搭建的主从基础上，进行过滤规则的添加，比如需要过滤dba_test数据库：

先关闭sql线程，要是在多源复制中，是关闭所有channel的sql thread。
mysql> stop slave sql_thread;
Query OK, 0 rows affected (0.01 sec)

#过滤1个库
mysql> CHANGE REPLICATION FILTER REPLICATE_IGNORE_DB=(dba_test);

#过滤2个库
mysql> CHANGE REPLICATION FILTER REPLICATE_IGNORE_DB=(dba_test1,dba_test);
Query OK, 0 rows affected (0.00 sec)

mysql> start slave sql_thread;
Query OK, 0 rows affected (0.04 sec)

通过show slave status 查看：

              Replicate_Do_DB: 
          Replicate_Ignore_DB: dba_test1,dba_test
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 

比如设置同步dba_test2库中t1开头的表：

mysql> stop slave sql_thread;
Query OK, 0 rows affected (0.01 sec)

mysql> CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE =(‘dba_test2.t1%‘);
Query OK, 0 rows affected (0.00 sec)

mysql> start slave sql_thread;
Query OK, 0 rows affected (0.04 sec)

还原成默认值，即设置成空（）：

mysql> stop slave sql_thread;
Query OK, 0 rows affected (0.01 sec)

mysql> CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE=();
Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE REPLICATION FILTER Replicate_Ignore_DB=();
Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE REPLICATION FILTER Replicate_Wild_Ignore_Table=();
Query OK, 0 rows affected (0.00 sec)

mysql> start slave sql_thread;
Query OK, 0 rows affected (0.04 sec)

用红色字体标记的这个参数就是设置在配置文件的参数，如上面的几个参数既可以在命令行里执行（5.7）也可以在配置文件里添加。注意一点是在线执行完后，一定要在配置文件里写，以免重启后失效。

...

3，基于组提交（LOGICAL_CLOCK）的并行复制

①：原理说明

MySQL5.7通过参数--slave-parallel-type=type进行控制并行复制的方式，可选值有DATABASE（默认）和LOGICAL_CLOCK，详细的说明可以看MySQL 5.7并行复制实现原理与调优。

MySQL5.6版本之前，Slave服务器上有两个线程：I/O线程和SQL线程。I/O线程负责接收二进制日志（更准确的说是二进制日志的event），SQL线程进行回放二进制日志。

MySQL5.6的并行复制是基于库的（database），开启并行复制SQL线程就变为了coordinator线程，coordinator线程主要负责以前两部分的内容：判断可以并行执行，那么选择worker线程执行事务的二进制日志；判断不可以并行执行，如该操作是DDL，亦或者是事务跨schema操作，则等待所有的worker线程执行完成之后，再执行当前的日志。对于有多个数据库的实例，开启并行的执行SQL，对从库能有较大的提升。但对单个库，开启多线程复制，性能可能比单线程还差。

MySQL5.7的并行复制是基于组提交（LOGICAL_CLOCK），即master服务器上是怎么并行执行的slave上就怎样进行并行回放，很好的解决了主从复制延迟的问题。主要思想是一个组提交的事务都是可以并行回放到从，原理是基于锁的冲突检测，因为这些事务都已进入到事务的prepare阶段，则说明事务之间没有任何冲突（否则就不可能提交）。若将slave_parallel_workers设置为0，则MySQL 5.7退化为原单线程复制，但将slave_parallel_workers设置为1，则SQL线程功能转化为coordinator线程，但是只有1个worker线程进行回放，也是单线程复制。然而，这两种性能却又有一些的区别，因为多了一次coordinator线程的转发，因此slave_parallel_workers=1的性能反而比0还要差。

总的来说就是：并发线程执行不同的事务只要在同一时刻能够commit（说明线程之间没有锁冲突），那么master节点就可以将这一组的事务标记并在slave机器上安全的进行并发重放主库提交的事务。所以尽可能的使所有线程能在同一时刻提交可以，可以极大的提高slave机器并发执行事务的数量使主备数据同步。有兴趣的可以看MySQL和MariaDB实现对比。

相关参数：

binlog_group_commit_sync_delay：表示binlog提交后等待延迟多少时间再同步到磁盘，单位是微秒，默认0，不延迟。设置延迟可以让多个事务在用一时刻提交，提高binlog组提交的并发数和效率，从而提高slave的吞吐量。

binlog_group_commit_sync_no_delay_count：表示在等待上面参数超时之前，如果有足够多的事务，则停止等待直接提交。单位是事务数，默认0。 

上面提到一个组提交的事务都是可以并行回放到从，那么如何知道事务是否在一组中？在MySQL 5.7版本中，其设计方式是将组提交的信息存放在GTID中。那么如果用户没有开启GTID功能，即将参数gtid_mode设置为OFF呢？故MySQL 5.7又引入了称之为Anonymous_Gtid的二进制日志event类型，如：

mysql> SHOW BINLOG EVENTS in ‘mysql-bin-3306.000004‘ limit 5;
+-----------------------+-----+----------------+-----------+-------------+-----------------------------------------+
| Log_name              | Pos | Event_type     | Server_id | End_log_pos | Info                                    |
+-----------------------+-----+----------------+-----------+-------------+-----------------------------------------+
| mysql-bin-3306.000004 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.13-6-log, Binlog ver: 4 |
| mysql-bin-3306.000004 | 123 | Previous_gtids |         1 |         154 |                                         |
| mysql-bin-3306.000004 | 154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= ‘ANONYMOUS‘    |
| mysql-bin-3306.000004 | 219 | Query          |         1 |         306 | BEGIN                                   |
| mysql-bin-3306.000004 | 306 | Intvar         |         1 |         338 | INSERT_ID=3462831                       |
+-----------------------+-----+----------------+-----------+-------------+-----------------------------------------+

关于event_type的更多信息可以看：MySQL【Row】下的 Event_type和MySQL【statement】下的 Event_type，这里Gtid有自己类型的event。这意味着在MySQL 5.7版本中即使不开启GTID，每个事务开始前也是会存在一个Anonymous_Gtid，而这GTID中就存在着组提交的信息。通过上述的SHOW BINLOG EVENTS，我们并没有发现有关组提交的任何信息。但是通过mysqlbinlog工具，用户就能发现组提交的内部信息：

root@t22:~# mysqlbinlog mysql-bin-3306.000004 | grep last_committed
#160726 23:40:09 server id 1  end_log_pos 302010138 CRC32 0xf5950910     Anonymous_GTID    last_committed=1566    sequence_number=1567
#160726 23:40:09 server id 1  end_log_pos 302010676 CRC32 0xb9b3038c     Anonymous_GTID    last_committed=1566    sequence_number=1568
#160726 23:40:09 server id 1  end_log_pos 302011214 CRC32 0x30f1ec4e     Anonymous_GTID    last_committed=1566    sequence_number=1569
#160726 23:40:09 server id 1  end_log_pos 302011752 CRC32 0x44443efe     Anonymous_GTID    last_committed=1566    sequence_number=1570
#160726 23:40:09 server id 1  end_log_pos 302012290 CRC32 0x79fe16ec     Anonymous_GTID    last_committed=1566    sequence_number=1571
#160726 23:40:09 server id 1  end_log_pos 302012828 CRC32 0x5ab82ffa     Anonymous_GTID    last_committed=1567    sequence_number=1572
#160726 23:40:09 server id 1  end_log_pos 302013366 CRC32 0x84be9418     Anonymous_GTID    last_committed=1571    sequence_number=1573
#160726 23:40:09 server id 1  end_log_pos 302013904 CRC32 0x9c8945e1     Anonymous_GTID    last_committed=1571    sequence_number=1574
#160726 23:40:09 server id 1  end_log_pos 302014442 CRC32 0x7949a96a     Anonymous_GTID    last_committed=1571    sequence_number=1575
#160726 23:40:09 server id 1  end_log_pos 302014980 CRC32 0xfce4bad5     Anonymous_GTID    last_committed=1571    sequence_number=1576
#160726 23:40:09 server id 1  end_log_pos 302015518 CRC32 0x41b1077a     Anonymous_GTID    last_committed=1572    sequence_number=1577

可以发现较之原来的二进制日志内容多了last_committed和sequence_number，last_committed表示事务提交的时候，上次事务提交的编号。如果事务具有相同的last_committed，表示这些事务都在一组内，可以进行并行的回放。例如上述last_committed为1566的事务有5个，表示组提交时提交了5个事务，而这5个事务在从上可以并行执行。

② 开启并行复制：只需要在slave开启参数：

[mysqld]
# * slave
slave_parallel_workers    = 4               ###并行复制的线程数
slave_parallel_type       = LOGICAL_CLOCK   ###并行复制的类型，默认database

master_info_repository    = table
relay_log_info_repository = table

relay_log_recovery           = 1

③ 强一致性的提交顺序：

通过参数slave_preserve_commit_order可以控制Slave上的binlog提交顺序和Master上的binlog的提交顺序一样，保证GTID的顺序。该参数只能用于开启了logical clock并且启用了binlog的复制。即对于多线程复制，该参数用来保障事务在slave上执行的顺序与relay log中的顺序严格一致。

比如两个事务依次操作了2个DB：A和B，尽管事务A、B分别被worker X、Y线程接收，但是因为线程调度的问题，有可能导致A的执行时机落后于B。如果经常是“跨DB”操作，那么可以考虑使用此参数限定顺序。当此参数开启时，要求任何worker线程执行事务时，只有当前事务中之前的所有事务都执行后（被其他worker线程执行），才能执行和提交。（每个事务中，都记录了当前GTID的privious GTID，只有privious GTID被提交后，当前GTID事务才能提交）

开启该参数可能会有一点的消耗，因为会让slave的binlog提交产生等待。

④ 并行复制的测试：测试的工具是sysbench，之前介绍过sysbench安装、使用和测试，现在使用sysbench的0.5版本，不支持--test=oltp，需要用lua脚本来代替。可以看可以看sysbench安装、使用、结果解读和使用sysbench对mysql压力测试。

分2种情况：一种是原始的单线程复制，或则是基于database的复制，第二种是MySQL5.7基于组提交的并行复制。

1）单线程复制：这里用insert.lua脚本的目的是想在纯写入的条件下，看从是否有延迟。

mysql> show variables like ‘slave_parallel%‘;
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| slave_parallel_type    | DATABASE |
| slave_parallel_workers | 0        |
+------------------------+----------+

生成测试数据和表：生成4张表，存储引擎为innodb，100万数据。

sysbench --test=/usr/share/doc/sysbench/tests/db/insert.lua --mysql-table-engine=innodb --mysql-host=127.0.0.1 --mysql-db=dba_test --oltp-table-size=1000000 --oltp_tables_count=4 --rand-init=on --mysql-user=zjy --mysql-password=zjy  prepare

压力测试：更多的压力测试见MySQL压力测试基准值

sysbench --test=/usr/share/doc/sysbench/tests/db/insert.lua --mysql-table-engine=innodb --mysql-host=127.0.0.1 --mysql-db=dba_test --num-threads=8 --oltp-table-size=1000000 --oltp_tables_count=4 --oltp-read-only=off --report-interval=10 --rand-type=uniform --max-time=600 --max-requests=0 --percentile=99 --mysql-user=zjy --mysql-password=zjy run

清理：

 sysbench --test=/usr/share/doc/sysbench/tests/db/insert.lua --mysql-table-engine=innodb --mysql-host=127.0.0.1 --mysql-db=dba_test --num-threads=8 --oltp-table-size=1000000 --oltp_tables_count=4 --oltp-read-only=off --report-interval=10 --rand-type=uniform --max-time=600 --max-requests=0 --percentile=99 --mysql-user=zjy --mysql-password=zjy cleanup

测试结果：

因为主从在同一台PC机上，性能不高。从上面5分钟的压测结果来看：有延迟，最大延迟达到360s；从库QPS保持在2000左右。因为压测只在一个库里，所以database的并行复制和单线程复制效果一样。

2）并行复制：

mysql> show variables like ‘slave_parallel%‘;
+------------------------+---------------+
| Variable_name          | Value         |
+------------------------+---------------+
| slave_parallel_type    | LOGICAL_CLOCK |
| slave_parallel_workers | 8             |
+------------------------+---------------+

按照上面的方法生成数据和表、压测。测试结果：

因为主从在同一台PC机上，性能不高。从上面5分钟的压测结果来看：有延迟，最大延迟达到180s；从库QPS保持在3500左右。对比单线程的并发，确实提升了50%，但是还是有延迟。可以修改一个参数binlog_group_commit_sync_delay来优化。

本次的测试目的是想说明并行复制能够提高从库服务器的利用率和可用性，提升多少还要看服务器性能。本文测试的环境是一个普通的PC机，主从数据库在一起，并且使用insert.lua的纯写入脚本，IO争用的厉害，只能看到一点优势。有兴趣的可以使用oltp.lua脚本和主从分开进行测试。更多的一些测试说明可以看MySQL 5.7并行复制实现原理与调优。

4，Gtid功能的增强

之前介绍过MySQL5.6 新特性之GTID，GTID的基本概念可以看这篇文章，在MySQL5.7中对Gtid做了一些增强，现在进行一些说明。

GTID即全局事务ID（global transaction identifier），GTID实际上是由UUID+TID（Sequence Number）组成的。其中UUID是一个MySQL实例的唯一标识。TID代表了该实例上已经提交的事务数量，并且随着事务提交单调递增，所以GTID能够保证每个MySQL实例事务的执行（不会重复执行同一个事务，并且会补全没有执行的事务）。下面是一个GTID的具体形式：

4e659069-3cd8-11e5-9a49-001c4270714e:1

GTID的目的是简化复制的使用过程和降低复制集群维护的难度，不再依赖Master的binlog文件名和文件中的位置。 

CHANGE MASTER TO MASTER_LOG_FILE=‘Master-bin.000010’, MASTER_LOG_POS=‘214’;
简化成：
CHANGE MASTER TO MASTER_AUTO_POSITION=1;

MASTER_AUTO_POSITION的原理：

MySQL Server记录了所有已经执行了的事务的GTID，包括复制过来的（可以通过select @@global.gtid_executed查看）。

Slave记录了所有从Master接收过来的事务的GTID（可以通过Retrieve_gtid_set查看）。

Slave连接到Master时，会把gtid_executed中的gtid发给Master，Master会自动跳过这些事务，只将没有复制的事务发送到Slave去。

上面介绍的是GTID的基本概念，GTID相关变量：

binlog_gtid_simple_recovery ：MySQL5.7.7之后默认on，这个参数控制了当mysql启动或重启时，mysql在搜寻GTIDs时是如何迭代使用binlog文件。该参数为真时，mysql-server只需打开最老的和最新的这2个binlog文件，gtid_purged参数的值和gtid_executed参数的值可以根据这些文件中的Previous_gtids_log_event或者Gtid_log_event计算得出。这确保了当mysql-server重启或清理binlog时，只需打开2个binlog文件。当这个参数设置为off，在mysql恢复期间，为了初始化gtid_executed，所有以最新文件开始的binlog都要被检查。并且为了初始化gtid_purged，所有的binlog都要被检查。这可能需要非常长的时间，建议开启。注意：MySQL5.6中，默认为off，调整这个选项设置也同样会提升性能，但是在一些特殊场景下，计算gtids值可能会出错。而保持这个选项值为off，能确保计算总是正确。

enforce_gtid_consistency：默认off，可选值有on和warn。根据该变量的值，服务器只允许可以安全使用GTID记录的语句通过，强制GTID一致性。在启用基于GTID复制之前将此变量需要设置为on。

OFF  ：不检测是否有GTID不支持的语句和事务。
Warn ：当检测到不支持GTID的语句和事务，返回警告，并在日志中记录。
ON   ：当检测到不支持GTID的语句和事务，返回错误。

gtid_mode：控制是否开启GTID，默认OFF。可选值有OFF、OFF_PERMISSIVE、ON、ON_PERMISSIVE。

OFF            ：不产生GTID，Slave只接受不带GTID的事务
OFF_PERMISSIVE ：不产生GTID，Slave即接受不带GTID的事务，也接受带GTID的事务
ON_PERMISSIVE  ：产生GTID，Slave即接受不带GTID的事务，也接受带GTID的事务
ON             ：产生GTID，Slave只能接受带GTID的事务。

session_track_gtids：控制用于捕获GTIDs和在OK PACKE返回的跟踪器。 

OFF       ：关闭
OWN_GTID  ：返回当前事务产生的GTID
ALL_GTIDS ：返回系统执行的所有GTID，也就是GTID_EXECUTED

gtid_purged：已经被删除的binlog的事务。

gtid_owned： 表示正在执行的事务的gtid以及对应的线程ID。

gtid_executed：表示已经在该实例上执行过的事务（mysql.gtid_executed）， 执行RESET MASTER会将该变量置空（清空mysql.gtid_executed），可以通过设置GTID_NEXT执行一个空事务，来影响GTID_EXECUTED。GTID_NEXT是SESSION级别变量，表示下一个将被使用的GTID。

gtid_executed_compression_period：默认1000个事务，表示控制每执行多少个事务，对此表（mysql.gtid_executed）进行压缩。

介绍了GTID的概念和变量，现在说下MySQL5.7下GTID增强的一些特性：

①：在线开启GTID。MySQL5.6开启GTID的功能需要重启服务器生效。

mysql> set global gtid_mode=on;
ERROR 1788 (HY000): The value of @@GLOBAL.GTID_MODE can only be changed one step at a time: OFF <-> OFF_PERMISSIVE <-> ON_PERMISSIVE <-> ON. Also note that this value must be stepped up or down simultaneously on all servers. See the Manual for instructions.

mysql> set global gtid_mode=OFF_PERMISSIVE;
Query OK, 0 rows affected (0.17 sec)

mysql> set global gtid_mode=ON_PERMISSIVE;
Query OK, 0 rows affected (0.14 sec)

#等一段时间, 让不带GTID的binlog events在所有的服务器上执行完毕 

mysql> set global gtid_mode=ON;
ERROR 3111 (HY000): SET @@GLOBAL.GTID_MODE = ON is not allowed because ENFORCE_GTID_CONSISTENCY is not ON.

mysql> set global enforce_gtid_consistency=on;
Query OK, 0 rows affected (0.00 sec)

mysql> set global gtid_mode=ON;
Query OK, 0 rows affected (0.16 sec)

• < 上一篇

  SQL Server遍历表的几种方法

• 下一篇 >

  【MySQL】容器集群支持数据库实践