Debian11 MySQL8 MGR 单主集群配置
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
的 UUIDuuidgen
暂存,稍后在编辑配置文件时,设定为
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
的 UUID062b006d-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
的 UUID062b006d-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;
-
创建用于复制的用户
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
-
-
所有
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
-