Debian11 MySQL8 MGR 单主集群配置

附件

mysql.cnf参考

Prepare

  • Linux系统

    uname -a

    Linux VM-MySQL8-I1 5.10.0-18-amd64 #1 SMP Debian 5.10.140-1 (2022-09-02) x86_64 GNU/Linux

  • MySQL

    select version() from dual;

    8.0.31

  • MGR规划（单主）

    Master:

    IP：192.168.56.159

    连接端口：33096

    MGR端口：33097

    server-id：10001

    Slaves:

    # node2

    IP：192.168.56.158

    连接端口：33096

    MGR端口：33097

    server-id：10002

    # node3

    IP：192.168.56.157

    连接端口：33096

    MGR端口：33097

    server-id：10003

  • MySQL复制组帐户

    账户：repuser

    密码：xxxxxxxxxxxxxxxxxx

  • 本地域名配置 /etc/hosts

    192.168.56.159 node1.mysql.db.xxxxxx.com

    192.168.56.158 node2.mysql.db.xxxxxx.com

    192.168.56.157 node3.mysql.db.xxxxxx.com

  • MGR GROUP的 UUID，使用uuidgen生成，后面会描述使用方法

    062b006d-ac57-4e5f-a7fb-16a88fc98ec5

配置所有节点

  • 配置本地host

    vim /etc/hosts

    效果

    127.0.0.1       localhost
192.168.56.159  VM-MySQL8-I1

# The following lines are desirable for IPv6 capable hosts
::1     localhost ip6-localhost ip6-loopback
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters

192.168.56.159 node1.mysql.db.xxxxxx.com
192.168.56.158 node2.mysql.db.xxxxxx.com
192.168.56.157 node3.mysql.db.xxxxxx.com

  • 测试域名配置，及网络连通性

    ping node1.mysql.db.xxxxxx.com
ping node2.mysql.db.xxxxxx.com
ping node3.mysql.db.xxxxxx.com

  • 开放防火墙

    查看防火墙状态

    ufw status

    开放端口

    ufw allow 33096/tcp
ufw allow 33097/tcp

    确认端口开放状态

    ufw status

  • 安装uuid-runtime，后面会用到

    apt install -y uuid-runtime

配置主节点MGR Master

  • 生成MGR GROUP的 UUID

    uuidgen

    暂存，稍后在编辑配置文件时，设定为loose-group_replication_group_name的值（注意：每个节点不同）

    062b006d-ac57-4e5f-a7fb-16a88fc98ec5

  • 备份原配置文件

    cp /etc/mysql/conf.d/mysql.cnf /etc/mysql/conf.d/mysql.cnf.bak

  • 修改MySQL8配置文件mysql.cnf

    vim /etc/mysql/conf.d/mysql.cnf

    特殊修改：

    # server-id必须是唯一的
server-id=10001

# 相当于此GROUP的名字,是UUID值,不能和集群内其他GTID值的UUID混用,可用uuidgen来生成一个新的,
# 主要是用来区分整个内网里边的各个不同的GROUP,而且也是这个group内的GTID值的UUID
loose-group_replication_group_name='062b006d-ac57-4e5f-a7fb-16a88fc98ec5'

# 本地MGR的IP地址和端口，host:port,是MGR的端口,不是数据库的端口
loose-group_replication_local_address='192.168.56.159:33097'

# 需要接受本MGR实例控制的服务器IP地址和端口,是MGR的端口,不是数据库的端口
#loose-group_replication_group_seeds='192.168.56.159:33097,192.168.56.158:33097,192.168.56.157:33097'

# 是否启动单主模式，如果启动，则本实例是主库，提供读写，其他实例仅提供读,如果为off就是多主模式了
loose-group_replication_single_primary_mode=on

    内容如下

    # Copyright (c) 2015, 2022, Oracle and/or its affiliates.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License, version 2.0,
