开始本文的前提:

  1. 【3】台,RHEL 7.6的Linux服务器
  2. 每台服务器,安装好MySQL【5.7.26】;MySQL数据库的安装步骤,在本文中不重复赘述

本文将呈现【3】节点的【MySQL MGR】的技术细节;
涉及架构包括:

  1. 单主模式
  2. 多主模式

环境介绍:

操作系统:RHEL 7.6 64bit
防火墙【firewalld】:已停止;禁用开机启动
SELINUX:已禁用
MySQL版本:5.7.26
内存:2G

节点1:
192.168.40.11

节点2:
192.168.40.12

节点3:
192.168.40.13

文件:/etc/hosts

[root@mysql1 ~]# cat /etc/hosts
#127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
#::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

# Local
127.0.0.1       localhost

# Pub
#192.168.40.254 linux

# MySQL
192.168.40.11   mysql1
192.168.40.12   mysql2
192.168.40.13   mysql3

# Puppet Server side
192.168.40.252  puppet puppet.adamhuan.com
[root@mysql1 ~]# 

修改配置文件【/etc/my.cnf】

节点【1】

[root@mysql1 ~]# ls -ltr /etc | grep my.cnf
-rw-r--r--   1 root root      570 Aug  4 09:45 my.cnf.rpmsave-20190804-0945
drwxr-xr-x.  2 root root        6 Aug  4 09:46 my.cnf.d
-rw-r--r--   1 root root      636 Aug  4 19:49 my.cnf_old_20190805_01
-rw-r--r--   1 root root     1488 Aug  4 20:23 my.cnf
[root@mysql1 ~]# 
[root@mysql1 ~]# cp /etc/my.cnf /etc/my.cnf_orig_20190815
[root@mysql1 ~]# 
[root@mysql1 ~]# ls -ltr /etc | grep my.cnf
-rw-r--r--   1 root root      570 Aug  4 09:45 my.cnf.rpmsave-20190804-0945
drwxr-xr-x.  2 root root        6 Aug  4 09:46 my.cnf.d
-rw-r--r--   1 root root      636 Aug  4 19:49 my.cnf_old_20190805_01
-rw-r--r--   1 root root     1488 Aug  4 20:23 my.cnf
-rw-r--r--   1 root root     1488 Aug 14 18:01 my.cnf_orig_20190815
[root@mysql1 ~]# 
[root@mysql1 ~]# cat /etc/my.cnf
# File: MySQL config file: my.cnf
# Default Location: /etc/my.cnf
# MySQL version: Ver 14.14 Distrib 5.7.26-29
# ===================================

# %%%%%%%%%%%%%%%%%%%%%%
# ---
# MySQL: 服务进程
# ---
[mysqld]

# ---> 文件
# 服务进程ID文件
pid-file=/var/run/mysqld/mysqld.pid

# 错误日志文件
log-error=/var/log/mysqld.log

# 网络套接字文件
socket=/var/lib/mysql/mysql.sock

# ---> 目录

# 软件所在目录
#basedir = /home/mysql/mysql/

# 数据目录
datadir=/var/lib/mysql
#tmpdir=

# ---> 参数

symbolic-links=0

# MySQL:服务编号
# -- diff
server-id=1

# 用户
user=mysql

# 字符集
character_set_server = utf8mb4

# 端口
port=3306

# 存储引擎
default_storage_engine=innodb

# !!!--> UNDO
#innodb_undo_directory=
#innodb_undo_tablespaces=

# !!!--> GTID
gtid-mode=ON
enforce-gtid-consistency=ON

# !!!--> Binlog
# Binlog模式
log_bin=binlog
binlog_format=ROW
binlog_checksum=NONE

# 文件:binlog
log-bin=/var/lib/mysql/mysql-bin
log-bin-index=/var/lib/mysql/mysql-bin

# !!!--> Master
master_info_repository=TABLE

# !!!--> Slave
log_slave_updates=ON

# !!!--> 文件:relaylog
relay_log_info_repository=TABLE

# !!!--> Transaction - 事务
# 设置Write_Set的哈希算法
# Write_Set用于MGR实例之间的数据校验
transaction_write_set_extraction=XXHASH64

# !!!--> MGR - MySQL Group Replication - 组复制

# 以前缀【loose-】开头,代表:如果组复制的插件没有加载的情况下,也能运行并启动组复制

# 定义一个Group Name,必须是UUID格式,可以自定义
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
# 禁止实例启动时,自动开启组复制
loose-group_replication_start_on_boot=OFF

# -- diff
# 配置当前实例的IP与组复制内部沟通端口,不同组成员之间需要区分开来
loose-group_replication_local_address="192.168.40.11:33061"

# 设置组复制的种子成员,需要包含当前实例
loose-group_replication_group_seeds="192.168.40.11:33061,192.168.40.12:33061,192.168.40.13:33061"
# 关闭引导组复制
loose-group_replication_bootstrap_group=OFF

# !!!--> REPLICATION / master-slave - report setting
# -- diff
report_host=192.168.40.11

# -- diff
report_port=3306

# %%%%%%%%%%%%%%%%%%%%%%
# ---
# MySQL: 服务进程 - 安全模式
# ---
[mysqld_safe]

# ---> 文件
# 错误日志文件
log-error=/var/log/mariadb/mariadb.log

# 服务进程ID文件
pid-file=/var/run/mariadb/mariadb.pid


# %%%%%%%%%%%%%%%%%%%%%%
# ---
# MySQL: 客户端进程
# ---
[client]

# ---> 文件
# 网络套接字文件
socket=/var/lib/mysql/mysql.sock

# ---> 参数
# 端口
port=3306

# %%%%%%%%%%%%%%%%%%%%%%
# ---
# MySQL: 命令行工具
# ---
[mysql]

# ---> 参数

no-auto-rehash

# 提示符
prompt="\u@db \R:\m:\s [\d]>"


# %%%%%%%%%%%%%%%%%%%%%%
# ---
# MySQL: 其他的包含配置文件的目录
# ---
# 包含配置文件的目录
!includedir /etc/my.cnf.d


# ===================================
# Finished
[root@mysql1 ~]# 

节点【2】

[root@mysql2 ~]# ls -ltr /etc | grep my.cnf
-rw-r--r--   1 root root      570 Aug 13 17:53 my.cnf.rpmsave-20190813-1753
-rw-r--r--   1 root root      636 Aug 13 17:54 my.cnf
drwxr-xr-x.  2 root root        6 Aug 13 17:54 my.cnf.d
[root@mysql2 ~]# 
[root@mysql2 ~]# cp /etc/my.cnf /etc/my.cnf_orig_20190815
[root@mysql2 ~]# 
[root@mysql2 ~]# ls -ltr /etc | grep my.cnf
-rw-r--r--   1 root root      570 Aug 13 17:53 my.cnf.rpmsave-20190813-1753
-rw-r--r--   1 root root      636 Aug 13 17:54 my.cnf
drwxr-xr-x.  2 root root        6 Aug 13 17:54 my.cnf.d
-rw-r--r--   1 root root      636 Aug 14 18:01 my.cnf_orig_20190815
[root@mysql2 ~]# 
[root@mysql2 ~]# cat /etc/my.cnf
# File: MySQL config file: my.cnf
# Default Location: /etc/my.cnf
# MySQL version: Ver 14.14 Distrib 5.7.26-29
# ===================================

# %%%%%%%%%%%%%%%%%%%%%%
# ---
# MySQL: 服务进程
# ---
[mysqld]

# ---> 文件
# 服务进程ID文件
pid-file=/var/run/mysqld/mysqld.pid

# 错误日志文件
log-error=/var/log/mysqld.log

# 网络套接字文件
socket=/var/lib/mysql/mysql.sock

# ---> 目录

# 软件所在目录
#basedir = /home/mysql/mysql/

# 数据目录
datadir=/var/lib/mysql
#tmpdir=

# ---> 参数

symbolic-links=0

# MySQL:服务编号
# -- diff
server-id=2

# 用户
user=mysql

# 字符集
character_set_server = utf8mb4

# 端口
port=3306

# 存储引擎
default_storage_engine=innodb

# !!!--> UNDO
#innodb_undo_directory=
#innodb_undo_tablespaces=

# !!!--> GTID
gtid-mode=ON
enforce-gtid-consistency=ON

# !!!--> Binlog
# Binlog模式
log_bin=binlog
binlog_format=ROW
binlog_checksum=NONE

# 文件:binlog
log-bin=/var/lib/mysql/mysql-bin
log-bin-index=/var/lib/mysql/mysql-bin

# !!!--> Master
master_info_repository=TABLE

# !!!--> Slave
log_slave_updates=ON

