开始

基础环境

IP	node
192.168.0.9	s1
192.168.0.6	s2
192.168.0.3	s3

安装mysql

至少三个 MySQL 服务器实例，最大实例数是九个分别在3个节点安装myslq

安装教程

组复制配置实例

s1配置

配置my.cnf

# on s1 add
[mysqld]
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
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

plugin_load_add='group_replication.so'
transaction_write_set_extraction=XXHASH64
group_replication_group_name="7af32843-ac0d-11ec-90d7-000c29156cda"
group_replication_start_on_boot=off
group_replication_local_address= "192.168.0.9:33061"
group_replication_group_seeds= "192.168.0.9:33061,192.168.0.6:33061,192.168.0.3:33061"
report_host=192.168.0.9
#report_port=3306
group_replication_bootstrap_group=off

保存重启s1,systemctl restart mysqld.service

创建s1用户凭证

# on s1
mysql> set sql_log_bin=0; 
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'password';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'MyNewPass4!';
Query OK, 0 rows affected (0.00 sec)

mysql>  GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> set sql_sql_log_bin = 1;  
ERROR 1193 (HY000): Unknown system variable 'sql_sql_log_bin'
mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='MyNewPass4!' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.01 sec)

引导组s1启动

# on s1
mysql> set global group_replication_bootstrap_group = ON;
Query OK, 0 rows affected (0.00 sec)

#可不执行
mysql> set global group_replication_ip_whitelist = '192.168.0.9,192.168.0.6,192.168.0.3';
Query OK, 0 rows affected (0.00 sec)

#可不执行
mysql> set global group_replication_ip_whitelist = '192.168.0.9/24';
Query OK, 0 rows affected (0.00 sec)

mysql> start group_replication;
Query OK, 0 rows affected (2.06 sec)

mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)

问题1

执行start group_replication;异常

mysql> start group_replication;
ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.

解决1

# 查看错误日志
[root@localhost ]# cd /var/log/
[root@localhost log]# tail -f mysqld.log 
xx
2022-03-25T08:09:10.428585Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error connecting to the local group communication engine instance.'
2022-03-25T08:09:10.456843Z 0 [ERROR] Plugin group_replication reported: '[GCS] The member was unable to join the group. Local port: 33061'
2022-03-25T08:10:00.424339Z 3 [ERROR] Plugin group_replication reported: 'Timeout on wait for view after joining group'
2022-03-25T08:10:00.424437Z 3 [Note] Plugin group_replication reported: 'Requesting to leave the group despite of not being a member'
2022-03-25T08:10:00.424472Z 3 [ERROR] Plugin group_replication reported: '[GCS] The member is leaving a group without being on one.'

xxx

#执行解决 on s1,s2,s3
[root@localhost ~]#  setenforce 0

查看组成员

# on s1
# 此表中的信息确认组中有一个具有唯一标识符的成员 `5f7c64f7-ac0a-11ec-a86e-000c29156cda`，它正在`ONLINE`并且正在`s1` 侦听端口上的客户端连接 `3306`

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+---------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST   | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+---------------+-------------+--------------+
| group_replication_applier | 5f7c64f7-ac0a-11ec-a86e-000c29156cda | 192.168.0.9 |        3306 | ONLINE       |
+---------------------------+--------------------------------------+---------------+-------------+--------------+
1 row in set (0.00 sec)

测试

# 创建库和表，等待测试
mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.00 sec)

mysql> USE test;
Database changed
mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1 VALUES (1, 'Luis');
Query OK, 1 row affected (0.02 sec)

s2配置

添加第二个实例，服务器 s2，首先为其创建配置文件。配置类似于用于服务器 s1 的配置，除了 server_id

配置my.cnf

# on s2 add
[mysqld]
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
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

plugin_load_add='group_replication.so'
transaction_write_set_extraction=XXHASH64
group_replication_group_name="7af32843-ac0d-11ec-90d7-000c29156cda"
group_replication_start_on_boot=off
group_replication_local_address= "192.168.0.6:33061"
group_replication_group_seeds= "192.168.0.9:33061,192.168.0.6:33061,192.168.0.3:33061"
report_host=192.168.0.6
group_replication_bootstrap_group=off

保存重启s2,systemctl restart mysqld.service

创建s2用户凭证

mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'MyNewPass4!';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)

mysql>  CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='MyNewPass4!' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.01 sec)

引导组s2启动

与之前在 s1 上执行的步骤不同，在启动组复制之前不要发出SET GLOBAL group_replication_bootstrap_group=ON;，因为组已经由服务器 s1 创建和引导。此时服务器 s2 只需添加到已经存在的组中

mysql> set global group_replication_allow_local_disjoint_gtids_join = ON;  
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (5.80 sec)

查看组成员

mysql>  select * from performance_schema.replication_group_members;  
+---------------------------+--------------------------------------+---------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST   | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+---------------+-------------+--------------+
| group_replication_applier | 5f7c64f7-ac0a-11ec-a86e-000c29156cda | 192.168.0.9 |        3306 | ONLINE       |
| group_replication_applier | 7f6d9f5e-ac0a-11ec-8e84-000c29e12c86 | 192.168.0.6 |        3306 | ONLINE       |
+---------------------------+--------------------------------------+---------------+-------------+--------------+
2 rows in set (0.00 sec)

验证