# as published by the Free Software Foundation.
#
# This program is also distributed with certain software (including
# but not limited to OpenSSL) that is licensed under separate terms,
# as designated in a particular file or component or in included license
# documentation.  The authors of MySQL hereby grant you an additional
# permission to link the program and your derivative works with the
# separately licensed software that they have included with MySQL.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License, version 2.0, for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301  USA

#
# The MySQL  Client configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[client]
default-character-set=utf8mb4

[mysql]

[mysqld]
port=33096

datadir=/home/mysql/data
socket=/home/mysql/mysqld.sock

symbolic-links=0
lower_case_table_names=1

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

# server-id必须是唯一的
server-id=10001
log-bin=mysql-bin
log-bin-index=binlogs.index
# MGR使用乐观锁,所以官网建议隔离级别是RC,减少锁粒度
transaction_isolation=READ-COMMITTED
# binlog格式,MGR要求必须是ROW,不过就算不是MGR,也最好用row
binlog_format=row
binlog_rows_query_log_events=on
# binlog校验规则,5.6之后的高版本是CRC32,低版本都是NONE,但是MGR要求使用NONE
binlog_checksum=none

slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=4
slave_preserve_commit_order=1

# GTID
# 开启GTID,必须开启
gtid_mode=on
# 强制GTID的一致性
enforce_gtid_consistency=1
# 因为集群会在故障恢复时互相检查binlog的数据,
# 所以需要记录下集群内其他服务器发过来已经执行过的binlog,按GTID来区分是否执行过.
log-slave-updates=1
binlog_gtid_simple_recovery=1

#relay_log=/usr/mysql/relay.log
#relay-log-index=/usr/mysql/relay.index
# 基于安全的考虑,MGR集群要求复制模式要改成slave记录记录到表中,不然就报错
master_info_repository=table
# 同上配套
relay_log_info_repository=table

# MGR
plugin_load="group_replication=group_replication.so"

# 组复制设置
# 记录事务的算法,官网建议设置该参数使用 XXHASH64 算法
transaction_write_set_extraction=XXHASH64

# 相当于此GROUP的名字,是UUID值,不能和集群内其他GTID值的UUID混用,可用uuidgen来生成一个新的,
# 主要是用来区分整个内网里边的各个不同的GROUP,而且也是这个group内的GTID值的UUID
loose-group_replication_group_name='062b006d-ac57-4e5f-a7fb-16a88fc98ec5'

# IP地址白名单,默认只添加127.0.0.1,不会允许来自外部主机的连接,按需安全设置
#loose-group_replication_ip_whitelist='127.0.0.1/8,192.168.56.0/24,10.10.10.0/24'

# 是否随服务器启动而自动启动组复制,不建议直接启动,怕故障恢复时有扰乱数据准确性的特殊情况
loose-group_replication_start_on_boot=on

# 本地MGR的IP地址和端口，host:port,是MGR的端口,不是数据库的端口
loose-group_replication_local_address='192.168.56.159:33097'

# 需要接受本MGR实例控制的服务器IP地址和端口,是MGR的端口,不是数据库的端口
#loose-group_replication_group_seeds='192.168.56.159:33097,192.168.56.158:33097,192.168.56.157:33097'

# 开启引导模式,添加组成员，用于第一次搭建MGR或重建MGR的时候使用,只需要在集群内的其中一台开启
# 如果打开会造成脑裂 
# 是否启动集群，注意，该选项任何时候只能用于一个节点，通常情况下启动集群的时候使用，启动之后需要关闭该选项
loose-group_replication_bootstrap_group=on

# 是否启动单主模式，如果启动，则本实例是主库，提供读写，其他实例仅提供读,如果为off就是多主模式了
loose-group_replication_single_primary_mode=on

# 多主模式下,强制检查每一个实例是否允许该操作,如果不是多主,可以关闭
#loose-group_replication_enforce_update_everywhere_checks=off

# 权重选择
loose-group_replication_member_weight = 50

