mysql 8.0 MRG

MRG部署

1.首先对部署的节点配置好hosts文件,如下

172.16.227.157 weisir01

172.16.227.158 weisir02

172.16.227.159 weisir03

2.关掉防火墙，一定切记

3.配置资源准备

ip	port	role	备注
172.16.227.157	3311	主节点（primary）	端口对外提供数据请求
172.16.227.157	3301		端口作为集群通信端口
172.16.227.158	3312	从节点（secondary）	端口对外提供数据请求
172.16.227.158	3302		端口作为集群通信端口
172.16.227.159	3313	从节点（secondary）	端口对外提供数据请求
172.16.227.159	3303		端口作为集群通信端口

4.初始化数据（三个节点都执行）

PS：我这三个节点配置都相同，一条命令执行在三个机器

/usr/local/mysql80/bin/mysqld --no-defaults --initialize-insecure --user=mysql --basedir=/usr/local/mysql80 --datadir=/80/data

5.配置文件配置

 primary节点
[mysqld]
datadir=/80/data
basedir=/usr/local/mysql80
plugin_dir=/usr/local/mysql80/lib/plugin
port=3311
socket=/tmp/mysql80.sock
server_id=11
log-bin=/80/log/mysql-bin
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 = '172.16.227.157:3301'
loose-group_replication_group_seeds = '172.16.227.157:3301,172.16.227.158:3302,172.16.227.159:3303'
loose-group_replication_bootstrap_group = OFF
[mysql]
port=3311
socket=/tmp/mysql80.sock

secondary(172.16.227.158)
[mysqld]
datadir=/80/data
basedir=/usr/local/mysql80
plugin_dir=/usr/local/mysql80/lib/plugin
port=3312
socket=/tmp/mysql80.sock
server_id=12
log-bin=/80/log/mysql-bin
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 = '172.16.227.158:3302'
loose-group_replication_group_seeds = '172.16.227.157:3301,172.16.227.158:3302,172.16.227.159:3303'
loose-group_replication_bootstrap_group = OFF

[mysql]
port=3312
socket=/tmp/mysql80.sock


secondary(172.16.227.159)
[mysqld]
datadir=/80/data
basedir=/usr/local/mysql80
plugin_dir=/usr/local/mysql80/lib/plugin
port=3313
socket=/tmp/mysql80.sock
server_id=13
log-bin=/80/log/mysql-bin
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 = '172.16.227.159:3303'
loose-group_replication_group_seeds = '172.16.227.157:3301,172.16.227.158:3302,172.16.227.159:3303'
loose-group_replication_bootstrap_group = OFF

[mysql]
port=3313
socket=/tmp/mysql80.sock

6.启动实例

  /usr/local/mysql80/bin/mysqld --defaults-file=/80/conf/my.cnf &

当启动的时候可能会报错，根据提示需要用root用户启动

[root@weisir01 data] # 2021-04-19T08:44:44.003913Z 0 [System] [MY-010116] [Server] /usr/local/mysql80/bin/mysqld (mysqld 8.0.18) starting as process 1383
2021-04-19T08:44:44.019504Z 0 [ERROR] [MY-010123] [Server] Fatal error: Please read "Security" section of the manual to find out how to run mysqld as root!
2021-04-19T08:44:44.019547Z 0 [ERROR] [MY-010119] [Server] Aborting
2021-04-19T08:44:44.019760Z 0 [System] [MY-010910] [Server] /usr/local/mysql80/bin/mysqld: Shutdown complete (mysqld 8.0.18)  MySQL Community Server - GPL.

然后就在命令行配上，启动成功了

/usr/local/mysql80/bin/mysqld --defaults-file=/80/conf/my.cnf --user=root &

[root@weisir01 data] # 2021-04-19T08:47:16.097975Z 0 [System] [MY-010116] [Server] /usr/local/mysql80/bin/mysqld (mysqld 8.0.18) starting as process 1506
2021-04-19T08:47:17.704598Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2021-04-19T08:47:17.709934Z 0 [Warning] [MY-000067] [Server] unknown variable 'loose-group_replication_recovery_use_ssl=ON'.
2021-04-19T08:47:17.710093Z 0 [Warning] [MY-000067] [Server] unknown variable 'loose-group_replication_group_name=5dbabbe6-8050-49a0-9131-1de449167446'.
2021-04-19T08:47:17.710252Z 0 [Warning] [MY-000067] [Server] unknown variable 'loose-group_replication_start_on_boot=OFF'.
2021-04-19T08:47:17.710384Z 0 [Warning] [MY-000067] [Server] unknown variable 'loose-group_replication_local_address=172.16.227.157:3301'.
2021-04-19T08:47:17.710539Z 0 [Warning] [MY-000067] [Server] unknown variable 'loose-group_replication_group_seeds=172.16.227.157:3301,172.16.227.158:3302,172.16.227.159:3303'.
2021-04-19T08:47:17.710674Z 0 [Warning] [MY-000067] [Server] unknown variable 'loose-group_replication_bootstrap_group=OFF'.
2021-04-19T08:47:17.729800Z 0 [System] [MY-010931] [Server] /usr/local/mysql80/bin/mysqld: ready for connections. Version: '8.0.18'  socket: '/tmp/mysql80.sock'  port: 3311  MySQL Community Server - GPL.
2021-04-19T08:47:17.874472Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/tmp/mysqlx.sock' bind-address: '::' port: 33060

前面是基础，后面才是重头戏，来吧，让我们接着往下走，嗨起来
在启动的时候也可以把输出信息追加到一个文件中

mysqld --defaults-file=/80/conf/my.cnf  --user=root > /tmp/primary_output.log 2>&1 &

如果不加的话，每次有信息输出都会打印在屏幕上，看着心烦，😂
7.安装复制插件（所有节点）

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;

8.创建复制组用户（所有节点）

在启动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;

9.在primary上启动复制（primary上执行）

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

set global group_replication_bootstrap_group=ON;

start group_replication;

10.连接secondaries到primary节点（secondaries上执行）

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

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

11.在secondaries上启动组复制

mysql> start group_replication;

12.确认集群状态

select * from performance_schema.replication_group_members\G;

13.关闭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)

查看集群状态：（每个节点都可以）

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 | 195ef6aa-a0eb-11eb-b7ba-000c29070575 | weisir01    |        3311 | ONLINE       | PRIMARY     | 8.0.18         |
| group_replication_applier | 263d17b3-a0eb-11eb-b0c8-000c296885dd | weisir02    |        3312 | ONLINE       | SECONDARY   | 8.0.18         |
| group_replication_applier | 2c63b397-a0eb-11eb-bb42-000c2950f4c9 | weisir03    |        3313 | ONLINE       | SECONDARY   | 8.0.18         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

如果以上每个步骤都执行了，一般都不会出错误，我是踩了一天的坑部署好的，第一次部署的时候是hosts没有配，报错，第二次报错的时候是因为。。。。我也忘了，第三次报错是因为主节点没有开启组复制（SET GLOBAL group_replication_bootstrap_group=ON;）第四次部署报错是因为没有关闭防火墙，踩了一遍又一遍的坑