# !!!--> 文件:relaylog
relay_log_info_repository=TABLE

# !!!--> Transaction - 事务
# 设置Write_Set的哈希算法
# Write_Set用于MGR实例之间的数据校验
transaction_write_set_extraction=XXHASH64

# !!!--> MGR - MySQL Group Replication - 组复制

# 以前缀【loose-】开头,代表:如果组复制的插件没有加载的情况下,也能运行并启动组复制

# 定义一个Group Name,必须是UUID格式,可以自定义
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
# 禁止实例启动时,自动开启组复制
loose-group_replication_start_on_boot=OFF

# -- diff
# 配置当前实例的IP与组复制内部沟通端口,不同组成员之间需要区分开来
loose-group_replication_local_address="192.168.40.12:33061"

# 设置组复制的种子成员,需要包含当前实例
loose-group_replication_group_seeds="192.168.40.11:33061,192.168.40.12:33061,192.168.40.13:33061"
# 关闭引导组复制
loose-group_replication_bootstrap_group=OFF

# !!!--> REPLICATION / master-slave - report setting
# -- diff
report_host=192.168.40.12

# -- diff
report_port=3306

# %%%%%%%%%%%%%%%%%%%%%%
# ---
# MySQL: 服务进程 - 安全模式
# ---
[mysqld_safe]

# ---> 文件
# 错误日志文件
log-error=/var/log/mariadb/mariadb.log

# 服务进程ID文件
pid-file=/var/run/mariadb/mariadb.pid


# %%%%%%%%%%%%%%%%%%%%%%
# ---
# MySQL: 客户端进程
# ---
[client]

# ---> 文件
# 网络套接字文件
socket=/var/lib/mysql/mysql.sock

# ---> 参数
# 端口
port=3306

# %%%%%%%%%%%%%%%%%%%%%%
# ---
# MySQL: 命令行工具
# ---
[mysql]

# ---> 参数

no-auto-rehash

# 提示符
prompt="\u@db \R:\m:\s [\d]>"


# %%%%%%%%%%%%%%%%%%%%%%
# ---
# MySQL: 其他的包含配置文件的目录
# ---
# 包含配置文件的目录
!includedir /etc/my.cnf.d


# ===================================
# Finished
[root@mysql2 ~]# 

节点【3】

[root@mysql3 ~]# ls -ltr /etc | grep my.cnf
-rw-r--r--   1 root root      570 Aug 13 18:08 my.cnf.rpmsave-20190813-1808
-rw-r--r--   1 root root      636 Aug 13 18:08 my.cnf
drwxr-xr-x.  2 root root        6 Aug 13 18:08 my.cnf.d
[root@mysql3 ~]# 
[root@mysql3 ~]# cp /etc/my.cnf /etc/my.cnf_orig_20190815
[root@mysql3 ~]# 
[root@mysql3 ~]# ls -ltr /etc | grep my.cnf
-rw-r--r--   1 root root      570 Aug 13 18:08 my.cnf.rpmsave-20190813-1808
-rw-r--r--   1 root root      636 Aug 13 18:08 my.cnf
drwxr-xr-x.  2 root root        6 Aug 13 18:08 my.cnf.d
-rw-r--r--   1 root root      636 Aug 14 18:01 my.cnf_orig_20190815
[root@mysql3 ~]# 
[root@mysql3 ~]# cat /etc/my.cnf
# File: MySQL config file: my.cnf
# Default Location: /etc/my.cnf
# MySQL version: Ver 14.14 Distrib 5.7.26-29
# ===================================

# %%%%%%%%%%%%%%%%%%%%%%
# ---
# MySQL: 服务进程
# ---
[mysqld]

# ---> 文件
# 服务进程ID文件
pid-file=/var/run/mysqld/mysqld.pid

# 错误日志文件
log-error=/var/log/mysqld.log

# 网络套接字文件
socket=/var/lib/mysql/mysql.sock

# ---> 目录

# 软件所在目录
#basedir = /home/mysql/mysql/

# 数据目录
datadir=/var/lib/mysql
#tmpdir=

# ---> 参数

symbolic-links=0

# MySQL:服务编号
# -- diff
server-id=3

# 用户
user=mysql

# 字符集
character_set_server = utf8mb4

# 端口
port=3306

# 存储引擎
default_storage_engine=innodb

# !!!--> UNDO
#innodb_undo_directory=
#innodb_undo_tablespaces=

# !!!--> GTID
gtid-mode=ON
enforce-gtid-consistency=ON

# !!!--> Binlog
# Binlog模式
log_bin=binlog
binlog_format=ROW
binlog_checksum=NONE

# 文件:binlog
log-bin=/var/lib/mysql/mysql-bin
log-bin-index=/var/lib/mysql/mysql-bin

# !!!--> Master
master_info_repository=TABLE

# !!!--> Slave
log_slave_updates=ON

# !!!--> 文件:relaylog
relay_log_info_repository=TABLE

# !!!--> Transaction - 事务
# 设置Write_Set的哈希算法
# Write_Set用于MGR实例之间的数据校验
transaction_write_set_extraction=XXHASH64

# !!!--> MGR - MySQL Group Replication - 组复制

# 以前缀【loose-】开头,代表:如果组复制的插件没有加载的情况下,也能运行并启动组复制

# 定义一个Group Name,必须是UUID格式,可以自定义
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
# 禁止实例启动时,自动开启组复制
loose-group_replication_start_on_boot=OFF

# -- diff
# 配置当前实例的IP与组复制内部沟通端口,不同组成员之间需要区分开来
loose-group_replication_local_address="192.168.40.13:33061"

# 设置组复制的种子成员,需要包含当前实例
loose-group_replication_group_seeds="192.168.40.11:33061,192.168.40.12:33061,192.168.40.13:33061"
# 关闭引导组复制
loose-group_replication_bootstrap_group=OFF

# !!!--> REPLICATION / master-slave - report setting
# -- diff
report_host=192.168.40.13

# -- diff
report_port=3306

# %%%%%%%%%%%%%%%%%%%%%%
# ---
# MySQL: 服务进程 - 安全模式
# ---
[mysqld_safe]

# ---> 文件
# 错误日志文件
log-error=/var/log/mariadb/mariadb.log

# 服务进程ID文件
pid-file=/var/run/mariadb/mariadb.pid


# %%%%%%%%%%%%%%%%%%%%%%
# ---
# MySQL: 客户端进程
# ---
[client]

# ---> 文件
# 网络套接字文件
socket=/var/lib/mysql/mysql.sock

# ---> 参数
# 端口
port=3306

# %%%%%%%%%%%%%%%%%%%%%%
# ---
# MySQL: 命令行工具
# ---
[mysql]

# ---> 参数

no-auto-rehash

# 提示符
prompt="\u@db \R:\m:\s [\d]>"


# %%%%%%%%%%%%%%%%%%%%%%
# ---
# MySQL: 其他的包含配置文件的目录
# ---
# 包含配置文件的目录
!includedir /etc/my.cnf.d


# ===================================
# Finished
[root@mysql3 ~]# 

在所有节点上,初始化数据库:

[root@mysql1 ~]# service mysql stop
Redirecting to /bin/systemctl stop mysql.service
[root@mysql1 ~]# 
[root@mysql1 ~]# service mysql status
Redirecting to /bin/systemctl status mysql.service
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: inactive (dead) since Wed 2019-08-14 18:39:40 PDT; 9min ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 7782 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 7254 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 7785 (code=exited, status=0/SUCCESS)