skip-host-cache
skip-name-resolve
skip-external-locking
character-set-server=utf8mb4
event_scheduler=on
log_bin_trust_function_creators=on

max_connections=3000
external-locking=FALSE
#max_allowed_packet=32M
#sort_buffer_size=8M
#join_buffer_size=2M
thread_cache_size=300
#query_cache_size=64M
#query_cache_limit=4M
#read_rnd_buffer_size=8M

#innodb_buffer_pool_size=8096M
#innodb_log_file_size=128M
innodb_log_files_in_group=2
#innodb_log_buffer_size=2M
innodb_flush_log_at_trx_commit=1
sync_binlog=1

# only_full_group_by报错  
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

[mysqld_safe]
log-error=/var/log/mysqld-safe.log

  • 重启MySQL，并查看状态为active (running)

    systemctl restart mysql

    systemctl status mysql

    ● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: active (running) since Wed 2022-11-30 02:45:29 CST; 7h left
       Docs: man:mysqld(8)
             http://dev.mysql.com/doc/refman/en/using-systemd.html
    Process: 491 ExecStartPre=/usr/share/mysql-8.0/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
   Main PID: 535 (mysqld)
     Status: "Server is operational"
      Tasks: 39 (limit: 2332)
     Memory: 456.8M
        CPU: 11.411s
     CGroup: /system.slice/mysql.service
             └─535 /usr/sbin/mysqld

11月 30 02:45:09 VM-MySQL8-I2 systemd[1]: Starting MySQL Community Server...
11月 30 02:45:29 VM-MySQL8-I2 systemd[1]: Started MySQL Community Server.

slave1配置

  • 使用MGR GROUP的 UUID

    062b006d-ac57-4e5f-a7fb-16a88fc98ec5

  • 备份原配置文件

    cp /etc/mysql/conf.d/mysql.cnf /etc/mysql/conf.d/mysql.cnf.bak

  • 修改MySQL8配置文件

    vim /etc/mysql/conf.d/mysql.cnf

    特殊修改：

    # server-id必须是唯一的
server-id=10002

# 相当于此GROUP的名字,是UUID值,不能和集群内其他GTID值的UUID混用,可用uuidgen来生成一个新的,
# 主要是用来区分整个内网里边的各个不同的GROUP,而且也是这个group内的GTID值的UUID
loose-group_replication_group_name='1ad71c0f-6c09-4f1e-ad53-649e291495d2'

# 本地MGR的IP地址和端口，host:port,是MGR的端口,不是数据库的端口
loose-group_replication_local_address='192.168.56.158:33097'

# 需要接受本MGR实例控制的服务器IP地址和端口,是MGR的端口,不是数据库的端口
loose-group_replication_group_seeds='192.168.56.159:33097,192.168.56.158:33097,192.168.56.157:33097'

# 是否启动单主模式，如果启动，则本实例是主库，提供读写，其他实例仅提供读,如果为off就是多主模式了
#loose-group_replication_single_primary_mode=off

    内容如下

    # Copyright (c) 2015, 2022, Oracle and/or its affiliates.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License, version 2.0,
# as published by the Free Software Foundation.
#
# This program is also distributed with certain software (including
# but not limited to OpenSSL) that is licensed under separate terms,
# as designated in a particular file or component or in included license
# documentation.  The authors of MySQL hereby grant you an additional
# permission to link the program and your derivative works with the
# separately licensed software that they have included with MySQL.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License, version 2.0, for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301  USA

#
# The MySQL  Client configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[client]
default-character-set=utf8mb4

[mysql]

[mysqld]
port=33096

datadir=/home/mysql/data
socket=/home/mysql/mysqld.sock

symbolic-links=0
lower_case_table_names=1

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

