Mysql5.7 一主两从架构上基于GTID主从复制+并行复制+增强半同步复制环境

原创 Leo 2023-07-29

352

文档课题：Mysql5.7 一主两从架构上基于GTID主从复制+并行复制+增强半同步复制环境.
数据库：MySQL 5.7
系统：rhel 7.3

环境：

1、安装mysql
说明：在三个节点上运用yum方式分别安装mysql 5.7.42，参考https://blog.51cto.com/u_12991611/6604946.
1.1、关闭防火墙
--关闭所有节点防火墙.
[root@leo-mysql-master ~]# cat /etc/*release
NAME="Red Hat Enterprise Linux Server"
VERSION="7.3 (Maipo)"
ID="rhel"
ID_LIKE="fedora"
VERSION_ID="7.3"
PRETTY_NAME="Red Hat Enterprise Linux Server 7.3 (Maipo)"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:redhat:enterprise_linux:7.3:GA:server"
HOME_URL="https://www.redhat.com/"
BUG_REPORT_URL="https://bugzilla.redhat.com/"

REDHAT_BUGZILLA_PRODUCT="Red Hat Enterprise Linux 7"
REDHAT_BUGZILLA_PRODUCT_VERSION=7.3
REDHAT_SUPPORT_PRODUCT="Red Hat Enterprise Linux"
REDHAT_SUPPORT_PRODUCT_VERSION="7.3"
Red Hat Enterprise Linux Server release 7.3 (Maipo)
Red Hat Enterprise Linux Server release 7.3 (Maipo)

[root@leo-mysql-master ~]# systemctl stop firewalld
[root@leo-mysql-master ~]# systemctl disable firewalld
Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
Removed symlink /etc/systemd/system/basic.target.wants/firewalld.service.
1.2、关闭selinux
[root@leo-mysql-master ~]# sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
1.3、重启系统
--重启系统，使参数生效.
[root@leo-mysql-master ~]# reboot
[root@leo-mysql-master ~]# getenforce
Disabled
[root@leo-mysql-master ~]# firewall-cmd --state
not running
2、配置基于GTID的主从复制
2.1、理论知识
传统的基于binlog position的复制方式存在严重缺点：如果slave连接master时指定的binlog文件或position错误，会造成遗漏或重复，很多时候前后数据存在依赖性，如此便会导致数据不一致.从MYSQL5.6开始，mysql开始支持GTID复制.GTID全称是global transaction id，即全局事务ID.GTID的分配方式为uuid:trans_id，其中uuid在每个mysql服务器都唯一，记录在$datadir/auto.cnf中.若主从复制结构中任意两台服务器uuid重复(比如直接冷备份时，auto.conf中的内容是一致的)，在启动复制功能时会报错.此时可以删除auto.conf文件再重启mysqld.
基于GTID主从复制的优点：
-保证同一个事务在某slave上绝对只执行一次，没有执行过的gtid事务总会被执行；
-不用像传统复制那样保证binlog的坐标准确，因为根本不需要binlog以及坐标；
-故障转移到新的master时很方便，简化很多任务；
-很容易判断master和slave的数据是否一致，只要master上提交的事务在slave上也提交过，那么一定是一致的；
-MySQL提供可以控制跳过某些gtid事务的选项，防止slave第一次启动复制时执行master上的所有事务而导致耗时过久；
-虽然对于row-based和statement-based的格式都能进行gtid复制，但建议采用row-based格式.
2.2、基于GTID主从复制环境部署步骤
2.2.1、主节点操作
2.2.1.1、修改主节点my.cnf文件
mysql-master主数据库上操作.
--在my.cnf文件中配置GTID主从复制.
[root@leo-mysql-master ~]# cp /etc/my.cnf /etc/my.cnf.bak
[root@leo-mysql-master ~]# >/etc/my.cnf
[root@leo-mysql-master ~]# vi /etc/my.cnf
添加如下内容：
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
symbolic-links = 0
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid

#GTID:
server_id = 1
gtid_mode = on
enforce_gtid_consistency = on

#binlog
log_bin = mysql-bin
log-slave-updates = 1
binlog_format = row
sync-master-info = 1
sync_binlog = 1

#relay log
skip_slave_start = 1

2.2.1.2、重启mysql进程
--配置完my.cnf后重启mysql服务.
[root@leo-mysql-master ~]# systemctl restart mysqld
2.2.1.3、确认相关参数
--登录mysql查看master状态，发现多出一项”Executed_Gtid_Set”.
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> show global variables like '%uuid%';
+---------------+--------------------------------------+
| Variable_name | Value                                |
+---------------+--------------------------------------+
| server_uuid   | c91dd7a0-18a7-11ee-aa0c-000c291140b1 |
+---------------+--------------------------------------+
1 row in set (0.01 sec)

--确认gtid功能是否打开.
mysql> show global variables like '%gtid%';
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| binlog_gtid_simple_recovery      | ON    |
| enforce_gtid_consistency         | ON    |
| gtid_executed                    |       |
| gtid_executed_compression_period | 1000 |
| gtid_mode                        | ON    |
| gtid_owned                       |       |
| gtid_purged                      |       |
| session_track_gtids              | OFF   |
+----------------------------------+-------+
8 rows in set (0.01 sec)

--确认binlog日志功能是否打开.
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)
2.2.1.4、创建复制用户
--创建slave复制用户，并刷新权限.
mysql> grant replication slave,replication client on *.* to 'slave'@'192.168.133.91' identified by "slave@12345";
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for slave@'192.168.133.91';
+--------------------------------------------------------------------------------+
| Grants for slave@192.168.133.91                                                |
+--------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'192.168.133.91' |
+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)

--再次查看master状态
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000001 |      623 |              |                  | c91dd7a0-18a7-11ee-aa0c-000c291140b1:1-2 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

注意：启动配置之前，同样需要对从节点进行初始化.对从节点初始化的方法基本和基于日志点相同，只不过在启动GTID模式后，在备份中所记录的就不是备份时的二进制日志文件名和偏移量，而是备份时最后的GTID值.
2.2.1.5、创建测试库
--在主节点创建booksDB库，此后备份该库.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> create database booksDB character set utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

