gtid模式
master配置:
[root@server1 ~]# vim /etc/my.cnf
gtid_mode=ON
enforce_gtid_consistency=ON
重启服务
[root@server1 ~]# systemctl restart mysqld
slave配置:
[root@server2 ~]# vim /etc/my.cnf
gtid_mode=ON
enforce_gtid_consistency=ON
[root@server3 ~]# vim /etc/my.cnf
重启服务
[root@server2 ~]# systemctl restart mysqld
[root@server2 ~]# mysql -pWestos+1
#首先停止slave
mysql> stop slave;
#重新配置
mysql> CHANGE MASTER TO MASTER_HOST='192.168.52.120', MASTER_USER='repl', MASTER_PASSWORD='Westos+1', MASTER_AUTO_POSITION = 1;
#再次启动
mysql> start slave;
mysql> show slave status\G;
其它slave节点以此类推
mysql> show slave status\G;
半同步模式
master配置:
#安装半同步模块
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
-> FROM INFORMATION_SCHEMA.PLUGINS
-> WHERE PLUGIN_NAME LIKE '%semi%';
#激活master半同步模式
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
#查看半同步参数
mysql> SHOW VARIABLES LIKE 'rpl_semi_sync%';
#查看半同步状态
mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
半同步参数写入配置文件,确保重启后依然生效
[root@server1 mysql]# vim /etc/my.cnf
rpl_semi_sync_master_enabled=1
slave配置:
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
mysql> SET GLOBAL rpl_semi_sync_slave_enabled =1;
#需要重启IO线程,slave端的半同步才能生效
mysql> STOP SLAVE IO_THREAD;
mysql> START SLAVE IO_THREAD;
mysql> SHOW VARIABLES LIKE 'rpl_semi_sync%';
mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
server2:
server3:
#半同步参数写入配置文件
[root@server2 ~]# vim /etc/my.cnf
[root@server3 ~]# vim /etc/my.cnf
测试:
master写入数据:
mysql> insert into user values('user2','222');
mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
停止所有slave节点的IO线程:
mysql> STOP SLAVE IO_THREAD;
master节点再次写入数据:
mysql> insert into user values('user3','333');
Query OK, 1 row affected (10.00 sec)
mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
所有slave节点再次启动IO线程,mysql会自动切回半同步模式
mysql> START SLAVE IO_THREAD;
延迟复制
在其中一个slave中执行
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_DELAY =30;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave ;
Query OK, 0 rows affected (0.01 sec)
在master上插入数据后,slave不会立即更新,而是需要等待指定的延迟后才会执行。
并行复制
默认slave节点sql单线程回放,会造成数据同步延时较高
slave节点添加以下参数
[root@server2 ~]# 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
重启服务
[root@server2 ~]# systemctl restart mysqld
[root@server2 ~]# mysql -pWestos+1
mysql> show processlist;