# server-id必须是唯一的
server-id=10002
log-bin=mysql-bin
log-bin-index=binlogs.index
# MGR使用乐观锁,所以官网建议隔离级别是RC,减少锁粒度
transaction_isolation=READ-COMMITTED
# binlog格式,MGR要求必须是ROW,不过就算不是MGR,也最好用row
binlog_format=row
binlog_rows_query_log_events=on
# binlog校验规则,5.6之后的高版本是CRC32,低版本都是NONE,但是MGR要求使用NONE
binlog_checksum=none

slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=4
slave_preserve_commit_order=1

# GTID
# 开启GTID,必须开启
gtid_mode=on
# 强制GTID的一致性
enforce_gtid_consistency=1
# 因为集群会在故障恢复时互相检查binlog的数据,
# 所以需要记录下集群内其他服务器发过来已经执行过的binlog,按GTID来区分是否执行过.
log-slave-updates=1
binlog_gtid_simple_recovery=1

#relay_log=/usr/mysql/relay.log
#relay-log-index=/usr/mysql/relay.index
# 基于安全的考虑,MGR集群要求复制模式要改成slave记录记录到表中,不然就报错
master_info_repository=table
# 同上配套
relay_log_info_repository=table

# MGR
plugin_load="group_replication=group_replication.so"

# 组复制设置
# 记录事务的算法,官网建议设置该参数使用 XXHASH64 算法
transaction_write_set_extraction=XXHASH64

# 相当于此GROUP的名字,是UUID值,不能和集群内其他GTID值的UUID混用,可用uuidgen来生成一个新的,
# 主要是用来区分整个内网里边的各个不同的GROUP,而且也是这个group内的GTID值的UUID
loose-group_replication_group_name='062b006d-ac57-4e5f-a7fb-16a88fc98ec5'

# IP地址白名单,默认只添加127.0.0.1,不会允许来自外部主机的连接,按需安全设置
#loose-group_replication_ip_whitelist='127.0.0.1/8,192.168.56.0/24,10.10.10.0/24'

# 是否随服务器启动而自动启动组复制,不建议直接启动,怕故障恢复时有扰乱数据准确性的特殊情况
loose-group_replication_start_on_boot=on

# 本地MGR的IP地址和端口，host:port,是MGR的端口,不是数据库的端口
loose-group_replication_local_address='192.168.56.158:33097'

# 需要接受本MGR实例控制的服务器IP地址和端口,是MGR的端口,不是数据库的端口
loose-group_replication_group_seeds='192.168.56.159:33097,192.168.56.158:33097,192.168.56.157:33097'

# 开启引导模式,添加组成员，用于第一次搭建MGR或重建MGR的时候使用,只需要在集群内的其中一台开启
# 如果打开会造成脑裂 
# 是否启动集群，注意，该选项任何时候只能用于一个节点，通常情况下启动集群的时候使用，启动之后需要关闭该选项
loose-group_replication_bootstrap_group=off

# 是否启动单主模式，如果启动，则本实例是主库，提供读写，其他实例仅提供读,如果为off就是多主模式了
#loose-group_replication_single_primary_mode=off

# 多主模式下,强制检查每一个实例是否允许该操作,如果不是多主,可以关闭
#loose-group_replication_enforce_update_everywhere_checks=off

# 权重选择
loose-group_replication_member_weight = 50

skip-host-cache
skip-name-resolve
skip-external-locking
character-set-server=utf8mb4
#lower_case_table_names=1
event_scheduler=on
log_bin_trust_function_creators=on

max_connections=3000
external-locking=FALSE
#max_allowed_packet=32M
#sort_buffer_size=8M
#join_buffer_size=2M
thread_cache_size=300
#query_cache_size=64M
#query_cache_limit=4M
#read_rnd_buffer_size=8M

#innodb_buffer_pool_size=8096M
#innodb_log_file_size=128M
innodb_log_files_in_group=2
#innodb_log_buffer_size=2M
innodb_flush_log_at_trx_commit=1
sync_binlog=1
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

[mysqld_safe]
log-error=/var/log/mysqld-safe.log