Aug 14 17:45:04 mysql1.adamhuan.com systemd[1]: Starting MySQL Server...
Aug 14 17:45:30 mysql1.adamhuan.com systemd[1]: Started MySQL Server.
Aug 14 18:39:38 mysql1.adamhuan.com systemd[1]: Stopping MySQL Server...
Aug 14 18:39:40 mysql1.adamhuan.com systemd[1]: Stopped MySQL Server.
[root@mysql1 ~]# 
[root@mysql1 ~]# cat /etc/my.cnf | grep datadir
datadir=/var/lib/mysql
[root@mysql1 ~]# 
[root@mysql1 ~]# ls -ltr /var/lib/mysql
total 110672
-rw-r----- 1 mysql mysql 50331648 Aug  4 09:46 ib_logfile1
-rw-r----- 1 mysql mysql       56 Aug  4 09:46 auto.cnf
-rw-r--r-- 1 mysql mysql     1120 Aug  4 09:46 ca.pem
-rw------- 1 mysql mysql     1676 Aug  4 09:46 ca-key.pem
-rw------- 1 mysql mysql     1676 Aug  4 09:46 server-key.pem
-rw-r--r-- 1 mysql mysql     1120 Aug  4 09:46 server-cert.pem
-rw------- 1 mysql mysql     1680 Aug  4 09:46 client-key.pem
-rw-r--r-- 1 mysql mysql     1120 Aug  4 09:46 client-cert.pem
-rw-r--r-- 1 mysql mysql      452 Aug  4 09:46 public_key.pem
-rw------- 1 mysql mysql     1680 Aug  4 09:46 private_key.pem
drwxr-x--- 2 mysql mysql     8192 Aug  4 09:46 performance_schema
drwxr-x--- 2 mysql mysql     4096 Aug  4 09:46 mysql
drwxr-x--- 2 mysql mysql     8192 Aug  4 09:46 sys
-rw-r----- 1 mysql mysql      588 Aug 14 17:45 mysql-bin.000001
-rw-r----- 1 mysql mysql       64 Aug 14 17:45 mysql-bin.index
-rw-r----- 1 mysql mysql      217 Aug 14 18:39 mysql-bin.000002
-rw-r----- 1 mysql mysql      326 Aug 14 18:39 ib_buffer_pool
-rw-r----- 1 mysql mysql 50331648 Aug 14 18:39 ib_logfile0
-rw-r----- 1 mysql mysql 12582912 Aug 14 18:39 ibdata1
[root@mysql1 ~]# 
[root@mysql1 ~]# rm -rf /var/lib/mysql/*
[root@mysql1 ~]# 
[root@mysql1 ~]# ls -ltr /var/lib/mysql
total 0
[root@mysql1 ~]# 

[root@mysql1 ~]# which mysqld
/usr/sbin/mysqld
[root@mysql1 ~]# 
[root@mysql1 ~]# mysqld --initialize-insecure --datadir=/var/lib/mysql --user=mysql
[root@mysql1 ~]# 
[root@mysql1 ~]# ls -ltr /var/lib/mysql
total 110668
-rw-r----- 1 mysql mysql 50331648 Aug 14 18:52 ib_logfile1
-rw-r----- 1 mysql mysql       32 Aug 14 18:52 mysql-bin.index
-rw-r----- 1 mysql mysql       56 Aug 14 18:52 auto.cnf
-rw------- 1 mysql mysql     1676 Aug 14 18:52 ca-key.pem
-rw-r--r-- 1 mysql mysql     1120 Aug 14 18:52 ca.pem
-rw------- 1 mysql mysql     1680 Aug 14 18:52 server-key.pem
-rw-r--r-- 1 mysql mysql     1120 Aug 14 18:52 server-cert.pem
-rw------- 1 mysql mysql     1680 Aug 14 18:52 client-key.pem
-rw-r--r-- 1 mysql mysql     1120 Aug 14 18:52 client-cert.pem
-rw-r--r-- 1 mysql mysql      452 Aug 14 18:52 public_key.pem
-rw------- 1 mysql mysql     1680 Aug 14 18:52 private_key.pem
drwxr-x--- 2 mysql mysql     8192 Aug 14 18:52 performance_schema
drwxr-x--- 2 mysql mysql     4096 Aug 14 18:52 mysql
drwxr-x--- 2 mysql mysql     8192 Aug 14 18:52 sys
-rw-r----- 1 mysql mysql      169 Aug 14 18:52 mysql-bin.000001
-rw-r----- 1 mysql mysql      417 Aug 14 18:52 ib_buffer_pool
-rw-r----- 1 mysql mysql 50331648 Aug 14 18:52 ib_logfile0
-rw-r----- 1 mysql mysql 12582912 Aug 14 18:52 ibdata1
[root@mysql1 ~]# 

在所有节点上,启动MySQL数据库服务:

[root@mysql1 ~]# service mysql status
Redirecting to /bin/systemctl status mysql.service
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: inactive (dead) since Wed 2019-08-14 18:39:40 PDT; 15min ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 7782 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 7254 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 7785 (code=exited, status=0/SUCCESS)

Aug 14 17:45:04 mysql1.adamhuan.com systemd[1]: Starting MySQL Server...
Aug 14 17:45:30 mysql1.adamhuan.com systemd[1]: Started MySQL Server.
Aug 14 18:39:38 mysql1.adamhuan.com systemd[1]: Stopping MySQL Server...
Aug 14 18:39:40 mysql1.adamhuan.com systemd[1]: Stopped MySQL Server.
[root@mysql1 ~]# 
[root@mysql1 ~]# service mysql start
Redirecting to /bin/systemctl start mysql.service
[root@mysql1 ~]# 
[root@mysql1 ~]# service mysql status
Redirecting to /bin/systemctl status mysql.service
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Wed 2019-08-14 18:55:17 PDT; 5s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 8108 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 8081 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 8111 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─8111 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Aug 14 18:55:16 mysql1.adamhuan.com systemd[1]: Starting MySQL Server...
Aug 14 18:55:17 mysql1.adamhuan.com systemd[1]: Started MySQL Server.
[root@mysql1 ~]# 

这个过程中的日志【/var/log/mysqld.log】:

[root@mysql2 ~]# tail -f /var/log/mysqld.log
2019-08-15T01:53:27.167546Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2019-08-15T01:53:27.222650Z 0 [Warning] unknown variable 'loose-group_replication_group_name=aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa'
2019-08-15T01:53:27.222680Z 0 [Warning] unknown variable 'loose-group_replication_start_on_boot=OFF'
2019-08-15T01:53:27.222683Z 0 [Warning] unknown variable 'loose-group_replication_local_address=192.168.40.12:33061'
2019-08-15T01:53:27.222685Z 0 [Warning] unknown variable 'loose-group_replication_group_seeds=192.168.40.11:33061,192.168.40.12:33061,192.168.40.13:33061'
2019-08-15T01:53:27.222687Z 0 [Warning] unknown variable 'loose-group_replication_bootstrap_group=OFF'
2019-08-15T01:53:27.224401Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 794882d1-beff-11e9-9f51-000c2968d274.
2019-08-15T01:53:27.224743Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2019-08-15T01:53:27.458149Z 0 [Warning] CA certificate ca.pem is self signed.
2019-08-15T01:53:27.948593Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.


===================================================

2019-08-15T01:57:00.003769Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-08-15T01:57:00.020396Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.26-29-log) starting as process 8138 ...
2019-08-15T01:57:00.033205Z 0 [Note] InnoDB: PUNCH HOLE support available
2019-08-15T01:57:00.033325Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2019-08-15T01:57:00.033330Z 0 [Note] InnoDB: Uses event mutexes
2019-08-15T01:57:00.033335Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2019-08-15T01:57:00.033338Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.7
2019-08-15T01:57:00.033341Z 0 [Note] InnoDB: Using Linux native AIO
2019-08-15T01:57:00.035195Z 0 [Note] InnoDB: Number of pools: 1
2019-08-15T01:57:00.035821Z 0 [Note] InnoDB: Using CPU crc32 instructions
2019-08-15T01:57:00.048070Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2019-08-15T01:57:00.090091Z 0 [Note] InnoDB: Completed initialization of buffer pool
2019-08-15T01:57:00.118514Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2019-08-15T01:57:00.150005Z 0 [Note] InnoDB: Crash recovery did not find the parallel doublewrite buffer at /var/lib/mysql/xb_doublewrite
2019-08-15T01:57:00.152695Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2019-08-15T01:57:00.175494Z 0 [Note] InnoDB: Created parallel doublewrite buffer at /var/lib/mysql/xb_doublewrite, size 3932160 bytes
2019-08-15T01:57:00.188633Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2019-08-15T01:57:00.188703Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2019-08-15T01:57:00.268318Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2019-08-15T01:57:00.277827Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2019-08-15T01:57:00.277853Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2019-08-15T01:57:00.287581Z 0 [Note] InnoDB: Waiting for purge to start
2019-08-15T01:57:00.337990Z 0 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.7.26-29 started; log sequence number 2530251
2019-08-15T01:57:00.350585Z 0 [Note] Plugin 'FEDERATED' is disabled.
2019-08-15T01:57:00.375030Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2019-08-15T01:57:00.377558Z 0 [Note] InnoDB: Buffer pool(s) load completed at 190814 18:57:00
2019-08-15T01:57:00.414207Z 0 [Warning] unknown variable 'loose-group_replication_group_name=aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa'
2019-08-15T01:57:00.414245Z 0 [Warning] unknown variable 'loose-group_replication_start_on_boot=OFF'
2019-08-15T01:57:00.414251Z 0 [Warning] unknown variable 'loose-group_replication_local_address=192.168.40.12:33061'
2019-08-15T01:57:00.414253Z 0 [Warning] unknown variable 'loose-group_replication_group_seeds=192.168.40.11:33061,192.168.40.12:33061,192.168.40.13:33061'
2019-08-15T01:57:00.414256Z 0 [Warning] unknown variable 'loose-group_replication_bootstrap_group=OFF'
2019-08-15T01:57:00.419728Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
2019-08-15T01:57:00.419766Z 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory.
2019-08-15T01:57:00.420359Z 0 [Warning] CA certificate ca.pem is self signed.
2019-08-15T01:57:00.420399Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
2019-08-15T01:57:00.420493Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2019-08-15T01:57:00.420534Z 0 [Note] IPv6 is available.
2019-08-15T01:57:00.420543Z 0 [Note]   - '::' resolves to '::';
2019-08-15T01:57:00.420560Z 0 [Note] Server socket created on IP: '::'.
2019-08-15T01:57:00.434472Z 0 [Note] Failed to start slave threads for channel ''
2019-08-15T01:57:00.444518Z 0 [Note] Event Scheduler: Loaded 0 events
2019-08-15T01:57:00.445451Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.26-29-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Percona Server (GPL), Release 29, Revision 11ad961