mysql> use booksDB;
Database changed
mysql> create table books
    -> (
    -> bk_id int not null primary key,
    -> bk_title varchar(50) not null,
    -> copyright year not null
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into books values
    -> (11078,'Learning MYSQL',2010),
    -> (11033,'Study Html',2011),
    -> (11035,'How to use php',2003),
    -> (11072,'Teach yourself javascript',2005),
    -> (11028,'Learning C++',2005),
    -> (11069,'MYSQL professional',2009),
    -> (11026,'Guide to MySQL 5.7',2008),
    -> (11041,'Inside VC++',2011);
Query OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0

mysql> create table authors
    -> (
    -> auth_id int not null primary key,
    -> auth_name varchar(20),
    -> auth_gender char(1)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into authors values
    -> (1001,'WriterX','f'),
    -> (1002,'WriterA','f'),
    -> (1003,'WriterB','m'),
    -> (1004,'WriterC','f'),
    -> (1011,'WriterD','f'),
    -> (1012,'WriterE','m'),
    -> (1013,'WriterF','m'),
    -> (1014,'WriterG','f'),
    -> (1015,'WriterH','f');
Query OK, 9 rows affected (0.00 sec)
Records: 9 Duplicates: 0 Warnings: 0

mysql> create table authorbook
    -> (
    -> auth_id int not null,
    -> bk_id int not null,
  -> primary key (auth_id,bk_id),
    -> foreign key (auth_id) references authors (auth_id),
    -> foreign key (bk_id) references books (bk_id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into authorbook values
    -> (1001,11033),(1002,11035),(1003,11072),(1004,11028),
    -> (1011,11078),(1012,11026),(1012,11041),(1014,11069);
Query OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0
2.2.1.6、备份booksDB库
--使用mysqldump备份booksDB库.
[root@leo-mysql-master ~]# mysqldump --single-transaction --master-data=2 --triggers --routines --databases booksDB -uroot -p > /root/booksDB.sql
Enter password:
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.

注意：
mysql5.6使用mysqldump备份时，指定备份的具体库，使用--database
mysql5.7使用mysqldump备份时，指定备份的具体库，使用--databases
[root@leo-mysql-master ~]# ls /root/booksDB.sql
/root/booksDB.sql
2.2.1.7、拷贝备份集
--将booksDB.sql文件拷贝到mysql-slave1从节点.
[root@leo-mysql-master ~]# rsync -e "ssh -p22" -avpgolr /root/booksDB.sql root@192.168.133.91:/root/
The authenticity of host '192.168.133.91 (192.168.133.91)' can't be established.
ECDSA key fingerprint is 3b:99:d4:0f:4a:8d:84:d9:ae:98:0c:b4:43:97:68:46.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.133.91' (ECDSA) to the list of known hosts.
root@192.168.133.91's password:
sending incremental file list
booksDB.sql

sent 4533 bytes received 31 bytes 829.82 bytes/sec
total size is 4454 speedup is 0.98

参数说明：
-e:指定使用rsh、ssh方式进行数据同步，一般使用ssh
-a:表示以递归方式传输文件，并保持所有文件属性
-v:输出详细过程
-p:保持文件权限
-g:保持文件属组信息
-o:保持文件属主信息
-l:保留软连接
-r:对子目录以递归模式处理
-p22:22表示端口
2.2.2、从节点slave1操作
2.2.2.1、修改slave1从节点my.cnf文件
--现进行mysql-slave1从节点1的相关配置.
说明：从节点1在my.cnf文件中配置GTID主从复制，除server_id不同外，其余与主节点配置相同.从节点在配置文件还需添加"read_only＝on"，使从节点只能读，此配置不影响从节点复制，不过此参数对超级用户无效.
[root@leo-mysql-slave1 ~]# cp /etc/my.cnf /etc/my.cnf.bak
[root@leo-mysql-slave1 ~]# >/etc/my.cnf
[root@leo-mysql-slave1 ~]# vi /etc/my.cnf
添加如下：
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
symbolic-links = 0
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid

#GTID:
server_id = 2
gtid_mode = on
enforce_gtid_consistency = on

#binlog
log_bin = mysql-bin
log-slave-updates = 1
binlog_format = row
sync-master-info = 1
sync_binlog = 1

#relay log
skip_slave_start = 1
read_only = on
2.2.2.2、重启mysql进程
--配置完成my.cnf后，重启mysql服务.
[root@leo-mysql-slave1 ~]# systemctl restart mysqld
2.2.2.3、导入数据
--将主节点备份的数据booksDB.sql导入从节点1中.
[root@leo-mysql-slave1 ~]# ls /root/booksDB.sql
/root/booksDB.sql
[root@leo-mysql-slave1 ~]# mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.42-log MySQL Community Server (GPL)

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

mysql> source /root/booksDB.sql

mysql> use booksDB
Database changed
mysql> show tables;
+-------------------+
| Tables_in_booksDB |
+-------------------+
| authorbook        |
| authors           |
| books             |
+-------------------+
3 rows in set (0.00 sec)

mysql> select * from books;
+-------+---------------------------+-----------+
| bk_id | bk_title                  | copyright |
+-------+---------------------------+-----------+
| 11026 | Guide to MySQL 5.7        |      2008 |
| 11028 | Learning C++              |      2005 |
| 11033 | Study Html                |      2011 |
| 11035 | How to use php            |      2003 |
| 11041 | Inside VC++               |      2011 |
| 11069 | MYSQL professional        |      2009 |
| 11072 | Teach yourself javascript |      2005 |
| 11078 | Learning MYSQL            |      2010 |
+-------+---------------------------+-----------+
8 rows in set (0.01 sec)
2.2.2.4、配置主从复制
--在从节点slave1使用change master配置主从复制.
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> change master to master_host='192.168.133.90',master_user='slave',master_password='slave@12345',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

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

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.133.90
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 2848
               Relay_Log_File: leo-mysql-slave1-relay-bin.000002
                Relay_Log_Pos: 414
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 2848
              Relay_Log_Space: 632
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: c91dd7a0-18a7-11ee-aa0c-000c291140b1
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set: c91dd7a0-18a7-11ee-aa0c-000c291140b1:1-9
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

说明：如上所示，mysql-slave1从节点和mysql-master主节点主从同步关系配置成功.
2.2.3、数据同步测试
2.2.3.1、主节点更新数据
--mysql-master主节点进行数据更新.
[root@leo-mysql-master ~]# mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.42-log MySQL Community Server (GPL)

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show master status \G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 2848
     Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: c91dd7a0-18a7-11ee-aa0c-000c291140b1:1-9
1 row in set (0.00 sec)

mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|         2 |      | 3306 |         1 | f272bb56-18a7-11ee-ac87-00505629ec4f |
+-----------+------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)

mysql> use booksDB;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select * from authorbook;
+---------+-------+
| auth_id | bk_id |
+---------+-------+
|    1012 | 11026 |
|    1004 | 11028 |
|    1001 | 11033 |
|    1002 | 11035 |
|    1012 | 11041 |
|    1014 | 11069 |
|    1003 | 11072 |
|    1011 | 11078 |
+---------+-------+
8 rows in set (0.00 sec)

mysql> delete from authorbook where auth_id=1012;
Query OK, 2 rows affected (0.00 sec)

mysql> select * from authorbook;
+---------+-------+
| auth_id | bk_id |
+---------+-------+
|    1004 | 11028 |
|    1001 | 11033 |
|    1002 | 11035 |
|    1014 | 11069 |
|    1003 | 11072 |
|    1011 | 11078 |
+---------+-------+
6 rows in set (0.00 sec)
2.2.3.2、从节点1确认数据
--mysql-slave1从节点查看.
mysql> select * from authorbook;
+---------+-------+
| auth_id | bk_id |
+---------+-------+
|    1004 | 11028 |
|    1001 | 11033 |
|    1002 | 11035 |
|    1014 | 11069 |
|    1003 | 11072 |
|    1011 | 11078 |
+---------+-------+
6 rows in set (0.00 sec)

小结：如上所示，mysql-slave1从节点已经同步完成删除的数据，至此基于GTID的主从同步复制架构成功部署.
3、并行复制
3.1、理论知识
并行复制解决主从复制延迟问题，一般MySQL主从复制有三个线程参与，都是单线程：Binlog Dump(主) -> IO Thread (从) -> SQL Thread(从).
复制出现延迟因素:
- SQL线程繁忙(a、应用数据量较大；b、从库本身的一些操作有锁和资源的冲突；c、主库可以并发写，从库SQL线程不可以)—主要原因
- 网络抖动导致IO线程复制延迟—次要原因
MySQL主从复制延迟解决办法：
MySQL从5.6开始引进多个SQL Thread的概念，可以并发还原数据，即并行复制技术.并行复制机制是MySQL非常重要的特性，可以很好的解决主从延迟问题.
在MySQL 5.6中，设置参数slave_parallel_workers = 4(>1)，表示有4个SQL Thread(coordinator线程)用来进行并行复制，状态为：Waiting for an event from Coordinator.但并行复制只是基于Schema，也就是基于库.若数据库实例存在多个Schema，此设置对于Slave复制的速度有很大提升.如果是单库多表的场景，那基于库的并发就没有用.
核心思想：不同schema下表在并发提交时，数据相互不影响，即slave节点可以对relay log中不同schema各分配一个类似SQL功能的线程来重放relay log中主库已经提交的事务，保持与主库数据一致.
但基于schema的并行复制存在以下两个问题：
1) crash safe功能不好做，因为可能之后执行的事务由于并行复制的关系先完成执行，那么当发生crash时此部分处理逻辑比较复杂.
2) 最为关键的问题是当用户实例仅有一个库，就无法实现并行回放，甚至性能会比原来的单线程更差，而单库多表的场景比多库多表的场景更多.
注意：mysql 5.6是基于库级别的并行，当有多个数据库时，可以将slave_parallel_workers设置为数据库的数量，为了避免新建库后来回修改，也可以将该参数设置的大一些.
在MySQL 5.7引入基于组提交的并行复制(官方称为Enhanced Multi-threaded Slaves，即MTS)，设置参数slave_parallel_workers>0且global.slave_parallel_type＝'LOGICAL_CLOCK '，即可支持在一个schema下，slave_parallel_workers个worker线程并发执行relay log中主库提交的事务.
核心思想：一个组提交的事务都可以并行回放（配合binary log group commit）；从节点relay log中last_committed相同的事务(sequence_num不同)可以并发执行.其中变量slave-parallel-type可以有两个值：
1) DATABASE默认值，基于库的并行复制方式；
2) LOGICAL_CLOCK，基于组提交的并行复制方式.
MySQL 5.7开启Enhanced Multi-Threaded Slave很简单，只需在从节点的my.cnf文件中添加如下配置即可:
# slave
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=8            #通常建议设置4-8，太多线程会增加线程之间的同步开销
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON

MySQL5.7是基于组提交的并行复制，并且是支持"真正"的并行复制功能，这其中最为主要的原因就是从节点的回放与主节点一致.不再有库的并行复制限制，对于二进制日志格式也无特殊要求(基于库的并行复制也没有要求）.
MySQL5.7的并行复制期望最大化还原主库的并行度，实现方式是在binlog event中增加必要信息，以便从节点根据这些信息实现并行复制.MySQL5.7的并行复制建立在group commit的基础上，所有在主库上能够完成prepared的语句没有数据冲突，就可以在从节点并行复制.
所以在并行复制环境中，除了在从节点中配置之外，还需要在Master主节点上的my.cnf文件中添加binlog_group_commit配置，否则从库无法做到基于事物的并行复制:
binlog_group_commit_sync_delay = 100
binlog_group_commit_sync_no_delay_count = 10

参数说明：
binlog_group_commit_sync_delay控制日志在刷盘前日志提交需要等待的时间，默认是0也就是说提交后立即刷盘，但并不代表是关闭了组提交，当设置为0以上的时候，就允许多个事物的日志同时间一起提交刷盘，也就是组提交.组提交是并行复制的基础，该值大于0表示打开了组提交的延迟功能，而组提交是默认开启的，最大值只能设置为1000000微妙.
binlog_group_commit_sync_no_delay_count表示在binlog_group_commit_sync_delay等待时间内，如果事物数达到该参数的设定值，就会触动一次组提交，如果该值设为0就不会有任何的影响.如果到达时间但是事物数并没有达到的话，也是会进行一次组提交操作的.

MySQL 5.7并行复制的思想简单易懂，简而言之一个组提交的事务都是可以并行回放，因为这些事务都已进入到事务的prepare阶段，则说明事务之间没有任何冲突（否则就不可能提交）.为了兼容MySQL 5.6基于库的并行复制，5.7引入了新的变量slave-parallel-type，其可以配置的值有：
- DATABASE：默认值，基于库的并行复制方式
- LOGICAL_CLOCK：基于组提交的并行复制方式
支持并行复制的GTID
如何确认事务是否在一组中？MySQL5.7的设计方式是将组提交的信息存放在GTID中.若用户没有开启GTID功能，即将参数gtid_mode设置为OFF呢？此时MySQL5.7引入称为Anonymous_Gtid的二进制日志event类型，如:
mysql> SHOW BINLOG EVENTS in 'mysql-bin.000003';
+------------------+-----+----------------+-----------+-------------+-----------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+-----------------------------------------------+
| mysql-bin.000003 | 4   | Format_desc    | 88        | 123         | Server ver: 5.7.7-rc-debug-log, Binlog ver: 4 |
| mysql-bin.000003 | 123 | Previous_gtids | 88        | 194         | f11232f7-ff07-11e4-8fbb-00ff55e152c6:1-2 |
| mysql-bin.000003 | 194 | Anonymous_Gtid | 88        | 259        | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 259 | Query          | 88        | 330         | BEGIN |
| mysql-bin.000003 | 330 | Table_map      | 88        | 373        | table_id: 108 (aaa.t) |
| mysql-bin.000003 | 373 | Write_rows     | 88        | 413         | table_id: 108 flags: STMT_END_F |
......
这意味着在MySQL5.7中即使不开启GTID，每个事务开始前也是会存在一个Anonymous_Gtid，而这GTID中就存在着组提交的信息.
LOGICAL_CLOCK
然而通过上述的SHOW BINLOG EVENTS，并没有发现有关组提交的任何信息.但是通过mysqlbinlog工具，用户就能发现组提交的内部信息:
如下查看一个binlog日志
root@localhost:~# mysqlbinlog mysql-bin.0000002 | grep last_committed
#190112 17:45:16 server id 1 end_log_pos 219 CRC32 0xca2ee8c2 GTID    last_committed=0        sequence_number=1       rbr_only=yes
#190112 17:45:21 server id 1 end_log_pos 506 CRC32 0xe8047dd2 GTID    last_committed=1        sequence_number=2       rbr_only=yes
#190112 17:45:16 server id 1 end_log_pos 219 CRC32 0xca2ee8c2 GTID    last_committed=2        sequence_number=3       rbr_only=yes
#190112 17:45:21 server id 1 end_log_pos 506 CRC32 0xe8047dd2 GTID    last_committed=3        sequence_number=4       rbr_only=yes

可以发现较之原来的二进制日志内容多了last_committed和sequence_number，last_committed表示事务提交时上次事务提交的编号，如果事务具有相同的last_committed，表示这些事务都在一组内，可以进行并行的回放(一般是当执行的sql语句并发数大的情况下会进行组提交).上面这个binlog日志里没有组提交信息(last_committed数值都不相等)，下一个事物的last_committed永远都和上一个事物的sequence_number是相等的，这是因为事物是顺序提交的！下面看一下组提交模式的事物：
root@localhost:~# mysqlbinlog mysql-bin.0000006 | grep last_committed
#150520 14:23:11 server id 88 end_log_pos 259 CRC32 0x4ead9ad6 GTID last_committed=0 sequence_number=1
#150520 14:23:11 server id 88 end_log_pos 1483 CRC32 0xdf94bc85 GTID last_committed=0 sequence_number=2
#150520 14:23:11 server id 88 end_log_pos 2708 CRC32 0x0914697b GTID last_committed=0 sequence_number=3
#150520 14:23:11 server id 88 end_log_pos 3934 CRC32 0xd9cb4a43 GTID last_committed=0 sequence_number=4
#150520 14:23:11 server id 88 end_log_pos 5159 CRC32 0x06a6f531 GTID last_committed=0 sequence_number=5
#150520 14:23:11 server id 88 end_log_pos 6386 CRC32 0xd6cae930 GTID last_committed=0 sequence_number=6
#150520 14:23:11 server id 88 end_log_pos 7610 CRC32 0xa1ea531c GTID last_committed=6 sequence_number=7
#150520 14:23:11 server id 88 end_log_pos 8834 CRC32 0x96864e6b GTID last_committed=6 sequence_number=8
#150520 14:23:11 server id 88 end_log_pos 10057 CRC32 0x2de1ae55 GTID last_committed=6 sequence_number=9
#150520 14:23:11 server id 88 end_log_pos 11280 CRC32 0x5eb13091 GTID last_committed=6 sequence_number=10
#150520 14:23:11 server id 88 end_log_pos 12504 CRC32 0x16721011 GTID last_committed=6 sequence_number=11
#150520 14:23:11 server id 88 end_log_pos 13727 CRC32 0xe2210ab6 GTID last_committed=6 sequence_number=12
#150520 14:23:11 server id 88 end_log_pos 14952 CRC32 0xf41181d3 GTID last_committed=12 sequence_number=13
...
例如上述last_committed为0的事务有6个，其意味着此6个事物是作为一个组提交的，它们在perpare阶段获取相同的last_committed且相互不影响，因此这6个事务在从节点便可以并行回放.
总之：MySQL 5.7推出的Enhanced Multi-Threaded Slave解决困扰MySQL长达数十年的复制延迟问题，主从复制延迟问题已不在存在.
3.2、基于GTID的并行复制环境部署
3.2.1、修改从节点1 my.cnf文件
--在mysql-slave1从节点的my.cnf文件中添加下面MTS并行复制的配置.
[root@leo-mysql-slave1 ~]# cat /etc/my.cnf
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
symbolic-links = 0
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid

#GTID:
server_id = 2
gtid_mode = on
enforce_gtid_consistency = on

#binlog
log_bin = mysql-bin
log-slave-updates = 1
binlog_format = row
sync-master-info = 1
sync_binlog = 1

#relay log
skip_slave_start = 1
read_only = on

#MTS
slave-parallel-type = LOGICAL_CLOCK              #开启逻辑时钟的复制
slave-parallel-workers = 4                       #这里设置线程数为4 (最大线程数不能超过16，即最大线程为16)
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = on

说明：黄颜色高亮为新增部分.
3.2.2、重启mysql进程
--修改从节点1 my.cnf文件后重启mysql服务.
[root@leo-mysql-slave1 ~]# systemctl restart mysqld
3.2.3、修改主节点my.cnf文件
--修改mysql-master主节点的my.cnf文件.
[root@leo-mysql-master ~]# vi /etc/my.cnf
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
symbolic-links = 0
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid

#GTID:
server_id = 1
gtid_mode = on
enforce_gtid_consistency = on

#binlog
log_bin = mysql-bin
log-slave-updates = 1
binlog_format = row
sync-master-info = 1
sync_binlog = 1

#relay log
skip_slave_start = 1

#不配置binlog_group_commit从库无法做到基于事物的并行复制
binlog_group_commit_sync_delay = 100
binlog_group_commit_sync_no_delay_count = 10

#为了数据安全再配置
sync_binlog=1
innodb_flush_log_at_trx_commit =1

说明：
a、黄颜色高亮为新增部分.
b、sync_binlog参数控制binlog写入磁盘的方式，设置为1表示每次commit都写入磁盘.此刷新的是redo log，即ib_logfile0，而不是binlog.
3.2.4、重启mysql进程
--主节点重启mysql进程.
[root@leo-mysql-master ~]# systemctl restart mysqld
3.2.5、参数确认
3.2.5.1、主节点参数
--主节点登录mysql查看binlog_group_commit.
[root@leo-mysql-master ~]# mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.42-log MySQL Community Server (GPL)

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like 'binlog_group_commit_%';
+-----------------------------------------+-------+
| Variable_name                           | Value |
+-----------------------------------------+-------+
| binlog_group_commit_sync_delay          | 100   |
| binlog_group_commit_sync_no_delay_count | 10    |
+-----------------------------------------+-------+
2 rows in set (0.00 sec)

说明：主节点需设置binlog_group_commit的两个参数，否则从节点无法做到基于事物的并行复制，以上两个参数共同决定是否触发组提交操作.
binlog_group_commit_sync_delay：表示该事务组总共等待多长时间后进行提交(如上总共要等待100毫秒)
binlog_group_commit_sync_no_delay_count：表示该事务组提交之前总共等待累积到多少个事务(如上要累计到10个事务)
满足任何一个条件则进行后续操作，此等待可以让更多事务的binlog通过一次写binlog文件磁盘来完成提交，从而获得更高的吞吐量.
3.2.5.2、从节点参数
--登录mysql-slave1从数据库，如上在配置并行复制后，主从节点的mysqld服务都已重启，此时需要重启从节点上的slave才能恢复正常的主从同步状态.
注意：只要主节点的mysqld服务重启，那么从节点上也要重启slave，以恢复主从同步状态.
[root@leo-mysql-slave1 ~]# mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.42-log MySQL Community Server (GPL)

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show slave status \G
……
             Slave_IO_Running: No
            Slave_SQL_Running: No
              ……

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

mysql> show slave status \G
……
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              ……
1 row in set (0.00 sec)

说明：如上所示mysql-slave1从节点恢复与mysql-master主节点的同步关系.
--从节点1查看优化项.
mysql> select id,relay_log_name,relay_log_pos,Master_log_name,Master_log_pos,Checkpoint_relay_log_name,Checkpoint_relay_log_pos from slave_worker_info;
+----+----------------+---------------+-----------------+----------------+---------------------------+--------------------------+
| id | relay_log_name | relay_log_pos | Master_log_name | Master_log_pos | Checkpoint_relay_log_name | Checkpoint_relay_log_pos |
+----+----------------+---------------+-----------------+----------------+---------------------------+--------------------------+
| 1 |                |             0 |                 |              0 |                           |                        0 |
| 2 |                |             0 |                 |              0 |                           |                        0 |
| 3 |                |             0 |                 |              0 |                           |                        0 |
| 4 |                |             0 |                 |              0 |                           |                        0 |
+----+----------------+---------------+-----------------+----------------+---------------------------+--------------------------+
4 rows in set (0.00 sec)

说明：如上所示，从节点1下的slave_worker_info表下有4个线程.
--也可使用如下命令查看线程数，跟my.cnf文件里配置相同.
mysql> show variables like '%slave_para%';
+------------------------+---------------+
| Variable_name          | Value         |
+------------------------+---------------+
| slave_parallel_type    | LOGICAL_CLOCK |
| slave_parallel_workers | 4             |
+------------------------+---------------+
2 rows in set (0.00 sec)
3.2.6、同步复制测试
3.2.6.1、主节点更新数据
--在mysql-master主节点插入新数据.
mysql> select * from authorbook;
+---------+-------+
| auth_id | bk_id |
+---------+-------+
|    1004 | 11028 |
|    1001 | 11033 |
|    1002 | 11035 |
|    1014 | 11069 |
|    1003 | 11072 |
|    1011 | 11078 |
+---------+-------+
6 rows in set (0.00 sec)

mysql> insert into authorbook values (1012,11026),(1012,11041);
Query OK, 2 rows affected (0.10 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from authorbook;
+---------+-------+
| auth_id | bk_id |
+---------+-------+
|    1012 | 11026 |
|    1004 | 11028 |
|    1001 | 11033 |
|    1002 | 11035 |
|    1012 | 11041 |
|    1014 | 11069 |
|    1003 | 11072 |
|    1011 | 11078 |
+---------+-------+
8 rows in set (0.00 sec)

3.2.6.2、从节点1确认数据
--然后在mysql-slave1从节点查看，发现新数据已经同步到从节点1.
mysql> select * from authorbook;
+---------+-------+
| auth_id | bk_id |
+---------+-------+
|    1012 | 11026 |
|    1004 | 11028 |
|    1001 | 11033 |
|    1002 | 11035 |
|    1012 | 11041 |
|    1014 | 11069 |
|    1003 | 11072 |
|    1011 | 11078 |
+---------+-------+
8 rows in set (0.00 sec)
3.2.6.3、解析binlog文件
--最后在mysql-master主节点查看组提交信息.
[root@leo-mysql-master ~]# cd /var/lib/mysql
[root@leo-mysql-master mysql]# ll mysql-bin*
-rw-r----- 1 mysql mysql 3152 Jul 2 20:01 mysql-bin.000001
-rw-r----- 1 mysql mysql 475 Jul 2 20:28 mysql-bin.000002
-rw-r----- 1 mysql mysql   38 Jul 2 20:01 mysql-bin.index
[root@leo-mysql-master mysql]# mysqlbinlog mysql-bin.000002 | grep last_committed
#230702 20:28:26 server id 1 end_log_pos 259 CRC32 0xf73bb7bd GTID    last_committed=0        sequence_number=1       rbr_only=yes

说明：如上所示，通过最新的binlog日志看到last_committed数值仅有一个，即没有事务在同一个组内提交，也就是说该日志里没有组提交信息.
原因是没有满足binlog_group_commit的两个条件.
4、半同步复制
4.1、理论知识
默认情况下MySQL的复制是异步的，master将新生成的binlog发送给各slave后，无需等待slave的ack回复(slave将接收到的binlog写进relay log后才会回复ack)，直接认为此次DDL/DML成功.半同步复制(semi-synchronous replication)是指master将新生成的binlog发送给各slave时, 只需等待一个(默认)slave返回的ack信息就返回成功.
MySQL 5.7对半同步复制作了大改进，新增了一个master线程.在MySQL 5.7以前，master上的binlog dump线程负责两件事：
a、 dump日志给slave的io_thread；
b、接收来自slave的ack消息，它们是串行的工作方式.在MySQL 5.7中新增一个专门负责接收ack消息的线程ack collector thread，因此master上有两个线程独立工作，可以同时发送binlog到slave和接收slave的ack.还新增了几个变量，其中最重要的是rpl_semi_sync_master_wait_point，其使得MySQL半同步复制有两种工作模型.
半同步复制的两种类型
从MySQL 5.7.2开始，MySQL支持两种类型的半同步复制.这两种类型由变量 rpl_semi_sync_master_wait_point (MySQL 5.7.2之前没有该变量)控制，其有两个取值：AFTER_SYNC和AFTER_COMMIT.
在MySQL 5.7.2之后，默认值为AFTER_SYNC，在此版本之前，等价的类型为AFTER_COMMIT.该变量控制master何时提交、何时接收ack以及何时回复成功信息给客户端.
AFTER_SYNC模式：master将新事务写进binlog(buffer)后发送给slave，再sync到自己的binlog file(disk), 之后才允许接收slave的ack回复，接收到ack之后才会提交事务，并返回成功信息给客户端.
AFTER_COMMIT模式：master将新事务写进binlog(buffer)后发送给slave，再sync到自己的binlog file(disk)，然后直接提交事务.之后才允许接收slave的ack回复，然后再返回成功信息给客户端.
以下图片方便理解.(前提: 已经设置sync_binlog=1，否则binlog刷盘时间由操作系统决定)
根据以上图解分析两种模式的优缺点：
AFTER_SYNC
- 所有客户端的数据相同，因为客户端显示的数据都是在接收到slave的ack后提交后的数据；
- 此模式下如果master突然故障，不会丢失数据，因为所有成功的事务都已写进slave的relay log中，slave的数据是最新的.
AFTER_COMMIT
- 不同客户端的数据可能不一样，对于发起事务请求的那个客户端，其只有在master提交事务且收到slave的ack后才能看到提交的数据.但对于那些非本次事务的请求客户端，它们在master提交后就能看到提交后的数据，这时候master可能还没收到slave的ack.
- 如果master收到ack回复前，slave和master都出现故障，那么将丢失这个事务中的数据.
在MySQL 5.7.2之前，等价的模式是AFTER_COMMIT，在此版本之后，默认的模式为AFTER_SYNC，该模式能最大程度地保证数据安全性，且性能上并不比 AFTER_COMMIT差.

4.2、基于GTID的半同步复制环境部署
MySQL半同步复制配置可以参考: https://www.cnblogs.com/kevingrace/p/10228694.html
开启半同步复制的方法有两种：a、mysql命令行启动；b、my.cnf文件里添加启动配置；
说明：推荐在my.cnf文件里添加半同步启动配置方式.
4.2.1、修改主节点my.cnf文件
--配置mysql-master主节点上my.cnf文件，添加启动半同步复制的配置.
[root@leo-mysql-master mysql]# cat /etc/my.cnf
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
symbolic-links = 0
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid

#GTID:
server_id = 1
gtid_mode = on
enforce_gtid_consistency = on

#binlog
log_bin = mysql-bin
log-slave-updates = 1
binlog_format = row
sync-master-info = 1
sync_binlog = 1

#relay log
skip_slave_start = 1

#不配置binlog_group_commit从库无法做到基于事物的并行复制
binlog_group_commit_sync_delay = 100
binlog_group_commit_sync_no_delay_count = 10

#为了数据安全再配置
sync_binlog=1
innodb_flush_log_at_trx_commit =1

#开启半同步复制 (超时时间为1s)
plugin-load=rpl_semi_sync_master=semisync_master.so
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_master_timeout = 1000

说明：黄颜色高亮部分为添加内容.
4.2.2、重启主节点mysql进程
--重启主节点mysql服务.
[root@leo-mysql-master mysql]# systemctl restart mysqld
4.2.3、修改从节点my.cnf文件
--修改mysql-slave1从节点上my.cnf文件，添加启动半同步复制的配置.
[root@leo-mysql-slave1 ~]# vi /etc/my.cnf
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
symbolic-links = 0
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid

#GTID:
server_id = 2
gtid_mode = on
enforce_gtid_consistency = on

#binlog
log_bin = mysql-bin
log-slave-updates = 1
binlog_format = row
sync-master-info = 1
sync_binlog = 1

#relay log
skip_slave_start = 1
read_only = on

#MTS
slave-parallel-type = LOGICAL_CLOCK
slave-parallel-workers = 4
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = on

# 开启半同步复制
plugin-load=rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_slave_enabled=1

说明：黄颜色高亮为新增部分.
4.2.4、重启从节点1 mysql进程
[root@leo-mysql-slave1 ~]# systemctl restart mysqld
4.2.5、主节点加载插件
--在mysql-master主节点上加载插件(前提/usr/lib64/mysql/plugin/semisync_master.so文件存在，一般mysql安装后就默认产生)
[root@leo-mysql-master mysql]# ll /usr/lib64/mysql/plugin/semisync_master.so
-rwxr-xr-x 1 root root 937968 Mar 17 00:00 /usr/lib64/mysql/plugin/semisync_master.so
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
ERROR 1125 (HY000): Function 'rpl_semi_sync_master' already exists
--查看插件是否加载成功.
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%';
+----------------------+---------------+
| PLUGIN_NAME          | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE        |
+----------------------+---------------+
1 row in set (0.00 sec)

--查看半同步是否在运行.
mysql> show status like 'rpl_semi_sync_master_status';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | ON    |
+-----------------------------+-------+
1 row in set (0.00 sec)
4.2.6、从节点加载插件
在mysql-slave1从节点上加载插件(前提是/usr/lib64/mysql/plugin/semisync_slave.so 文件存在，一般mysql安装后就默认产生)
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
ERROR 1125 (HY000): Function 'rpl_semi_sync_slave' already exists
--查看插件是否加载成功.
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%';
+---------------------+---------------+
| PLUGIN_NAME         | PLUGIN_STATUS |
+---------------------+---------------+
| rpl_semi_sync_slave | ACTIVE        |
+---------------------+---------------+
1 row in set (0.00 sec)
4.2.7、确认从节点1半同步运行情况
--此时查看mysql-slave1的半同步是否运行.
mysql> show status like 'rpl_semi_sync_slave_status';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | OFF   |
+----------------------------+-------+
1 row in set (0.00 sec)

说明：此处值为off，明明已经在my.cnf文件开启.这是因为此时还没生效，必须重启数据上的IO线程才能生效.
4.2.7、重启从节点1 IO线程
mysql> stop slave io_thread;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> start slave io_thread;
Query OK, 0 rows affected (0.00 sec)
然后在查看mysql-slave1的半同步状态，发现已经开启.
mysql> show status like 'Rpl_semi_sync_slave_status';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+
1 row in set (0.00 sec)
4.2.8、重启从节点1 slave.
--查看此时主从同步状态.
mysql> show slave status \G
……
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              ……
--发现主从同步异常，此时重启slave即可.
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

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

mysql> show slave status \G
……
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              ……
4.2.9、主节点确认半同步架构中从节点数据
--在mysql-master主节点查看.
mysql> show status like '%Rpl_semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 0     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)

说明：Rpl_semi_sync_master_clients数值为1，说明此时mysql-master主节点存在一个半同步复制的从节点，即mysql-slave1节点.Rpl_semi_sync_master_yes_tx数值为0，说明此时还没有半同步复制的sql语句被执行.
4.3、主从半同步测试
4.3.1、主节点更新数据
--接着在mysql-master主节点删除数据，测试半同步复制.
mysql> use booksDB;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from authorbook;
+---------+-------+
| auth_id | bk_id |
+---------+-------+
|    1012 | 11026 |
|    1004 | 11028 |
|    1001 | 11033 |
|    1002 | 11035 |
|    1012 | 11041 |
|    1014 | 11069 |
|    1003 | 11072 |
|    1011 | 11078 |
+---------+-------+
8 rows in set (0.00 sec)

mysql> delete from authorbook where auth_id=1012;
Query OK, 2 rows affected (0.14 sec)
4.3.2、从节点查看数据
--mysql-slave1从节点1查看，发现新数据已经同步过来.
mysql> use booksDB;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from authorbook;
+---------+-------+
| auth_id | bk_id |
+---------+-------+
|    1004 | 11028 |
|    1001 | 11033 |
|    1002 | 11035 |
|    1014 | 11069 |
|    1003 | 11072 |
|    1011 | 11078 |
+---------+-------+
6 rows in set (0.00 sec)
4.3.3、主节点再次确认半同步从节点数据
--接着再去mysql-master主节点查看.
mysql> show status like '%Rpl_semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 1     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 2346 |
| Rpl_semi_sync_master_tx_wait_time          | 2346 |
| Rpl_semi_sync_master_tx_waits              | 1     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 1     |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)

说明：如上所示Rpl_semi_sync_master_yes_tx的数值更新为1，即发生过一条半同步复制的sql语句，就是上面执行的那条delete sql语句.
小结：以上为mysql-master主节点和mysql-slave1从节点之间实现的基于GTID的主从复制、并行复制、半同步复制，即"一主一从"架构.现将mysql-slave2从节点2添加进去该架构，调整为"一主两从"的同步架构.

5、添加从节点2
说明：在"一主一从"架构下添加从节点2使其成为"一主两从"的同步架构，
5.1、主节点操作
5.1.1、添加同步权限
mysql> grant replication slave,replication client on *.* to 'slave'@'192.168.133.92' identified by "slave@12345";
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
5.1.2、备份主库
[root@leo-mysql-master ~]# mysqldump -u root -p --lock-all-tables --master-data=2 --flush-logs --all-databases --triggers --routines --events > 92_slave.sql
Enter password:
[root@leo-mysql-master ~]# ll 92_slave.sql
-rw-r--r-- 1 root root 893084 Jul 2 22:30 92_slave.sql
5.1.3、拷贝备份文件
--将备份文件拷贝到mysql-slave2从节点上.
[root@leo-mysql-master ~]# rsync -e "ssh -p22" -avpgolr /root/92_slave.sql root@192.168.133.92:/root/
The authenticity of host '192.168.133.92 (192.168.133.92)' can't be established.
ECDSA key fingerprint is 3b:99:d4:0f:4a:8d:84:d9:ae:98:0c:b4:43:97:68:46.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.133.92' (ECDSA) to the list of known hosts.
root@192.168.133.92's password:
sending incremental file list
92_slave.sql

sent 893272 bytes received 31 bytes 198511.78 bytes/sec
total size is 893084 speedup is 1.00
--记录此时的gtid.
mysql> show global variables like 'gtid_%';
+----------------------------------+-------------------------------------------+
| Variable_name                    | Value                                     |
+----------------------------------+-------------------------------------------+
| gtid_executed                    | c91dd7a0-18a7-11ee-aa0c-000c291140b1:1-14 |
| gtid_executed_compression_period | 1000                                      |
| gtid_mode                        | ON                                        |
| gtid_owned                       |                                           |
| gtid_purged                      |                                           |
+----------------------------------+-------------------------------------------+
5 rows in set (0.00 sec)
5.2、从节点2操作
5.2.1、修改my.cnf文件
--在mysql-slave2操作，首先在my.cnf添加同步配置(跟mysql-slave1从节点的my.cnf配置相比，除了将server_id修改为3之外，其他配置内容一样)
[root@leo-mysql-slave2 ~]# cp /etc/my.cnf /etc/my.cnf.bak
[root@leo-mysql-slave2 ~]# >/etc/my.cnf
[root@leo-mysql-slave2 ~]# vi /etc/my.cnf
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock

symbolic-links = 0

log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid

#GTID:
server_id = 3
gtid_mode = on
enforce_gtid_consistency = on

#binlog
log_bin = mysql-bin
log-slave-updates = 1
binlog_format = row
sync-master-info = 1
sync_binlog = 1

#relay log
skip_slave_start = 1
read_only = on

#MTS
slave-parallel-type = LOGICAL_CLOCK             #开启逻辑时钟的复制
slave-parallel-workers = 4                      #最大线程16
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = on

# 开启半同步复制
plugin-load=rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_slave_enabled=1
5.2.2、重启mysql进程
--配置完成my.cnf后，重启mysql服务.
[root@leo-mysql-slave2 ~]# systemctl restart mysqld
5.2.3、恢复备份库
[root@leo-mysql-slave2 ~]# ll 92_slave.sql
-rw-r--r--. 1 root root 893084 Jul 2 22:30 92_slave.sql
[root@leo-mysql-slave2 ~]# mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.42-log MySQL Community Server (GPL)

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> source /root/92_slave.sql
……(省略若干sql语句)
mysql> select * from booksDB.authorbook;
+---------+-------+
| auth_id | bk_id |
+---------+-------+
|    1004 | 11028 |
|    1001 | 11033 |
|    1002 | 11035 |
|    1014 | 11069 |
|    1003 | 11072 |
|    1011 | 11078 |
+---------+-------+
6 rows in set (0.00 sec)

--检查当前mysql-slave2从节点上gtid
mysql> show global variables like 'gtid_%';
+----------------------------------+-------------------------------------------+
| Variable_name                    | Value                                     |
+----------------------------------+-------------------------------------------+
| gtid_executed                    | c91dd7a0-18a7-11ee-aa0c-000c291140b1:1-14 |
| gtid_executed_compression_period | 1000                                      |
| gtid_mode                        | ON                                        |
| gtid_owned                       |                                           |
| gtid_purged                      | c91dd7a0-18a7-11ee-aa0c-000c291140b1:1-14 |
+----------------------------------+-------------------------------------------+
5 rows in set (0.00 sec)

说明：由于是从master-master主库备份恢复过来的数据，所以此时mysql-slave2从节点的gtid和主库的gtid相同.
5.2.4、从节点2进行主从同步
--接着从节点2进行主从同步.
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> change master to master_host='192.168.133.90',master_user='slave',master_password='slave@12345',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

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

mysql> show slave status \G
……
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              ……
5.2.5、参数确认
--并行复制查看.
mysql> show variables like '%slave_para%';
+------------------------+---------------+
| Variable_name          | Value         |
+------------------------+---------------+
| slave_parallel_type    | LOGICAL_CLOCK |
| slave_parallel_workers | 4             |
+------------------------+---------------+
2 rows in set (0.01 sec)

mysql> select id,relay_log_name,relay_log_pos,master_log_name,master_log_pos,checkpoint_relay_log_name,checkpoint_relay_log_pos from mysql.slave_worker_info;
+----+----------------+---------------+-----------------+----------------+---------------------------+--------------------------+
| id | relay_log_name | relay_log_pos | master_log_name | master_log_pos | checkpoint_relay_log_name | checkpoint_relay_log_pos |
+----+----------------+---------------+-----------------+----------------+---------------------------+--------------------------+
| 1 |                |             0 |                 |              0 |                           |                        0 |
| 2 |               |             0 |                 |              0 |                           |                        0 |
| 3 |                |             0 |                 |              0 |                           |                        0 |
| 4 |                |             0 |                 |              0 |                           |                        0 |
+----+----------------+---------------+-----------------+----------------+---------------------------+--------------------------+
4 rows in set (0.00 sec)
5.2.6、从节点2开启半同步
--接着在mysql-slave2从节点2上开启半同步.
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
ERROR 1125 (HY000): Function 'rpl_semi_sync_slave' already exists
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%';
+---------------------+---------------+
| PLUGIN_NAME         | PLUGIN_STATUS |
+---------------------+---------------+
| rpl_semi_sync_slave | ACTIVE        |
+---------------------+---------------+
1 row in set (0.00 sec)

mysql> stop slave io_thread;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave io_thread;
Query OK, 0 rows affected (0.00 sec)

mysql> show status like 'Rpl_semi_sync_slave_status';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+
1 row in set (0.00 sec)
5.3、主节点确认半同步架构中从节点信息
--现在回到mysql-master主节点查看相关参数.
mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|         3 |      | 3306 |        1 | ff224f3d-18e5-11ee-bf74-005056346327 |
|         2 |      | 3306 |         1 | f272bb56-18a7-11ee-ac87-00505629ec4f |
+-----------+------+------+-----------+--------------------------------------+
2 rows in set (0.01 sec)

说明：如上信息可知mysql-master主节点当前有两个从节点，分别为mysql-slave1和mysql-slave2.
mysql> show status like '%Rpl_semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 2     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 3     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 1707 |
| Rpl_semi_sync_master_tx_wait_time          | 5123 |
| Rpl_semi_sync_master_tx_waits              | 3     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 3     |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)