slave2配置

  • 使用MGR GROUP的 UUID

    062b006d-ac57-4e5f-a7fb-16a88fc98ec5

  • 备份原配置文件

    cp /etc/mysql/conf.d/mysql.cnf /etc/mysql/conf.d/mysql.cnf.bak

  • 修改MySQL8配置文件

    vim /etc/mysql/conf.d/mysql.cnf

    特殊修改：

    # server-id必须是唯一的
server-id=10003

# 相当于此GROUP的名字,是UUID值,不能和集群内其他GTID值的UUID混用,可用uuidgen来生成一个新的,
# 主要是用来区分整个内网里边的各个不同的GROUP,而且也是这个group内的GTID值的UUID
loose-group_replication_group_name='6f3e5cc4-6c46-430c-8b9b-518250a44f30'

# 本地MGR的IP地址和端口，host:port,是MGR的端口,不是数据库的端口
loose-group_replication_local_address='192.168.56.157:33097'

# 需要接受本MGR实例控制的服务器IP地址和端口,是MGR的端口,不是数据库的端口
loose-group_replication_group_seeds='192.168.56.159:33097,192.168.56.158:33097,192.168.56.157:33097'

# 是否启动单主模式，如果启动，则本实例是主库，提供读写，其他实例仅提供读,如果为off就是多主模式了
#loose-group_replication_single_primary_mode=off

    内容如下

    # Copyright (c) 2015, 2022, Oracle and/or its affiliates.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License, version 2.0,
# as published by the Free Software Foundation.
#
# This program is also distributed with certain software (including
# but not limited to OpenSSL) that is licensed under separate terms,
# as designated in a particular file or component or in included license
# documentation.  The authors of MySQL hereby grant you an additional
# permission to link the program and your derivative works with the
# separately licensed software that they have included with MySQL.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License, version 2.0, for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301  USA

#
# The MySQL  Client configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[client]
default-character-set=utf8mb4

[mysql]

[mysqld]
port=33096

datadir=/home/mysql/data
socket=/home/mysql/mysqld.sock

symbolic-links=0
lower_case_table_names=1

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

# server-id必须是唯一的
server-id=10003
log-bin=mysql-bin
log-bin-index=binlogs.index
# MGR使用乐观锁,所以官网建议隔离级别是RC,减少锁粒度
transaction_isolation=READ-COMMITTED
# binlog格式,MGR要求必须是ROW,不过就算不是MGR,也最好用row
binlog_format=row
binlog_rows_query_log_events=on
# binlog校验规则,5.6之后的高版本是CRC32,低版本都是NONE,但是MGR要求使用NONE
binlog_checksum=none

slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=4
slave_preserve_commit_order=1

# GTID
# 开启GTID,必须开启
gtid_mode=on
# 强制GTID的一致性
enforce_gtid_consistency=1
# 因为集群会在故障恢复时互相检查binlog的数据,
# 所以需要记录下集群内其他服务器发过来已经执行过的binlog,按GTID来区分是否执行过.
log-slave-updates=1
binlog_gtid_simple_recovery=1

#relay_log=/usr/mysql/relay.log
#relay-log-index=/usr/mysql/relay.index
# 基于安全的考虑,MGR集群要求复制模式要改成slave记录记录到表中,不然就报错
master_info_repository=table
# 同上配套
relay_log_info_repository=table

# MGR
plugin_load="group_replication=group_replication.so"

# 组复制设置
# 记录事务的算法,官网建议设置该参数使用 XXHASH64 算法
transaction_write_set_extraction=XXHASH64

# 相当于此GROUP的名字,是UUID值,不能和集群内其他GTID值的UUID混用,可用uuidgen来生成一个新的,
# 主要是用来区分整个内网里边的各个不同的GROUP,而且也是这个group内的GTID值的UUID
loose-group_replication_group_name='062b006d-ac57-4e5f-a7fb-16a88fc98ec5'

