1、异步复制（Async Replication）
主库将更新写入Binlog日志文件后，不需要等待数据更新是否已经复制到从库中，就可以继续处理更多的请求。Master将事件写入binlog，但并不知道Slave是否或何时已经接收且已处理。在异步复制的机制的情况下，如果Master宕机，事务在Master上已提交，但很可能这些事务没有传到任何的Slave上。假设有Master->Salve故障转移的机制，此时Slave也可能会丢失事务。MySQL复制默认是异步复制，异步复制提供了最佳性能。

2、同步复制（Sync Replication）
主库将更新写入Binlog日志文件后，需要等待数据更新已经复制到从库中，并且已经在从库执行成功，然后才能返回继续处理其它的请求。同步复制提供了最佳安全性，保证数据安全，数据不会丢失，但对性能有一定的影响。

3、半同步复制（Semi-Sync Replication）
主库提交更新写入二进制日志文件后，等待数据更新写入了从服务器中继日志中，然后才能再继续处理其它请求。该功能确保至少有1个从库接收完主库传递过来的binlog内容已经写入到自己的relay log里面了，才会通知主库上面的等待线程，该操作完毕。
半同步复制，是最佳安全性与最佳性能之间的一个折中。
MySQL 5.5版本之后引入了半同步复制功能，主从服务器必须安装半同步复制插件，才能开启该复制功能。如果等待超时，超过rpl_semi_sync_master_timeout参数设置时间（默认值为10000，表示10秒），则关闭半同步复制，并自动转换为异步复制模式。当master dump线程发送完一个事务的所有事件之后，如果在rpl_semi_sync_master_timeout内，收到了从库的响应，则主从又重新恢复为增强半同步复制。
ACK (Acknowledge character）即是确认字符。

4、增强半同步复制（lossless Semi-Sync Replication、无损复制）
增强半同步是在MySQL 5.7引入，其实半同步可以看成是一个过渡功能，因为默认的配置就是增强半同步，所以，大家一般说的半同步复制其实就是增强的半同步复制，也就是无损复制。
增强半同步和半同步不同的是，等待ACK时间不同
rpl_semi_sync_master_wait_point = AFTER_SYNC（默认）
半同步的问题是因为等待ACK的点是Commit之后，此时Master已经完成数据变更，用户已经可以看到最新数据，当Binlog还未同步到Slave时，发生主从切换，那么此时从库是没有这个最新数据的，用户看到的是老数据。
增强半同步将等待ACK的点放在提交Commit之前，此时数据还未被提交，外界看不到数据变更，此时如果发送主从切换，新库依然还是老数据，不存在数据不一致的问题。

图示如下：

异步复制示例

MySQL主从复制之1主2从异步复制搭建及同步测试参考：https://www.xmmup.com/dbbao64mysqlzhucongzhi1zhu2congyibufuzhidajianjitongbuceshi.html

如何配置半同步复制

1.分别在主从安装插件
  主： INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
  从： INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
2.主服务器开启半同步
  set global  rpl_semi_sync_master_enabled=on;
3.从服务器开启半同步
  set global  rpl_semi_sync_slave_enabled=on;

4.配置主从同步（GTID模式示例）
server-id=1   （从为2，保证唯一性）
log-bin=master-bin  
log-bin-index=master-bin.index
binlog-format=ROW
gtid-mode=on
enforce-gtid-consistency=true

1.分别在主从安装插件

主： INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

从： INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

2.主服务器开启半同步

set global rpl_semi_sync_master_enabled=on;

3.从服务器开启半同步

set global rpl_semi_sync_slave_enabled=on;

4.配置主从同步（GTID模式示例）

server-id=1 （从为2，保证唯一性）

log-bin=master-bin

log-bin-index=master-bin.index

binlog-format=ROW

gtid-mode=on

enforce-gtid-consistency=true

延迟从库

延迟从库:从库落后于主库一段时间。
SQL线程延时:数据已经写入relaylog中了,只是让SQL线程"慢点"运行

MySQL延迟复制的好处主要有几点：
1.误删除时，能更快恢复数据。有时候手抖了，把线上数据给误删除了，或者误删除库、表、其他对象，或不加WHERE条件的更新、删除，都可以让延迟从库在误操作前的时间点停下，然后进行恢复。
2.把延迟从库作为专用的备份节点。虽然有一定的延迟，但并不影响利用该节点作为备份角色，也不影响生产节点数据库。
3.还可以把延迟从库当做一些问题、案例研究的对象。个别时候，可能有些Binlog Event在普通从库上会有问题（例如：早期版本中无主键会导致从库更新非常慢的经典问题），这时就有时间在延迟从库上慢慢琢磨研究了。

一般企业建议3-6小时，具体看公司运维人员对于故障的反应时间。

启用延迟从库的方法也挺简单的：

#直接用CHANGE MASTER TO设置，后面的N单位是秒数：
STOP SLAVE SQL_THREAD;
CHANGE MASTER TO MASTER_DELAY = N;
START SLAVE SQL_THREAD;
查询：
show slave status\G
SQL_Delay: 0

#直接用CHANGE MASTER TO设置，后面的N单位是秒数：

STOP SLAVE SQL_THREAD;

CHANGE MASTER TO MASTER_DELAY = N;

START SLAVE SQL_THREAD;

查询：

show slave status\G

SQL_Delay: 0

并行复制

MySQL 5.6提供了并行复制，但是这种并行只是基于database的（slave-parallel-type=DATABASE）。如果用户的MySQL数据库实例中存在多个database，对于从库复制的速度的确可以有比较大的帮助。如果是基于单个database的复制依然无法做到真正的并行回放，这个阶段很多DBA将数据库进行垂直拆分，将一个database拆分成几个database，通过设置slave_parallel_workers=n，可以进行database级别的并行复制，但对于热点业务复制延迟依然无法解决。参数slave_parallel_workers默认值为0，表示禁用并行。

到了MySQL 5.7，才实现了真正的并行复制（slave-parallel-type=LOGICAL_CLOCK），复制效率提升很多。MySQL 5.7的并行复制，multi-threaded slave即MTS，期望最大化还原主库的并行度，实现方式是在binlog event中增加必要的信息，以便slave节点根据这些信息实现并行复制。

要开启 MySQL 5.7 并行复制需要以下2步：
1、首先在主库设置 binlog_group_commit_sync_delay 的值大于0 。

set global binlog_group_commit_sync_delay=10;
set global binlog_group_commit_sync_no_delay_count=10;

1 2	set global binlog_group_commit_sync_delay=10; set global binlog_group_commit_sync_no_delay_count=10;

binlog_group_commit_sync_delay 全局动态变量，单位微妙，默认0，范围：0～1000000（1秒）。
表示 binlog 提交后等待延迟多少时间再同步到磁盘，默认0 ，不延迟。当设置为 0 以上的时候，就允许多个事务的日志同时一起提交，也就是我们说的组提交。组提交是并行复制的基础，我们设置这个值的大于 0 就代表打开了组提交的功能。
binlog_group_commit_sync_no_delay_count 全局动态变量，单位个数，默认0，范围：0～1000000。
表示等待延迟提交的最大事务数，如果上面参数的时间没到，但事务数到了，则直接同步到磁盘。若 binlog_group_commit_sync_delay 没有开启，则该参数也不会开启。

2、其次要在 Slave 主机上设置如下几个参数：

set global slave-parallel-type=LOGICAL_CLOCK;
set global slave-parallel-workers=16;

1 2	set global slave-parallel-type=LOGICAL_CLOCK; set global slave-parallel-workers=16;

一、1主2从之增强半同步复制（无损复制）示例

半同步复制比异步复制多了如下步骤：

-- 1.分别在主从安装插件
   主： INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
   从： INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
-- 2.主服务器开启半同步
 set global  rpl_semi_sync_master_enabled=on;
-- 3.从服务器开启半同步
 set global  rpl_semi_sync_slave_enabled=on;

-- 1.分别在主从安装插件

主： INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

从： INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

-- 2.主服务器开启半同步

set global rpl_semi_sync_master_enabled=on;

-- 3.从服务器开启半同步

set global rpl_semi_sync_slave_enabled=on;

1、MySQL环境初始化

-- 设置主从的网络环境
docker pull mysql:5.7.36
docker network create --subnet=172.72.0.0/24 mysqln0

-- 删除之前的容器
docker rm -f MSS57M33650 MSS57S33651 MSS57S33652

-- 创建参数文件路径
mkdir -p /lhrmysqltest3/master/conf.d
mkdir -p /lhrmysqltest3/slave1/conf.d
mkdir -p /lhrmysqltest3/slave2/conf.d

-- 申请主库环境
docker run -d --name MSS57M33650 \
   -h master -p 33650:3306 --net=mysqln0 --ip 172.72.0.50 \
   -v /lhrmysqltest3/master/conf.d:/etc/mysql/conf.d  \
   -e MYSQL_ROOT_PASSWORD=lhr \
   mysql:5.7.36

-- 申请从库1环境
docker run -d --name MSS57S33651 \
   -h slave1 -p 33651:3306 --net=mysqln0 --ip 172.72.0.51 \
   -v /lhrmysqltest3/slave1/conf.d:/etc/mysql/conf.d \
   -e MYSQL_ROOT_PASSWORD=lhr \
   mysql:5.7.36

-- 申请从库2环境
docker run -d --name MSS57S33652 \
   -h slave2 -p 33652:3306 --net=mysqln0 --ip 172.72.0.52 \
   -v /lhrmysqltest3/slave2/conf.d:/etc/mysql/conf.d \
   -e MYSQL_ROOT_PASSWORD=lhr \
   mysql:5.7.36

-- 配置主库参数
cat > /lhrmysqltest3/master/conf.d/my.cnf <<"EOF"
[mysqld]
port=3306
character_set_server=utf8mb4
secure_file_priv=
server-id = 5733650
log-bin = 
binlog_format=row
skip-name-resolve
gtid-mode=ON
enforce-gtid-consistency=on
report_host=172.72.0.50
default-time-zone = '+8:00'
log_timestamps = SYSTEM
log_slave_updates = 1
master_info_repository='table'
relay_log_info_repository='table'
relay_log_recovery=1

EOF

-- 配置从库1参数
cat > /lhrmysqltest3/slave1/conf.d/my.cnf <<"EOF"
[mysqld]
port=3306
character_set_server=utf8mb4
secure_file_priv=
server-id = 5733651
log-bin = 
binlog_format=row
gtid-mode=ON
enforce-gtid-consistency=ON
skip_name_resolve
report_host=172.72.0.51
default-time-zone = '+8:00'
log_timestamps = SYSTEM
log_slave_updates = 1
master_info_repository='table'
relay_log_info_repository='table'
relay_log_recovery=1

EOF

-- 配置从库2参数
cat > /lhrmysqltest3/slave2/conf.d/my.cnf <<"EOF"
[mysqld]
user=mysql
port=3306
character_set_server=utf8mb4
secure_file_priv=
server-id = 5733652
log-bin = 
binlog_format=row
gtid-mode=ON
enforce-gtid-consistency=ON
skip_name_resolve
report_host=172.72.0.52
default-time-zone = '+8:00'
log_timestamps = SYSTEM
log_slave_updates = 1
master_info_repository='table'
relay_log_info_repository='table'
relay_log_recovery=1

EOF

-- 重启3台环境
docker restart MSS57M33650 MSS57S33651 MSS57S33652
docker ps

[root@docker35 ~]# docker ps
CONTAINER ID   IMAGE          COMMAND                  CREATED          STATUS          PORTS                                                    NAMES
b6311aac4c08   mysql:5.7.36   "docker-entrypoint.s…"   6 seconds ago    Up 3 seconds    33060/tcp, 0.0.0.0:33652->3306/tcp, :::33652->3306/tcp   MSS57S33652
b1f5f35c4fea   mysql:5.7.36   "docker-entrypoint.s…"   11 seconds ago   Up 8 seconds    33060/tcp, 0.0.0.0:33651->3306/tcp, :::33651->3306/tcp   MSS57S33651
9a9826423f2d   mysql:5.7.36   "docker-entrypoint.s…"   17 seconds ago   Up 15 seconds   33060/tcp, 0.0.0.0:33650->3306/tcp, :::33650->3306/tcp   MSS57M33650

-- 登陆
docker exec -it MSS57M33650 bash
docker exec -it MSS57M33650 mysql -uroot -plhr

-- 查询
mysql -uroot -plhr -h192.168.88.35 -P33650 -e "select @@hostname,@@server_id,@@server_uuid"
mysql -uroot -plhr -h192.168.88.35 -P33651 -e "select @@hostname,@@server_id,@@server_uuid"
mysql -uroot -plhr -h192.168.88.35 -P33652 -e "select @@hostname,@@server_id,@@server_uuid"

C:\Users\lhrxxt>mysql -uroot -plhr -h192.168.88.35 -P33650 -e "select @@hostname,@@server_id,@@server_uuid"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+-------------+--------------------------------------+
| @@hostname | @@server_id | @@server_uuid                        |
+------------+-------------+--------------------------------------+
| master     |     5733650 | a5c6a094-dbf1-11ec-8f03-0242ac480032 |
+------------+-------------+--------------------------------------+

C:\Users\lhrxxt>mysql -uroot -plhr -h192.168.88.35 -P33651 -e "select @@hostname,@@server_id,@@server_uuid"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+-------------+--------------------------------------+
| @@hostname | @@server_id | @@server_uuid                        |
+------------+-------------+--------------------------------------+
| slave1     |     5733651 | a99e5d38-dbf1-11ec-bfe9-0242ac480033 |
+------------+-------------+--------------------------------------+

C:\Users\lhrxxt>mysql -uroot -plhr -h192.168.88.35 -P33652 -e "select @@hostname,@@server_id,@@server_uuid"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+-------------+--------------------------------------+
| @@hostname | @@server_id | @@server_uuid                        |
+------------+-------------+--------------------------------------+
| slave2     |     5733652 | ac918575-dbf1-11ec-b236-0242ac480034 |
+------------+-------------+--------------------------------------+

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

-- 设置主从的网络环境

docker pull mysql:5.7.36

docker network create --subnet=172.72.0.0/24 mysqln0

-- 删除之前的容器

docker rm -f MSS57M33650 MSS57S33651 MSS57S33652

-- 创建参数文件路径

mkdir -p /lhrmysqltest3/master/conf.d

mkdir -p /lhrmysqltest3/slave1/conf.d

mkdir -p /lhrmysqltest3/slave2/conf.d

-- 申请主库环境

docker run -d --name MSS57M33650 \

-h master -p 33650:3306 --net=mysqln0 --ip 172.72.0.50 \

-v /lhrmysqltest3/master/conf.d:/etc/mysql/conf.d \

-e MYSQL_ROOT_PASSWORD=lhr \

mysql:5.7.36

-- 申请从库1环境

docker run -d --name MSS57S33651 \

-h slave1 -p 33651:3306 --net=mysqln0 --ip 172.72.0.51 \

-v /lhrmysqltest3/slave1/conf.d:/etc/mysql/conf.d \

-e MYSQL_ROOT_PASSWORD=lhr \

mysql:5.7.36

-- 申请从库2环境

docker run -d --name MSS57S33652 \

-h slave2 -p 33652:3306 --net=mysqln0 --ip 172.72.0.52 \

-v /lhrmysqltest3/slave2/conf.d:/etc/mysql/conf.d \

-e MYSQL_ROOT_PASSWORD=lhr \

mysql:5.7.36

-- 配置主库参数

cat > /lhrmysqltest3/master/conf.d/my.cnf <<"EOF"

[mysqld]

port=3306

character_set_server=utf8mb4

secure_file_priv=

server-id = 5733650

log-bin =

binlog_format=row

skip-name-resolve

gtid-mode=ON

enforce-gtid-consistency=on

report_host=172.72.0.50

default-time-zone = '+8:00'

log_timestamps = SYSTEM

log_slave_updates = 1

master_info_repository='table'

relay_log_info_repository='table'

relay_log_recovery=1

EOF

-- 配置从库1参数

cat > /lhrmysqltest3/slave1/conf.d/my.cnf <<"EOF"

[mysqld]

port=3306

character_set_server=utf8mb4

secure_file_priv=

server-id = 5733651

log-bin =

binlog_format=row

gtid-mode=ON

enforce-gtid-consistency=ON

skip_name_resolve

report_host=172.72.0.51

default-time-zone = '+8:00'

log_timestamps = SYSTEM

log_slave_updates = 1

master_info_repository='table'

relay_log_info_repository='table'

relay_log_recovery=1

EOF

-- 配置从库2参数

cat > /lhrmysqltest3/slave2/conf.d/my.cnf <<"EOF"

[mysqld]

user=mysql

port=3306

character_set_server=utf8mb4

secure_file_priv=

server-id = 5733652

log-bin =

binlog_format=row

gtid-mode=ON

enforce-gtid-consistency=ON

skip_name_resolve

report_host=172.72.0.52

default-time-zone = '+8:00'

log_timestamps = SYSTEM

log_slave_updates = 1

master_info_repository='table'

relay_log_info_repository='table'

relay_log_recovery=1

EOF

-- 重启3台环境

docker restart MSS57M33650 MSS57S33651 MSS57S33652

docker ps

[root@docker35 ~]# docker ps

CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES

b6311aac4c08 mysql:5.7.36 "docker-entrypoint.s…" 6 seconds ago Up 3 seconds 33060/tcp, 0.0.0.0:33652->3306/tcp, :::33652->3306/tcp MSS57S33652

b1f5f35c4fea mysql:5.7.36 "docker-entrypoint.s…" 11 seconds ago Up 8 seconds 33060/tcp, 0.0.0.0:33651->3306/tcp, :::33651->3306/tcp MSS57S33651

9a9826423f2d mysql:5.7.36 "docker-entrypoint.s…" 17 seconds ago Up 15 seconds 33060/tcp, 0.0.0.0:33650->3306/tcp, :::33650->3306/tcp MSS57M33650

-- 登陆

docker exec -it MSS57M33650 bash

docker exec -it MSS57M33650 mysql -uroot -plhr

-- 查询

mysql -uroot -plhr -h192.168.88.35 -P33650 -e "select @@hostname,@@server_id,@@server_uuid"

mysql -uroot -plhr -h192.168.88.35 -P33651 -e "select @@hostname,@@server_id,@@server_uuid"

mysql -uroot -plhr -h192.168.88.35 -P33652 -e "select @@hostname,@@server_id,@@server_uuid"

C:\Users\lhrxxt>mysql -uroot -plhr -h192.168.88.35 -P33650 -e "select @@hostname,@@server_id,@@server_uuid"

mysql: [Warning] Using a password on the command line interface can be insecure.

+------------+-------------+--------------------------------------+

| @@hostname | @@server_id | @@server_uuid |

+------------+-------------+--------------------------------------+

| master | 5733650 | a5c6a094-dbf1-11ec-8f03-0242ac480032 |

+------------+-------------+--------------------------------------+

C:\Users\lhrxxt>mysql -uroot -plhr -h192.168.88.35 -P33651 -e "select @@hostname,@@server_id,@@server_uuid"

mysql: [Warning] Using a password on the command line interface can be insecure.

+------------+-------------+--------------------------------------+

| @@hostname | @@server_id | @@server_uuid |

+------------+-------------+--------------------------------------+

| slave1 | 5733651 | a99e5d38-dbf1-11ec-bfe9-0242ac480033 |

+------------+-------------+--------------------------------------+

C:\Users\lhrxxt>mysql -uroot -plhr -h192.168.88.35 -P33652 -e "select @@hostname,@@server_id,@@server_uuid"

mysql: [Warning] Using a password on the command line interface can be insecure.

+------------+-------------+--------------------------------------+

| @@hostname | @@server_id | @@server_uuid |

+------------+-------------+--------------------------------------+

| slave2 | 5733652 | ac918575-dbf1-11ec-b236-0242ac480034 |

+------------+-------------+--------------------------------------+

本人提供Oracle、MySQL、PG等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！

2、主库配置


-- 主库创建复制用户repl
mysql -uroot -plhr -h192.168.88.35 -P33650
grant replication slave on *.* to repl@'%' identified by 'lhr';
select user,host,grant_priv,password_last_changed,authentication_string from mysql.user; 

create database lhrdb;
use lhrdb;
create table mytb1(id int,name varchar(30));
insert into mytb1 values(1,'a'),(2,'b');

-- 安装插件
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS  WHERE PLUGIN_NAME LIKE '%semi%';

-- 配置参数，后期需要配置到参数文件中去
set global  rpl_semi_sync_master_enabled=on;

-- 查询
show master status ;
show slave hosts;
select @@hostname,@@server_id,@@server_uuid;

-- 主库创建复制用户repl

mysql -uroot -plhr -h192.168.88.35 -P33650

grant replication slave on *.* to repl@'%' identified by 'lhr';

select user,host,grant_priv,password_last_changed,authentication_string from mysql.user;

create database lhrdb;

use lhrdb;

create table mytb1(id int,name varchar(30));

insert into mytb1 values(1,'a'),(2,'b');

-- 安装插件

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%';

-- 配置参数，后期需要配置到参数文件中去

set global rpl_semi_sync_master_enabled=on;

-- 查询

show master status ;

show slave hosts;

select @@hostname,@@server_id,@@server_uuid;

执行过程：

MySQL [lhrdb]> show master status ;
+-------------------+----------+--------------+------------------+------------------------------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+-------------------+----------+--------------+------------------+------------------------------------------+
| master-bin.000004 |     1085 |              |                  | 1bc15639-bc5b-11eb-b1eb-0242ac480032:1-9 |
+-------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.06 sec)

