标签：slave 21 semi 5.7 sync 7.3 master mysql Rpl
文档课题：rhel 7.3搭建MySQL 5.7.21(一主一从GTID半同步复制)
数据库：MySQL 5.7.21
系统：rhel 7.3
环境：
角色 主机名 IP 操作系统 server_id MySQL安装包
master mysql-leo-master 192.168.133.111 rhel 7.3 1 Percona-Server-5.7.21-20-Linux.x86_64.ssl101.tar.gz
slave mysql-leo-slave 192.168.133.112 rhel 7.3 2 Percona-Server-5.7.21-20-Linux.x86_64.ssl101.tar.gz
说明：在主从节点上分别安装好mysql 5.7.21.
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、基于GTID主从复制环境部署
2.1、主节点操作
2.1.1、修改主库my.cnf文件
--在my.cnf文件中配置GTID主从复制.
[root@leo-mysql-master ~]# cp /etc/my.cnf /etc/my.cnf.bak
[mysql@mysql-leo-master etc]$ vi my.cnf
[mysql]
#CLIENT#
port = 3306
socket = /mysql/data/mysql.sock
[mysqld]
basedir = /usr/local/mysql
datadir = /mysql/data/
server_id = 1
port = 3306
sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
character_set_server = utf8mb4
socket = /mysql/data/mysql.sock
secure-file-priv = /mysql/bak/file
log-bin = /mysql/binlog/mysql-bin
log-bin-index = /mysql/data/mysql-bin.index
relay-log = /mysql/data/relay-bin
relay-log-index = /mysql/data/relay-bin.index
#GTID
gtid_mode = on
enforce_gtid_consistency = on
innodb_buffer_pool_size = 2G
[mysqld_safe]
pid_file = /mysql/data/mysql.pid
log_error = /mysql/data/mysql_error.log
说明：蓝颜色高亮部分为新增配置.
2.1.2、重启mysql进程
--配置完my.cnf后重启mysql服务.
[mysql@mysql-leo-master ~]$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.21-20-log Percona Server (GPL), Release 20, Revision ed217b06ca3
Copyright (c) 2009-2018 Percona LLC and/or its affiliates
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
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> shutdown;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
[mysql@mysql-leo-master ~]$ /usr/local/mysql/bin/mysqld_safe --defaults-file=/home/mysql/etc/my.cnf &
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 | d0dddbd8-43bd-11ee-ac2f-000c29763af3 |
+---------------+--------------------------------------+
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.1.4、创建复制用户
--创建repl复制用户，并刷新权限.
mysql> grant replication slave,replication client on *.* to 'repl'@'192.168.%' identified by "repl@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 'repl'@'192.168.%';
+--------------------------------------------------------------------------+
| Grants for [email protected].% |
+--------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'192.168.%' |
+--------------------------------------------------------------------------+
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.000004 | 617 | | | d0dddbd8-43bd-11ee-ac2f-000c29763af3:1-2 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
注意：启动配置之前，同样需要对从节点进行初始化.对从节点初始化的方法基本和基于日志点相同，只不过在启动GTID模式后，在备份中所记录的就不是备份时的二进制日志文件名和偏移量，而是备份时最后的GTID值.
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
mysql> show tables;
+-------------------+
| Tables_in_booksDB |
+-------------------+
| authorbook |
| authors |
| books |
+-------------------+
3 rows in set (0.00 sec)
2.1.6、全库备份
--使用mysqldump备份全库.
[mysql@mysql-leo-master tmp]$ mysqldump -uroot -h127.0.0.1 -p -P3306 --max_allowed_packet=1G --master-data=2 --single-transaction -A -ER > /tmp/full_backupdb_full.sql
Enter password:
[mysql@mysql-leo-master tmp]$ ls -ltr full_backupdb_full.sql
-rw-rw-r--. 1 mysql mysql 791494 Aug 26 12:03 full_backupdb_full.sql
注意：
mysql5.6使用mysqldump备份时，指定备份的具体库，使用--database
mysql5.7使用mysqldump备份时，指定备份的具体库，使用--databases
2.1.7、拷贝备份集
--将full_backupdb_full.sql文件拷贝到mysql-leo-slave从节点.
[mysql@mysql-leo-master tmp]$ rsync -e "ssh -p22" -avpgolr /tmp/full_backupdb_full.sql [email protected]:/tmp
The authenticity of host '192.168.133.112 (192.168.133.112)' can't be established.
ECDSA key fingerprint is de:f3:3d:b0:c0:7b:25:e1:a0:d0:f0:c6:19:d2:35:b8.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.133.112' (ECDSA) to the list of known hosts.
[email protected]'s password:
sending incremental file list
full_backupdb_full.sql
sent 791695 bytes received 31 bytes 121804.00 bytes/sec
total size is 791494 speedup is 1.00
参数说明：
-e:指定使用rsh、ssh方式进行数据同步，一般使用ssh
-a:表示以递归方式传输文件，并保持所有文件属性
-v:输出详细过程
-p:保持文件权限
-g:保持文件属组信息
-o:保持文件属主信息
-l:保留软连接
-r:对子目录以递归模式处理
-p22:22表示端口
2.1.8、关闭防火墙
[root@mysql-leo-master ~]# systemctl stop firewalld
[root@mysql-leo-master ~]# systemctl disable firewalld
2.2、从节点操作
2.2.1、修改从库my.cnf文件
[mysql@mysql-leo-slave ~]$ cp /home/mysql/etc/my.cnf /home/mysql/etc/my.cnf.bak
[mysql@mysql-leo-slave ~]$ >/home/mysql/etc/my.cnf
添加如下：
[mysql]
#CLIENT#
port = 3306
socket = /mysql/data/mysql.sock
[mysqld]
basedir = /usr/local/mysql
datadir = /mysql/data/
server_id = 2
port = 3306
sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
character_set_server = utf8mb4
socket = /mysql/data/mysql.sock
secure-file-priv = /mysql/bak/file
log-bin = /mysql/binlog/mysql-bin
log-bin-index = /mysql/data/mysql-bin.index
relay-log = /mysql/data/relay-bin
relay-log-index = /mysql/data/relay-bin.index
#GTID
gtid_mode = on
enforce_gtid_consistency = on
innodb_buffer_pool_size = 2G
read_only = on
[mysqld_safe]
pid_file = /mysql/data/mysql.pid
log_error = /mysql/data/mysql_error.log
说明：从节点在my.cnf文件中配置GTID主从复制，除server_id不同外，其余与主节点配置相同.从节点在配置文件还需添加"read_only＝on"，使从节点只能读，此配置不影响从节点复制，不过此参数对超级用户无效.
2.2.2、重启mysql
--配置完成my.cnf后，重启mysql服务.
[mysql@mysql-leo-slave ~]$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.21-20-log Percona Server (GPL), Release 20, Revision ed217b06ca3
Copyright (c) 2009-2018 Percona LLC and/or its affiliates
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
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> shutdown;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
[mysql@mysql-leo-slave ~]$ /usr/local/mysql/bin/mysqld_safe --defaults-file=/home/mysql/etc/my.cnf &
2.2.3、导入数据
--将主节点备份的数据导入从节点中.
[mysql@mysql-leo-slave ~]$ ls -ltr /tmp/full_backupdb_full.sql
-rw-rw-r--. 1 mysql mysql 791494 Aug 26 12:03 /tmp/full_backupdb_full.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 /tmp/full_backupdb_full.sql
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| booksDB |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows 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> show tables;
+-------------------+
| Tables_in_booksDB |
+-------------------+
| authorbook |
| authors |
| books |
+-------------------+
3 rows in set (0.00 sec)
2.2.4、关闭防火墙
[root@mysql-leo-slave ~]# systemctl stop firewalld
[root@mysql-leo-slave ~]# systemctl disable firewalld
2.2.5、配置主从复制
--在从节点使用change master配置主从复制.
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change master to master_host='192.168.133.111',master_user='repl',master_password='repl@12345',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.133.111
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 2842
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 414
Relay_Master_Log_File: mysql-bin.000004
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: 2842
Relay_Log_Space: 615
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: d0dddbd8-43bd-11ee-ac2f-000c29763af3
Master_Info_File: /mysql/data/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: d0dddbd8-43bd-11ee-ac2f-000c29763af3:1-9
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
说明：如上所示，主从同步关系配置成功.
2.3、数据同步测试
2.3.1、主节点更新数据
--主节点进行数据更新.
[mysql@mysql-leo-master tmp]$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.21-20-log Percona Server (GPL), Release 20, Revision ed217b06ca3
Copyright (c) 2009-2018 Percona LLC and/or its affiliates
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
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.000004
Position: 2842
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: d0dddbd8-43bd-11ee-ac2f-000c29763af3:1-9
1 row in set (0.00 sec)
mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+-----------+------+------+-----------+--------------------------------------+
| 2 | | 3306 | 1 | 26d80a96-43be-11ee-a4ec-0050563efe7b |
+-----------+------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| booksDB |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.01 sec)
mysql> create database fruitsDB;
Query OK, 1 row affected (0.00 sec)
mysql> use fruitsDB
Database changed
mysql> create table fruits
-> (
-> f_id char(10) not null,
-> s_id int not null,
-> f_name char(255) not null,
-> f_price decimal(8,2) not null,
-> primary key(f_id)
-> ) ENGINE=innodb default charset=utf8 auto_increment=1;
mysql> select * from fruits;
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| a1 | 101 | apple | 5.20 |
| a2 | 103 | apricot | 2.20 |
| b1 | 101 | blackberry | 10.20 |
| b2 | 104 | berry | 7.60 |
| b5 | 107 | xxxx | 3.60 |
| bs1 | 102 | orange | 11.20 |
| bs2 | 105 | melon | 8.20 |
| c0 | 101 | cherry | 3.20 |
| l2 | 104 | lemon | 6.40 |
| m1 | 106 | mango | 15.70 |
| m2 | 105 | xbabay | 2.60 |
| m3 | 105 | xxtt | 11.60 |
| o2 | 103 | coconut | 9.20 |
| t1 | 102 | banana | 10.30 |
| t2 | 102 | grape | 5.30 |
| t4 | 107 | xbababa | 3.60 |
+------+------+------------+---------+
16 rows in set (0.00 sec)
2.3.2、从节点确认
[mysql@mysql-leo-slave ~]$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.21-20-log Percona Server (GPL), Release 20, Revision ed217b06ca3
Copyright (c) 2009-2018 Percona LLC and/or its affiliates
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
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 |
| booksDB |
| fruitsDB |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.01 sec)
mysql> use fruitsDB;
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 fruits;
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| a1 | 101 | apple | 5.20 |
| a2 | 103 | apricot | 2.20 |
| b1 | 101 | blackberry | 10.20 |
| b2 | 104 | berry | 7.60 |
| b5 | 107 | xxxx | 3.60 |
| bs1 | 102 | orange | 11.20 |
| bs2 | 105 | melon | 8.20 |
| c0 | 101 | cherry | 3.20 |
| l2 | 104 | lemon | 6.40 |
| m1 | 106 | mango | 15.70 |
| m2 | 105 | xbabay | 2.60 |
| m3 | 105 | xxtt | 11.60 |
| o2 | 103 | coconut | 9.20 |
| t1 | 102 | banana | 10.30 |
| t2 | 102 | grape | 5.30 |
| t4 | 107 | xbababa | 3.60 |
+------+------+------------+---------+
16 rows in set (0.00 sec)
小结：如上所示，主从同步正常，至此基于GTID的主从同步复制架构成功部署完毕.
3、半同步复制配置
3.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刷盘时间由操作系统决定)
3.2、基于GTID的半同步复制环境部署
MySQL半同步复制配置可以参考: https://www.cnblogs.com/kevingrace/p/10228694.html
开启半同步复制的方法有两种：a、mysql命令行启动；b、my.cnf文件里添加启动配置；
说明：推荐在my.cnf文件里添加半同步启动配置方式.
3.2.1、主节点加载插件
--在主节点上加载插件(前提semisync_master.so文件存在，一般mysql安装后就默认产生)
[root@mysql-leo-master ~]# find / -name semisync_master.so
/usr/local/Percona-Server-5.7.21-20-Linux.x86_64.ssl101/lib/mysql/plugin/semisync_master.so
[root@mysql-leo-master ~]# find / -name semisync_slave.so
/usr/local/Percona-Server-5.7.21-20-Linux.x86_64.ssl101/lib/mysql/plugin/semisync_slave.so
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%';
Empty set (0.01 sec)
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.00 sec)
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%';
+----------------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE |
| rpl_semi_sync_slave | ACTIVE |
+----------------------+---------------+
2 rows in set (0.00 sec)
--查看半同步参数情况.
mysql> show status like '%rpl_semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 0 |
| 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 | OFF |
| 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 |
| Rpl_semi_sync_slave_status | OFF |
+--------------------------------------------+-------+
15 rows in set (0.00 sec)
3.2.2、修改主库my.cnf文件
--配置主节点上my.cnf文件，添加启动半同步复制的配置.
[mysql@mysql-leo-master etc]$ vi my.cnf
[mysql]
#CLIENT#
port = 3306
socket = /mysql/data/mysql.sock
[mysqld]
basedir = /usr/local/mysql
datadir = /mysql/data/
server_id = 1
port = 3306
sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
character_set_server = utf8mb4
socket = /mysql/data/mysql.sock
secure-file-priv = /mysql/bak/file
log-bin = /mysql/binlog/mysql-bin
log-bin-index = /mysql/data/mysql-bin.index
relay-log = /mysql/data/relay-bin
relay-log-index = /mysql/data/relay-bin.index
#GTID
gtid_mode = on
enforce_gtid_consistency = on
innodb_buffer_pool_size = 2G
#开启半同步复制
plugin-load=rpl_semi_sync_master=semisync_master.so
rpl_semi_sync_master_wait_no_slave = 0
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_master_timeout = 10000
rpl_semi_sync_slave_enabled = 0
rpl_semi_sync_master_wait_for_slave_count = 1
rpl_semi_sync_master_wait_point = AFTER_SYNC
[mysqld_safe]
pid_file = /mysql/data/mysql.pid
log_error = /mysql/data/mysql_error.log
说明：蓝色高亮部分为添加内容.
3.2.3、重启主节点mysql进程
--重启主节点mysql服务.
[mysql@mysql-leo-master etc]$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.21-20-log Percona Server (GPL), Release 20, Revision ed217b06ca3
Copyright (c) 2009-2018 Percona LLC and/or its affiliates
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
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> shutdown;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
[mysql@mysql-leo-master etc]$ /usr/local/mysql/bin/mysqld_safe --defaults-file=/home/mysql/etc/my.cnf &
3.2.4、从节点加载插件
在从节点上加载插件(前提是/usr/lib64/mysql/plugin/semisync_slave.so 文件存在，一般mysql安装后就默认产生)
[root@mysql-leo-slave local]# find / -name semisync_master.so
/usr/local/Percona-Server-5.7.21-20-Linux.x86_64.ssl101/lib/mysql/plugin/semisync_master.so
[root@mysql-leo-slave local]# find / -name semisync_slave.so
/usr/local/Percona-Server-5.7.21-20-Linux.x86_64.ssl101/lib/mysql/plugin/semisync_slave.so
[mysql@mysql-leo-slave ~]$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.21-20-log Percona Server (GPL), Release 20, Revision ed217b06ca3
Copyright (c) 2009-2018 Percona LLC and/or its affiliates
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
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> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%';
Empty set (0.00 sec)
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.00 sec)
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%';
+----------------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE |
| rpl_semi_sync_slave | ACTIVE |
+----------------------+---------------+
2 rows in set (0.00 sec)
--查看半同步参数情况.
mysql> show status like '%rpl_semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 0 |
| 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 | OFF |
| 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 |
| Rpl_semi_sync_slave_status | OFF |
+--------------------------------------------+-------+
15 rows in set (0.00 sec)
3.2.5、修改从节点my.cnf文件
--修改从节点my.cnf文件，添加启动半同步复制的配置.
[mysql@mysql-leo-slave etc]$ vi my.cnf
--按如下修改
[mysql]
#CLIENT#
port = 3306
socket = /mysql/data/mysql.sock
[mysqld]
basedir = /usr/local/mysql
datadir = /mysql/data/
server_id = 2
port = 3306
sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
character_set_server = utf8mb4
socket = /mysql/data/mysql.sock
secure-file-priv = /mysql/bak/file
log-bin = /mysql/binlog/mysql-bin
log-bin-index = /mysql/data/mysql-bin.index
relay-log = /mysql/data/relay-bin
relay-log-index = /mysql/data/relay-bin.index
#GTID
gtid_mode = on
enforce_gtid_consistency = on
innodb_buffer_pool_size = 2G
read_only = on
#开启半同步复制
plugin-load=rpl_semi_sync_master=semisync_master.so
rpl_semi_sync_master_wait_no_slave = 0
rpl_semi_sync_master_enabled = 0
rpl_semi_sync_master_timeout = 10000
rpl_semi_sync_slave_enabled = 1
rpl_semi_sync_master_wait_for_slave_count = 1
rpl_semi_sync_master_wait_point = AFTER_SYNC
[mysqld_safe]
pid_file = /mysql/data/mysql.pid
log_error = /mysql/data/mysql_error.log
说明：蓝色高亮为新增部分.
3.2.6、重启从库mysql进程
mysql> shutdown;
Query OK, 0 rows affected (0.00 sec)
[mysql@mysql-leo-slave etc]$ /usr/local/mysql/bin/mysqld_safe --defaults-file=/home/mysql/etc/my.cnf &
3.2.7、开启slave进程
--从节点开启slave进程
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.133.111
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 194
Relay_Log_File: relay-bin.000006
Relay_Log_Pos: 407
Relay_Master_Log_File: mysql-bin.000006
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: 194
Relay_Log_Space: 648
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: d0dddbd8-43bd-11ee-ac2f-000c29763af3
Master_Info_File: /mysql/data/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: d0dddbd8-43bd-11ee-ac2f-000c29763af3:10-12
Executed_Gtid_Set: d0dddbd8-43bd-11ee-ac2f-000c29763af3:1-12
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
3.2.8、半同步验证
--从节点情况
mysql> show status like '%rpl_semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 0 |
| 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 | OFF |
| 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 |
| Rpl_semi_sync_slave_status | ON |
+--------------------------------------------+-------+
15 rows in set (0.00 sec)
--主节点情况.
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 |
| Rpl_semi_sync_slave_status | OFF |
+--------------------------------------------+-------+
15 rows in set (0.00 sec)
说明：Rpl_semi_sync_master_clients数值为1，说明此时主库存在一个半同步复制的从节点.Rpl_semi_sync_master_yes_tx数值为0，说明此时还没有半同步复制的sql语句被执行.
4、主从半同步测试
4.1、主节点更新数据
--接着在主库删除数据，测试半同步复制.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| booksDB |
| fruitsDB |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
--删除booksDB
mysql> drop database booksDB;
Query OK, 3 rows affected (0.02 sec)
4.2、从节点查看数据
--mysql-slave1从节点1查看，发现新数据已经同步过来.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| fruitsDB |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
4.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 | 869 |
| Rpl_semi_sync_master_tx_wait_time | 869 |
| 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 |
| Rpl_semi_sync_slave_status | OFF |
+--------------------------------------------+-------+
15 rows in set (0.01 sec)
说明：如上所示Rpl_semi_sync_master_yes_tx的数值更新为1，即发生过一条半同步复制的sql语句.因每个从库都有可能成为主库，所以每个节点都开启binlog日志，并创建replication账号以及semisync的so文件安装及相关配置
参考网址：https://blog.51cto.com/u_6215974/4938547
标签：slave,
21,
semi,
5.7,
sync,
7.3,
master,
mysql,
Rpl
From： https://blog.51cto.com/u_12991611/7245370