怎样进行MySQL5.7.17- Group Replication搭建

2025-06-22 17:06来源：互联网 [ 大中小 ]

背景：

mysql>INSTALL PLUGIN group_replication SONAME'group_replication.so'

mysql>SHOWPLUGINS;+----------------------------+----------+--------------------+----------------------+-------------+|Name|Status|Type|Library|License|+----------------------------+----------+--------------------+----------------------+-------------+|binlog|ACTIVE|STORAGEENGINE|NULL|PROPRIETARY|(...)|group_replication|ACTIVE|GROUPREPLICATION|group_replication.so|PROPRIETARY|+----------------------------+----------+--------------------+----------------------+-------------+

mysql> SET GLOBAL group_replication_bootstrap_group=ON;
mysql> START GROUP_REPLICATION;
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID| MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 550a7cbd-ccce-11e6-829a-a2d813521b2e | sh347|3307 | 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.01 sec)

3.1修改第二台数据库配置文件

[client]

port = 24802

socket = /home/data/mydata/3307/mysql.sock

[mysqld]

port = 24802

socket = /home/data/mydata/3307/mysql.sock

basedir = /home/data/mysql

datadir = /home/data/mydata/3307

pid-file = /home/data/mydata/3307/mysql.pid

##group replication####

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

transaction_write_set_extraction=XXHASH64

loose-group_replication_group_name = "77e497e2-c59a-11e6-9a68-525400159185"

loose-group_replication_start_on_boot=off

loose-group_replication_local_address="192.168.1.14:34062"

loose-group_replication_group_seeds="192.168.1.247:34061,192.168.1.14:34062,192.168.1.242:34063"

loose-group_replication_bootstrap_group=off

loose-group_replication_single_primary_mode=false

loose-group_replication_enforce_update_everywhere_checks=TRUE

3.2启动数据库进行配置

SET SQL_LOG_BIN=0;

CREATE USER rpl_user@'%';

GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'rpl_pass';

SET SQL_LOG_BIN=1;

CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery'

3.3 安装引擎，加入gp组

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

mysql> START GROUP_REPLICATION;

mysql> SELECT * FROM performance_schema.replication_group_members;

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

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

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

注意：与2操作相比少了两个步骤，千万别执行那两个步骤！那两个步骤是在搭建gp 初始化才需要执行的两个步骤

3.4检验数据

mysql> show databases;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| sys |

| test |

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

5 rows in set (0.00 sec)

mysql> select * from test.t1;

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

| c1 | c2 |

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

| 1 | Luis |

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

1 row in set (0.00 sec)

关闭247数据库

mysql> SELECT * FROM performance_schema.replication_group_members;

1 row in set (0.00 sec)

mysql> start GROUP_REPLICATION; ###重新打开即可

Query OK, 0 rows affected (3.50 sec)

mysql> SELECT * FROM performance_schema.replication_group_members;

2 rows in set (0.00 sec)

mysql> stop GROUP_REPLICATION;

Query OK, 0 rows affected (8.47 sec)

mysql> SELECT * FROM performance_schema.replication_group_members;

1 row in set (0.00 sec)

mysql> start GROUP_REPLICATION;

Query OK, 0 rows affected (5.49 sec)

mysql> SELECT * FROM performance_schema.replication_group_members;

2 rows in set (0.00 sec)

至此，数据库集群组搭建完毕！