说明：如上信息可知，mysql-master主节点现有两个半同步复制的从库，即mysql-slave1和mysql-slave2.
5.4、半同步数据测试
--如下测试数据同步效果.
5.4.1、mysql-master主节点更新数据
[root@leo-mysql-master ~]# mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.42-log MySQL Community Server (GPL)

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use booksDB;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from authorbook;
+---------+-------+
| auth_id | bk_id |
+---------+-------+
|    1004 | 11028 |
|    1001 | 11033 |
|    1002 | 11035 |
|    1014 | 11069 |
|    1003 | 11072 |
|    1011 | 11078 |
+---------+-------+
6 rows in set (0.00 sec)

mysql> insert into authorbook values (1012,11026),(1012,11041);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from authorbook;
+---------+-------+
| auth_id | bk_id |
+---------+-------+
|    1012 | 11026 |
|    1004 | 11028 |
|    1001 | 11033 |
|    1002 | 11035 |
|    1012 | 11041 |
|    1014 | 11069 |
|    1003 | 11072 |
|    1011 | 11078 |
+---------+-------+
8 rows in set (0.00 sec)
5.4.2、从节点验证数据
--在mysql-slave1从节点查看，发现数据已经同步.
mysql> select * from authorbook;
+---------+-------+
| auth_id | bk_id |
+---------+-------+
|    1012 | 11026 |
|    1004 | 11028 |
|    1001 | 11033 |
|    1002 | 11035 |
|    1012 | 11041 |
|    1014 | 11069 |
|    1003 | 11072 |
|    1011 | 11078 |
+---------+-------+
8 rows in set (0.00 sec)

