mysql8 复制组的问题_mysql 8.0 MGR组复制配置

一、配置组复制的步骤

1、初始化数据目录

2、配置主服务器(primary)

3、配置辅助服务器(secondaries)

4、启动mysql实例

5、安装组复制插件(primary and secondaries)

6、创建复制用户(replication user)的用户

7、在主服务器上启动组复制

8、将辅助(secondaries)服务器连接到主(primary)服务器

9、确认组复制的状态

二、配置过程

1、初始化数据目录

[mysql@node01 ~]$ mysqld --no-defaults --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql3306/data

[mysql@node02 ~]$ mysqld --no-defaults --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql3306/data

[mysql@node03 ~]$ mysqld --no-defaults --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql3306/data

2、配置primary

配置primary节点，我们需要准备好数据目录、端口，GTID以及组复制相关参数选项，还要添加plugin目录确保服务器可以找到组复制相关的插件，并且要打开binlog的日志校验。

因为group_replication_group_seeds参数需要配置组复制初始参与的服务器列表，所以必须提前决定每个服务器将要使用的端口。组复制设置中每个服务器需要配置两个端

口，一个用于客户端连接，一个用于内部组复制的消息通信。

node01:

[mysqld]

datadir=/data/mysql3306/data

basedir=/usr/local/mysql

plugin_dir=/usr/local/mysql/lib/plugin

port=24801

socket=/tmp/mysql.sock

server_id=1

gtid_mode= ON

enforce_gtid_consistency= ON

binlog_checksum=NONE

transaction_write_set_extraction = XXHASH64

loose-group_replication_recovery_use_ssl= ON

loose-group_replication_group_name = '5dbabbe6-8050-49a0-9131-1de449167446'

loose-group_replication_start_on_boot = OFF

loose-group_replication_local_address = '192.168.211.128:24901'

loose-group_replication_group_seeds = '192.168.211.128:24901,192.168.211.129:24902,192.168.211.130:24903'

loose-group_replication_bootstrap_group = OFF

[mysql]

port=24801

socket=/tmp/mysql.sock

这里参数文件没有配置log-bin参数，因为当服务器遇到组复制参数时，它将自动启用binlog，binlog在组复制里面时是必须的。如果你需要命名binlog文件或者将binlog定向

存储到某个目录中，那么需要配置log-bin参数。

3、配置secondaries

secondaries节点的配置与primary节点类似，区别在于对于特定参数(端口信息，数据目录，套接字，服务器ID)等需要设置成特定的值。除此之外，transaction_write_set_extraction参数

只在primary节点上设置，对于secondaries节点，我们添加group_replication_recovery_get_public_key参数并将其设置为ON。这个参数决定，primary节点到secondaries节点的请求是

否为基于RSA密钥对的密码交换所需的公钥。

node02:

[mysqld]

datadir=/data/mysql3306/data

log-bin=/data/mysql3306/binlog

log_slave_updates = ON

basedir=/usr/local/mysql

plugin_dir=/usr/local/mysql/lib/plugin

port=24802

socket=/tmp/mysql.sock

server_id=2

gtid_mode= ON

enforce_gtid_consistency= ON

binlog_checksum=NONE

loose-group_replication_recovery_get_public_key= ON

loose-group_replication_recovery_use_ssl= ON

loose-group_replication_group_name = '5dbabbe6-8050-49a0-9131-1de449167446'

loose-group_replication_start_on_boot = OFF

loose-group_replication_local_address = '192.168.211.129:24902'

loose-group_replication_group_seeds = '192.168.211.128:24901,192.168.211.129:24902,192.168.211.130:24903'

loose-group_replication_bootstrap_group = OFF

[mysql]

port=24802

socket=/tmp/mysql.sock

node03:

[mysqld]

datadir=/data/mysql3306/data

log-bin=/data/mysql3306/binlog

log_slave_updates = ON

basedir=/usr/local/mysql

plugin_dir=/usr/local/mysql/lib/plugin

port=24803

socket=/tmp/mysql.sock

server_id=3

gtid_mode= ON

enforce_gtid_consistency= ON

binlog_checksum=NONE

loose-group_replication_recovery_get_public_key= ON

loose-group_replication_recovery_use_ssl= ON

loose-group_replication_group_name = '5dbabbe6-8050-49a0-9131-1de449167446'

loose-group_replication_start_on_boot = OFF

loose-group_replication_local_address = '192.168.211.130:24903'

loose-group_replication_group_seeds = '192.168.211.128:24901,192.168.211.129:24902,192.168.211.130:24903'

loose-group_replication_bootstrap_group = OFF

[mysql]

port=24803

socket=/tmp/mysql.sock

4、启动实例

[mysql@node01 ~]$ mysqld --defaults-file=/etc/primary.cnf &

[mysql@node02 ~]$ mysqld --defaults-file=/etc/slave1.cnf &

[mysql@node03 ~]$ mysqld --defaults-file=/etc/slave2.cnf &

启动实例并且输出日志：

$ mysqld --defaults-file=/etc/primary.cnf > primary_output.log 2>&1 &

$ mysqld --defaults-file=/etc/slave1.cnf > slave1_output.log 2>&1 &

$ mysqld --defaults-file=/etc/slave2.cnf > slave2_output.log 2>&1 &

5、安装组复制插件(所有节点)

mysql实例启动后，必须安装组复制插件。

mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';

mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';

mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';

查看插件状态show plugins，或者从information_schema.plugins系统表中查询插件信息。

select * from information_schema.plugins where plugin_name = 'group_replication'G;

6、创建复制组用户(所有节点)