=============================================

在所有节点上,安装MGR的插件:

[root@mysql2 ~]# mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.26-29-log Percona Server (GPL), Release 29, Revision 11ad961

Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

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.

root@db 19:02:  [(none)]>
root@db 19:03:  [(none)]>select @@have_dynamic_loading ;
+------------------------+
| @@have_dynamic_loading |
+------------------------+
| YES                    |
+------------------------+
1 row in set (0.00 sec)

root@db 19:03:  [(none)]>
root@db 19:04:  [(none)]>show plugins;
+-------------------------------+----------+--------------------+---------+---------+
| Name                          | Status   | Type               | Library | License |
+-------------------------------+----------+--------------------+---------+---------+
| binlog                        | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| mysql_native_password         | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| sha256_password               | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| PERFORMANCE_SCHEMA            | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| InnoDB                        | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| XTRADB_READ_VIEW              | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| XTRADB_INTERNAL_HASH_TABLES   | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| XTRADB_RSEG                   | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| XTRADB_ZIP_DICT               | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| XTRADB_ZIP_DICT_COLS          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_TRX                    | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_LOCKS                  | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_LOCK_WAITS             | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP                    | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_RESET              | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMPMEM                 | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMPMEM_RESET           | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_PER_INDEX          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_PER_INDEX_RESET    | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_PAGE            | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_PAGE_LRU        | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_POOL_STATS      | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_TEMP_TABLE_INFO        | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_METRICS                | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_DEFAULT_STOPWORD    | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_DELETED             | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_BEING_DELETED       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_CONFIG              | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_INDEX_CACHE         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_INDEX_TABLE         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_TABLES             | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_TABLESTATS         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_INDEXES            | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_COLUMNS            | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_FIELDS             | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_FOREIGN            | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_FOREIGN_COLS       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_TABLESPACES        | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_DATAFILES          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CHANGED_PAGES          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_VIRTUAL            | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_TABLESPACES_ENCRYPTION | ACTIVE   | INFORMATION SCHEMA | NULL    | BSD     |
| INNODB_TABLESPACES_SCRUBBING  | ACTIVE   | INFORMATION SCHEMA | NULL    | BSD     |
| MEMORY                        | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| CSV                           | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MyISAM                        | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MRG_MYISAM                    | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| partition                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| FEDERATED                     | DISABLED | STORAGE ENGINE     | NULL    | GPL     |
| ARCHIVE                       | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| BLACKHOLE                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| ngram                         | ACTIVE   | FTPARSER           | NULL    | GPL     |
+-------------------------------+----------+--------------------+---------+---------+
52 rows in set (0.00 sec)

root@db 19:04:  [(none)]>
root@db 19:04:  [(none)]>install plugin group_replication soname 'group_replication.so';
Query OK, 0 rows affected (0.40 sec)

root@db 19:04:  [(none)]>
root@db 19:04:  [(none)]>show plugins;
+-------------------------------+----------+--------------------+----------------------+---------+
| Name                          | Status   | Type               | Library              | License |
+-------------------------------+----------+--------------------+----------------------+---------+
| binlog                        | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| mysql_native_password         | ACTIVE   | AUTHENTICATION     | NULL                 | GPL     |
| sha256_password               | ACTIVE   | AUTHENTICATION     | NULL                 | GPL     |
| PERFORMANCE_SCHEMA            | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| InnoDB                        | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| XTRADB_READ_VIEW              | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| XTRADB_INTERNAL_HASH_TABLES   | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| XTRADB_RSEG                   | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| XTRADB_ZIP_DICT               | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| XTRADB_ZIP_DICT_COLS          | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_TRX                    | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_LOCKS                  | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_LOCK_WAITS             | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMP                    | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMP_RESET              | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMPMEM                 | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMPMEM_RESET           | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMP_PER_INDEX          | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMP_PER_INDEX_RESET    | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_BUFFER_PAGE            | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_BUFFER_PAGE_LRU        | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_BUFFER_POOL_STATS      | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_TEMP_TABLE_INFO        | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_METRICS                | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_DEFAULT_STOPWORD    | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_DELETED             | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_BEING_DELETED       | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_CONFIG              | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_INDEX_CACHE         | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_INDEX_TABLE         | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_TABLES             | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_TABLESTATS         | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_INDEXES            | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_COLUMNS            | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_FIELDS             | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_FOREIGN            | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_FOREIGN_COLS       | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_TABLESPACES        | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_DATAFILES          | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CHANGED_PAGES          | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_VIRTUAL            | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_TABLESPACES_ENCRYPTION | ACTIVE   | INFORMATION SCHEMA | NULL                 | BSD     |
| INNODB_TABLESPACES_SCRUBBING  | ACTIVE   | INFORMATION SCHEMA | NULL                 | BSD     |
| MEMORY                        | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| CSV                           | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| MyISAM                        | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| MRG_MYISAM                    | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| partition                     | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| FEDERATED                     | DISABLED | STORAGE ENGINE     | NULL                 | GPL     |
| ARCHIVE                       | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| BLACKHOLE                     | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| ngram                         | ACTIVE   | FTPARSER           | NULL                 | GPL     |
| group_replication             | ACTIVE   | GROUP REPLICATION  | group_replication.so | GPL     |
+-------------------------------+----------+--------------------+----------------------+---------+
53 rows in set (0.00 sec)

root@db 19:04:  [(none)]>

在所有节点上,设置MGR的复制账号:

[root@mysql1 ~]# mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.26-29-log Percona Server (GPL), Release 29, Revision 11ad961

Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

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.

root@db 19:08:  [(none)]>
root@db 19:08:  [(none)]>show variables like 'sql_log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin   | ON    |
+---------------+-------+
1 row in set (0.00 sec)

root@db 19:08:  [(none)]>
root@db 19:08:  [(none)]>set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

root@db 19:08:  [(none)]>
root@db 19:08:  [(none)]>show variables like 'sql_log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin   | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

root@db 19:08:  [(none)]>
root@db 19:08:  [(none)]>select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
3 rows in set (0.00 sec)

root@db 19:08:  [(none)]>
root@db 19:08:  [(none)]>create user repl@'%' identified by 'repl1';
Query OK, 0 rows affected (0.00 sec)

root@db 19:08:  [(none)]>select user,host from mysql.user; 
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| repl          | %         |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)

root@db 19:09:  [(none)]>
root@db 19:09:  [(none)]>show grants for repl@'%';
+----------------------------------+
| Grants for repl@%                |
+----------------------------------+
| GRANT USAGE ON *.* TO 'repl'@'%' |
+----------------------------------+
1 row in set (0.00 sec)

root@db 19:09:  [(none)]>
root@db 19:09:  [(none)]>grant replication slave on *.* to repl@'%';
Query OK, 0 rows affected (0.00 sec)

root@db 19:09:  [(none)]>
root@db 19:09:  [(none)]>show grants for repl@'%';                  
+----------------------------------------------+
| Grants for repl@%                            |
+----------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' |
+----------------------------------------------+
1 row in set (0.00 sec)

root@db 19:09:  [(none)]>
root@db 19:09:  [(none)]>flush privileges;
Query OK, 0 rows affected (0.00 sec)

root@db 19:09:  [(none)]>
root@db 19:09:  [(none)]>show variables like 'sql_log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin   | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

root@db 19:09:  [(none)]>
root@db 19:09:  [(none)]>set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)

root@db 19:10:  [(none)]>
root@db 19:10:  [(none)]>show variables like 'sql_log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin   | ON    |
+---------------+-------+
1 row in set (0.00 sec)

root@db 19:10:  [(none)]>
root@db 19:10:  [(none)]>show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      150 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

root@db 19:10:  [(none)]>
root@db 19:10:  [(none)]>show slave status;
Empty set (0.00 sec)