MySQL [lhrdb]> show slave hosts;
Empty set (0.06 sec)

MySQL [lhrdb]> select @@hostname,@@server_id,@@server_uuid;
+------------+-------------+--------------------------------------+
| @@hostname | @@server_id | @@server_uuid                        |
+------------+-------------+--------------------------------------+
| master     |   5733650 | 1bc15639-bc5b-11eb-b1eb-0242ac480032 |
+------------+-------------+--------------------------------------+
1 row in set (0.05 sec)

MySQL [lhrdb]> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.08 sec)

MySQL [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.08 sec)

MySQL [lhrdb]> 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 [lhrdb]> set global  rpl_semi_sync_master_enabled=on;
Query OK, 0 rows affected (0.05 sec)

MySQL [lhrdb]> show variables like '%semi%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | ON         |
| rpl_semi_sync_master_timeout              | 10000      |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
| rpl_semi_sync_slave_enabled               | OFF        |
| rpl_semi_sync_slave_trace_level           | 32         |
+-------------------------------------------+------------+
8 rows in set (0.01 sec)

MySQL [lhrdb]> show master status ;

+-------------------+----------+--------------+------------------+------------------------------------------+

+-------------------+----------+--------------+------------------+------------------------------------------+

+-------------------+----------+--------------+------------------+------------------------------------------+