# IP地址白名单,默认只添加127.0.0.1,不会允许来自外部主机的连接,按需安全设置
#loose-group_replication_ip_whitelist='127.0.0.1/8,192.168.56.0/24,10.10.10.0/24'

# 是否随服务器启动而自动启动组复制,不建议直接启动,怕故障恢复时有扰乱数据准确性的特殊情况
loose-group_replication_start_on_boot=on

# 本地MGR的IP地址和端口，host:port,是MGR的端口,不是数据库的端口
loose-group_replication_local_address='192.168.56.157:33097'

# 需要接受本MGR实例控制的服务器IP地址和端口,是MGR的端口,不是数据库的端口
loose-group_replication_group_seeds='192.168.56.159:33097,192.168.56.158:33097,192.168.56.157:33097'

# 开启引导模式,添加组成员，用于第一次搭建MGR或重建MGR的时候使用,只需要在集群内的其中一台开启
# 如果打开会造成脑裂 
# 是否启动集群，注意，该选项任何时候只能用于一个节点，通常情况下启动集群的时候使用，启动之后需要关闭该选项
loose-group_replication_bootstrap_group=off

# 是否启动单主模式，如果启动，则本实例是主库，提供读写，其他实例仅提供读,如果为off就是多主模式了
#loose-group_replication_single_primary_mode=off

# 多主模式下,强制检查每一个实例是否允许该操作,如果不是多主,可以关闭
#loose-group_replication_enforce_update_everywhere_checks=off

# 权重选择
loose-group_replication_member_weight = 50

skip-host-cache
skip-name-resolve
skip-external-locking
character-set-server=utf8mb4
event_scheduler=on
log_bin_trust_function_creators=on

max_connections=3000
external-locking=FALSE
#max_allowed_packet=32M
#sort_buffer_size=8M
#join_buffer_size=2M
thread_cache_size=300
#query_cache_size=64M
#query_cache_limit=4M
#read_rnd_buffer_size=8M

#innodb_buffer_pool_size=8096M
#innodb_log_file_size=128M
innodb_log_files_in_group=2
#innodb_log_buffer_size=2M
innodb_flush_log_at_trx_commit=1
sync_binlog=1
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

[mysqld_safe]
log-error=/var/log/mysqld-safe.log

配置MGR

  • 主节点MGR Master执行

    • 打开MySQL日志，实时查看日志输出

      tail -f -n 500 /var/log/mysql/error.log

    • 安装MGR插件

      install plugin group_replication soname 'group_replication.so';
show plugins;

      installed_group_replication

    • 创建用于复制的用户

      set sql_log_bin=0;

create user repuser@'%' identified by 'xxxxxxxxxxxxxxxxxx';
grant replication slave on *.* to repuser@'%';
grant replication client on *.* to repuser@'%';

create user repuser@'127.0.0.1' identified by 'xxxxxxxxxxxxxxxxxx';
grant replication slave on *.* to repuser@'127.0.0.1';
grant replication client on *.* to repuser@'127.0.0.1';

create user repuser@'localhost' identified by 'xxxxxxxxxxxxxxxxxx';
grant replication slave on *.* to repuser@'localhost';
grant replication client on *.* to repuser@'localhost';

set sql_log_bin=1;

    • 修改账户密码加密规则并更新用户密码，并刷新权限

      缺少此步，MySQL8会在主从运行一段时间后，报错

      Authentication plugin ‘caching_sha2_password’ reported error: Authentication requires secure connection. Error_code: MY-002061
      出现这个原因是MySQL8之前的版本中加密规则是mysql_native_password
      而在MySQL8之后,加密规则是caching_sha2_password,
      解决问题方法有两种,
      一种是升级navicat驱动,
      一种是把mysql用户登录密码加密规则还原成mysql_native_password
      这里用第二种方式 ，解决方法如下

      alter user 'repuser' @'%' identified by 'xxxxxxxxxxxxxxxxxx' password expire never;