当 s2 尝试加入组时，确保 s2 应用了与 s1 应用的相同的事务。一旦这个过程完成，s2 就可以作为成员加入该组，此时它被标记为 ONLINE。换句话说，它一定已经自动赶上了服务器 s1。一旦 s2 在线，它就会开始处理与该组的事务。验证 s2 确实与服务器 s1 同步

# on s2
mysql> SHOW DATABASES LIKE 'test';
+-----------------+
| Database (test) |
+-----------------+
| test            |
+-----------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM test.t1;
+----+------+
| c1 | c2   |
+----+------+
|  1 | Luis |
+----+------+
1 row in set (0.01 sec)

s3配置

# on s3 add
[mysqld]
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id=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="7af32843-ac0d-11ec-90d7-000c29156cda"
group_replication_start_on_boot=off
group_replication_local_address= "192.168.0.3:33061"
group_replication_group_seeds= "192.168.0.9:33061,192.168.0.6:33061,192.168.0.3:33061"
report_host = 192.168.0.3
group_replication_bootstrap_group=off

操作s3

s3和s2一样，参照s2即可


mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

mysql>  CREATE USER rpl_user@'%' IDENTIFIED BY 'MyNewPass4!';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.00 sec)

mysql>  FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql>  SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='MyNewPass4!' FOR CHANNEL 'group_replication_recovery';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    2
Current database: *** NONE ***

Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='MyNewPass4!' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> set global group_replication_allow_local_disjoint_gtids_join = ON;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>  START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (3.24 sec)

mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+---------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST   | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+---------------+-------------+--------------+
| group_replication_applier | 5f182e81-ac0a-11ec-b83c-000c29ff42eb | 192.168.0.3 |        3306 | ONLINE       |
| group_replication_applier | 5f7c64f7-ac0a-11ec-a86e-000c29156cda | 192.168.0.9 |        3306 | ONLINE       |
| group_replication_applier | 7f6d9f5e-ac0a-11ec-8e84-000c29e12c86 | 192.168.0.6 |        3306 | ONLINE       |
+---------------------------+--------------------------------------+---------------+-------------+--------------+
3 rows in set (0.00 sec)

单主模式和多主模式

默认模式是单主模式，在单主模式下部署时，此选项group_replication_enforce_update_everywhere_checks必须设置为 FALSE

单主模式

在这种模式下，组有一个设置为读写模式的单主服务器。组中的所有其他成员都设置为只读模式（带有 super-read-only=ON ）。这会自动发生。主服务器通常是引导组的第一个服务器，所有其他加入的服务器会自动了解主服务器并设置为只读

在主要成员失败时，自动主要选举机制会选择新的主要成员。选举过程是通过查看新视图并根据的值对潜在的新初选进行排序来执行的 group_replication_member_weight。假设组的所有成员都运行相同的 MySQL 版本，那么具有最高值的成员 group_replication_member_weight 被选为新的主节点。如果多个服务器具有相同 group_replication_member_weight的，则服务器将根据它们 server_uuid的字典顺序并通过选择第一个来确定优先级。选出新的主节点后，它会自动设置为读写，其他从节点保持为从节点，因此是只读的

多主模式

在多主模式下，没有单主的概念。无需进行选举程序，因为没有服务器扮演任何特殊角色，加入组时，所有服务器都设置为读写模式。

单主模式切换多主模式

# on s1,s2,s3
mysql>  stop group_replication;  
Query OK, 0 rows affected (9.54 sec)

# on s1,s2,s3
mysql> set global group_replication_single_primary_mode=OFF;  
Query OK, 0 rows affected (0.00 sec)

# on s1,s2,s3
mysql> set global group_replication_enforce_update_everywhere_checks=ON; 
Query OK, 0 rows affected (0.00 sec)

# 假设 on s1
mysql> SET GLOBAL group_replication_bootstrap_group=ON;  
Query OK, 0 rows affected (0.00 sec)

# on s1
mysql> START GROUP_REPLICATION;  
Query OK, 0 rows affected (2.02 sec)

# on s1
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)

 # on s2,s3
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (5.94 sec)

多主模式切换单主模式

# on s1,s2,s3
mysql> stop group_replication;  
Query OK, 0 rows affected (9.05 sec)

# on s1,s2,s3
mysql> set global group_replication_enforce_update_everywhere_checks=OFF;
Query OK, 0 rows affected (0.00 sec)

# on s1,s2,s3
mysql> set global group_replication_single_primary_mode=ON;  
Query OK, 0 rows affected (0.00 sec)

# 假设on s1
mysql> SET GLOBAL group_replication_bootstrap_group=ON;  
Query OK, 0 rows affected (0.00 sec)

# on s1
mysql> START GROUP_REPLICATION;  
Query OK, 0 rows affected (2.02 sec)

# on s1
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;  
Query OK, 0 rows affected (0.00 sec)

# on s2，s3
mysql> START GROUP_REPLICATION;  

Query OK, 0 rows affected, 1 warning (6.00 sec)

CentOS7安装mysql5.7组复制

开始

基础环境

安装mysql

组复制配置实例

s1配置

配置my.cnf

创建s1用户凭证

引导组s1启动

问题1

解决1

查看组成员

测试

s2配置

配置my.cnf

创建s2用户凭证

引导组s2启动

查看组成员

验证

s3配置

操作s3

单主模式和多主模式

单主模式

多主模式

单主模式切换多主模式

多主模式切换单主模式