1 row in set (0.06 sec)

MySQL [lhrdb]> show slave hosts;

Empty set (0.06 sec)

MySQL [lhrdb]> select @@hostname,@@server_id,@@server_uuid;

+------------+-------------+--------------------------------------+

| @@hostname | @@server_id | @@server_uuid |

+------------+-------------+--------------------------------------+

| master | 5733650 | 1bc15639-bc5b-11eb-b1eb-0242ac480032 |

+------------+-------------+--------------------------------------+

1 row in set (0.05 sec)

MySQL [lhrdb]> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

Query OK, 0 rows affected (0.08 sec)

MySQL [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

Query OK, 0 rows affected (0.08 sec)

MySQL [lhrdb]> 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 [lhrdb]> set global rpl_semi_sync_master_enabled=on;

Query OK, 0 rows affected (0.05 sec)

MySQL [lhrdb]> show variables like '%semi%';

+-------------------------------------------+------------+

| Variable_name | Value |

+-------------------------------------------+------------+

| rpl_semi_sync_master_enabled | ON |

| rpl_semi_sync_master_timeout | 10000 |

| rpl_semi_sync_master_trace_level | 32 |

| rpl_semi_sync_master_wait_for_slave_count | 1 |

| rpl_semi_sync_master_wait_no_slave | ON |

| rpl_semi_sync_master_wait_point | AFTER_SYNC |

| rpl_semi_sync_slave_enabled | OFF |

| rpl_semi_sync_slave_trace_level | 32 |

+-------------------------------------------+------------+

8 rows in set (0.01 sec)

3、从库1配置

-- 从库1
mysql -uroot -plhr -h192.168.88.35 -P33651

-- 安装插件
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS  WHERE PLUGIN_NAME LIKE '%semi%';

-- 配置参数，后期需要配置到参数文件中去
set global  rpl_semi_sync_slave_enabled=on;

-- 修改参数
change master to
master_host='172.72.0.50',
master_port=3306,
master_user='repl',
master_password='lhr',
master_auto_position=1;

-- 启动复制进程
start slave; 
show slave status \G;

SELECT * FROM lhrdb.mytb1;

-- 主库：
insert into mytb1 values(3,'c'),(4,'d');

-- 从库1

mysql -uroot -plhr -h192.168.88.35 -P33651

-- 安装插件

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%';

-- 配置参数，后期需要配置到参数文件中去

set global rpl_semi_sync_slave_enabled=on;

-- 修改参数

change master to

master_host='172.72.0.50',

master_port=3306,

master_user='repl',

master_password='lhr',

master_auto_position=1;

-- 启动复制进程

start slave;

show slave status \G;

SELECT * FROM lhrdb.mytb1;

-- 主库：

insert into mytb1 values(3,'c'),(4,'d');

4、从库2配置

-- S2
mysql -uroot -plhr -h192.168.88.35 -P33652

-- 安装插件
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS  WHERE PLUGIN_NAME LIKE '%semi%';

-- 配置参数，后期需要配置到参数文件中去
set global  rpl_semi_sync_slave_enabled=on;

-- 修改参数
change master to master_host='172.72.0.50',
master_port=3306,
master_user='repl',
master_password='lhr',
master_auto_position=1;

start slave; 
show slave status \G;

SELECT * FROM lhrdb.mytb1;

-- S2

mysql -uroot -plhr -h192.168.88.35 -P33652

-- 安装插件

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%';

-- 配置参数，后期需要配置到参数文件中去

set global rpl_semi_sync_slave_enabled=on;

-- 修改参数

change master to master_host='172.72.0.50',

master_port=3306,

master_user='repl',

master_password='lhr',

master_auto_position=1;

start slave;

show slave status \G;

SELECT * FROM lhrdb.mytb1;

5、主从查询

-- 主库
show slave hosts;
show master status;
show global status like 'rpl_semi%';
show variables like '%Rpl%';

-- 从库
show slave status;
show variables like '%Rpl%';

-- 线程查询
SELECT *
FROM performance_schema.threads a
WHERE a.`NAME` IN ( 'thread/sql/slave_IO', 'thread/sql/slave_sql' ) or a.PROCESSLIST_COMMAND in ('Binlog Dump','Binlog Dump GTID') ;

SELECT * FROM  information_schema.`PROCESSLIST` a where a.USER='system user' or a.command  in ('Binlog Dump','Binlog Dump GTID') ;

-- 主库

show slave hosts;

show master status;

show global status like 'rpl_semi%';

show variables like '%Rpl%';

-- 从库

show slave status;

show variables like '%Rpl%';

-- 线程查询

SELECT *

FROM performance_schema.threads a

WHERE a.`NAME` IN ( 'thread/sql/slave_IO', 'thread/sql/slave_sql' ) or a.PROCESSLIST_COMMAND in ('Binlog Dump','Binlog Dump GTID') ;

SELECT * FROM information_schema.`PROCESSLIST` a where a.USER='system user' or a.command in ('Binlog Dump','Binlog Dump GTID') ;

半同步复制查询：

-- 主库
MySQL [lhrdb]> show global 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             | 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      | 747   |
| Rpl_semi_sync_master_tx_wait_time          | 747   |
| 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)

