mysql级连型复制

Master：server3 172.25.25.3
Master&slave： server2 172.25.25.2
Slave： server1 172.25.25.1
先做好主从复制（基于gtid）
三个主机的配置文件/etc/my.cnf

##以server2为例
server-id=2（三个主机使用不同的数字）
log-bin=mysql-bin

gtid_mode=ON
enforce-gtid-consistency=true

Server3
建立用户并授权

 grant replication slave on *.* to 'server'@'172.25.25.%' identified by 'Redhat+123';

Server2

# 配置文件 /etc/my.cnf
log-slave-updates
# 重启
/etc/init.d/mysqld restart
# 授权
mysql> grant replication slave on *.* to 'server'@'172.25.25.%' identified by 'Redhat+123';
# 建立连接
mysql> CHANGE MASTER TO MASTER_HOST='172.25.25.3',MASTER_USER='server', mysql> MASTER_PASSWORD='Redhat+123',MASTER_AUTO_POSITION=1;
mysql> Start slave;
mysql> Show slave status\G

Server1
建立连接

mysql> CHANGE MASTER TO MASTER_HOST='172.25.25.2',MASTER_USER='server', mysql> MASTER_PASSWORD='Redhat+123',MASTER_AUTO_POSITION=1;
mysql> Start slave;
mysql> Show slave status\G

Server3
操作数据库：

mysql> create database test;

查看：

Server2

[root@server2 ~]# cd /var/lib/mysql
[root@server2 ~]# cat mysql-bin.index
./mysql-bin.000001
./mysql-bin.000002
./mysql-bin.000003
./mysql-bin.000004
./mysql-bin.000005
./mysql-bin.000006
./mysql-bin.000007
./mysql-bin.000008

[root@server2 mysql]# mysqlbinlog -v --base64-output=DECODE-ROWS mysql-bin.000008    #最后一个号
可以看见master上面对数据库的操作

在Server2，server1配置

编辑配置文件vim /etc/my.cnf
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
# 重启
/etc/init.d/mysqld restart

mysql> show processlist;

配置半同步

Server3

# 导入插件
mysql> install plugin rpl_semi_sync_master SONAME 'semisync_master.so';
mysql> set global rpl_semi_sync_master_enabled = 1;

Server2

# 导入插件
mysql> install plugin rpl_semi_sync_master SONAME 'semisync_master.so';

mysql> set global rpl_semi_sync_master_enabled = 1;

mysql> install plugin rpl_semi_sync_slave SONAME 'semisync_slave.so';

mysql> set global rpl_semi_sync_slave_enabled = 1;

Server1

# 导入插件
mysql> install plugin rpl_semi_sync_slave SONAME 'semisync_slave.so';

mysql> set global rpl_semi_sync_slave_enabled = 1;

mysql> stop slave IO_THREAD;

mysql> start slave IO_THREAD;
# 查看状态
mysql> show status like 'rpl_semi_sync%';

三个主机查看状态都为 on则配置成功

测试

Server2，关闭slave

mysql> stop slave IO_THREAD;

Server3
操作数据库

mysql> create table userlist (
    -> uesrname varchar(8) not null,
    -> password varchar(8) not null);   #需要等待十秒
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| userlist       |
+----------------+

Server1上查看

mysql> use test;
Database changed
mysql> show tables;    #没有同步，查询不到
Empty set (0.00 sec)

Server2
开启slave

mysql> start slave IO_THREAD;

Server1
可以同步到server3的数据

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| userlist       |
+----------------+