Mysql半同步与并行复制

一、Mysql半同步复制

条件：GTID配置完成

1、Master端配置

<1>安装插件、开启半同步

mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';  ##安装插件
mysql> set global rpl_semi_sync_master_enabled=1;       ##开启半同步

示图：查看master半同步是否可用

示图：master半同步状态开启

2、Slave端配置

<1>安装插件、开启半同步

mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
mysql> set global rpl_semi_sync_slave_enabled=1;

示图：查看slave半同步是否可用

示图：slave端半同步状态关闭

<2>停止并重新开始IO线程

mysql> stop slave io_thread;
mysql> start slave io_thread;

示图：再次查看slave半同步状态—开启

<3>Master端查看半同步客户端数量为1

测试：

将slave的io线程停掉，master写入数据，会默认等待10s，如果超时还未等到slave的ack，将自动切换到异步复制，当slave的io线程再次开启，复制将自动切换到半同步方式。

<1>从库关闭io线程

mysql> stop slave io_thread;

<2>主库写入数据时，出现 10000ms 的延迟，模式：半同步 --> 异步

mysql> insert into westos.usertb values('user4','111');

示图：

<3>从库开启io线程

mysql> start slave io_thread;

<4>主库再次写入数据

mysql> insert into westos.usertb values('user5','222');

示图：

<5>slave端查看数据—同步

二、并行复制

1、查看Slave的sql线程类型

mysql> show processlist; 
+----+-------------+-----------+-------+---------+------+--------------------------------------------------------+------------------+
| Id | User        | Host      | db    | Command | Time | State                                                  | Info             |
+----+-------------+-----------+-------+---------+------+--------------------------------------------------------+------------------+
|  6 | root        | localhost | mysql | Query   |    0 | starting                                               | show processlist |
|  8 | system user |           | NULL  | Connect | 3454 | Slave has read all relay log; waiting for more updates | NULL             |
| 10 | system user |           | NULL  | Connect | 3477 | Waiting for master to send event                       | NULL             |
+----+-------------+-----------+-------+---------+------+--------------------------------------------------------+------------------+

2、Slave端配置

[root@server6 ~]# vim /etc/my.cnf
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16	    ##线程数
master_info_repository=TABLE	##master_info存储方式为：table
relay_log_info_repository=TABLE	##relay_log_info存储方式为：table
relay_log_recovery=ON
[root@server6 ~]# /etc/init.d/mysqld restart

3、Slave原有的sql线程转化为调度器

slave复制方式已更新

mysql> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time | State                                                  | Info             |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
|  1 | system user |           | NULL | Connect |  193 | Slave has read all relay log; waiting for more updates | NULL             |
|  2 | system user |           | NULL | Connect |  194 | Waiting for master to send event                       | NULL             |
|  3 | system user |           | NULL | Connect |  194 | Waiting for an event from Coordinator                  | NULL             |
|  4 | system user |           | NULL | Connect |  194 | Waiting for an event from Coordinator                  | NULL             |
|  6 | system user |           | NULL | Connect |  194 | Waiting for an event from Coordinator                  | NULL             |
|  7 | system user |           | NULL | Connect |  194 | Waiting for an event from Coordinator                  | NULL             |
|  8 | system user |           | NULL | Connect |  194 | Waiting for an event from Coordinator                  | NULL             |
|  9 | system user |           | NULL | Connect |  194 | Waiting for an event from Coordinator                  | NULL             |
| 10 | system user |           | NULL | Connect |  194 | Waiting for an event from Coordinator                  | NULL             |
| 11 | system user |           | NULL | Connect |  194 | Waiting for an event from Coordinator                  | NULL             |
| 12 | system user |           | NULL | Connect |  194 | Waiting for an event from Coordinator                  | NULL             |
| 13 | system user |           | NULL | Connect |  194 | Waiting for an event from Coordinator                  | NULL             |
| 14 | system user |           | NULL | Connect |  194 | Waiting for an event from Coordinator                  | NULL             |
| 16 | system user |           | NULL | Connect |  194 | Waiting for an event from Coordinator                  | NULL             |
| 17 | system user |           | NULL | Connect |  194 | Waiting for an event from Coordinator                  | NULL             |
| 18 | system user |           | NULL | Connect |  194 | Waiting for an event from Coordinator                  | NULL             |
| 19 | system user |           | NULL | Connect |  194 | Waiting for an event from Coordinator                  | NULL             |
| 20 | system user |           | NULL | Connect |  194 | Waiting for an event from Coordinator                  | NULL             |
| 22 | root        | localhost | NULL | Query   |    0 | starting                                               | show processlist |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+

4、Slave端查看mysql数据库的表，新增两张表

mysql> use mysql;
mysql> show tables;