--在mysql-slave2从节点查看，发现数据已经同步.
mysql> select * from authorbook;
+---------+-------+
| auth_id | bk_id |
+---------+-------+
|    1012 | 11026 |
|    1004 | 11028 |
|    1001 | 11033 |
|    1002 | 11035 |
|    1012 | 11041 |
|    1014 | 11069 |
|    1003 | 11072 |
|    1011 | 11078 |
+---------+-------+
8 rows in set (0.00 sec)

说明：到此已完成Mysql5.7 一主两从架构上基于GTID主从复制+并行复制+增强半同步复制环境.

参考网址：https://blog.51cto.com/u_6215974/4938547

gtid主从复制并行复制半同步复制

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

Mysql5.7 一主两从架构上基于GTID主从复制+并行复制+增强半同步复制环境

1、安装mysql

1.1、关闭防火墙

1.2、关闭selinux

1.3、重启系统

2、配置基于GTID的主从复制

2.1、理论知识

2.2、基于GTID主从复制环境部署步骤

2.2.1、主节点操作

2.2.1.1、修改主节点my.cnf文件

2.2.1.2、重启mysql进程

2.2.1.3、确认相关参数

2.2.1.4、创建复制用户

2.2.1.5、创建测试库

2.2.1.6、备份booksDB库

