MySQL MGR(单主模式+多主模式)配置文档

拓扑图介绍：

单主模式

192.168.43.141 MGR01 （读写）

192.168.43.142 MGR02 （读）

192.168.43.143 MGR03 （读）

多主模式

192.168.43.141 MGR01 （读写）

192.168.43.142 MGR02 （读写）

192.168.43.143 MGR03 （读写）

OS : CentOS release 6.10 (Final) min

MySQL : mysql-5.7.31-1.el6.x86_64.rpm-bundle.tar

一、系统基本设置（所有节点）

1.iptables及selinux设置

[root@mgr01 opt]# /etc/init.d/iptables status

iptables: Firewall is not running.

[root@mgr01 opt]# chkconfig --list | grep iptables

iptables 0:off 1:off 2:off 3:off 4:off 5:off 6:off

[root@mgr01 opt]# getenforce

Disabled

2.ntp设置

[root@mgr01 opt]# chkconfig --list | grep ntpd

ntpd 0:off 1:off 2:on 3:on 4:on 5:on 6:off

ntpdate 0:off 1:off 2:off 3:off 4:off 5:off 6:off

[root@mgr01 opt]#

3./etc/hosts设置

[root@mgr01 opt]# cat /etc/hosts

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4

::1 localhost localhost.localdomain localhost6 localhost6.localdomain6

192.168.43.141 MGR01

192.168.43.142 MGR02

192.168.43.143 MGR03

[root@mgr01 opt]#

4.安装基本工具

[root@mgr01 opt]# yum install ntp vim wget net-tools mlocate openssh-* cc gcc make -y

二、安装MySQL（所有节点）

1.解压并安装

[root@mgr01 opt]# tar -xf mysql-5.7.31-1.el6.x86_64.rpm-bundle.tar

[root@mgr01 opt]# ls

mysql-5.7.31-1.el6.x86_64.rpm-bundle.tar

mysql-community-client-5.7.31-1.el6.x86_64.rpm

mysql-community-common-5.7.31-1.el6.x86_64.rpm

mysql-community-devel-5.7.31-1.el6.x86_64.rpm

mysql-community-embedded-5.7.31-1.el6.x86_64.rpm

mysql-community-embedded-devel-5.7.31-1.el6.x86_64.rpm

mysql-community-libs-5.7.31-1.el6.x86_64.rpm

mysql-community-libs-compat-5.7.31-1.el6.x86_64.rpm

mysql-community-server-5.7.31-1.el6.x86_64.rpm

mysql-community-test-5.7.31-1.el6.x86_64.rpm

[root@mgr01 opt]#

[root@mgr01 opt]# yum install -y mysql-community-*

[root@mgr01 opt]# /etc/init.d/mysqld start

Initializing MySQL database: [ OK ]

Starting mysqld: [ OK ]

[root@mgr01 opt]#

2.修改root密码

[root@mgr01 opt]# cat /var/log/mysqld.log|grep 'A temporary password'

2020-08-20T15:09:37.350937Z 1 [Note] A temporary password is generated for root@localhost: sl,:WvZDI9Bx

[root@mgr01 opt]# mysql -uroot -p'sl,:WvZDI9Bx'

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 3

Server version: 5.7.31

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> alter user 'root'@'localhost' identified by 'Test.123';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql>

三、配置MySQL

1.修改配置文件

MGR01上修改:

[root@mgr01 opt]# cat /etc/my.cnf

[mysqld]

datadir = /var/lib/mysql

socket = /var/lib/mysql/mysql.sock

symbolic-links = 0

log-error = /var/log/mysqld.log

pid-file = /var/run/mysqld/mysqld.pid

server_id=1

gtid_mode=ON

enforce_gtid_consistency=ON

binlog_checksum=NONE

log_bin=binlog

log_slave_updates=ON

binlog_format=ROW

master_info_repository=TABLE

relay_log_info_repository=TABLE

transaction_write_set_extraction=XXHASH64

loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"

loose-group_replication_start_on_boot=off

loose-group_replication_local_address="192.168.43.141:24901"

loose-group_replication_group_seeds="192.168.43.141:24901,192.168.43.142:24901,192.168.43.143:24901"

