一、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;