在启动mysql实例之后，必须创建一个用户，供服务器用于彼此连接。在组复制中，服务器都是互相“交流”的。这些命令与我们在mysql复制中使用的命令相同，我们需要在所有服务器

实例上创建这个用户。

set sql_log_bin=0;

create user rpl@'%' identified by 'mysql';

grant replication slave on *.* to rpl@'%';

flush privileges;

set sql_log_bin=1;

7、在primary上启动复制(primary上执行)

group_replication_bootstrap_group参数通常在第一次启动的时候设置成OFF，因为复制组还没有启动，我们在primary上启动复制组。参数group_replication_boostrap_group是可以动态设置的，我们可以动态的打开和关闭这个参数。

set global group_replication_bootstrap_group=ON;

start group_replication;

set global group_replication_bootstrap_group=OFF;

8、连接secondaries到primary节点(secondaries上执行)

mysql> change master to master_user="rpl",master_password='mysql' for channel 'group_replication_recovery';

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

现在我们已经将辅助设备配置为连接到主服务器，但是连接还没建立，我们必须通过启动组复制来完成此过程。

9、在secondaries上启动组复制

mysql> start group_replication;

2020-09-29T00:17:15.035829Z 10 [System] [MY-010597] [Repl] 'CHANGE MASTER TO FOR CHANNEL 'group_repl ication_applier' executed'. Previous state master

_host='', master_port= 3306, master_log_file='', ma ster_log_pos= 4, master_bind=''. New state master_host='', master_port= 0, master_log_file=

'', master_log_pos= 4, master_bind=''.

Query OK, 0 rows affected (4.30 sec)

mysql> 2020-09-29T00:17:19.332065Z 17 [System] [MY-010597] [Repl] 'CHANGE MASTER TO FOR CHANNEL 'gro up_replication_recovery' executed'. Previous state

master_host='', master_port= 3306, master_log_fil e='', master_log_pos= 4, master_bind=''. New state master_host='node01', master_port= 24801, master_

log_file='', master_log_pos= 4, master_bind=''.

2020-09-29T00:17:19.375174Z 18 [Warning] [MY-010897] [Repl] Storing MySQL user name or password info rmation in the master info repository is not secure

and is therefore not recommended. Please conside r using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the

MySQL Manual for more information.

2020-09-29T00:17:19.395921Z 18 [System] [MY-010562] [Repl] Slave I/O thread for channel 'group_repli cation_recovery': connected to master 'rpl@node01:24

801',replication started in log 'FIRST' at posit ion 4

2020-09-29T00:17:19.511547Z 17 [System] [MY-010597] [Repl] 'CHANGE MASTER TO FOR CHANNEL 'group_repl ication_recovery' executed'. Previous state master_ho

st='node01', master_port= 24801, master_log_fil e='', master_log_pos= 4, master_bind=''. New state master_host='', master_port= 0, master_log_ file=

'', master_log_pos= 4, master_bind=''.

start group_replication命令通常不会报告任何错误，并且可能需要更长的时间才能返回，这是因为当secondaries连接到主节点并开始与主节点协调时，很多工作都是在后台进行的。

10、确认集群状态

select * from performance_schema.replication_group_membersG;

mysql> select * from performance_schema.replication_group_membersG;

*************************** 1. row ***************************

CHANNEL_NAME: group_replication_applier

MEMBER_ID: 577274f9-00e7-11eb-89e9-00505625f5f5

MEMBER_HOST: node01

MEMBER_PORT: 24801

MEMBER_STATE: ONLINE

MEMBER_ROLE: PRIMARY

MEMBER_VERSION: 8.0.18

*************************** 2. row ***************************

CHANNEL_NAME: group_replication_applier

MEMBER_ID: 75ad0e91-00e7-11eb-a505-005056320441

MEMBER_HOST: node02

MEMBER_PORT: 24802

MEMBER_STATE: ONLINE

MEMBER_ROLE: SECONDARY

MEMBER_VERSION: 8.0.18

*************************** 3. row ***************************

CHANNEL_NAME: group_replication_applier

MEMBER_ID: 7bf9c4cb-00e7-11eb-8cc3-00505625d41f

MEMBER_HOST: node03

MEMBER_PORT: 24803

MEMBER_STATE: ONLINE

MEMBER_ROLE: SECONDARY

MEMBER_VERSION: 8.0.18

3 rows in set (0.01 sec)

ERROR:

No query specified

10、关闭MGR集群(secondaries先，primary后)

node02:

mysql> stop group_replication;

Query OK, 0 rows affected (4.68 sec)

node03:

mysql> stop group_replication;

Query OK, 0 rows affected (4.68 sec)

node01:

mysql> stop group_replication;

Query OK, 0 rows affected (5.17 sec)

开启MGR集群(primary先，secondaries后)

node01：

mysql> SET GLOBAL group_replication_bootstrap_group=ON;

Query OK, 0 rows affected (0.00 sec)

node02：

mysql> start group_replication;

node03：

mysql> start group_replication;

node01：

mysql> SET GLOBAL group_replication_bootstrap_group=OFF;

Query OK, 0 rows affected (0.00 sec)

node01查看集群状态：

mysql> select * from performance_schema.replication_group_members;

+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+

| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |

+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+

| group_replication_applier | 577274f9-00e7-11eb-89e9-00505625f5f5 | node01 | 24801 | ONLINE | PRIMARY | 8.0.18 |

| group_replication_applier | 75ad0e91-00e7-11eb-a505-005056320441 | node02 | 24802 | ONLINE | SECONDARY | 8.0.18 |

| group_replication_applier | 7bf9c4cb-00e7-11eb-8cc3-00505625d41f | node03 | 24803 | ONLINE | SECONDARY | 8.0.18 |

+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+

3 rows in set (0.00 sec)