Mysql8 MGR安装

1.版本选择

centos 8 会在 2021 年底结束，centos 7  会持续支持至 2024 年

mysql选择 8 的版本，mysql 8 新增了很多新特性，对运行效率及稳定性有 很大提高，安装包直接在 mysql 官网下载，无需登录。

2.节点信息

IP	Hostname	Server id	port	MGR port
192.168.31.211	node1	1	3306	33061
192.168.31.212	node2	2	3306	33062
192.168.31.213	node3	3	3306	33063

3.Mysql 配置文件修改安装

   

参考centos 7 安装 mysql 8_centosmysql添加环境变量-CSDN博客

在/etc/my.cnf 配置文件中添加如下参数：

binlog-ignore-db需要注释掉
 

     

plugin_load_add =group_replication.so
plugin_load_add =mysql_clone.so

transaction-write-set-extraction=XXHASH64  #此参数会废弃，可以不用添加

loose-group_replication_group_name='ce9be252-2b71-11e6-b8f4-00212844f856'

loose-group_replication_start_on_boot=off

##需要在不同节点修改成对应的配置hostname:MGR port

loose-group_replication_local_address='node1:33061'        

loose-group_replication_group_seeds='node1:33061,node2:33062,node3:33063'

loose-group_replication_bootstrap_group=off

binlog_transaction_dependency_tracking=WRITESET

     在/etc/hosts加入

192.168.31.211 node1

192.168.31.212 node2

192.168.31.213 node3

4.配置mgr

  分别再三个节点的建立复制用户

mysql> set sql_log_bin=0;

Query OK, 0 rows affected (0.00 sec)

mysql> create user repl@'%'   identified with mysql_native_password by 'repl';

Query OK, 0 rows affected (0.12 sec)

mysql> grant replication slave on *.* to repl;

Query OK, 0 rows affected (0.11 sec)

mysql> grant backup_admin on *.* to repl;

Query OK, 0 rows affected (0.10 sec)

mysql> set sql_log_bin=1;

Query OK, 0 rows affected (0.00 sec)

mysql> change replication source to source_user='repl',source_password='repl' for channel 'group_replication_recovery';

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

  

   第一次启动组时需使用group_replication_bootstrap_group系统变量引导组，该操作只能在单个服务器完成，并且只能启动一次，在node1节点进行引导：

  mysql> set global group_replication_bootstrap_group=on;

Query OK, 0 rows affected (0.00 sec)

mysql> start group_replication;

Query OK, 0 rows affected (1.29 sec)

mysql> set global group_replication_bootstrap_group=off;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from performance_schema.replication_group_members\G;

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

              CHANNEL_NAME: group_replication_applier

                 MEMBER_ID: 45db419c-c5dc-11ec-86bc-0800275f8117

               MEMBER_HOST: node1

               MEMBER_PORT: 3306

              MEMBER_STATE: ONLINE

               MEMBER_ROLE: PRIMARY

            MEMBER_VERSION: 8.0.27

MEMBER_COMMUNICATION_STACK: XCom

启动node2加入集群

启动start group_replication时可能会报错：

[ERROR] [MY-011526] [Repl] Plugin group_replication reported: 'This member has more executed transactions than those present in the group. Local transactions: 33b23f51-4de3-11ee-a277-00505698f3cc:1 > Group transactions: 1ce186da-68f1-5e5e-8e69-fe8bc6d3811d:1, 81804d08-4d2a-11ee-8668-00505698f4a2:1'

运行reset master即可

mysql> reset master;
Query OK, 0 rows affected (0.01 sec)
 

mysql> change replication source to source_user='repl',source_password='repl'  for channel 'group_replication_recovery';

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

mysql> start group_replication;

Query OK, 0 rows affected (6.95 sec)

启动node3加入集群：

mysql> change replication source to source_user='repl',source_password='repl'  for channel 'group_replication_recovery';

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

mysql> start group_replication;

Query OK, 0 rows affected (6.95 sec)

查看集群信息：

mysql> select * from performance_schema.replication_group_members\G;

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

              CHANNEL_NAME: group_replication_applier

                 MEMBER_ID: 45db419c-c5dc-11ec-86bc-0800275f8117

               MEMBER_HOST: node1

               MEMBER_PORT: 3306

              MEMBER_STATE: ONLINE

               MEMBER_ROLE: PRIMARY

            MEMBER_VERSION: 8.0.27

MEMBER_COMMUNICATION_STACK: XCom

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

              CHANNEL_NAME: group_replication_applier

                 MEMBER_ID: 7c681e6e-c5f4-11ec-9033-0800275f8117

               MEMBER_HOST: node3

               MEMBER_PORT: 3306

              MEMBER_STATE: RECOVERING

               MEMBER_ROLE: SECONDARY

            MEMBER_VERSION: 8.0.27

MEMBER_COMMUNICATION_STACK: XCom

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

              CHANNEL_NAME: group_replication_applier

                 MEMBER_ID: 95c0e1b7-c5f4-11ec-956f-0800275f8117

               MEMBER_HOST: node2

               MEMBER_PORT: 3306

              MEMBER_STATE: ONLINE

               MEMBER_ROLE: SECONDARY

            MEMBER_VERSION: 8.0.27

MEMBER_COMMUNICATION_STACK: XCom

3 rows in set (0.00 sec)

至此安装完毕

4.注意事项

1. 建议在搭建完成后再/etc/my.cnf配置文件中加入super_read_only=ON，防止宕机节点启动后，应用节点自动连接至宕机节点
2. 使用select group_replication_set_as_primary('2c242b61-68ca-11ee-902b-00505698f4a2')来切换主节点