alter user 'repuser' @'%' identified with mysql_native_password BY 'xxxxxxxxxxxxxxxxxx';
flush privileges;

    • 节点数据同步，并保持同步状态

      change master to master_user='repuser',master_password='xxxxxxxxxxxxxxxxxx' for channel 'group_replication_recovery';
show slave status for channel 'group_replication_recovery';

    • 初始化一个复制组，（start group_replication;执行后会占用端口33097）

      set global group_replication_bootstrap_group=on;
start group_replication;
set global group_replication_bootstrap_group=off;

    • 查看复制配置状态

      select * from performance_schema.replication_group_member_stats;
select * from performance_schema.replication_group_members;

    • 查看端口占用

      netstat -lnpt

      结果

      tcp6     0    0 :::33060          :::*                    LISTEN      25094/mysqld
tcp6     0    0 :::33096          :::*                    LISTEN      25094/mysqld
tcp6     0    0 :::33097          :::*                    LISTEN      25094/mysqld

      rep-port

  • 所有slave节点执行

    • 测试主节点连通性

      mysql -urepuser -pxxxxxxxxxxxxxxxxxx -h 192.168.56.159 -P 33096

      执行

      select now() from dual;
show global variables like 'server_uuid';
exit;

    • 打开MySQL日志，实时查看日志输出

      tail -f -n 500 /var/log/mysql/error.log

    • 安装MGR插件

      install PLUGIN group_replication SONAME 'group_replication.so';
show plugins;

    • 创建用于复制的用户

      set sql_log_bin=0;

create user repuser@'%' identified by 'xxxxxxxxxxxxxxxxxx';
grant replication slave on *.* to repuser@'%';
grant replication client on *.* to repuser@'%';

create user repuser@'127.0.0.1' identified by 'xxxxxxxxxxxxxxxxxx';
grant replication slave on *.* to repuser@'127.0.0.1';
grant replication client on *.* to repuser@'127.0.0.1';

create user repuser@'localhost' identified by 'xxxxxxxxxxxxxxxxxx';
grant replication slave on *.* to repuser@'localhost';
grant replication client on *.* to repuser@'localhost';

set sql_log_bin=1;

    • 节点数据同步，并保持同步状态

      change master to master_user='repuser',master_password='xxxxxxxxxxxxxxxxxx' for channel 'group_replication_recovery';
show slave status for channel 'group_replication_recovery';

    • 启动组复制，（start group_replication;执行后会占用端口33097）、

      执行reset master后，必须执行一个事务

      reset master;
show master status;
start group_replication;

    • 查看组复制状态

      select * from performance_schema.replication_group_member_stats;
select * from  performance_schema.replication_group_members;

    • 查看端口占用

      netsta -lnpt

      结果

      tcp6     0    0 :::33060          :::*                    LISTEN      25094/mysqld
tcp6     0    0 :::33096          :::*                    LISTEN      25094/mysqld
tcp6     0    0 :::33097          :::*                    LISTEN      25094/mysqld

    • 成功日志

      Plugin group_replication reported: 'Group membership changed to VM-MySQL8-I1:33096, VM-MySQL8-I2:33096, VM-MySQL8-I3:33096 on view 16697251924848859:14.'
Plugin group_replication reported: 'The member with address VM-MySQL8-I2:33096 was declared online within the replication group.'
Plugin group_replication reported: 'The member with address VM-MySQL8-I3:33096 was declared online within the replication group.'

  • 报错Plugin group_replication reported: 'There is already a member with server_uuid 671f949d-6fc3-11ed-aca9-566f97a20008. The member will now exit the group.

    Tips: 如果是克隆的虚拟机，会有可能存在server_uuid相同，报如上错误

    • 删除auto.cnf

      cd /var/lib/mysql/

      rm -f auto.cnf

    • 重启MySQL

      systemctl restart mysql