本文档通过看书总结，并个人测试后总结而得，有任何冒犯，请提出改正。
传统的MySQL高可用架构都是Master-Slave架构，无法解决选主问题，特别是出现脑裂时，导致数据库双写，这种情况是不允许发生的。
MGR集群要求MySQL实例至少三个，本实验环境用了三节点，数据库版本为MySQL 8.0.23，三节点需要配置/etc/hosts，用组间通信，同时建议关闭Iptables和Selinux
1、前期准备
系统：CentOS 6.8
数据库：MySQL 8.0.23
关闭iptables和selinux、配置/etc/hosts文件
节点信息：
172.16.117.10 MGR-1
172.16.117.11 MGR-2
172.16.117.12 MGR-3
2、准备参数文件
##MGR-1配置/etc/my.cnf
[root@MGR-1 ~]# cat /etc/my.cnf
######MGR集群参数配置#########
[mysqld]
#####通用选项#####
server-id=100 ##每个节点的server-id设置不同
max_allowed_packet = 16M
lower_case_table_names = 1 ##8.0初始化之后就不可以修改，需要重新初始化
basedir=/usr
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
#######innodb settings########
innodb_flush_log_at_trx_commit =1 ##设置为1立即写入日志文件并刷新，安全。设置为2性能好
innodb_buffer_pool_size=512M
#ssl
group_replication_recovery_get_public_key=ON
########replication复制配置#########
log-bin = mysql-bin
##从服务器将从主服务器上接收到的更新写入到本地的二进制文件中
log_slave_updates= ON
##sync_binlog改成1，更安全
##MGR使用乐观锁，官网建议的隔离级别是RC，减少锁粒度##
transaction_isolation = READ-COMMITTED
###MGR设置binlog格式为row###
binlog_format = row
##binlog检验规则，MGR要求是NONE##
binlog-checksum = NONE
##MGR使用的GTID##
gtid_mode = on
enforce_gtid_consistency = ON
##为了保证安全，将主从复制信息放到表中，MySQL 8.0默认放在TABLE###
#master_info_repository=TABLE
#relay_log_info_repository=TABLE
##每个事物收集写集合，使用XXHASH64哈希算法将其编码为散列##
transaction_write_set_extraction = XXHASH64
##MGR Group的名字，格式和server-uuid一致，不能和机器伤的mysql实例uuid重####
loose-group_replication_group_name='c9ce4d9c-7677-11eb-abb4-001c425ddfd5'
##IP地址白名单##
loose-group_replcation_ip_whitelist = '172.16.117.0/24'
##重启MySQL时，组复制不自动开启##
loose-group_replication_start_on_boot = off
loose-group_replication_bootstrap_group = off
##本地MGR实例控制的IP地址和端口号，这个是MGR的服务端口，不是数据库端口##
##要保证这个端口没有被占用，是MGR互相通信的端口##
loose-group_replication_local_address= '172.16.117.10:33061' ##自己主机所在的IP
##需要接受本MGR实例控制的服务器的IP地址和端口，此处是MGR的端口
loose-group_replication_group_seeds='172.16.117.10:33061,172.16.117.11:33061,172.16.117.12:33061'
##false为多主模式，true为单主
loose-group_replication_single_primary_mode = false
##多主模式，强制每一个实例进行冲突检测，不是多主可以关闭
loose-group_replication_enforce_update_everywhere_checks = true
###########配置完成################
##MGR-2配置/etc/my.cnf
[root@MGR-2 ~]# cat /etc/my.cnf
######MGR集群参数配置#########
[mysqld]
#####通用选项#####
server-id=101 ##每个节点的server-id设置不同
max_allowed_packet = 16M
lower_case_table_names = 1 ##8.0初始化之后就不可以修改，需要重新初始化
basedir=/usr
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
#######innodb settings########
innodb_flush_log_at_trx_commit =1 ##设置为1立即写入日志文件并刷新，安全。设置为2性能好
innodb_buffer_pool_size=512M
#ssl
group_replication_recovery_get_public_key=ON
########replication复制配置#########
log-bin = mysql-bin
##从服务器将从主服务器上接收到的更新写入到本地的二进制文件中
log_slave_updates= ON
##sync_binlog改成1，更安全
##MGR使用乐观锁，官网建议的隔离级别是RC，减少锁粒度##
transaction_isolation = READ-COMMITTED
###MGR设置binlog格式为row###
binlog_format = row
##binlog检验规则，MGR要求是NONE##
binlog-checksum = NONE
##MGR使用的GTID##
gtid_mode = on
enforce_gtid_consistency = ON
##为了保证安全，将主从复制信息放到表中，MySQL 8.0默认放在TABLE###
#master_info_repository=TABLE
#relay_log_info_repository=TABLE
##每个事物收集写集合，使用XXHASH64哈希算法将其编码为散列##
transaction_write_set_extraction = XXHASH64
##MGR Group的名字，格式和server-uuid一致，不能和机器伤的mysql实例uuid重####
loose-group_replication_group_name='c9ce4d9c-7677-11eb-abb4-001c425ddfd5'
##IP地址白名单##
loose-group_replcation_ip_whitelist = '172.16.117.0/24'
##重启MySQL时，组复制不自动开启##
loose-group_replication_start_on_boot = off
loose-group_replication_bootstrap_group = off
##本地MGR实例控制的IP地址和端口号，这个是MGR的服务端口，不是数据库端口##
##要保证这个端口没有被占用，是MGR互相通信的端口##
loose-group_replication_local_address= '172.16.117.11:33061' ##自己主机所在的IP
##需要接受本MGR实例控制的服务器的IP地址和端口，此处是MGR的端口
loose-group_replication_group_seeds='172.16.117.10:33061,172.16.117.11:33061,172.16.117.12:33061'
##false为多主模式，true为单主
loose-group_replication_single_primary_mode = false
##多主模式，强制每一个实例进行冲突检测，不是多主可以关闭
loose-group_replication_enforce_update_everywhere_checks = true
###########配置完成################
##MGR-3配置/etc/my.cnf
[root@MGR-3 ~]# cat /etc/my.cnf
######MGR集群参数配置#########
[mysqld]
#####通用选项#####
server-id=102 ##每个节点的server-id设置不同
max_allowed_packet = 16M
lower_case_table_names = 1 ##8.0初始化之后就不可以修改，需要重新初始化
basedir=/usr
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
#######innodb settings########
innodb_flush_log_at_trx_commit =1 ##设置为1立即写入日志文件并刷新，安全。设置为2性能好
innodb_buffer_pool_size=512M
#ssl
group_replication_recovery_get_public_key=ON
########replication复制配置#########
log-bin = mysql-bin
##从服务器将从主服务器上接收到的更新写入到本地的二进制文件中
log_slave_updates= ON
##sync_binlog改成1，更安全
##MGR使用乐观锁，官网建议的隔离级别是RC，减少锁粒度##
transaction_isolation = READ-COMMITTED
###MGR设置binlog格式为row###
binlog_format = row
##binlog检验规则，MGR要求是NONE##
binlog-checksum = NONE
##MGR使用的GTID##
gtid_mode = on
enforce_gtid_consistency = ON
##为了保证安全，将主从复制信息放到表中，MySQL 8.0默认放在TABLE###
#master_info_repository=TABLE
#relay_log_info_repository=TABLE
##每个事物收集写集合，使用XXHASH64哈希算法将其编码为散列##
transaction_write_set_extraction = XXHASH64
##MGR Group的名字，格式和server-uuid一致，不能和机器伤的mysql实例uuid重####
loose-group_replication_group_name='c9ce4d9c-7677-11eb-abb4-001c425ddfd5'
##IP地址白名单##
loose-group_replcation_ip_whitelist = '172.16.117.0/24'
##重启MySQL时，组复制不自动开启##
loose-group_replication_start_on_boot = off
loose-group_replication_bootstrap_group = off
##本地MGR实例控制的IP地址和端口号，这个是MGR的服务端口，不是数据库端口##
##要保证这个端口没有被占用，是MGR互相通信的端口##
loose-group_replication_local_address= '172.16.117.12:33061' ##自己主机所在的IP
##需要接受本MGR实例控制的服务器的IP地址和端口，此处是MGR的端口
loose-group_replication_group_seeds='172.16.117.10:33061,172.16.117.11:33061,172.16.117.12:33061'
##false为多主模式，true为单主
loose-group_replication_single_primary_mode = false
##多主模式，强制每一个实例进行冲突检测，不是多主可以关闭
loose-group_replication_enforce_update_everywhere_checks = true
###########配置完成################
3、启动数据库
##MGR-1
[root@MGR-1 ~]# /etc/init.d/mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
[root@MGR-1 ~]# mysql -uroot -proot@123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.23 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
##MGR-2
[root@MGR-2 ~]# /etc/init.d/mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
[root@MGR-2 ~]# mysql -uroot -proot@123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.23 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
##MGR-3
[root@MGR-3 ~]# /etc/init.d/mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
[root@MGR-3 ~]# mysql -uroot -proot@123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.23 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
4、创建复制账号
####创建用于同步的用户并授权，集群内所有节点都要做，注意⚠️：用户密码修改和创建用户需要设置binlog不记录，执行后再打开，否则会引起START GROUP_REPLICATION执行时报错
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
mysql> create user rep@'%' identified by 'rep';
Query OK, 0 rows affected (0.01 sec)
mysql> grant replication slave on *.* to rep@'%';
Query OK, 0 rows affected (0.01 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)
5、安装组复制插件
##集群内每个节点都需要安装组复制插件
mysql> install plugin group_replication soname 'group_replication.so';
Query OK, 0 rows affected (0.06 sec)
##查看数据库已经安装的插件
mysql> show plugins;
..
| group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL |
+---------------------------------+----------+--------------------+----------------------+---------+
6、创建同步规则认证信息
##集群中的每个节点都需要配置，MGR通过这个异步复制通道实现新节点加入集群自动从其他节点复制数据，不需要指定从那个节点复制，当一个成员会收到其他成员的配置信息。
mysql> change master to master_user='rep',master_password='rep' for channel 'group_replication_recovery';
Query OK, 0 rows affected, 5 warnings (0.02 sec)
7、启动MGR
##启动MGR需要先启动其中一个节点做引导，其他数据库才能顺利加入集群，特别是单主模式，主库须先启动并做引导。注意⚠️：只有首个节点开启引导初始化操作
MGR-1启动引导初始化
mysql> set global group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)
启动MGR
mysql> start group_replication;
Query OK, 0 rows affected (2.35 sec)
查看MGR启动是否成功，注意查看MEMBER_STATE字段状态是否为ONILINE
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 | 8a319c00-7680-11eb-9764-001c425ddfd5 | MGR-1 | 3306 | **ONLINE** | PRIMARY | 8.0.23 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
1 row in set (0.01 sec)
MGR-1启动成功后，关闭初始化引导操作
mysql> set global group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)
8、分别启动MGR集群的其他节点
##启动MGR-2、MGR-3
mysql> start group_replication;
Query OK, 0 rows affected (3.28 sec)
mysql> start group_replication;
Query OK, 0 rows affected (3.28 sec)
9、查看节点状态信息
10、集群功能测试
##MGR-1创建测试库及测试表，观察MGR-2、MGR-3是否同步
mysql> create database ccola;
Query OK, 1 row affected (0.04 sec)
mysql> use ccola;
Database changed
mysql> create table ccola (id int primary key,name varchar(20));
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql>
mysql> insert into ccola values (21,'ccola');
Query OK, 1 row affected (0.01 sec)
mysql> select * from ccola
-> ;
+----+-------+
| id | name |
+----+-------+
| 21 | ccola |
+----+-------+
1 row in set (0.00 sec)
MGR-2、MGR-3
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| ccola |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.01 sec)
mysql> use ccola;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from ccola;
+----+-------+
| id | name |
+----+-------+
| 21 | ccola |
+----+-------+
1 row in set (0.00 sec)
模拟MGR-2宕机，在MGR-3上插入数据，重新启动MGR-2
[root@MGR-2 ~]# /etc/init.d/mysqld stop
Stopping mysqld: [ OK ]
mysql> show variables like 'hostname';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| hostname | MGR-3 |
+---------------+-------+
1 row in set (0.01 sec)
mysql> use ccola;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from ccola;
+----+-------+
| id | name |
+----+-------+
| 21 | ccola |
+----+-------+
1 row in set (0.00 sec)
mysql> insert into ccola values(2,'yangyang');
Query OK, 1 row affected (0.02 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from ccola;
+----+----------+
| id | name |
+----+----------+
| 2 | yangyang |
| 21 | ccola |
+----+----------+
2 rows in set (0.00 sec)
启动MGR-2
[root@MGR-2 ~]# /etc/init.d/mysqld start
Starting mysqld: [ OK ]
[root@MGR-2 ~]# mysql -uroot -proot@123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.23 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> start group_replication;
Query OK, 0 rows affected (3.14 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 | 01062630-7681-11eb-a51f-001c4251db9a | MGR-2 | 3306 | ONLINE | PRIMARY | 8.0.23 |
| group_replication_applier | 8a319c00-7680-11eb-9764-001c425ddfd5 | MGR-1 | 3306 | ONLINE | PRIMARY | 8.0.23 |
| group_replication_applier | fb7c611c-7680-11eb-b90f-001c42a55598 | MGR-3 | 3306 | ONLINE | PRIMARY | 8.0.23 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)
mysql> use ccola;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from ccola;
+----+----------+
| id | name |
+----+----------+
| 2 | yangyang |
| 21 | ccola |
+----+----------+
2 rows in set (0.00 sec)
此时发现MGR-2重新加入组，并同步数据，测试完成。
相关查询语句：
##查看组成员信息及状态
*select * from performance_schema.replication_group_members;*
##查看组内同步情况及复制状态参数
*select * from performance_schema.replication_group_member_stats\G;*
##查看本节点执行队列是否有堆积
select count_transactions_in_queue from replication_group_member_stats where member_id=@@server_uuid;
扫一扫
724
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
