1.环境准备(已关闭防火墙和selinux)
1.1 服务器列表
1.2 修改3台服务器的hosts文件,否则会报错解析不到node1,原因参考(https://www.cnblogs.com/zhangdapangzo/p/16888205.html)
2.分别创建3个mysql实例
2.1 这里使用host network,目的是为了保证性能最大
3.根据官方文档创建my.cnf文件(https://dev.mysql.com/doc/refman/8.0/en/group-replication.html,各属性值解释也有)
3.1 vim /home/workspace/mysql/conf/my.cnf,按需求更改3个节点的值
分别重启docker实例,docker restart mgrNode1/mgrNode2/mgrNode3
4.进入docker容器,进行设置
4.1 docker exec -it mgrNode1 /bin/bash
mysql -uroot -p123456
4.2 在3台服务器上创建用于组复制的用户
mysql> SET SQL_LOG_BIN=0;
mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'password';
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
mysql> GRANT CONNECTION_ADMIN ON *.* TO rpl_user@'%';
mysql> GRANT BACKUP_ADMIN ON *.* TO rpl_user@'%';
mysql> GRANT GROUP_REPLICATION_STREAM ON *.* TO rpl_user@'%';
mysql> FLUSH PRIVILEGES;
mysql> SET SQL_LOG_BIN=1;
CHANGE REPLICATION SOURCE TO SOURCE_USER='rpl_user', SOURCE_PASSWORD='password' FOR CHANNEL 'group_replication_recovery';
4.3 安装组复制插件(mysql8.0.27默认已安装,如果没有,使用下面命令进行安装)
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
mysql> SHOW PLUGINS;
4.4 引导组复制(只在主节点进行)
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
mysql> START GROUP_REPLICATION USER='rpl_user', PASSWORD='password';
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
4.5 创建测试数据库
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');
4.6 将第二第三个实例添加到组
START GROUP_REPLICATION USER='rpl_user', PASSWORD='password';
4.7 查看组复制成员表信息performance_schema.replication_group_members,如果都为online则表示成功
SELECT * FROM performance_schema.replication_group_members;
5. 搭建过程中的错误
5.1 若主库启动报如下错:
Plugin group_replication reported: '[GCS] The member was unable to join the group. Local port: 33061'
解决方案:查看防火墙和selinux是否关闭
5.2 若从节点state值一直为RECOVERING,同时查看日志报如下错:
Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. Error_code: MY-002061
报错原因:由于mysql8.0之后加密规则变成 caching_sha2_password,所以使用MGR方式复制时,需要打开公钥访问
解决方案:在每个从节点执行如下操作
mysql> STOP GROUP_REPLICATION;
mysql> SET GLOBAL group_replication_recovery_get_public_key=ON;
mysql> START GROUP_REPLICATION;
5.3 若从节点报错
Slave SQL for channel 'group_replication_recovery': Error 'Can't create database 'test'; database exists' on query. Default database: 'test'. Query: 'CREATE DATABASE `test` /*!40100 COLLATE 'utf8mb4_general_ci' */', Error_code: MY-001007
解决方案:在从节点执行如下操作
mysql> STOP GROUP_REPLICATION;
mysql> set global super_read_only=0;
mysql> drop database test;
mysql> set global super_read_only=1;
mysql> START GROUP_REPLICATION;
mysql> SELECT * FROM performance_schema.replication_group_members;
5.4 若从库节点报错
Plugin group_replication reported: 'This member has more executed transactions than those present in the group. Local transactions: 6ef62e0a-1614-11ec-84c1-000c29e49c19:1-3, aadaaaaa-adda-adda-aaaa-aaaaaaddaaaa:1-7 > Group transactions: aadaaaaa-adda-adda-aaaa-aaaaaaddaaaa:1-2'
Plugin group_replication reported: 'The member contains transactions not present in the group. The member will now exit the group.'
解决方案:在从节点执行如下操作
mysql> STOP GROUP_REPLICATION;
mysql> RESET MASTER;
mysql> START GROUP_REPLICATION;
mysql> SELECT * FROM performance_schema.replication_group_members;