环境准备
| IP |
HOSTNAME |
SERVICE |
SYSTEM |
| 192.168.131.129 |
mysql-master1 |
mysql |
CentOS7.6 |
| 192.168.131.130 |
mysql-slave1 |
mysql |
CentOS7.6 |
| 192.168.131.131 |
mysql-slave2 |
mysql |
CentOS7.6 |
[root@localhost ~]# sestatus
SELinux status: disabled
[root@localhost ~]# systemctl status firewalld
● firewalld.service - firewalld - dynamic firewall daemon
Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
Active: inactive (dead)
Docs: man:firewalld(1)
[root@localhost ~]# cat /etc/redhat-release
CentOS Linux release 7.6.1810 (Core)
[root@localhost ~]# hostnamectl --static set-hostname mysql-master1
[root@localhost ~]# hostnamectl --static set-hostname mysql-slave1
[root@localhost ~]# hostnamectl --static set-hostname mysql-master2
[root@localhost ~]# hostnamectl --static set-hostname mysql-slave2
部署mysql
# mysql-master和mysql-slave都需要部署
[root@mysql-master ~]# wget https://repo.mysql.com/mysql57-community-release-el7-11.noarch.rpm
[root@mysql-master ~]# yum -y install mysql57-community-release-el7-11.noarch.rpm
[root@mysql-master ~]# yum -y install yum-utils # 安装yum管理工具
[root@mysql-master ~]# yum-config-manager --disable mysql80-community # 禁用8.0版本
[root@mysql-master ~]# yum-config-manager --enable mysql57-community # 启用5.7版本
[root@mysql-master ~]# yum repolist enabled | grep mysql # 检查一下，确保只有一个版本
mysql-connectors-community/x86_64 MySQL Connectors Community 165
mysql-tools-community/x86_64 MySQL Tools Community 115
mysql57-community/x86_64 MySQL 5.7 Community Server 444
[root@mysql-master ~]# yum -y install mysql-community-server
[root@mysql-master ~]# systemctl enable mysqld --now # 设为开机自启，并立即启动
# 修改默认密码（MySQL从5.7开始不允许首次安装后使用空密码进行登录！为了加强安全性，系统会随机生成一个密码以供管理员首次登录使用，这个密码记录在/var/log/mysqld.log文件中）
[root@mysql-master1 ~]# grep "temporary password" /var/log/mysqld.log
2020-08-11T01:38:32.872421Z 1 [Note] A temporary password is generated for root@localhost: pHj_Agoyi3of
[root@mysql-master1 ~]# mysql -uroot -p'pHj_Agoyi3of'
mysql> alter user 'root'@'localhost' identified by 'Test123.com';
主从复制
配置master1
[root@mysql-master1 ~]# cp /etc/my.cnf{,.bak}
[root@mysql-master1 ~]# > /etc/my.cnf
[root@mysql-master1 ~]# vim /etc/my.cnf
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
symbolic-links = 0
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
#GTID:
server_id = 1
gtid_mode = on
enforce_gtid_consistency = on
#binlog
log_bin = mysql-bin
log-slave-updates = 1
binlog_format = row
sync-master-info = 1
sync_binlog = 1
#relay log
skip_slave_start = 1
[root@mysql-master1 ~]# systemctl restart mysqld
[root@mysql-master1 ~]# mysql -uroot -p
Enter password:
mysql> show master status; # 查看master状态， 发现多了一项"Executed_Gtid_Set "
+------------------+----------+--------------+------------------+-------------------+
| 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 | 5d02c99a-db73-11ea-a39a-000c294ec5c2 |
+---------------+--------------------------------------+
1 row in set (0.00 sec)
mysql> show global variables like '%gtid%'; # 查看确认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.00 sec)
mysql> show variables like 'log_bin'; # 查看确认binlog日志功能打开
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set (0.00 sec)
mysql> grant replication slave,replication client on *.* to slave@'192.168.%' identified by "Slave@123";
Query OK, 0 rows affected, 1 warning (0.04 sec)
mysql> show grants for slave@'192.168.%';
+---------------------------------------------------------------------------+
| Grants for slave@192.168.% |
+---------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'192.168.%' |
+---------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show master status; # 查看master状态
+------------------+----------+--------------+------------------+----------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000001 | 466 | | | 5d02c99a-db73-11ea-a39a-000c294ec5c2:1 |
+------------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)
配置slave1
[root@mysql-master1 ~]# cp /etc/my.cnf{,.bak}
[root@mysql-master1 ~]# > /etc/my.cnf
[root@mysql-master1 ~]# vim /etc/my.cnf
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
symbolic-links = 0
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
#GTID:
server_id = 2
gtid_mode = on
enforce_gtid_consistency = on
#binlog
log_bin = mysql-bin
log-slave-updates = 1
binlog_format = row
sync-master-info = 1
sync_binlog = 1
#relay log
skip_slave_start = 1
[root@mysql-master1 ~]# mysql -uroot -p
Enter password:
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change master to master_host='192.168.131.129',master_user='slave',master_password='Slave@123',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.131.129
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 466
Relay_Log_File: mysql-slave1-relay-bin.000002
Relay_Log_Pos: 679
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
.........
.........
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
# mysql-slave1节点已经和mysql-master1节点配置完成主从同步关系
并行复制
一般Mysql主从复制有三个线程参与，都是单线程：Binlog Dump(主) -> IO Thread (从) -> SQL Thread(从)。复制出现延迟一般出在两个地方:
- SQL线程忙不过来 (可能需要应用数据量较大，可能和从库本身的一些操作有锁和资源的冲突；主库可以并发写，SQL线程不可以；主要原因)
- 网络抖动导致IO线程复制延迟(次要原因)。
MySQL主从复制延迟的解决办法：MySQL从5.6开始有了SQL Thread多个的概念，可以并发还原数据，即并行复制技术。并行复制的机制，是MySQL的一个非常重要的特性，可以很好的解决MySQL主从延迟问题！
MySQL 5.6版本支持所谓的并行复制，但是其并行只是基于schema的，也就是基于库的。
如果用户的MySQL数据库实例中存在多个schema，对于从机复制的速度的确可以有比较大的帮助。
但是基于schema的并行复制存在两个问题：
1) crash safe功能不好做，因为可能之后执行的事务由于并行复制的关系先完成执行，那么当发生crash的时候，这部分的处理逻辑是比较复杂的。
2) 最为关键的问题是这样设计的并行复制效果并不高，如果用户实例仅有一个库，那么就无法实现并行回放，甚至性能会比原来的单线程更差。而 单库多表是比多库多表更为常见的一种情形 。
注意：mysql 5.6 的MTS是基于库级别的并行，当有多个数据库时，可以将slave_parallel_workers设置为数据库的数量，为了避免新建库后来回修改，也可以将该参数设置的大一些。设置为库级别的事务时，不允许这样做，会报错。
在MySQL 5.7 中，引入了基于组提交的并行复制（官方称为Enhanced Multi-threaded Slaves，即MTS），设置参数 slave_parallel_workers>0 并且 global.slave_parallel_type='LOGICAL_CLOCK'，即可支持一个 schema 下， slave_parallel_workers 个的 worker 线程并发执行 relay log 中主库提交的事务。
其核心思想：一个组提交的事务都是可以并行回放（配合 binary log group commit ）；slave 机器的relay log 中 last_committed 相同的事务（ sequence_num 不同）可以并发执行。其中，变量 slave-parallel-type 可以有两个值：
1 ）DATABASE 默认值，基于库的并行复制方式；
2 ）LOGICAL_CLOCK，基于组提交的并行复制方式；
MySQL 5.7是基于组提交的并行复制，并且是支持"真正"的并行复制功能，这其中最为主要的原因：就是slave服务器的回放与主机是一致的, 即master服务器上是怎么并行执行的slave上就怎样进行并行回放。不再有库的并行复制限制，对于二进制日志格式也无特殊的要求（基于库的并行复制也没有要求）。
MySQL5.7的并行复制，期望最大化还原主库的并行度，实现方式是在binlog event中增加必要的信息，以便slave节点根据这些信息实现并行复制。MySQL5.7的并行复制建立在group commit的基础上，所有在主库上能够完成prepared的语句表示没有数据冲突，就可以在slave节点并行复制。
配置master1
[root@mysql-master1 ~]# cp /etc/my.cnf{,.bak}
[root@mysql-master1 ~]# vim /etc/my.cnf # 基于GTID主从复制结构，加入并行复制的配置
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
symbolic-links = 0
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
#GTID:
server_id = 1
gtid_mode = on
enforce_gtid_consistency = on
#binlog
log_bin = mysql-bin
log-slave-updates = 1
binlog_format = row
sync-master-info = 1
sync_binlog = 1
#relay log
skip_slave_start = 1
#不配置binlog_group_commit从库无法做到基于事物的并行复制
binlog_group_commit_sync_delay = 100
binlog_group_commit_sync_no_delay_count = 10
#为了数据安全再配置
sync_binlog=1
innodb_flush_log_at_trx_commit =1
#这个参数控制binlog写入 磁盘的方式。设置为1时，表示每次commit;都写入磁盘。这个刷新的是redo log 即ib_logfile0，而不是binlog
[root@mysql-master1 ~]# systemctl restart mysqld
[root@mysql-master1 ~]# mysql -uroot -p
Enter password:
mysql> show variables like 'binlog_group_commit_%';
+-----------------------------------------+-------+
| Variable_name | Value |
+-----------------------------------------+-------+
| binlog_group_commit_sync_delay | 100 |
| binlog_group_commit_sync_no_delay_count | 10 |
+-----------------------------------------+-------+
2 rows in set (0.01 sec)
# 设置binlog_group_commit的上面两个参数，否则从库无法做到基于事物的并行复制! 这两个参数共同决定了是否触发组提交操作！
# 第二个参数表示该事务组提交之前总共等待累积到多少个事务(如上要累计到10个事务)；
# 第一个参数则表示该事务组总共等待多长时间后进行提交(如上要总共等待100毫秒的时间)，任何一个条件满足则进行后续操作。
# 因为有这个等待，可以让更多事务的binlog通过一次写binlog文件磁盘来完成提交，从而获得更高的吞吐量。
配置slave1
'记住：只要主数据库的mysqld服务重启，那么从数据库上就要重启slave，以恢复主从同步状态！！！
[root@mysql-slave1 ~]# cp /etc/my.cnf{,.bak}
[root@mysql-slave1 ~]# vim /etc/my.cnf
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
symbolic-links = 0
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
#GTID:
server_id = 2
gtid_mode = on
enforce_gtid_consistency = on
#binlog
log_bin = mysql-bin
log-slave-updates = 1
binlog_format = row
sync-master-info = 1
sync_binlog = 1
#relay log
skip_slave_start = 1
read_only = on
#MTS
slave-parallel-type = LOGICAL_CLOCK #开启逻辑时钟的复制
slave-parallel-workers = 4 #这里设置线程数为4 (最大线程数不能超过16，即最大线程为16)
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = on
[root@mysql-slave1 ~]# systemctl restart mysqld
[root@mysql-slave1 ~]# mysql -uroot -p
Enter password:
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.10 sec)
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.131.129
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 466
Relay_Log_File: mysql-slave1-relay-bin.000002
Relay_Log_Pos: 679
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
.........
.........
Retrieved_Gtid_Set: 5d02c99a-db73-11ea-a39a-000c294ec5c2:1
Executed_Gtid_Set: 5d02c99a-db73-11ea-a39a-000c294ec5c2:1
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
# mysql-slave1从数据库恢复了与mysql-master1主数据库的同步关系了
# 查看线程数，这个跟在my.cnf文件里配置的是一样的
mysql> show variables like '%slave_para%';
+------------------------+---------------+
| Variable_name | Value |
+------------------------+---------------+
| slave_parallel_type | LOGICAL_CLOCK |
| slave_parallel_workers | 4 |
+------------------------+---------------+
2 rows in set (0.01 sec)
半同步复制
默认情况下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线程负责两件事：dump日志给slave的io_thread；接收来自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回复，然后再返回成功信息给客户端。
AFTER_SYNC和AFTER_COMMIT的优缺点
AFTER_SYNC
- 对于所有客户端来说，它们看到的数据是一样的，因为它们看到的数据都是在接收到slave的ack后提交后的数据。
- 这种模式下，如果master突然故障，不会丢失数据，因为所有成功的事务都已经写进slave的relay log中了，slave的数据是最新的。
AFTER_COMMIT
- 不同客户端看到的数据可能是不一样的。对于发起事务请求的那个客户端，它只有在master提交事务且收到slave的ack后才能看到提交的数据。但对于那些非本次事务的请求客户端，它们在master提交后就能看到提交后的数据，这时候master可能还没收到slave的ack。
- 如果master收到ack回复前，slave和master都故障了，那么将丢失这个事务中的数据。
在MySQL 5.7.2之前，等价的模式是 AFTER_COMMIT ，在此版本之后，默认的模式为 AFTER_SYNC ，该模式能最大程度地保证数据安全性，且性能上并不比 AFTER_COMMIT 差。
配置master1
[root@mysql-master1 ~]# cp /etc/my.cnf{,.bak}
[root@mysql-master1 ~]# vim /etc/my.cnf # 基于GTID主从复制和并行复制，加入半同步复制
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
symbolic-links = 0
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
#GTID:
server_id = 1
gtid_mode = on
enforce_gtid_consistency = on
#binlog
log_bin = mysql-bin
log-slave-updates = 1
binlog_format = row
sync-master-info = 1
sync_binlog = 1
#relay log
skip_slave_start = 1
#不配置binlog_group_commit从库无法做到基于事物的并行复制
binlog_group_commit_sync_delay = 100
binlog_group_commit_sync_no_delay_count = 10
#开启半同步复制 (超时时间为1s)
plugin-load=rpl_semi_sync_master=semisync_master.so
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_master_timeout = 1000
[root@mysql-master1 ~]# systemctl restart mysqld
[root@mysql-master1 ~]# mysql -uroot -p
Enter password:
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
ERROR 1125 (HY000): Function 'rpl_semi_sync_master' already exists
# 在mysql-master主数据库上加载 (前提是/usr/lib64/mysql/plugin/semisync_master.so 文件存在。 一般mysql安装后就默认产生)，我的已经默认带有这个function
mysql> select plugin_name,
-> plugin_status from information_schema.plugins
-> where plugin_name like '%semi%'; # 查看插件是否加载成功
+----------------------+---------------+
| plugin_name | plugin_status |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE |
+----------------------+---------------+
1 row in set (0.01 sec)
mysql> show status like 'Rpl_semi_sync_master_status'; # 查看半同步是否在运行
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | ON |
+-----------------------------+-------+
1 row in set (0.00 sec)
配置slave1
[root@mysql-slave1 ~]# cp /etc/my.cnf{,.bak}
[root@mysql-slave1 ~]# vim /etc/my.cnf
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
symbolic-links = 0
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
#GTID:
server_id = 1
gtid_mode = on
enforce_gtid_consistency = on
#binlog
log_bin = mysql-bin
log-slave-updates = 1
binlog_format = row
sync-master-info = 1
sync_binlog = 1
#relay log
skip_slave_start = 1
#不配置binlog_group_commit从库无法做到基于事物的并行复制
binlog_group_commit_sync_delay = 100
binlog_group_commit_sync_no_delay_count = 10
#为了数据安全再配置
sync_binlog=1
innodb_flush_log_at_trx_commit =1
#这个参数控制binlog写入 磁盘的方式。设置为1时，表示每次commit;都写入磁盘。这个刷新的是redo log 即ib_logfile0，而不是binlog
# 开启半同步复制
plugin-load=rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_slave_enabled=1
[root@mysql-slave1 ~]# systemctl restart mysqld
[root@mysql-slave1 ~]# mysql -uroot -p
Enter password:
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.00 sec)
# 在mysql-slave1从数据库上加载 (前提是/usr/lib64/mysql/plugin/semisync_slave.so 文件存在。 一般mysql安装后就默认产生)
mysql> select plugin_name,
-> plugin_status from information_schema.plugins
-> where plugin_name like '%semi%'; # 查看插件是否加载成功
+----------------------+---------------+
| plugin_name | plugin_status |
+----------------------+---------------+
| rpl_semi_sync_slave | ACTIVE |
| rpl_semi_sync_master | ACTIVE |
+----------------------+---------------+
2 rows in set (0.00 sec)
mysql> show status like 'Rpl_semi_sync_slave_status';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | OFF |
+----------------------------+-------+
1 row in set (0.00 sec)
# 发现是OFF，这是因为此时还没有生效，必须从数据库上的IO线程才能生产！！
mysql> stop slave IO_THREAD;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> start slave IO_THREAD;
Query OK, 0 rows affected (0.00 sec)
mysql> show status like 'Rpl_semi_sync_slave_status'; # 然后再查看mysql-slave1的半同步状态，发现就已经开启了！
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.00 sec)
mysql> show slave status \G # 再次查看主从同步状态，发现主从同步出现异常，这个时候再重启下slave即可！
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.131.129
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 194
Relay_Log_File: mysql-slave1-relay-bin.000005
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: No
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.131.129
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 194
Relay_Log_File: mysql-slave1-relay-bin.000007
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
master1查看Rpl_semi
mysql> show status like '%Rpl_semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
# 从上面信息，发现Rpl_semi_sync_master_clients的数值为1，说明此时mysql-master主数据库已经有一个半同步复制的从机，即mysql-slave1节点。
# Rpl_semi_sync_master_yes_tx的数值为0， 说明此时还没有半同步复制的sql语句被执行。主库写入数据后，Rpl_semi_sync_master_yes_tx的数值为sql语句的数量
slave2加入主从复制&并行复制&半同步复制
[root@mysql-slave2 ~]# cp /etc/my.cnf{,.bak}
[root@mysql-slave2 ~]# > /etc/my.cnf
[root@mysql-slave2 ~]# vim /etc/my.cnf
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
symbolic-links = 0
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
#GTID:
server_id = 3
gtid_mode = on
enforce_gtid_consistency = on
#binlog
log_bin = mysql-bin
log-slave-updates = 1
binlog_format = row
sync-master-info = 1
sync_binlog = 1
#relay log
skip_slave_start = 1
read_only = on
#MTS
slave-parallel-type = LOGICAL_CLOCK #开启逻辑时钟的复制
slave-parallel-workers = 4 #最大线程16
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = on
# 开启半同步复制
plugin-load=rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_slave_enabled=1
[root@mysql-slave2 ~]# systemctl restart mysqld
[root@mysql-slave2 ~]# mysql -uroot -p
Enter password:
mysql> show global variables like 'gtid_%';
+----------------------------------+-------+
| Variable_name | Value |
+----------------------------------+-------+
| gtid_executed | |
| gtid_executed_compression_period | 1000 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | |
+----------------------------------+-------+
5 rows in set (0.01 sec)
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change master to master_host='192.168.131.129',master_user='slave',master_password='Slave@123',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.131.129
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 194
Relay_Log_File: mysql-slave2-relay-bin.000003
Relay_Log_Pos: 407
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
mysql> show variables like '%slave_para%'; # 查看并行复制
+------------------------+---------------+
| Variable_name | Value |
+------------------------+---------------+
| slave_parallel_type | LOGICAL_CLOCK |
| slave_parallel_workers | 4 |
+------------------------+---------------+
2 rows in set (0.00 sec)
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so'; # 开启半同步复制
Query OK, 0 rows affected (0.04 sec)
mysql> select plugin_name,
-> plugin_status from information_schema.plugins
-> where plugin_name like '%semi%';
+----------------------+---------------+
| plugin_name | plugin_status |
+----------------------+---------------+
| rpl_semi_sync_slave | ACTIVE |
| rpl_semi_sync_master | ACTIVE |
+----------------------+---------------+
2 rows in set (0.00 sec)
mysql> stop slave IO_THREAD;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave IO_THREAD;
Query OK, 0 rows affected (0.00 sec)
mysql> show status like 'Rpl_semi_sync_slave_status';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.00 sec)
# 回到mysql-master1主数据库查看
mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+-----------+------+------+-----------+--------------------------------------+
| 3 | | 3306 | 1 | 5d138126-db73-11ea-988b-000c29bef1e6 |
| 2 | | 3306 | 1 | 5d0fecd7-db73-11ea-b20e-000c2986ee9d |
+-----------+------+------+-----------+--------------------------------------+
2 rows in set (0.00 sec)
# mysql-master1主数据库现在有两个从数据库，分别为mysql-slave1 和 mysql-slave2
mysql> show status like '%Rpl_semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 2 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
+--------------------------------------------+-------+
14 rows in set (0.01 sec)
# mysql-master1主数据库现在有两个半同步复制的从库，即mysql-slave1 和mysql-slave2