MySQL [lhrdb]> show variables like '%Rpl%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | ON         |
| rpl_semi_sync_master_timeout              | 10000      |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
| rpl_semi_sync_slave_enabled               | OFF        |
| rpl_semi_sync_slave_trace_level           | 32         |
| rpl_stop_slave_timeout                    | 31536000   |
+-------------------------------------------+------------+
9 rows in set (0.09 sec)

-- 从库
MySQL [(none)]> show global status like 'Rpl_semi_sync_slave_status%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+
1 row in set (0.05 sec)

-- 主库

MySQL [lhrdb]> show global 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 | 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 | 747 |

| Rpl_semi_sync_master_tx_wait_time | 747 |

| 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)

MySQL [lhrdb]> show variables like '%Rpl%';

+-------------------------------------------+------------+

| Variable_name | Value |

+-------------------------------------------+------------+

| rpl_semi_sync_master_enabled | ON |

| rpl_semi_sync_master_timeout | 10000 |

| rpl_semi_sync_master_trace_level | 32 |

| rpl_semi_sync_master_wait_for_slave_count | 1 |

| rpl_semi_sync_master_wait_no_slave | ON |

| rpl_semi_sync_master_wait_point | AFTER_SYNC |

| rpl_semi_sync_slave_enabled | OFF |

| rpl_semi_sync_slave_trace_level | 32 |

| rpl_stop_slave_timeout | 31536000 |

+-------------------------------------------+------------+

9 rows in set (0.09 sec)

-- 从库

MySQL [(none)]> show global status like 'Rpl_semi_sync_slave_status%';

+----------------------------+-------+

| Variable_name | Value |

+----------------------------+-------+

| Rpl_semi_sync_slave_status | ON |

+----------------------------+-------+

1 row in set (0.05 sec)

Rpl_semi_sync_master_clients表示记录支持半同步的slave的个数

Rpl_semi_sync_master_status标记master现在是否是半同步复制状态

6、测试半同步复制

测试过程：

1、关闭从库io线程，模拟网络卡顿
2、主库插入数据，等待10秒，同步方式自动转换为异步模式
3、从库启动io线程
4、同步方式变为半同步模式

先关闭一个从库，我们测试1主1从的情况。

二、配置延迟复制示例

我们把从库2配置为延迟从库，如下所示：

STOP SLAVE SQL_THREAD;
CHANGE MASTER TO MASTER_DELAY = 120;
START SLAVE SQL_THREAD;

show slave status \G
返回：SQL_Delay: 120

-- 主库插入数据
insert into mytb1 values(5,'e'),(6,'f');
select * from mytb1;

-- 从库2查询
show slave status \G

Seconds_Behind_Master: 110
SQL_Remaining_Delay: 46
Slave_SQL_Running_State: Waiting until MASTER_DELAY seconds after master executed event

STOP SLAVE SQL_THREAD;

CHANGE MASTER TO MASTER_DELAY = 120;

START SLAVE SQL_THREAD;

show slave status \G

返回：SQL_Delay: 120

-- 主库插入数据

insert into mytb1 values(5,'e'),(6,'f');

select * from mytb1;

-- 从库2查询

show slave status \G

Seconds_Behind_Master: 110

SQL_Remaining_Delay: 46

Slave_SQL_Running_State: Waiting until MASTER_DELAY seconds after master executed event

三、配置并行复制示例

3.1、主库配置

set global binlog_group_commit_sync_delay=10;
set global binlog_group_commit_sync_no_delay_count=10;
show variables like 'binlog_group_commit_sync_%';

set global binlog_group_commit_sync_delay=10;

set global binlog_group_commit_sync_no_delay_count=10;

show variables like 'binlog_group_commit_sync_%';

3.2、从库2配置并行复制

只在从库2做配置，从库1不做配置。

STOP SLAVE SQL_THREAD;
set global slave_parallel_type=LOGICAL_CLOCK;
set global slave_parallel_workers=4;

show variables like 'slave_parallel%';

start SLAVE SQL_THREAD;

-- 线程查询
SELECT *
FROM performance_schema.threads a
WHERE a.`NAME` IN ( 'thread/sql/slave_IO', 'thread/sql/slave_sql','thread/sql/slave_worker' ) or a.PROCESSLIST_COMMAND in ('Binlog Dump','Binlog Dump GTID') ;

SELECT * FROM  information_schema.`PROCESSLIST` a where a.USER='system user' or a.command  in ('Binlog Dump','Binlog Dump GTID') ;

MySQL [(none)]> SELECT *
    -> FROM performance_schema.threads a
    -> WHERE a.`NAME` IN ( 'thread/sql/slave_IO', 'thread/sql/slave_sql','thread/sql/slave_worker' ) or a.PROCESSLIST_COMMAND in ('Binlog Dump','Binlog Dump GTID') ;