2.2.1.7、拷贝备份集

2.2.2、从节点slave1操作

2.2.2.1、修改slave1从节点my.cnf文件

2.2.2.2、重启mysql进程

2.2.2.3、导入数据

2.2.2.4、配置主从复制

2.2.3、数据同步测试

2.2.3.1、主节点更新数据

2.2.3.2、从节点1确认数据

3、并行复制

3.1、理论知识

3.2、基于GTID的并行复制环境部署

3.2.1、修改从节点1 my.cnf文件

3.2.2、重启mysql进程

3.2.3、修改主节点my.cnf文件

3.2.4、重启mysql进程

3.2.5、参数确认

3.2.5.1、主节点参数

3.2.5.2、从节点参数

3.2.6、同步复制测试

3.2.6.1、主节点更新数据

3.2.6.2、从节点1确认数据

3.2.6.3、解析binlog文件

4、半同步复制

4.1、理论知识

4.2、基于GTID的半同步复制环境部署

4.2.1、修改主节点my.cnf文件

4.2.2、重启主节点mysql进程

4.2.3、修改从节点my.cnf文件

4.2.4、重启从节点1 mysql进程

4.2.5、主节点加载插件

4.2.6、从节点加载插件

4.2.7、确认从节点1半同步运行情况

4.2.7、重启从节点1 IO线程

4.2.8、重启从节点1 slave.

4.2.9、主节点确认半同步架构中从节点数据

4.3、主从半同步测试

4.3.1、主节点更新数据

4.3.2、从节点查看数据

4.3.3、主节点再次确认半同步从节点数据

5、添加从节点2

5.1、主节点操作

5.1.1、添加同步权限

5.1.2、备份主库

5.1.3、拷贝备份文件

5.2、从节点2操作

5.2.1、修改my.cnf文件

5.2.2、重启mysql进程

5.2.3、恢复备份库

5.2.4、从节点2进行主从同步

5.2.5、参数确认

5.2.6、从节点2开启半同步

5.3、主节点确认半同步架构中从节点信息

5.4、半同步数据测试

5.4.1、mysql-master主节点更新数据

5.4.2、从节点验证数据

评论