loose-group_replication_bootstrap_group=off

report_host=192.168.43.141

report_port=3306

[root@mgr01 opt]# /etc/init.d/mysqld restart

Stopping mysqld: [ OK ]

Starting mysqld: [ OK ]

[root@mgr01 opt]#

MGR02上修改:

[root@mgr02 opt]# cat /etc/my.cnf

[mysqld]

datadir = /var/lib/mysql

socket = /var/lib/mysql/mysql.sock

symbolic-links = 0

log-error = /var/log/mysqld.log

pid-file = /var/run/mysqld/mysqld.pid

server_id=2

gtid_mode=ON

enforce_gtid_consistency=ON

binlog_checksum=NONE

log_bin=binlog

log_slave_updates=ON

binlog_format=ROW

master_info_repository=TABLE

relay_log_info_repository=TABLE

transaction_write_set_extraction=XXHASH64

loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"

loose-group_replication_start_on_boot=off

loose-group_replication_local_address="192.168.43.142:24901"

loose-group_replication_group_seeds="192.168.43.141:24901,192.168.43.142:24901,192.168.43.143:24901"

loose-group_replication_bootstrap_group=off

report_host=192.168.43.142

report_port=3306

[root@mgr02 opt]# /etc/init.d/mysqld restart

Stopping mysqld: [ OK ]

Starting mysqld: [ OK ]

[root@mgr02 opt]#

MGR03上修改:

[root@mgr03 opt]# cat /etc/my.cnf

[mysqld]

datadir = /var/lib/mysql

socket = /var/lib/mysql/mysql.sock

symbolic-links = 0

log-error = /var/log/mysqld.log

pid-file = /var/run/mysqld/mysqld.pid

server_id=3

gtid_mode=ON

enforce_gtid_consistency=ON

binlog_checksum=NONE

log_bin=binlog

log_slave_updates=ON

binlog_format=ROW

master_info_repository=TABLE

relay_log_info_repository=TABLE

transaction_write_set_extraction=XXHASH64

loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"

loose-group_replication_start_on_boot=off

loose-group_replication_local_address="192.168.43.143:24901"

loose-group_replication_group_seeds="192.168.43.141:24901,192.168.43.142:24901,192.168.43.143:24901"

loose-group_replication_bootstrap_group=off

report_host=192.168.43.143

report_port=3306

[root@mgr03 opt]# /etc/init.d/mysqld restart

Stopping mysqld: [ OK ]

Starting mysqld: [ OK ]

[root@mgr03 opt]#

2.安装MGR插件，设置复制账号（所有MGR节点都要执行）

[root@mgr01 opt]# mysql -uroot -p'Test.123'

mysql: [Warning] Using a password on the command line interface can be i nsecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.7.31-log MySQL Community Server (GPL)

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 stat ement.

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

Query OK, 0 rows affected (0.05 sec)

mysql> SET SQL_LOG_BIN=0;

Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER repl@'%' IDENTIFIED BY 'Test.123';

Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%';

Query OK, 0 rows affected (0.00 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)

mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='Test.123' FOR CHANNEL 'group_replication_recovery';

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

mysql>

3.启动MGR单主模式

这里：我们将MGR01设置为主，即读写数据库服务器

以下为：MGR01上的操作

mysql> SET GLOBAL group_replication_bootstrap_group=ON;

Query OK, 0 rows affected (0.01 sec)

mysql> START GROUP_REPLICATION;