+-----------+-------------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+--------------------------------------------------------+------------------+------------------+------+--------------+---------+-----------------+--------------+
| THREAD_ID | NAME                    | TYPE       | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB | PROCESSLIST_COMMAND | PROCESSLIST_TIME | PROCESSLIST_STATE                                      | PROCESSLIST_INFO | PARENT_THREAD_ID | ROLE | INSTRUMENTED | HISTORY | CONNECTION_TYPE | THREAD_OS_ID |
+-----------+-------------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+--------------------------------------------------------+------------------+------------------+------+--------------+---------+-----------------+--------------+
|        33 | thread/sql/slave_io     | FOREGROUND |              8 | root             | 172.72.0.1       | NULL           | Connect             |             1203 | Waiting for master to send event                       | NULL             |               30 | NULL | YES          | YES     | NULL            |           79 |
|        36 | thread/sql/slave_sql    | FOREGROUND |             11 | root             | 172.72.0.1       | NULL           | Connect             |              312 | Slave has read all relay log; waiting for more updates | NULL             |               30 | NULL | YES          | YES     | NULL            |           82 |
|        37 | thread/sql/slave_worker | FOREGROUND |             12 | root             | 172.72.0.1       | NULL           | Connect             |              312 | Waiting for an event from Coordinator                  | NULL             |               36 | NULL | YES          | YES     | NULL            |           83 |
|        38 | thread/sql/slave_worker | FOREGROUND |             13 | root             | 172.72.0.1       | NULL           | Connect             |              312 | Waiting for an event from Coordinator                  | NULL             |               36 | NULL | YES          | YES     | NULL            |           84 |
|        39 | thread/sql/slave_worker | FOREGROUND |             14 | root             | 172.72.0.1       | NULL           | Connect             |              312 | Waiting for an event from Coordinator                  | NULL             |               36 | NULL | YES          | YES     | NULL            |           85 |
|        40 | thread/sql/slave_worker | FOREGROUND |             15 | root             | 172.72.0.1       | NULL           | Connect             |              312 | Waiting for an event from Coordinator                  | NULL             |               36 | NULL | YES          | YES     | NULL            |           86 |
+-----------+-------------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+--------------------------------------------------------+------------------+------------------+------+--------------+---------+-----------------+--------------+
6 rows in set (0.05 sec)

MySQL [(none)]> SELECT * FROM  information_schema.`PROCESSLIST` a where a.USER='system user' or a.command  in ('Binlog Dump','Binlog Dump GTID') ;
+----+-------------+------+------+---------+------+--------------------------------------------------------+------+
| ID | USER        | HOST | DB   | COMMAND | TIME | STATE                                                  | INFO |
+----+-------------+------+------+---------+------+--------------------------------------------------------+------+
| 14 | system user |      | NULL | Connect |   37 | Waiting for an event from Coordinator                  | NULL |
| 15 | system user |      | NULL | Connect |   37 | Waiting for an event from Coordinator                  | NULL |
| 12 | system user |      | NULL | Connect |   37 | Waiting for an event from Coordinator                  | NULL |
| 13 | system user |      | NULL | Connect |   37 | Waiting for an event from Coordinator                  | NULL |
| 11 | system user |      | NULL | Connect |   37 | Slave has read all relay log; waiting for more updates | NULL |
|  8 | system user |      | NULL | Connect |  928 | Waiting for master to send event                       | NULL |
+----+-------------+------+------+---------+------+--------------------------------------------------------+------+
6 rows in set (0.05 sec)

STOP SLAVE SQL_THREAD;

set global slave_parallel_type=LOGICAL_CLOCK;

set global slave_parallel_workers=4;

show variables like 'slave_parallel%';

start SLAVE SQL_THREAD;

-- 线程查询

SELECT *

FROM performance_schema.threads a

WHERE a.`NAME` IN ( 'thread/sql/slave_IO', 'thread/sql/slave_sql','thread/sql/slave_worker' ) or a.PROCESSLIST_COMMAND in ('Binlog Dump','Binlog Dump GTID') ;

SELECT * FROM information_schema.`PROCESSLIST` a where a.USER='system user' or a.command in ('Binlog Dump','Binlog Dump GTID') ;

MySQL [(none)]> SELECT *

-> FROM performance_schema.threads a

-> WHERE a.`NAME` IN ( 'thread/sql/slave_IO', 'thread/sql/slave_sql','thread/sql/slave_worker' ) or a.PROCESSLIST_COMMAND in ('Binlog Dump','Binlog Dump GTID') ;

+-----------+-------------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+--------------------------------------------------------+------------------+------------------+------+--------------+---------+-----------------+--------------+

| 33 | thread/sql/slave_io | FOREGROUND | 8 | root | 172.72.0.1 | NULL | Connect | 1203 | Waiting for master to send event | NULL | 30 | NULL | YES | YES | NULL | 79 |

| 36 | thread/sql/slave_sql | FOREGROUND | 11 | root | 172.72.0.1 | NULL | Connect | 312 | Slave has read all relay log; waiting for more updates | NULL | 30 | NULL | YES | YES | NULL | 82 |

| 37 | thread/sql/slave_worker | FOREGROUND | 12 | root | 172.72.0.1 | NULL | Connect | 312 | Waiting for an event from Coordinator | NULL | 36 | NULL | YES | YES | NULL | 83 |

| 38 | thread/sql/slave_worker | FOREGROUND | 13 | root | 172.72.0.1 | NULL | Connect | 312 | Waiting for an event from Coordinator | NULL | 36 | NULL | YES | YES | NULL | 84 |

| 39 | thread/sql/slave_worker | FOREGROUND | 14 | root | 172.72.0.1 | NULL | Connect | 312 | Waiting for an event from Coordinator | NULL | 36 | NULL | YES | YES | NULL | 85 |

| 40 | thread/sql/slave_worker | FOREGROUND | 15 | root | 172.72.0.1 | NULL | Connect | 312 | Waiting for an event from Coordinator | NULL | 36 | NULL | YES | YES | NULL | 86 |

6 rows in set (0.05 sec)

MySQL [(none)]> SELECT * FROM information_schema.`PROCESSLIST` a where a.USER='system user' or a.command in ('Binlog Dump','Binlog Dump GTID') ;

+----+-------------+------+------+---------+------+--------------------------------------------------------+------+

+----+-------------+------+------+---------+------+--------------------------------------------------------+------+

+----+-------------+------+------+---------+------+--------------------------------------------------------+------+

6 rows in set (0.05 sec)

3.3、主库压测测试并行复制

因为这里的架构为1主2从，我只配置了从库2为并行复制，从库1不是并行复制，那么接下来测试并行复制的效果。

首先，取消从库2的延迟复制功能：

STOP SLAVE SQL_THREAD;
CHANGE MASTER TO MASTER_DELAY = 0;
START SLAVE SQL_THREAD;

show slave status \G
返回：SQL_Delay: 0

STOP SLAVE SQL_THREAD;

CHANGE MASTER TO MASTER_DELAY = 0;

START SLAVE SQL_THREAD;

show slave status \G

返回：SQL_Delay: 0

首先，主库进行压测生成数据：

-- 主库修改
set GLOBAL max_connections=10000; 
set global max_prepared_stmt_count=65536;

-- 做压测
sysbench /usr/share/sysbench/oltp_common.lua --time=300 --mysql-host=172.72.0.50 --mysql-port=3306 \
--mysql-user=root --mysql-password=lhr --mysql-db=lhrdb --table-size=100000 --tables=10 \
--threads=16 --events=999999999   prepare

sysbench /usr/share/sysbench/oltp_read_write.lua --time=300 --mysql-host=172.72.0.50 --mysql-port=3306 \
--mysql-user=root --mysql-password=lhr --mysql-db=lhrdb --table-size=1000000 --tables=10 --threads=16 \
--events=999999999  --report-interval=10 --db-ps-mode=disable --forced-shutdown=1 run

-- 查询从库
mysql -uroot -plhr -h192.168.88.35 -P33651 -e "show slave status \G" | grep Seconds_Behind_Master
mysql -uroot -plhr -h192.168.88.35 -P33652 -e "show slave status \G" | grep Seconds_Behind_Master

-- 主库修改

set GLOBAL max_connections=10000;

set global max_prepared_stmt_count=65536;

-- 做压测

sysbench /usr/share/sysbench/oltp_common.lua --time=300 --mysql-host=172.72.0.50 --mysql-port=3306 \

--mysql-user=root --mysql-password=lhr --mysql-db=lhrdb --table-size=100000 --tables=10 \

--threads=16 --events=999999999 prepare

sysbench /usr/share/sysbench/oltp_read_write.lua --time=300 --mysql-host=172.72.0.50 --mysql-port=3306 \

--mysql-user=root --mysql-password=lhr --mysql-db=lhrdb --table-size=1000000 --tables=10 --threads=16 \