root@db 19:10:  [(none)]>
root@db 19:10:  [(none)]>change master to master_user='repl',master_password='repl1' for channel 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.34 sec)

root@db 19:11:  [(none)]>
root@db 19:11:  [(none)]>show slave status; 
Empty set (0.00 sec)

root@db 19:11:  [(none)]>

启动MGR:单主模式 – 主库

节点【1】:

[root@mysql1 ~]# mysql -u root 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.26-29-log Percona Server (GPL), Release 29, Revision 11ad961

Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

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.

root@db 19:15:  [(none)]>
root@db 19:15:  [(none)]>show variables like 'group_replication_bootstrap_group';
+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| group_replication_bootstrap_group | OFF   |
+-----------------------------------+-------+
1 row in set (0.00 sec)

root@db 19:16:  [(none)]>
root@db 19:16:  [(none)]>set global group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)

root@db 19:16:  [(none)]>show variables like 'group_replication_bootstrap_group';
+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| group_replication_bootstrap_group | ON    |
+-----------------------------------+-------+
1 row in set (0.00 sec)

root@db 19:16:  [(none)]>
root@db 19:16:  [(none)]>start group_replication;
Query OK, 0 rows affected (3.29 sec)

root@db 19:16:  [(none)]>set global group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)

root@db 19:17:  [(none)]>
root@db 19:17:  [(none)]>show variables like 'group_replication_bootstrap_group';
+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| group_replication_bootstrap_group | OFF   |
+-----------------------------------+-------+
1 row in set (0.00 sec)

root@db 19:17:  [(none)]>

这个过程的日志:

2019-08-15T02:11:23.825503Z 5 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=mysql1-relay-bin' to avoid this problem.
2019-08-15T02:11:24.151318Z 5 [Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'. Previous state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''.
2019-08-15T02:16:51.230069Z 6 [Note] Plugin group_replication reported: 'Group communication SSL configuration: group_replication_ssl_mode: "DISABLED"'
2019-08-15T02:16:51.230202Z 6 [Note] Plugin group_replication reported: '[GCS] Added automatically IP ranges 127.0.0.1/8,192.168.40.11/24 to the whitelist'
2019-08-15T02:16:51.230346Z 6 [Warning] Plugin group_replication reported: '[GCS] Automatically adding IPv4 localhost address to the whitelist. It is mandatory that it is added.'
2019-08-15T02:16:51.230400Z 6 [Note] Plugin group_replication reported: '[GCS] SSL was not enabled'
2019-08-15T02:16:51.230420Z 6 [Note] Plugin group_replication reported: 'Initialized group communication with configuration: group_replication_group_name: "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"; group_replication_local_address: "192.168.40.11:33061"; group_replication_group_seeds: "192.168.40.11:33061,192.168.40.12:33061,192.168.40.13:33061"; group_replication_bootstrap_group: true; group_replication_poll_spin_loops: 0; group_replication_compression_threshold: 1000000; group_replication_ip_whitelist: "AUTOMATIC"'
2019-08-15T02:16:51.230472Z 6 [Note] Plugin group_replication reported: '[GCS] Configured number of attempts to join: 0'
2019-08-15T02:16:51.230482Z 6 [Note] Plugin group_replication reported: '[GCS] Configured time between attempts to join: 5 seconds'
2019-08-15T02:16:51.230506Z 6 [Note] Plugin group_replication reported: 'Member configuration: member_id: 1; member_uuid: "61006e5f-beff-11e9-9d5e-000c2998ef22"; single-primary mode: "true"; group_replication_auto_increment_increment: 7; '
2019-08-15T02:16:51.552650Z 8 [Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_applier' executed'. Previous state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='<NULL>', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''.
2019-08-15T02:16:51.571393Z 11 [Note] Slave SQL thread for channel 'group_replication_applier' initialized, starting replication in log 'FIRST' at position 0, relay log './mysql1-relay-bin-group_replication_applier.000001' position: 4
2019-08-15T02:16:51.572767Z 6 [Note] Plugin group_replication reported: 'Group Replication applier module successfully initialized!'
2019-08-15T02:16:51.572831Z 6 [Note] Plugin group_replication reported: 'auto_increment_increment is set to 7'
2019-08-15T02:16:51.572846Z 6 [Note] Plugin group_replication reported: 'auto_increment_offset is set to 1'
2019-08-15T02:16:52.513704Z 0 [Note] Plugin group_replication reported: 'XCom protocol version: 3'
2019-08-15T02:16:52.513737Z 0 [Note] Plugin group_replication reported: 'XCom initialized and ready to accept incoming connections on port 33061'
2019-08-15T02:16:53.531054Z 14 [Note] Plugin group_replication reported: 'Only one server alive. Declaring this server as online within the replication group'
2019-08-15T02:16:53.531459Z 0 [Note] Plugin group_replication reported: 'Group membership changed to 192.168.40.11:3306 on view 15658354135168253:1.'
2019-08-15T02:16:53.547169Z 0 [Note] Plugin group_replication reported: 'This server was declared online within the replication group'
2019-08-15T02:16:53.547363Z 0 [Note] Plugin group_replication reported: 'A new primary with address 192.168.40.11:3306 was elected, enabling conflict detection until the new primary applies all relay logs.'
2019-08-15T02:16:53.547467Z 16 [Note] Plugin group_replication reported: 'This server is working as primary member.'

查看MGR当前的状态:

root@db 19:18:  [(none)]>select database();
+------------+
| database() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)

root@db 19:18:  [(none)]>select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

root@db 19:18:  [(none)]>
root@db 19:18:  [(none)]>desc performance_schema.replication_group_members;
+--------------+----------+------+-----+---------+-------+
| Field        | Type     | Null | Key | Default | Extra |
+--------------+----------+------+-----+---------+-------+
| CHANNEL_NAME | char(64) | NO   |     | NULL    |       |
| MEMBER_ID    | char(36) | NO   |     | NULL    |       |
| MEMBER_HOST  | char(60) | NO   |     | NULL    |       |
| MEMBER_PORT  | int(11)  | YES  |     | NULL    |       |
| MEMBER_STATE | char(64) | NO   |     | NULL    |       |
+--------------+----------+------+-----+---------+-------+
5 rows in set (0.00 sec)

root@db 19:19:  [(none)]>
root@db 19:19:  [(none)]>select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+---------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST   | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+---------------+-------------+--------------+
| group_replication_applier | 61006e5f-beff-11e9-9d5e-000c2998ef22 | 192.168.40.11 |        3306 | ONLINE       |
+---------------------------+--------------------------------------+---------------+-------------+--------------+
1 row in set (0.00 sec)

root@db 19:19:  [(none)]>

这样,单主模式下的MGR的主库的配置就完成了。


启动MGR:单主模式 – 成员库

单主模式下,成员库(从库)直接启动MGR,就可以加入MGR:

节点【2 / 3】

[root@mysql2 ~]# mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.26-29-log Percona Server (GPL), Release 29, Revision 11ad961

Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

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.

root@db 19:22:  [(none)]>
root@db 19:22:  [(none)]>start group_replication;
Query OK, 0 rows affected (12.25 sec)

root@db 19:23:  [(none)]>

MGR启动成功后,去MGR主库上查看当前MGR的状态:

节点【1】:

root@db 19:24:  [(none)]>select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+---------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST   | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+---------------+-------------+--------------+
| group_replication_applier | 61006e5f-beff-11e9-9d5e-000c2998ef22 | 192.168.40.11 |        3306 | ONLINE       |
| group_replication_applier | 794882d1-beff-11e9-9f51-000c2968d274 | 192.168.40.12 |        3306 | ONLINE       |
+---------------------------+--------------------------------------+---------------+-------------+--------------+
2 rows in set (0.00 sec)

root@db 19:24:  [(none)]>

这个过程中的日志:

节点二加入MGR时候的日志

节点【1】

2019-08-15T02:22:59.759502Z 0 [Note] Plugin group_replication reported: 'Members joined the group: 192.168.40.12:3306'
2019-08-15T02:22:59.759825Z 0 [Note] Plugin group_replication reported: 'Group membership changed to 192.168.40.11:3306, 192.168.40.12:3306 on view 15658354135168253:2.'
2019-08-15T02:22:59.812330Z 19 [Note] Start binlog_dump to master_thread_id(19) slave_server(2), pos(, 4)
2019-08-15T02:22:59.855596Z 0 [Note] Plugin group_replication reported: 'The member with address 192.168.40.12:3306 was declared online within the replication group'

节点【2】

2019-08-15T02:22:48.482965Z 8 [Note] Plugin group_replication reported: 'Group communication SSL configuration: group_replication_ssl_mode: "DISABLED"'
2019-08-15T02:22:48.483068Z 8 [Note] Plugin group_replication reported: '[GCS] Added automatically IP ranges 127.0.0.1/8,192.168.40.12/24 to the whitelist'
2019-08-15T02:22:48.483215Z 8 [Warning] Plugin group_replication reported: '[GCS] Automatically adding IPv4 localhost address to the whitelist. It is mandatory that it is added.'
2019-08-15T02:22:48.492134Z 8 [Note] Plugin group_replication reported: '[GCS] SSL was not enabled'
2019-08-15T02:22:48.492180Z 8 [Note] Plugin group_replication reported: 'Initialized group communication with configuration: group_replication_group_name: "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"; group_replication_local_address: "192.168.40.12:33061"; group_replication_group_seeds: "192.168.40.11:33061,192.168.40.12:33061,192.168.40.13:33061"; group_replication_bootstrap_group: false; group_replication_poll_spin_loops: 0; group_replication_compression_threshold: 1000000; group_replication_ip_whitelist: "AUTOMATIC"'
2019-08-15T02:22:48.492216Z 8 [Note] Plugin group_replication reported: '[GCS] Configured number of attempts to join: 0'
2019-08-15T02:22:48.492221Z 8 [Note] Plugin group_replication reported: '[GCS] Configured time between attempts to join: 5 seconds'
2019-08-15T02:22:48.492241Z 8 [Note] Plugin group_replication reported: 'Member configuration: member_id: 2; member_uuid: "794882d1-beff-11e9-9f51-000c2968d274"; single-primary mode: "true"; group_replication_auto_increment_increment: 7; '
2019-08-15T02:22:48.817450Z 10 [Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_applier' executed'. Previous state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='<NULL>', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''.
2019-08-15T02:22:48.825430Z 13 [Note] Slave SQL thread for channel 'group_replication_applier' initialized, starting replication in log 'FIRST' at position 0, relay log './mysql2-relay-bin-group_replication_applier.000001' position: 4
2019-08-15T02:22:48.825887Z 8 [Note] Plugin group_replication reported: 'Group Replication applier module successfully initialized!'
2019-08-15T02:22:48.825909Z 8 [Note] Plugin group_replication reported: 'auto_increment_increment is set to 7'
2019-08-15T02:22:48.825914Z 8 [Note] Plugin group_replication reported: 'auto_increment_offset is set to 2'
2019-08-15T02:22:55.067575Z 0 [Note] Plugin group_replication reported: 'XCom protocol version: 3'
2019-08-15T02:22:55.067622Z 0 [Note] Plugin group_replication reported: 'XCom initialized and ready to accept incoming connections on port 33061'
2019-08-15T02:22:59.729993Z 8 [Note] Plugin group_replication reported: 'This server is working as secondary member with primary member address 192.168.40.11:3306.'
2019-08-15T02:22:59.731361Z 16 [Note] Plugin group_replication reported: 'Establishing group recovery connection with a possible donor. Attempt 1/10'
2019-08-15T02:22:59.732093Z 0 [Note] Plugin group_replication reported: 'Group membership changed to 192.168.40.11:3306, 192.168.40.12:3306 on view 15658354135168253:2.'
2019-08-15T02:22:59.755980Z 16 [Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'. Previous state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='192.168.40.11', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''.
2019-08-15T02:22:59.769369Z 16 [Note] Plugin group_replication reported: 'Establishing connection to a group replication recovery donor 61006e5f-beff-11e9-9d5e-000c2998ef22 at 192.168.40.11 port: 3306.'
2019-08-15T02:22:59.770525Z 18 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2019-08-15T02:22:59.773945Z 18 [Note] Slave I/O thread for channel 'group_replication_recovery': connected to master 'repl@192.168.40.11:3306',replication started in log 'FIRST' at position 4
2019-08-15T02:22:59.774664Z 19 [Note] Slave SQL thread for channel 'group_replication_recovery' initialized, starting replication in log 'FIRST' at position 0, relay log './mysql2-relay-bin-group_replication_recovery.000001' position: 4
2019-08-15T02:22:59.793166Z 16 [Note] Plugin group_replication reported: 'Terminating existing group replication donor connection and purging the corresponding logs.'
2019-08-15T02:22:59.794135Z 19 [Note] Slave SQL thread for channel 'group_replication_recovery' exiting, replication stopped in log 'mysql-bin.000002' at position 758
2019-08-15T02:22:59.795717Z 18 [Note] Slave I/O thread killed while reading event for channel 'group_replication_recovery'
2019-08-15T02:22:59.795750Z 18 [Note] Slave I/O thread exiting for channel 'group_replication_recovery', read up to log 'mysql-bin.000002', position 758
2019-08-15T02:22:59.812013Z 16 [Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'. Previous state master_host='192.168.40.11', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='<NULL>', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''.
2019-08-15T02:22:59.825005Z 0 [Note] Plugin group_replication reported: 'This server was declared online within the replication group'

节点三加入MGR的时候的日志

节点【1】

2019-08-15T02:27:10.440253Z 0 [Note] Plugin group_replication reported: 'Members joined the group: 192.168.40.13:3306'
2019-08-15T02:27:10.440673Z 0 [Note] Plugin group_replication reported: 'Group membership changed to 192.168.40.11:3306, 192.168.40.12:3306, 192.168.40.13:3306 on view 15658354135168253:3.'
2019-08-15T02:27:10.559377Z 0 [Note] Plugin group_replication reported: 'The member with address 192.168.40.13:3306 was declared online within the replication group'

节点【2】





节点【3】






最后,再看看MGR当前的状态:

[root@mysql1 ~]# mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 24
Server version: 5.7.26-29-log Percona Server (GPL), Release 29, Revision 11ad961

Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

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.

root@db 19:29:  [(none)]>select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+---------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST   | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+---------------+-------------+--------------+
| group_replication_applier | 61006e5f-beff-11e9-9d5e-000c2998ef22 | 192.168.40.11 |        3306 | ONLINE       |
| group_replication_applier | 794882d1-beff-11e9-9f51-000c2968d274 | 192.168.40.12 |        3306 | ONLINE       |
| group_replication_applier | 7a8c2267-beff-11e9-9cb1-000c290a3ba7 | 192.168.40.13 |        3306 | ONLINE       |
+---------------------------+--------------------------------------+---------------+-------------+--------------+
3 rows in set (0.00 sec)

root@db 19:30:  [(none)]>

可以看到,上面的查询结果中并没有关于MGR成员节点的角色信息;
这一块的信息可以通过以下SQL查询:

SELECT 
    MEMBER_ID,
    MEMBER_HOST,
    MEMBER_PORT,
    MEMBER_STATE,
    IF(global_status.VARIABLE_NAME IS NOT NULL,
        'PRIMARY',
        'SECONDARY') AS MEMBER_ROLE
FROM
    performance_schema.replication_group_members
        LEFT JOIN
    performance_schema.global_status ON global_status.VARIABLE_NAME = 'group_replication_primary_member'
        AND global_status.VARIABLE_VALUE = replication_group_members.MEMBER_ID;

执行效果如下:

root@db 19:47:  [(none)]>SELECT 
    ->     MEMBER_ID,
    ->     MEMBER_HOST,
    ->     MEMBER_PORT,
    ->     MEMBER_STATE,
    ->     IF(global_status.VARIABLE_NAME IS NOT NULL,
    ->         'PRIMARY',
    ->         'SECONDARY') AS MEMBER_ROLE
    -> FROM
    ->     performance_schema.replication_group_members
    ->         LEFT JOIN
    ->     performance_schema.global_status ON global_status.VARIABLE_NAME = 'group_replication_primary_member'
    ->         AND global_status.VARIABLE_VALUE = replication_group_members.MEMBER_ID;
+--------------------------------------+---------------+-------------+--------------+-------------+
| MEMBER_ID                            | MEMBER_HOST   | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE |
+--------------------------------------+---------------+-------------+--------------+-------------+
| 61006e5f-beff-11e9-9d5e-000c2998ef22 | 192.168.40.11 |        3306 | ONLINE       | PRIMARY     |
| 794882d1-beff-11e9-9f51-000c2968d274 | 192.168.40.12 |        3306 | ONLINE       | SECONDARY   |
| 7a8c2267-beff-11e9-9cb1-000c290a3ba7 | 192.168.40.13 |        3306 | ONLINE       | SECONDARY   |
+--------------------------------------+---------------+-------------+--------------+-------------+
3 rows in set (0.00 sec)

root@db 19:47:  [(none)]>

这样,就可以清晰地看到MGR的成员节点的角色(ROLE)信息了。


至此,MGR的单主模式,配置完成。


测试以下:

在主库上创建一个库,并建表写入一些数据,看看从库是否能够同步到数据。

节点【1】:创建数据库

root@db 19:53:  [(none)]>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

root@db 19:54:  [(none)]>create database adamhuan;
Query OK, 1 row affected (0.01 sec)

root@db 19:54:  [(none)]>
root@db 19:54:  [(none)]>show databases;          
+--------------------+
| Database           |
+--------------------+
| information_schema |
| adamhuan           |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

root@db 19:54:  [(none)]>

节点【2 / 3】

root@db 19:53:  [(none)]>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

root@db 19:54:  [(none)]>
root@db 19:54:  [(none)]>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| adamhuan           |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

root@db 19:54:  [(none)]>

可以看到,从库同步到了主库的变化。

这个过程中的日志

节点【1】

2019-08-15T02:54:28.833495Z 8 [Note] Plugin group_replication reported: 'Primary had applied all relay logs, disabled conflict detection'

节点【2】

2019-08-15T02:54:28.801646Z 10 [Note] Plugin group_replication reported: 'Primary had applied all relay logs, disabled conflict detection'

节点【3】

2019-08-15T02:54:28.833724Z 10 [Note] Plugin group_replication reported: 'Primary had applied all relay logs, disabled conflict detection'

将MGR从【单主模式】切换成【多主模式】


在所有节点上, 停MGR:

root@db 20:05:  [(none)]>SELECT      MEMBER_ID,     MEMBER_HOST,     MEMBER_PORT,     MEMBER_STATE,     IF(global_status.VARIABLE_NAME IS NOT NULL,         'PRIMARY',         'SECONDARY') AS MEMBER_ROLE FROM     performance_schema.replication_group_members         LEFT JOIN     performance_schema.global_status ON global_status.VARIABLE_NAME = 'group_replication_primary_member'         AND global_status.VARIABLE_VALUE = replication_group_members.MEMBER_ID;
+--------------------------------------+---------------+-------------+--------------+-------------+
| MEMBER_ID                            | MEMBER_HOST   | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE |
+--------------------------------------+---------------+-------------+--------------+-------------+
| 61006e5f-beff-11e9-9d5e-000c2998ef22 | 192.168.40.11 |        3306 | ONLINE       | PRIMARY     |
| 794882d1-beff-11e9-9f51-000c2968d274 | 192.168.40.12 |        3306 | ONLINE       | SECONDARY   |
| 7a8c2267-beff-11e9-9cb1-000c290a3ba7 | 192.168.40.13 |        3306 | ONLINE       | SECONDARY   |
+--------------------------------------+---------------+-------------+--------------+-------------+
3 rows in set (0.00 sec)

root@db 20:06:  [(none)]>
root@db 20:06:  [(none)]>stop group_replication;
Query OK, 0 rows affected (9.42 sec)

root@db 20:06:  [(none)]>
root@db 20:06:  [(none)]>SELECT      MEMBER_ID,     MEMBER_HOST,     MEMBER_PORT,     MEMBER_STATE,     IF(global_status.VARIABLE_NAME IS NOT NULL,         'PRIMARY',         'SECONDARY') AS MEMBER_ROLE FROM     performance_schema.replication_group_members         LEFT JOIN     performance_schema.global_status ON global_status.VARIABLE_NAME = 'group_replication_primary_member'         AND global_status.VARIABLE_VALUE = replication_group_members.MEMBER_ID;
+--------------------------------------+---------------+-------------+--------------+-------------+
| MEMBER_ID                            | MEMBER_HOST   | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE |
+--------------------------------------+---------------+-------------+--------------+-------------+
| 61006e5f-beff-11e9-9d5e-000c2998ef22 | 192.168.40.11 |        3306 | OFFLINE      | SECONDARY   |
+--------------------------------------+---------------+-------------+--------------+-------------+
1 row in set (0.00 sec)

root@db 20:06:  [(none)]>

这个过程中的日志:

节点【1】

2019-08-15T03:06:11.923056Z 27 [Note] Plugin group_replication reported: 'Plugin 'group_replication' is stopping.'
2019-08-15T03:06:11.923119Z 27 [Note] Plugin group_replication reported: 'Going to wait for view modification'
2019-08-15T03:06:15.342959Z 0 [Note] Plugin group_replication reported: 'Group membership changed: This member has left the group.'
2019-08-15T03:06:20.347623Z 27 [Note] Plugin group_replication reported: 'auto_increment_increment is reset to 1'
2019-08-15T03:06:20.347678Z 27 [Note] Plugin group_replication reported: 'auto_increment_offset is reset to 1'
2019-08-15T03:06:20.347852Z 11 [Note] Error reading relay log event for channel 'group_replication_applier': slave SQL thread was killed
2019-08-15T03:06:20.347863Z 11 [Note] Slave SQL thread for channel 'group_replication_applier' exiting, replication stopped in log 'FIRST' at position 65
2019-08-15T03:06:20.348695Z 8 [Note] Plugin group_replication reported: 'The group replication applier thread was killed'
2019-08-15T03:06:20.348948Z 27 [Note] Plugin group_replication reported: 'Plugin 'group_replication' has been stopped.'

节点【2】

2019-08-15T03:06:12.404854Z 0 [Warning] Plugin group_replication reported: 'Members removed from the group: 192.168.40.11:3306'
2019-08-15T03:06:12.404916Z 0 [Note] Plugin group_replication reported: 'Primary server with address 192.168.40.11:3306 left the group. Electing new Primary.'
2019-08-15T03:06:12.405050Z 0 [Note] Plugin group_replication reported: 'A new primary with address 192.168.40.12:3306 was elected, enabling conflict detection until the new primary applies all relay logs.'
2019-08-15T03:06:12.405128Z 30 [Note] Plugin group_replication reported: 'This server is working as primary member.'
2019-08-15T03:06:12.405187Z 0 [Note] Plugin group_replication reported: 'Group membership changed to 192.168.40.12:3306, 192.168.40.13:3306 on view 15658354135168253:4.'

节点【3】

2019-08-15T03:06:12.436596Z 0 [Warning] Plugin group_replication reported: 'Members removed from the group: 192.168.40.11:3306'
2019-08-15T03:06:12.436728Z 0 [Note] Plugin group_replication reported: 'Primary server with address 192.168.40.11:3306 left the group. Electing new Primary.'
2019-08-15T03:06:12.436877Z 0 [Note] Plugin group_replication reported: 'A new primary with address 192.168.40.12:3306 was elected, enabling conflict detection until the new primary applies all relay logs.'
2019-08-15T03:06:12.436966Z 24 [Note] Plugin group_replication reported: 'This server is working as secondary member with primary member address 192.168.40.12:3306.'
2019-08-15T03:06:12.437027Z 0 [Note] Plugin group_replication reported: 'Group membership changed to 192.168.40.12:3306, 192.168.40.13:3306 on view 15658354135168253:4.'

在所有节点上,设置参数:

root@db 20:09:  [(none)]>show variables like 'group_replication_single_primary_mode';
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| group_replication_single_primary_mode | ON    |
+---------------------------------------+-------+
1 row in set (0.00 sec)

root@db 20:09:  [(none)]>set global group_replication_single_primary_mode=OFF;
Query OK, 0 rows affected (0.00 sec)

root@db 20:09:  [(none)]>show variables like 'group_replication_single_primary_mode';
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| group_replication_single_primary_mode | OFF   |
+---------------------------------------+-------+
1 row in set (0.00 sec)

root@db 20:09:  [(none)]>
root@db 20:10:  [(none)]>show variables like 'group_replication_enforce_update_everywhere_checks';
+----------------------------------------------------+-------+
| Variable_name                                      | Value |
+----------------------------------------------------+-------+
| group_replication_enforce_update_everywhere_checks | OFF   |
+----------------------------------------------------+-------+
1 row in set (0.00 sec)

root@db 20:10:  [(none)]>
root@db 20:10:  [(none)]>set global group_replication_enforce_update_everywhere_checks=ON;        
Query OK, 0 rows affected (0.00 sec)

root@db 20:11:  [(none)]>
root@db 20:11:  [(none)]>show variables like 'group_replication_enforce_update_everywhere_checks';
+----------------------------------------------------+-------+
| Variable_name                                      | Value |
+----------------------------------------------------+-------+
| group_replication_enforce_update_everywhere_checks | ON    |
+----------------------------------------------------+-------+
1 row in set (0.00 sec)

root@db 20:11:  [(none)]>

然后,在其中任意某一个节点上设置参数,并启动MGR:

root@db 20:13:  [(none)]>show variables like 'group_replication_bootstrap_group';
+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| group_replication_bootstrap_group | OFF   |
+-----------------------------------+-------+
1 row in set (0.00 sec)

root@db 20:14:  [(none)]>set global group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)

root@db 20:14:  [(none)]>show variables like 'group_replication_bootstrap_group';
+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| group_replication_bootstrap_group | ON    |
+-----------------------------------+-------+
1 row in set (0.00 sec)

root@db 20:14:  [(none)]>
root@db 20:14:  [(none)]>start group_replication;
Query OK, 0 rows affected (2.02 sec)

root@db 20:15:  [(none)]>set global group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)

root@db 20:15:  [(none)]>show variables like 'group_replication_bootstrap_group';
+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| group_replication_bootstrap_group | OFF   |
+-----------------------------------+-------+
1 row in set (0.00 sec)

root@db 20:15:  [(none)]>

这个过程的日志如下:

2019-08-15T03:15:28.390923Z 28 [Note] Plugin group_replication reported: 'Group communication SSL configuration: group_replication_ssl_mode: "DISABLED"'
2019-08-15T03:15:28.391018Z 28 [Note] Plugin group_replication reported: '[GCS] Added automatically IP ranges 127.0.0.1/8,192.168.40.12/24 to the whitelist'
2019-08-15T03:15:28.391094Z 28 [Warning] Plugin group_replication reported: '[GCS] Automatically adding IPv4 localhost address to the whitelist. It is mandatory that it is added.'
2019-08-15T03:15:28.391124Z 28 [Note] Plugin group_replication reported: '[GCS] SSL was not enabled'
2019-08-15T03:15:28.391139Z 28 [Note] Plugin group_replication reported: 'Initialized group communication with configuration: group_replication_group_name: "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"; group_replication_local_address: "192.168.40.12:33061"; group_replication_group_seeds: "192.168.40.11:33061,192.168.40.12:33061,192.168.40.13:33061"; group_replication_bootstrap_group: true; group_replication_poll_spin_loops: 0; group_replication_compression_threshold: 1000000; group_replication_ip_whitelist: "AUTOMATIC"'
2019-08-15T03:15:28.391156Z 28 [Note] Plugin group_replication reported: '[GCS] Configured number of attempts to join: 0'
2019-08-15T03:15:28.391161Z 28 [Note] Plugin group_replication reported: '[GCS] Configured time between attempts to join: 5 seconds'
2019-08-15T03:15:28.391176Z 28 [Note] Plugin group_replication reported: 'Member configuration: member_id: 2; member_uuid: "794882d1-beff-11e9-9f51-000c2968d274"; single-primary mode: "false"; group_replication_auto_increment_increment: 7; '
2019-08-15T03:15:28.391733Z 33 [Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_applier' executed'. Previous state master_host='<NULL>', master_port= 0, master_log_file='', master_log_pos= 830, master_bind=''. New state master_host='<NULL>', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''.
2019-08-15T03:15:28.395645Z 36 [Note] Slave SQL thread for channel 'group_replication_applier' initialized, starting replication in log 'FIRST' at position 0, relay log './mysql2-relay-bin-group_replication_applier.000002' position: 1095
2019-08-15T03:15:28.395996Z 28 [Note] Plugin group_replication reported: 'Group Replication applier module successfully initialized!'
2019-08-15T03:15:28.396015Z 28 [Note] Plugin group_replication reported: 'auto_increment_increment is set to 7'
2019-08-15T03:15:28.396020Z 28 [Note] Plugin group_replication reported: 'auto_increment_offset is set to 2'
2019-08-15T03:15:28.396604Z 0 [Note] Plugin group_replication reported: 'XCom protocol version: 3'
2019-08-15T03:15:28.396626Z 0 [Note] Plugin group_replication reported: 'XCom initialized and ready to accept incoming connections on port 33061'
2019-08-15T03:15:29.404797Z 39 [Note] Plugin group_replication reported: 'Only one server alive. Declaring this server as online within the replication group'
2019-08-15T03:15:29.404849Z 0 [Note] Plugin group_replication reported: 'Group membership changed to 192.168.40.12:3306 on view 15658389294045576:1.'
2019-08-15T03:15:29.405732Z 0 [Note] Plugin group_replication reported: 'This server was declared online within the replication group'

查看MGR的当前状态:

root@db 20:17:  [(none)]>SELECT      MEMBER_ID,     MEMBER_HOST,     MEMBER_PORT,     MEMBER_STATE,     IF(global_status.VARIABLE_NAME IS NOT NULL,         'PRIMARY',         'SECONDARY') AS MEMBER_ROLE FROM     performance_schema.replication_group_members         LEFT JOIN     performance_schema.global_status ON global_status.VARIABLE_NAME = 'group_replication_primary_member'         AND global_status.VARIABLE_VALUE = replication_group_members.MEMBER_ID;
+--------------------------------------+---------------+-------------+--------------+-------------+
| MEMBER_ID                            | MEMBER_HOST   | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE |
+--------------------------------------+---------------+-------------+--------------+-------------+
| 794882d1-beff-11e9-9f51-000c2968d274 | 192.168.40.12 |        3306 | ONLINE       | SECONDARY   |
+--------------------------------------+---------------+-------------+--------------+-------------+
1 row in set (0.01 sec)

root@db 20:17:  [(none)]>

接着,把其他两个节点的MGR也启动:

root@db 20:13:  [(none)]>start group_replication;
Query OK, 0 rows affected (6.01 sec)

root@db 20:18:  [(none)]>

最后,等到所有节点的MGR都启动好了以后,再看看MGR的状态:

root@db 20:19:  [(none)]>SELECT      MEMBER_ID,     MEMBER_HOST,     MEMBER_PORT,     MEMBER_STATE,     IF(global_status.VARIABLE_NAME IS NOT NULL,         'PRIMARY',         'SECONDARY') AS MEMBER_ROLE FROM     performance_schema.replication_group_members         LEFT JOIN     performance_schema.global_status ON global_status.VARIABLE_NAME = 'group_replication_primary_member'         AND global_status.VARIABLE_VALUE = replication_group_members.MEMBER_ID;
+--------------------------------------+---------------+-------------+--------------+-------------+
| MEMBER_ID                            | MEMBER_HOST   | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE |
+--------------------------------------+---------------+-------------+--------------+-------------+
| 61006e5f-beff-11e9-9d5e-000c2998ef22 | 192.168.40.11 |        3306 | ONLINE       | SECONDARY   |
| 794882d1-beff-11e9-9f51-000c2968d274 | 192.168.40.12 |        3306 | ONLINE       | SECONDARY   |
| 7a8c2267-beff-11e9-9cb1-000c290a3ba7 | 192.168.40.13 |        3306 | ONLINE       | SECONDARY   |
+--------------------------------------+---------------+-------------+--------------+-------------+
3 rows in set (0.00 sec)

root@db 20:19:  [(none)]>

测试一下:

任意一个节点创建一个表:

节点【3】

root@db 20:22:  [(none)]>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| adamhuan           |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

root@db 20:22:  [(none)]>use adamhuan
Database changed
root@db 20:22:  [adamhuan]>show tables;
Empty set (0.00 sec)

root@db 20:22:  [adamhuan]>
root@db 20:22:  [adamhuan]>create table people(id int,name varchar(30));
Query OK, 0 rows affected (0.00 sec)

root@db 20:23:  [adamhuan]>
root@db 20:23:  [adamhuan]>show tables;
+--------------------+
| Tables_in_adamhuan |
+--------------------+
| people             |
+--------------------+
1 row in set (0.00 sec)

root@db 20:23:  [adamhuan]>
root@db 20:23:  [adamhuan]>desc people;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)

root@db 20:23:  [adamhuan]>

节点【1 / 2】

root@db 20:20:  [(none)]>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| adamhuan           |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

root@db 20:22:  [(none)]>use adamhuan
Database changed
root@db 20:22:  [adamhuan]>show tables;
Empty set (0.00 sec)

root@db 20:22:  [adamhuan]>show tables;
+--------------------+
| Tables_in_adamhuan |
+--------------------+
| people             |
+--------------------+
1 row in set (0.00 sec)

root@db 20:23:  [adamhuan]>

可以看到,同步到了数据


从MGR的【多主模式】切回【单主模式】

# 所有节点执行
mysql> stop group_replication;
mysql> set global group_replication_enforce_update_everywhere_checks=OFF;
mysql> set global group_replication_single_primary_mode=ON;

# 主节点执行
SET GLOBAL group_replication_bootstrap_group=ON; 
START GROUP_REPLICATION; 
SET GLOBAL group_replication_bootstrap_group=OFF;

# 从节点执行
START GROUP_REPLICATION; 

终了,…

说点什么

avatar

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据

  Subscribe  
提醒
隐藏
变装