Query OK, 0 rows affected (2.16 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;

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

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

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

1 row in set (0.00 sec)

以下为：MGR02和MGR03上的操作

mysql> START GROUP_REPLICATION;

Query OK, 0 rows affected (5.82 sec)

以下为：MGR01上的操作

mysql> SELECT * FROM performance_schema.replication_group_members;

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

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

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

3 rows in set (0.00 sec)

四、测试功能

1.验证MGR单主模式下节点数据的同步

MGR01进行写入操作：

mysql> show databases;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| sys |

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

4 rows in set (0.00 sec)

mysql> CREATE DATABASE kevin CHARACTER SET utf8 COLLATE utf8_general_ci;

Query OK, 1 row affected (0.01 sec)

mysql> use kevin;

Database changed

mysql> create table if not exists test (id int(10) PRIMARY KEY AUTO_INCREMENT,name varchar(50) NOT NULL);

Query OK, 0 rows affected (0.03 sec)

mysql> insert into kevin.test values(1,"wangshibo"),(2,"guohuihui"),(3,"yangyang"),(4,"shikui");

Query OK, 4 rows affected (0.09 sec)

Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from kevin.test;

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

| id | name |

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

| 1 | wangshibo |

| 2 | guohuihui |

| 3 | yangyang |

| 4 | shikui |

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

4 rows in set (0.00 sec)

mysql>

MGR01与MGR02的查询：

mysql> select * from kevin.test;

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

| id | name |

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

| 1 | wangshibo |

| 2 | guohuihui |

| 3 | yangyang |

| 4 | shikui |

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

4 rows in set (0.00 sec)

观察可知：已经同步

2.从节点不可写测试

以下为在MGR02和MGR03上的操作：

mysql> delete from kevin.test where id>3;

ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

五、单主模式切换多主模式

（一）停止单主模式，开启多主模式

1.停止组复制(在所有MGR节点上执行)：

mysql> stop group_replication;

Query OK, 0 rows affected (9.08 sec)

mysql> set global group_replication_single_primary_mode=OFF;

Query OK, 0 rows affected (0.00 sec)

mysql> set global group_replication_enforce_update_everywhere_checks=ON;

Query OK, 0 rows affected (0.00 sec)

2.随便选择某个MGR节点执行 (比如这里选择在MGR01节点):

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 (2.20 sec)

mysql> SET GLOBAL group_replication_bootstrap_group=OFF;

Query OK, 0 rows affected (0.00 sec)

3.然后在其他的MGR节点执行 (这里指MGR02和MGR03节点上执行):

mysql> START GROUP_REPLICATION;

Query OK, 0 rows affected, 1 warning (5.89 sec)

4.查看MGR组信息 (在任意一个MGR节点上都可以查看)

mysql> SELECT * FROM performance_schema.replication_group_members;

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

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

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

3 rows in set (0.00 sec)

（二）验证MGR多主模式的节点数据同步

MGR01读写：

mysql> select * from kevin.test;

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

| id | name |

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

| 1 | wangshibo |

| 2 | guohuihui |

| 3 | yangyang |

| 4 | shikui |

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

4 rows in set (0.00 sec)

mysql> insert into kevin.test values(5,"pony"),(6,"jack"),(7,"bob"),(8,"allen");

Query OK, 4 rows affected (0.02 sec)

Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from kevin.test;

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

| id | name |

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

| 1 | wangshibo |

| 2 | guohuihui |

| 3 | yangyang |

| 4 | shikui |

| 5 | pony |

| 6 | jack |

| 7 | bob |

| 8 | allen |

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

8 rows in set (0.00 sec)

MGR02读写：

mysql> insert into kevin.test values(9,"ponyMa"),(10,"jackMa"),(11,"Frank");

Query OK, 3 rows affected (0.02 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from kevin.test;

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

| id | name |

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

| 1 | wangshibo |

| 2 | guohuihui |

| 3 | yangyang |

| 4 | shikui |

| 5 | pony |

| 6 | jack |

| 7 | bob |

| 8 | allen |

| 9 | ponyMa |

| 10 | jackMa |

| 11 | Frank |

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

11 rows in set (0.00 sec)

mysql> delete from kevin.test where id > 8;

Query OK, 3 rows affected (0.03 sec)

mysql> select * from kevin.test;

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

| id | name |

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

| 1 | wangshibo |

| 2 | guohuihui |

| 3 | yangyang |

| 4 | shikui |

| 5 | pony |

| 6 | jack |

| 7 | bob |

| 8 | allen |

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

8 rows in set (0.00 sec)

mysql>

MGR03读写：

mysql> insert into kevin.test values(12,"Bill"),(13,"Smarker");

Query OK, 2 rows affected (0.01 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from kevin.test;

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

| id | name |

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

| 1 | wangshibo |

| 2 | guohuihui |

| 3 | yangyang |

| 4 | shikui |

| 5 | pony |

| 6 | jack |

| 7 | bob |

| 8 | allen |

| 12 | Bill |

| 13 | Smarker |

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

10 rows in set (0.00 sec)

mysql> delete from kevin.test where name='pony';

Query OK, 1 row affected (0.00 sec)

mysql> select * from kevin.test;

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

| id | name |

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

| 1 | wangshibo |

| 2 | guohuihui |

| 3 | yangyang |

| 4 | shikui |

| 6 | jack |

| 7 | bob |

| 8 | allen |

| 12 | Bill |

| 13 | Smarker |

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

9 rows in set (0.00 sec)

mysql>

六、多主模式切换单主模式

（一）切换为单主模式

1.停止组复制(在所有MGR节点上执行)：

mysql> stop group_replication;

Query OK, 0 rows affected (9.29 sec)

mysql> set global group_replication_enforce_update_everywhere_checks=OFF;

Query OK, 0 rows affected (0.00 sec)

mysql> set global group_replication_single_primary_mode=ON;

Query OK, 0 rows affected (0.00 sec)

2.选择一个节点作为主节点, 在主节点上执行 (这里选择MGR-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 (2.12 sec)

mysql> SET GLOBAL group_replication_bootstrap_group=OFF;

Query OK, 0 rows affected (0.00 sec)

3.在其他剩余的节点, 也就是从库节点上执行 (这里从库节点指的就是MGR-node2和MGR-node3):

mysql> START GROUP_REPLICATION;

Query OK, 0 rows affected, 1 warning (6.16 sec)

4.查看MGR组信息 (在任意一个MGR节点上都可以查看)

mysql> SELECT * FROM performance_schema.replication_group_members;

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

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

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

3 rows in set (0.00 sec)

（二）测试单主模式功能

1.MGR01读写正常：

mysql> select * from kevin.test;

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

| id | name |

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

| 1 | wangshibo |

| 2 | guohuihui |

| 3 | yangyang |

| 4 | shikui |

| 6 | jack |

| 7 | bob |

| 8 | allen |

| 12 | Bill |

| 13 | Smarker |

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

9 rows in set (0.00 sec)

mysql> insert into kevin.test values(14,"pony");

Query OK, 1 row affected (0.04 sec)

mysql> select * from kevin.test;

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

| id | name |

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

| 1 | wangshibo |

| 2 | guohuihui |

| 3 | yangyang |

| 4 | shikui |

| 6 | jack |

| 7 | bob |

| 8 | allen |

| 12 | Bill |

| 13 | Smarker |

| 14 | pony |

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

10 rows in set (0.00 sec)

2.MGR01与MGR02无法写入

mysql> insert into kevin.test values(15,"Jacker");

ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

mysql> select * from kevin.test;

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

| id | name |

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

| 1 | wangshibo |

| 2 | guohuihui |

| 3 | yangyang |

| 4 | shikui |

| 6 | jack |

| 7 | bob |

| 8 | allen |

| 12 | Bill |

| 13 | Smarker |

| 14 | pony |

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

10 rows in set (0.00 sec)

七、故障模拟及过程分析

（一）模拟故障

我们这里模拟MGR01的MySQL服务挂掉以后，单主模式下集群的情况。

1.关闭MGR01上的MySQL服务

[root@mgr01 opt]# /etc/init.d/mysqld stop

Stopping mysqld: [ OK ]

[root@mgr01 opt]# /etc/init.d/mysqld status

mysqld is stopped

[root@mgr01 opt]#

2.这里，我们在MGR02进行读写操作，是正常的。

说明此时已经选举MGR-node2节点为新的主节点了

mysql> SELECT * FROM performance_schema.replication_group_members;

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

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

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

2 rows in set (0.00 sec)

mysql> select * from kevin.test;

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

| id | name |

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

| 1 | wangshibo |

| 2 | guohuihui |

| 3 | yangyang |

| 4 | shikui |

| 6 | jack |

| 7 | bob |

| 8 | allen |

| 12 | Bill |

| 13 | Smarker |

| 14 | pony |

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

10 rows in set (0.01 sec)

mysql> insert into kevin.test values(15,"Jacker");

Query OK, 1 row affected (0.01 sec)

mysql> select * from kevin.test;

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

| id | name |

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

| 1 | wangshibo |

| 2 | guohuihui |

| 3 | yangyang |

| 4 | shikui |

| 6 | jack |

| 7 | bob |

| 8 | allen |

| 12 | Bill |

| 13 | Smarker |

| 14 | pony |

| 15 | Jacker |

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

11 rows in set (0.00 sec)

mysql>

3.MGR03节点还是从节点, 只能读不能写

mysql> select * from kevin.test;

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

| id | name |

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

| 1 | wangshibo |

| 2 | guohuihui |

| 3 | yangyang |

| 4 | shikui |

| 6 | jack |

| 7 | bob |

| 8 | allen |

| 12 | Bill |

| 13 | Smarker |

| 14 | pony |

| 15 | Jacker |

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

11 rows in set (0.00 sec)

mysql> insert into kevin.test values(120,"Bill"),(130,"Smarker");

ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

mysql>

（二）故障恢复后如何处理？

1.恢复MGR01的MySQL服务，手动激活下该节点的组复制功能

[root@mgr01 opt]# /etc/init.d/mysqld start

Starting mysqld: [ OK ]

[root@mgr01 opt]# /etc/init.d/mysqld status

mysqld (pid 3087) is running...

[root@mgr01 opt]#

[root@mgr01 opt]# mysql -uroot -p'Test.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 3

Server version: 5.7.31-log MySQL Community Server (GPL)

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 (2.79 sec)

mysql> SELECT * FROM performance_schema.replication_group_members;

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

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

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

3 rows in set (0.00 sec)

2.观察MGR01的读写情况

mysql> select * from kevin.test;

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

| id | name |

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

| 1 | wangshibo |

| 2 | guohuihui |

| 3 | yangyang |

| 4 | shikui |

| 6 | jack |

| 7 | bob |

| 8 | allen |

| 12 | Bill |

| 13 | Smarker |

| 14 | pony |

| 15 | Jacker |

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

11 rows in set (0.00 sec)

mysql> insert into kevin.test values(140,"Mapony");

ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

mysql>

我们发现：MGR01节点恢复后, 则变为了从库节点, 只能读不能写.

如果从节点挂了, 恢复后, 只需要手动激活下该节点的组复制功能("START GROUP_REPLICATION;"),

即可正常加入到MGR组复制集群内并自动同步其他节点数据.

（三）多主模式模式下的故障模拟与测试

1.按照第五节的内容进行单主转多主模式（略）

2.任意挂掉一台服务器的MySQL服务，我们选MGR03进行测试

[root@mgr03 opt]# /etc/init.d/mysqld stop

Stopping mysqld: [ OK ]

[root@mgr03 opt]#

3.测试发现其他2节点读写正常

mysql> SELECT * FROM performance_schema.replication_group_members;

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

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

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

2 rows in set (0.00 sec)

mysql> select * from kevin.test;

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

| id | name |

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

| 1 | wangshibo |

| 2 | guohuihui |

| 3 | yangyang |

| 4 | shikui |

| 6 | jack |

| 7 | bob |

| 8 | allen |

| 12 | Bill |

| 13 | Smarker |

| 14 | pony |

| 15 | Jacker |

| 1200 | Bill |

| 1300 | Smarker |

| 1400 | Mapony |

| 1500 | Jacker |

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

15 rows in set (0.00 sec)

mysql> insert into kevin.test values(15000,"Jacker");

Query OK, 1 row affected (0.01 sec)

mysql> select * from kevin.test;

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

| id | name |

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

| 1 | wangshibo |

| 2 | guohuihui |

| 3 | yangyang |

| 4 | shikui |

| 6 | jack |

| 7 | bob |

| 8 | allen |

| 12 | Bill |

| 13 | Smarker |

| 14 | pony |

| 15 | Jacker |

| 1200 | Bill |

| 1300 | Smarker |

| 1400 | Mapony |

| 1500 | Jacker |

| 15000 | Jacker |

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

16 rows in set (0.00 sec)

mysql> delete from kevin.test where name='Jacker';

Query OK, 3 rows affected (0.01 sec)

mysql> select * from kevin.test;

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

| id | name |

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

| 1 | wangshibo |

| 2 | guohuihui |

| 3 | yangyang |

| 4 | shikui |

| 6 | jack |

| 7 | bob |

| 8 | allen |

| 12 | Bill |

| 13 | Smarker |

| 14 | pony |

| 1200 | Bill |

| 1300 | Smarker |

| 1400 | Mapony |

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

13 rows in set (0.00 sec)

mysql>

4.故障恢复处理(恢复MGR03的MySQL服务)

[root@mgr03 opt]# /etc/init.d/mysqld start

Starting mysqld: [ OK ]

[root@mgr03 opt]# mysql -uroot -p'Test.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 3

Server version: 5.7.31-log MySQL Community Server (GPL)

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> SELECT * FROM performance_schema.replication_group_members;

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

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

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

1 row in set (0.00 sec)

mysql> START GROUP_REPLICATION;

ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.

这里我们跟进日志信息进行故障排查：

[root@mgr03 ~]# tail -f /var/log/mysqld.log

2020-08-20T16:47:16.413305Z 0 [ERROR] Plugin group_replication reported: 'The member configuration is not

compatible with the group configuration. Variables such as single_primary_mode or enforce_update_everywhere_checks

must have the same value on every server in the group. (member configuration option: [group_replication_single_primary_mode],

group configuration option: [group_replication_enforce_update_everywhere_checks]).'

mysql> show variables like '%single_primary_mode%';

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

| Variable_name | Value |

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

| group_replication_single_primary_mode | ON |

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

1 row in set (0.02 sec)

mysql> show variables like '%enforce_update_everywhere_checks%';

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

| Variable_name | Value |

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

| group_replication_enforce_update_everywhere_checks | OFF |

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

1 row in set (0.01 sec)

mysql> set group_replication_enforce_update_everywhere_checks=ON;

ERROR 1229 (HY000): Variable 'group_replication_enforce_update_everywhere_checks' is a GLOBAL variable and should be set with SET GLOBAL

mysql> set group_replication_single_primary_mode=OFF;

ERROR 1229 (HY000): Variable 'group_replication_single_primary_mode' is a GLOBAL variable and should be set with SET GLOBAL

mysql> SET GLOBAL group_replication_enforce_update_everywhere_checks=ON;

ERROR 1231 (42000): Cannot enable enforce_update_everywhere_checks while single_primary_mode is enabled.

mysql> SET GLOBAL group_replication_single_primary_mode=OFF;

Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%enforce_update_everywhere_checks%';

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

| Variable_name | Value |

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

| group_replication_enforce_update_everywhere_checks | OFF |

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

1 row in set (0.00 sec)

mysql> SET GLOBAL group_replication_enforce_update_everywhere_checks=ON;

Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL group_replication_single_primary_mode=OFF;

Query OK, 0 rows affected (0.00 sec)

mysql> START GROUP_REPLICATION;

Query OK, 0 rows affected (2.94 sec)

mysql> SELECT * FROM performance_schema.replication_group_members;

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

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

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

3 rows in set (0.00 sec)

mysql>

mysql> insert into kevin.test values(200,"Test"),(300,"MGr");

Query OK, 2 rows affected (0.01 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from kevin.test;

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

| id | name |

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

| 1 | wangshibo |

| 2 | guohuihui |

| 3 | yangyang |

| 4 | shikui |

| 6 | jack |

| 7 | bob |

| 8 | allen |

| 12 | Bill |

| 13 | Smarker |

| 14 | pony |

| 200 | Test |

| 300 | MGr |

| 1200 | Bill |

| 1300 | Smarker |

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

14 rows in set (0.00 sec)

mysql> delete from kevin.test where id > 3;

Query OK, 11 rows affected (0.01 sec)

mysql> select * from kevin.test;

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

| id | name |

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

| 1 | wangshibo |

| 2 | guohuihui |

| 3 | yangyang |

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

3 rows in set (0.00 sec)

观察可知：恢复正常。

参阅：

https://cloud.tencent.com/developer/article/1432589