--events=999999999 --report-interval=10 --db-ps-mode=disable --forced-shutdown=1 run

-- 查询从库

mysql -uroot -plhr -h192.168.88.35 -P33651 -e "show slave status \G" | grep Seconds_Behind_Master

mysql -uroot -plhr -h192.168.88.35 -P33652 -e "show slave status \G" | grep Seconds_Behind_Master

结果：

[root@docker35 ~]# mysql -uroot -plhr -h192.168.88.35 -P33651 -e "show slave status \G" | grep Seconds_Behind_Master
mysql: [Warning] Using a password on the command line interface can be insecure.
        Seconds_Behind_Master: 126
[root@docker35 ~]# mysql -uroot -plhr -h192.168.88.35 -P33652 -e "show slave status \G" | grep Seconds_Behind_Master
mysql: [Warning] Using a password on the command line interface can be insecure.
        Seconds_Behind_Master: 62
[root@docker35 ~]# 
[root@docker35 ~]# mysql -uroot -plhr -h192.168.88.35 -P33651 -e "show slave status \G" | grep Seconds_Behind_Master
mysql: [Warning] Using a password on the command line interface can be insecure.
        Seconds_Behind_Master: 139
[root@docker35 ~]# mysql -uroot -plhr -h192.168.88.35 -P33652 -e "show slave status \G" | grep Seconds_Behind_Master
mysql: [Warning] Using a password on the command line interface can be insecure.
        Seconds_Behind_Master: 65
[root@docker35 ~]# 
[root@docker35 ~]# mysql -uroot -plhr -h192.168.88.35 -P33651 -e "show slave status \G" | grep Seconds_Behind_Master
mysql: [Warning] Using a password on the command line interface can be insecure.
        Seconds_Behind_Master: 214
[root@docker35 ~]# mysql -uroot -plhr -h192.168.88.35 -P33652 -e "show slave status \G" | grep Seconds_Behind_Master
mysql: [Warning] Using a password on the command line interface can be insecure.
        Seconds_Behind_Master: 78

[root@docker35 ~]# mysql -uroot -plhr -h192.168.88.35 -P33651 -e "show slave status \G" | grep Seconds_Behind_Master

mysql: [Warning] Using a password on the command line interface can be insecure.

Seconds_Behind_Master: 126

[root@docker35 ~]# mysql -uroot -plhr -h192.168.88.35 -P33652 -e "show slave status \G" | grep Seconds_Behind_Master

mysql: [Warning] Using a password on the command line interface can be insecure.

Seconds_Behind_Master: 62

[root@docker35 ~]#

[root@docker35 ~]# mysql -uroot -plhr -h192.168.88.35 -P33651 -e "show slave status \G" | grep Seconds_Behind_Master

mysql: [Warning] Using a password on the command line interface can be insecure.

Seconds_Behind_Master: 139

[root@docker35 ~]# mysql -uroot -plhr -h192.168.88.35 -P33652 -e "show slave status \G" | grep Seconds_Behind_Master

mysql: [Warning] Using a password on the command line interface can be insecure.

Seconds_Behind_Master: 65

[root@docker35 ~]#

[root@docker35 ~]# mysql -uroot -plhr -h192.168.88.35 -P33651 -e "show slave status \G" | grep Seconds_Behind_Master

mysql: [Warning] Using a password on the command line interface can be insecure.

Seconds_Behind_Master: 214

[root@docker35 ~]# mysql -uroot -plhr -h192.168.88.35 -P33652 -e "show slave status \G" | grep Seconds_Behind_Master

mysql: [Warning] Using a password on the command line interface can be insecure.

Seconds_Behind_Master: 78

可以看到，从库2比从库1能至少快一倍。

若是将从库的slave_parallel_workers配置为16，则配置了并行复制的库基本无延迟，而没有配置并行复制的库，延迟会越来越严重：

STOP SLAVE SQL_THREAD;
set global slave_parallel_workers=16;

show variables like 'slave_parallel%';

start SLAVE SQL_THREAD;

-- 线程查询
SELECT *
FROM performance_schema.threads a
WHERE a.`NAME` IN ( 'thread/sql/slave_IO', 'thread/sql/slave_sql','thread/sql/slave_worker' ) or a.PROCESSLIST_COMMAND in ('Binlog Dump','Binlog Dump GTID') ;

SELECT * FROM  information_schema.`PROCESSLIST` a where a.USER='system user' or a.command  in ('Binlog Dump','Binlog Dump GTID') ;

MySQL [lhrdb]> -- 线程查询
MySQL [lhrdb]> SELECT *
    -> FROM performance_schema.threads a
    -> WHERE a.`NAME` IN ( 'thread/sql/slave_IO', 'thread/sql/slave_sql','thread/sql/slave_worker' ) or a.PROCESSLIST_COMMAND in ('Binlog Dump','Binlog Dump GTID') ;
+-----------+-------------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+--------------------------------------------------------+------------------+------------------+------+--------------+---------+-----------------+--------------+
| THREAD_ID | NAME                    | TYPE       | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB | PROCESSLIST_COMMAND | PROCESSLIST_TIME | PROCESSLIST_STATE                                      | PROCESSLIST_INFO | PARENT_THREAD_ID | ROLE | INSTRUMENTED | HISTORY | CONNECTION_TYPE | THREAD_OS_ID |
+-----------+-------------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+--------------------------------------------------------+------------------+------------------+------+--------------+---------+-----------------+--------------+
|        29 | thread/sql/slave_io     | FOREGROUND |              4 | root             | 192.168.88.64    | NULL           | Connect             |             1780 | Waiting for master to send event                       | NULL             |               28 | NULL | YES          | YES     | NULL            |          154 |
|       106 | thread/sql/slave_sql    | FOREGROUND |             81 | root             | 192.168.88.64    | NULL           | Connect             |               24 | Slave has read all relay log; waiting for more updates | NULL             |               28 | NULL | YES          | YES     | NULL            |          168 |
|       107 | thread/sql/slave_worker | FOREGROUND |             82 | root             | 192.168.88.64    | NULL           | Connect             |              322 | Waiting for an event from Coordinator                  | BEGIN            |              106 | NULL | YES          | YES     | NULL            |          169 |
|       108 | thread/sql/slave_worker | FOREGROUND |             83 | root             | 192.168.88.64    | NULL           | Connect             |              322 | Waiting for an event from Coordinator                  | BEGIN            |              106 | NULL | YES          | YES     | NULL            |          170 |
|       109 | thread/sql/slave_worker | FOREGROUND |             84 | root             | 192.168.88.64    | NULL           | Connect             |              322 | Waiting for an event from Coordinator                  | BEGIN            |              106 | NULL | YES          | YES     | NULL            |          171 |
|       110 | thread/sql/slave_worker | FOREGROUND |             85 | root             | 192.168.88.64    | NULL           | Connect             |              322 | Waiting for an event from Coordinator                  | BEGIN            |              106 | NULL | YES          | YES     | NULL            |          172 |
|       111 | thread/sql/slave_worker | FOREGROUND |             86 | root             | 192.168.88.64    | NULL           | Connect             |              322 | Waiting for an event from Coordinator                  | BEGIN            |              106 | NULL | YES          | YES     | NULL            |          173 |
|       112 | thread/sql/slave_worker | FOREGROUND |             87 | root             | 192.168.88.64    | NULL           | Connect             |              322 | Waiting for an event from Coordinator                  | BEGIN            |              106 | NULL | YES          | YES     | NULL            |          174 |
|       113 | thread/sql/slave_worker | FOREGROUND |             88 | root             | 192.168.88.64    | NULL           | Connect             |              322 | Waiting for an event from Coordinator                  | BEGIN            |              106 | NULL | YES          | YES     | NULL            |          175 |
|       114 | thread/sql/slave_worker | FOREGROUND |             89 | root             | 192.168.88.64    | NULL           | Connect             |              322 | Waiting for an event from Coordinator                  | BEGIN            |              106 | NULL | YES          | YES     | NULL            |          176 |
|       115 | thread/sql/slave_worker | FOREGROUND |             90 | root             | 192.168.88.64    | NULL           | Connect             |              322 | Waiting for an event from Coordinator                  | BEGIN            |              106 | NULL | YES          | YES     | NULL            |          177 |
|       116 | thread/sql/slave_worker | FOREGROUND |             91 | root             | 192.168.88.64    | NULL           | Connect             |              322 | Waiting for an event from Coordinator                  | BEGIN            |              106 | NULL | YES          | YES     | NULL            |          178 |
|       117 | thread/sql/slave_worker | FOREGROUND |             92 | root             | 192.168.88.64    | NULL           | Connect             |              322 | Waiting for an event from Coordinator                  | BEGIN            |              106 | NULL | YES          | YES     | NULL            |          179 |
|       118 | thread/sql/slave_worker | FOREGROUND |             93 | root             | 192.168.88.64    | NULL           | Connect             |              322 | Waiting for an event from Coordinator                  | BEGIN            |              106 | NULL | YES          | YES     | NULL            |          180 |
|       119 | thread/sql/slave_worker | FOREGROUND |             94 | root             | 192.168.88.64    | NULL           | Connect             |              322 | Waiting for an event from Coordinator                  | BEGIN            |              106 | NULL | YES          | YES     | NULL            |          181 |
|       120 | thread/sql/slave_worker | FOREGROUND |             95 | root             | 192.168.88.64    | NULL           | Connect             |              322 | Waiting for an event from Coordinator                  | BEGIN            |              106 | NULL | YES          | YES     | NULL            |          182 |
|       121 | thread/sql/slave_worker | FOREGROUND |             96 | root             | 192.168.88.64    | NULL           | Connect             |              322 | Waiting for an event from Coordinator                  | BEGIN            |              106 | NULL | YES          | YES     | NULL            |          183 |
|       122 | thread/sql/slave_worker | FOREGROUND |             97 | root             | 192.168.88.64    | NULL           | Connect             |              322 | Waiting for an event from Coordinator                  | BEGIN            |              106 | NULL | YES          | YES     | NULL            |          184 |
+-----------+-------------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+--------------------------------------------------------+------------------+------------------+------+--------------+---------+-----------------+--------------+
18 rows in set (0.01 sec)

