搭建环境
准备工作
需要准备三台虚拟机并安装数据库
我的实验设置:
主库 | server3 | 172.25.70.13 |
从库1 | server4 | 172.25.70.14 |
从库2 | server1 | 172.25.70.11 |
如果做过异步或者半同步的实验,需要清除之前的实验环境
systemctl stop mysqld 先关闭数据库
rm -fr /var/lib/mysql/* 初始化
systemctl start mysqld 启动数据库
grep password /var/log/mysqld.log 查看初始化后生成的密码
主库配置
编辑主库的配置文件 vim /etc/my.cnf
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server_id=1
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
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="c36cc649-3f17-11e9-960e-525400cf2a01"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "172.25.70.1:24901"
loose-group_replication_group_seeds= "172.25.70.1:24901,172.25.70.2:24901,172.25.70.3:24901"
loose-group_replication_bootstrap_group=off
loose-group_replication_ip_whitelist="127.0.0.1,172.25.1.0/24"
loose-group_replication_enforce_update_everywhere_checks=ON
loose-group_replication_single_primary_mode=OFF
其中
binlog_checksum=NONE 关闭binlog校验
binlog_format=ROW 组复制依赖基于行的复制格式
loose-group_replication_group_name="c36cc649-3f17-11e9-960e-525400cf2a01"
这里的参数不能和该组中任何一个节点的UUID相同
loose-group_replication_bootstrap_group=off
插件是否自动引导,这个选项一般都要off掉,只需要由发起组复制的节点开启,并只启动一次,如果是on,
下次再启动时,会生成一个同名的组,可能会发生脑裂。
后两行是开启多主模式的参数
主库登录数据库后,先修改密码
mysql> alter user root@localhost identified by 'Wsp+123ld'; 修改数据库密码
mysql> SET SQL_LOG_BIN=0; 关闭二进制日志,防止传到其他server上
mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'Wsp+123ld';
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%'; 创建用户并授权
mysql> FLUSH PRIVILEGES;
mysql> SET SQL_LOG_BIN=1; 开启日志
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Wsp+123ld' FOR CHANNEL 'group_replication_recovery';
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
mysql> SHOW PLUGINS; ##查看插件
| group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL |
+----------------------------+----------+--------------------+----------------------+---------+
mysql> SET GLOBAL group_replication_bootstrap_group=ON; ##组复制发起节点开启这个参数
mysql> START GROUP_REPLICATION;
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
mysql> SELECT * FROM performance_schema.replication_group_members; ##查看server1是否online
alter user root@localhost identified by 'Wsp+123ld';
SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED BY 'Wsp+123ld';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Wsp+123ld' FOR CHANNEL 'group_replication_recovery';
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
SHOW PLUGINS;
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
SELECT * FROM performance_schema.replication_group_members;
从库配置
server_id=2
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
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="c36cc649-3f17-11e9-960e-525400cf2a01"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "172.25.1.2:24901"
loose-group_replication_group_seeds= "172.25.1.1:24901,172.25.1.2:24901,172.25.1.3:24901"
loose-group_replication_bootstrap_group=off
loose-group_replication_ip_whitelist="127.0.0.1,172.25.1.0/24"
loose-group_replication_enforce_update_everywhere_checks=ON
loose-group_replication_single_primary_mode=OFF
其中
loose-group_replication_local_address= "172.25.1.2:24901" 这里填写从库的IP
loose-group_replication_group_name="c36cc649-3f17-11e9-960e-525400cf2a01" 这里和主库的保持一致!!!!!!
从库查看是否已同步,两个从库配置相同
mysql> alter user root@localhost identified by 'Wsp+123ld'; #修改root用户密码
mysql> SET SQL_LOG_BIN=0;
mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'Wsp+123ld';
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
mysql> FLUSH PRIVILEGES;
mysql> SET SQL_LOG_BIN=1;
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Wsp+123ld' FOR CHANNEL 'group_replication_recovery';
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
mysql> START GROUP_REPLICATION;
'这里直接开启组复制可能会报错,查看mysql日志'
cat /var/log/mysqld.log ,找到解决办法
2019-03-14T08:51:52.838185Z 0 [Note] Plugin group_replication reported: 'To force this member into the group you can use the group_replication_allow_local_disjoint_gtids_join option'
重启数据库并登录
mysql -pWsp+123ld
mysql> STOP GROUP_REPLICATION;
mysql> set global group_replication_allow_local_disjoint_gtids_join=on; ##兼容加入组,直接做这一步 不用等报错,前提是每台机器做hosts解析组内其他机器ip对应的主机名
mysql> START GROUP_REPLICATION;
恢复正常
alter user root@localhost identified by 'Wsp+123ld';
SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED BY 'Wsp+123ld';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Wsp+123ld' FOR CHANNEL 'group_replication_recovery';
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
set global group_replication_allow_local_disjoint_gtids_join=on;
START GROUP_REPLICATION;
从库配置好后在主库上查看
mysql> SELECT * FROM performance_schema.replication_group_members;
测试
主库加入数据
mysql> CREATE DATABASE test;
mysql> USE test;
mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);
mysql> INSERT INTO t1 VALUES (1, 'Luis');
mysql> SELECT * FROM t1;
从库上查看
小结
更多详细信息可以取官方网站了解
配置实例进行组复制
用户凭证
搭建过程中的问题及注意点:
- 主库和从库的配置文件中,组名的值要保持一致,但是不能和所有节点的UUID相同,
- 进入数据库后,主库需要执行开启组复制的指令,而从库需要执行加入组的指令
systemctl stop mysqld
rm -fr /var/lib/mysql/*
> /var/log/mysqld.log
systemctl start mysqld
passwd=`grep password /var/log/mysqld.log|grep generated|cut -d : -f 4|sed s/[[:space:]]//g`
echo "$passwd"
mysql -uroot -p"$passwd"
写了一个小脚本,方便净化环境