目录
一、gtid模式
master配置:
vim /etc/my.cnf
gtid_mode=ON
enforce-gtid-consistency=ON ##加入这两行
/etc/init.d/mysqld restart
slave配置:
vim /etc/my.cnf
gtid_mode=ON
enforce-gtid-consistency=ON
/etc/init.d/mysqld restart
mysql -p123456
#首先停止slave
mysql> stop slave;
#重新配置
mysql> CHANGE MASTER TO MASTER_HOST='192.168.189.132', MASTER_USER='yyl', MASTER_PASSWORD='123456', MASTER_AUTO_POSITION = 1;
mysql> start slave;
mysql> show slave status\G;
其它slave节点以此类推
二、半同步模式
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半同步模式
SET GLOBAL rpl_semi_sync_master_enabled = 1;
#查看半同步参数
SHOW VARIABLES LIKE 'rpl_semi_sync%';
#查看半同步状态
SHOW STATUS LIKE 'Rpl_semi_sync%';
slave配置:
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled =1;
#需要重启IO线程,slave端的半同步才能生效
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;
SHOW VARIABLES LIKE 'rpl_semi_sync%';
SHOW STATUS LIKE 'Rpl_semi_sync%';
其它slave节点以此类推
半同步参数写入配置文件,确保重启后依然生效
vim /etc/my.cnf
rpl_semi_sync_master_enabled=1 #master
rpl_semi_sync_slave_enabled=1 #slave
测试:
master写入数据:
停止所有slave节点的IO线程:
master节点再次写入数据:
#等待默认超时时间10秒后,mysql自动切换为异步模式
所有slave节点再次启动IO线程,mysql会自动切回半同步模式
三、延迟复制
在其中一个slave中执行
stop slave;
CHANGE MASTER TO MASTER_DELAY =30;
start slave ;
在master上插入数据后,slave不会立即更新,而是需要等待指定的延迟后才会执行。
四、并行复制
默认slave节点sql单线程回放,会造成数据同步延时较高
slave节点添加以下参数
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组复制
vm1
首先停止数据库
/etc/init.d/mysqld stop
清除数据
cd /data/mysql
rm -fr *
配置文件
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id=1 #vm1=1 ,vm2=2, vm3=3
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
plugin_load_add='group_replication.so'
transaction_write_set_extraction=XXHASH64
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address= "vm1:33061"
group_replication_group_seeds= "vm1:33061,vm2:33061,vm3:33061"
group_replication_bootstrap_group=off
group_replication_ip_whitelist="192.168.189.0/24,127.0.0.1/8"
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON
group_replication_allow_local_disjoint_gtids_join=1
#根据实际情况修改主机名和网段
初始化等
mysqld --initialize --user=mysql
/etc/init.d/mysqld start
mysql -p
#首先修改临时密码
alter user root@localhost identified by '123456';
CREATE USER yyl@'%' IDENTIFIED BY '123456'; #创建用户
GRANT REPLICATION SLAVE ON *.* TO yyl@'%'; #给权限
FLUSH PRIVILEGES; #刷新
CHANGE MASTER TO MASTER_USER='yyl', MASTER_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery';
SET GLOBAL group_replication_bootstrap_group=ON; #只在vm1上执行
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF; #只在server1上执行
SELECT * FROM performance_schema.replication_group_members;
三台全部配置完成:
测试
所有节点都可以读写数据
vm1
vm2
vm3
六、慢查询
set global slow_query_log=ON;
show variables like "long%"; #默认10秒
set long_query_time=5;
select sleep(5); #模拟慢查询语句
show variables like "slow%";
七、mysql路由器
安装软件
dnf install -y mysql-router-community-8.0.21-1.el7.x86_64.rpm
配置服务
cd /etc/mysqlrouter/
vim mysqlrouter.conf
[routing:ro]
bind_address = 0.0.0.0
bind_port = 7001
destinations = 192.168.189.111:3306,192.168.189.112:3306,192.168.189.113:3306
routing_strategy = round-robin
[routing:rw]
bind_address = 0.0.0.0
bind_port = 7002
destinations = 192.168.189.113:3306,192.168.189.112:3306,192.168.189.111:3306
routing_strategy = first-available
安装mysql客户端工具
dnf install -y mariadb
在mysql集群中创建远程测试用户
grant all on test.* to 'yyl'@'%' identified by '123456';
连接mysql路由器(vm4)
mysql -h 192.168.189.111 -P 7001 -u yyl -p123456
select * from test.userdb;
在vm1上查看网络连接
yum install -y lsof #lsof可以直观的查看
lsof -i :3306
另一个端口7002
mysql -h 192.168.189.111 -P 7002 -u yyl -p123456
lsof -i :3306 #vm3上