MySQL [lhrdb]>
MySQL [lhrdb]> SELECT * FROM  information_schema.`PROCESSLIST` a where a.USER='system user' or a.command  in ('Binlog Dump','Binlog Dump GTID') ;
+----+-------------+------+------+---------+------+--------------------------------------------------------+------+
| ID | USER        | HOST | DB   | COMMAND | TIME | STATE                                                  | INFO |
+----+-------------+------+------+---------+------+--------------------------------------------------------+------+
| 96 | system user |      | NULL | Connect |  322 | Waiting for an event from Coordinator                  | NULL |
| 97 | system user |      | NULL | Connect |  322 | Waiting for an event from Coordinator                  | NULL |
| 94 | system user |      | NULL | Connect |  322 | Waiting for an event from Coordinator                  | NULL |
| 95 | system user |      | NULL | Connect |  322 | Waiting for an event from Coordinator                  | NULL |
| 92 | system user |      | NULL | Connect |  322 | Waiting for an event from Coordinator                  | NULL |
| 93 | system user |      | NULL | Connect |  322 | Waiting for an event from Coordinator                  | NULL |
| 90 | system user |      | NULL | Connect |  322 | Waiting for an event from Coordinator                  | NULL |
| 91 | system user |      | NULL | Connect |  322 | Waiting for an event from Coordinator                  | NULL |
| 88 | system user |      | NULL | Connect |  322 | Waiting for an event from Coordinator                  | NULL |
| 89 | system user |      | NULL | Connect |  322 | Waiting for an event from Coordinator                  | NULL |
| 86 | system user |      | NULL | Connect |  322 | Waiting for an event from Coordinator                  | NULL |
| 87 | system user |      | NULL | Connect |  322 | Waiting for an event from Coordinator                  | NULL |
| 84 | system user |      | NULL | Connect |  322 | Waiting for an event from Coordinator                  | NULL |
| 82 | system user |      | NULL | Connect |  322 | Waiting for an event from Coordinator                  | NULL |
| 85 | system user |      | NULL | Connect |  322 | Waiting for an event from Coordinator                  | NULL |
| 83 | system user |      | NULL | Connect |  322 | Waiting for an event from Coordinator                  | NULL |
| 81 | system user |      | NULL | Connect |   24 | Slave has read all relay log; waiting for more updates | NULL |
|  4 | system user |      | NULL | Connect | 1780 | Waiting for master to send event                       | NULL |
+----+-------------+------+------+---------+------+--------------------------------------------------------+------+
18 rows in set (0.01 sec)

STOP SLAVE SQL_THREAD;

set global slave_parallel_workers=16;

show variables like 'slave_parallel%';

start SLAVE SQL_THREAD;

-- 线程查询

SELECT *

FROM performance_schema.threads a

WHERE a.`NAME` IN ( 'thread/sql/slave_IO', 'thread/sql/slave_sql','thread/sql/slave_worker' ) or a.PROCESSLIST_COMMAND in ('Binlog Dump','Binlog Dump GTID') ;

SELECT * FROM information_schema.`PROCESSLIST` a where a.USER='system user' or a.command in ('Binlog Dump','Binlog Dump GTID') ;

MySQL [lhrdb]> -- 线程查询

MySQL [lhrdb]> SELECT *

-> FROM performance_schema.threads a

-> WHERE a.`NAME` IN ( 'thread/sql/slave_IO', 'thread/sql/slave_sql','thread/sql/slave_worker' ) or a.PROCESSLIST_COMMAND in ('Binlog Dump','Binlog Dump GTID') ;

| 29 | thread/sql/slave_io | FOREGROUND | 4 | root | 192.168.88.64 | NULL | Connect | 1780 | Waiting for master to send event | NULL | 28 | NULL | YES | YES | NULL | 154 |

| 106 | thread/sql/slave_sql | FOREGROUND | 81 | root | 192.168.88.64 | NULL | Connect | 24 | Slave has read all relay log; waiting for more updates | NULL | 28 | NULL | YES | YES | NULL | 168 |

| 107 | thread/sql/slave_worker | FOREGROUND | 82 | root | 192.168.88.64 | NULL | Connect | 322 | Waiting for an event from Coordinator | BEGIN | 106 | NULL | YES | YES | NULL | 169 |

| 108 | thread/sql/slave_worker | FOREGROUND | 83 | root | 192.168.88.64 | NULL | Connect | 322 | Waiting for an event from Coordinator | BEGIN | 106 | NULL | YES | YES | NULL | 170 |

| 109 | thread/sql/slave_worker | FOREGROUND | 84 | root | 192.168.88.64 | NULL | Connect | 322 | Waiting for an event from Coordinator | BEGIN | 106 | NULL | YES | YES | NULL | 171 |

| 110 | thread/sql/slave_worker | FOREGROUND | 85 | root | 192.168.88.64 | NULL | Connect | 322 | Waiting for an event from Coordinator | BEGIN | 106 | NULL | YES | YES | NULL | 172 |

| 111 | thread/sql/slave_worker | FOREGROUND | 86 | root | 192.168.88.64 | NULL | Connect | 322 | Waiting for an event from Coordinator | BEGIN | 106 | NULL | YES | YES | NULL | 173 |

| 112 | thread/sql/slave_worker | FOREGROUND | 87 | root | 192.168.88.64 | NULL | Connect | 322 | Waiting for an event from Coordinator | BEGIN | 106 | NULL | YES | YES | NULL | 174 |

| 113 | thread/sql/slave_worker | FOREGROUND | 88 | root | 192.168.88.64 | NULL | Connect | 322 | Waiting for an event from Coordinator | BEGIN | 106 | NULL | YES | YES | NULL | 175 |

| 114 | thread/sql/slave_worker | FOREGROUND | 89 | root | 192.168.88.64 | NULL | Connect | 322 | Waiting for an event from Coordinator | BEGIN | 106 | NULL | YES | YES | NULL | 176 |

| 115 | thread/sql/slave_worker | FOREGROUND | 90 | root | 192.168.88.64 | NULL | Connect | 322 | Waiting for an event from Coordinator | BEGIN | 106 | NULL | YES | YES | NULL | 177 |

| 116 | thread/sql/slave_worker | FOREGROUND | 91 | root | 192.168.88.64 | NULL | Connect | 322 | Waiting for an event from Coordinator | BEGIN | 106 | NULL | YES | YES | NULL | 178 |

| 117 | thread/sql/slave_worker | FOREGROUND | 92 | root | 192.168.88.64 | NULL | Connect | 322 | Waiting for an event from Coordinator | BEGIN | 106 | NULL | YES | YES | NULL | 179 |

| 118 | thread/sql/slave_worker | FOREGROUND | 93 | root | 192.168.88.64 | NULL | Connect | 322 | Waiting for an event from Coordinator | BEGIN | 106 | NULL | YES | YES | NULL | 180 |

| 119 | thread/sql/slave_worker | FOREGROUND | 94 | root | 192.168.88.64 | NULL | Connect | 322 | Waiting for an event from Coordinator | BEGIN | 106 | NULL | YES | YES | NULL | 181 |

| 120 | thread/sql/slave_worker | FOREGROUND | 95 | root | 192.168.88.64 | NULL | Connect | 322 | Waiting for an event from Coordinator | BEGIN | 106 | NULL | YES | YES | NULL | 182 |

| 121 | thread/sql/slave_worker | FOREGROUND | 96 | root | 192.168.88.64 | NULL | Connect | 322 | Waiting for an event from Coordinator | BEGIN | 106 | NULL | YES | YES | NULL | 183 |

| 122 | thread/sql/slave_worker | FOREGROUND | 97 | root | 192.168.88.64 | NULL | Connect | 322 | Waiting for an event from Coordinator | BEGIN | 106 | NULL | YES | YES | NULL | 184 |

18 rows in set (0.01 sec)

MySQL [lhrdb]>

MySQL [lhrdb]> SELECT * FROM information_schema.`PROCESSLIST` a where a.USER='system user' or a.command in ('Binlog Dump','Binlog Dump GTID') ;

+----+-------------+------+------+---------+------+--------------------------------------------------------+------+

+----+-------------+------+------+---------+------+--------------------------------------------------------+------+

+----+-------------+------+------+---------+------+--------------------------------------------------------+------+

18 rows in set (0.01 sec)

重新测试，可以看到从库2基本无延迟了：

[root@docker35 ~]# mysql -uroot -plhr -h192.168.88.35 -P33651 -e "show slave status \G" | grep Seconds_Behind_Master
mysql: [Warning] Using a password on the command line interface can be insecure.
        Seconds_Behind_Master: 50
[root@docker35 ~]# mysql -uroot -plhr -h192.168.88.35 -P33652 -e "show slave status \G" | grep Seconds_Behind_Master
mysql: [Warning] Using a password on the command line interface can be insecure.
        Seconds_Behind_Master: 0
[root@docker35 ~]# 
[root@docker35 ~]# mysql -uroot -plhr -h192.168.88.35 -P33651 -e "show slave status \G" | grep Seconds_Behind_Master
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql -uroot -plhr -h192.168.88.35 -P33652 -e "show slave status \G" | grep Seconds_Behind_Master
        Seconds_Behind_Master: 131
[root@docker35 ~]# mysql -uroot -plhr -h192.168.88.35 -P33652 -e "show slave status \G" | grep Seconds_Behind_Master
mysql: [Warning] Using a password on the command line interface can be insecure.
        Seconds_Behind_Master: 1

[root@docker35 ~]# mysql -uroot -plhr -h192.168.88.35 -P33651 -e "show slave status \G" | grep Seconds_Behind_Master
mysql: [Warning] Using a password on the command line interface can be insecure.
        Seconds_Behind_Master: 210
[root@docker35 ~]# mysql -uroot -plhr -h192.168.88.35 -P33652 -e "show slave status \G" | grep Seconds_Behind_Master
mysql: [Warning] Using a password on the command line interface can be insecure.
        Seconds_Behind_Master: 1
[root@docker35 ~]# 
[root@docker35 ~]# mysql -uroot -plhr -h192.168.88.35 -P33651 -e "show slave status \G" | grep Seconds_Behind_Master
mysql -uroot -plhr -h192.168.88.35 -P33652 -e "show slave status \G" | grep Seconds_Behind_Mastermysql: [Warning] Using a password on the command line interface can be insecure.
        Seconds_Behind_Master: 381
[root@docker35 ~]# mysql -uroot -plhr -h192.168.88.35 -P33652 -e "show slave status \G" | grep Seconds_Behind_Master
mysql: [Warning] Using a password on the command line interface can be insecure.
        Seconds_Behind_Master: 1

[root@docker35 ~]# mysql -uroot -plhr -h192.168.88.35 -P33651 -e "show slave status \G" | grep Seconds_Behind_Master

mysql: [Warning] Using a password on the command line interface can be insecure.

Seconds_Behind_Master: 50

[root@docker35 ~]# mysql -uroot -plhr -h192.168.88.35 -P33652 -e "show slave status \G" | grep Seconds_Behind_Master

mysql: [Warning] Using a password on the command line interface can be insecure.

Seconds_Behind_Master: 0

[root@docker35 ~]#

[root@docker35 ~]# mysql -uroot -plhr -h192.168.88.35 -P33651 -e "show slave status \G" | grep Seconds_Behind_Master

mysql: [Warning] Using a password on the command line interface can be insecure.

mysql -uroot -plhr -h192.168.88.35 -P33652 -e "show slave status \G" | grep Seconds_Behind_Master

Seconds_Behind_Master: 131

[root@docker35 ~]# mysql -uroot -plhr -h192.168.88.35 -P33652 -e "show slave status \G" | grep Seconds_Behind_Master

mysql: [Warning] Using a password on the command line interface can be insecure.

Seconds_Behind_Master: 1

[root@docker35 ~]# mysql -uroot -plhr -h192.168.88.35 -P33651 -e "show slave status \G" | grep Seconds_Behind_Master

mysql: [Warning] Using a password on the command line interface can be insecure.

Seconds_Behind_Master: 210

[root@docker35 ~]# mysql -uroot -plhr -h192.168.88.35 -P33652 -e "show slave status \G" | grep Seconds_Behind_Master

mysql: [Warning] Using a password on the command line interface can be insecure.

Seconds_Behind_Master: 1

[root@docker35 ~]#

[root@docker35 ~]# mysql -uroot -plhr -h192.168.88.35 -P33651 -e "show slave status \G" | grep Seconds_Behind_Master

mysql -uroot -plhr -h192.168.88.35 -P33652 -e "show slave status \G" | grep Seconds_Behind_Mastermysql: [Warning] Using a password on the command line interface can be insecure.

Seconds_Behind_Master: 381

[root@docker35 ~]# mysql -uroot -plhr -h192.168.88.35 -P33652 -e "show slave status \G" | grep Seconds_Behind_Master

mysql: [Warning] Using a password on the command line interface can be insecure.

Seconds_Behind_Master: 1

打赏赞(4)

标签： MySQL 主从复制半同步复制增强半同步复制并行复制延迟从库延迟复制无损复制高可用

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

发表评论取消回复

2022年 8月
一	二	三	四	五	六	日
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30	31

嘿，我是小麦，需要帮助随时找我哦

18509239930
个人微信
麦老师QQ聊天
个人邮箱
点击加入QQ群
个人微店
回到顶部

小麦苗

MySQL主从复制之增强半同步（无损复制）、延迟和并行复制

简介

主从复制方式

异步复制示例

如何配置半同步复制

延迟从库

并行复制

一、1主2从之增强半同步复制（无损复制）示例

1、MySQL环境初始化

2、主库配置

3、从库1配置

4、从库2配置

5、主从查询

6、测试半同步复制

二、配置延迟复制示例

三、配置并行复制示例

3.1、主库配置

3.2、从库2配置并行复制

3.3、主库压测测试并行复制

您可能还喜欢...

发表评论取消回复

博客寄语

博客概况

搜标题

搜索本网站

标签云☁

博客日历

博客归档

博客分类

MySQL主从复制之增强半同步（无损复制）、延迟和并行复制

简介

主从复制方式

异步复制示例

如何配置半同步复制

延迟从库

并行复制

一、1主2从之增强半同步复制（无损复制）示例

1、MySQL环境初始化

2、主库配置

3、从库1配置

4、从库2配置

5、主从查询

6、测试半同步复制

二、配置延迟复制示例

三、配置并行复制示例

3.1、主库配置

3.2、从库2配置并行复制

3.3、主库压测测试并行复制

您可能还喜欢...

MySQL权限丢失导致只能看见information_schema一个库的解决办法

MySQL shell工具介绍

MySQL查询事务的隔离级别

发表评论 取消回复

博客寄语

博客概况

搜标题

搜索本网站

标签云☁

博客日历

博客归档

博客分类

发表评论取消回复