MySQL组复制（GROUP Replication）实现数据库的高可用

最新推荐文章于 2023-08-14 07:35:58 发布

zhl52306

最新推荐文章于 2023-08-14 07:35:58 发布

阅读量354

点赞数

文章标签： mysql 数据库服务器 Powered by 金山文档

本文链接：https://blog.csdn.net/zhl52306/article/details/129178813

版权

单主模式

环境说明：三台主机安装mysql，部署组复制至少需要3台服务器

主机名和名称解析

#修改三台服务器的主机名

hostnamectl set-hostname node1.zhao.org

hostnamectl set-hostname node2.zhao.org

hostnamectl set-hostname node3.zhao.org

#写入hosts

cat >> /etc/hosts <<EOF

10.0.0.4 node1.zhao.org

10.0.0.5 node2.zhao.org

10.0.0.6 node3.zhao.org

EOF

#数据库采用二进制包方式安装，这里我直接用脚本方式安装

说明：主机名必须与hosts写入的域名一致，否则会出现解析问题，导致组成员无法加入组复制；

所有主机修改配置文件

/etc/my.cnf 配置文件说明

#服务器编号

server_id=1

#开启binlog的GTID模式，默认为OFF

gtid_mode=ON

#开启后MysQL只允许能够保障事务安全，并且能够被日志记录的sQL语句被执行，默认为OFF

enforce_gtid_consistency=ON

#加载组复制模块

plugin_load_add ='group_replication.so'

或者采用如下命令安装复制插件

INSTALL PLUGIN group_replication SONAME 'group_replication.so';

#复制组的名称，必须是有效格式的UUID，可以使用命令uuidgen生成，所有节点必须相同。默认为空

loose-group_replication_group_name="aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee"

#确定服务器是否应该在服务器启动期间启动组复制。默认为ON

loose-group_replication_start_on_boot=OFF

#为复制组中其他的成员提供的网络地址，指定为“主机:端口”的格式化字符串。很多人想当然认为端口应该是3306，其实不然，MGR需要开启新端口24901同步交换

loose-group_replication_local_address="10.0.0.4:24901"

#用于建立新成员到组的连接组成员列表。这个列表指定为由分隔号间隔的组成员网络地址列表，类似host1:port1、host2:port2的格式。同样采用ne~n2的主机名替代

loose-group_replication_group_seeds="10.0.0.4:24901,10.0.0.5:24901,10.0.0.5:24901"

#配置此服务器为引导组，这个选项必须仅在一台服务器上设置，并且仅当第一次启动组或者重新启动整个组时。成功引导组启动后，将此选项设置为关闭。

loose-group_replication_bootstrap_group=off

#使用mysql_native_password密码策略

default_authentication_plugin=mysql_native_password

#关闭binlog校验，默认值为CRC32，此项可选

binlog_checksum=NONE

#定义用于事务期间哈希写入提取的算法，组复制模式下必须设置为XXHASH64。此为默认值，可不设置

transaction_write_set_extraction=XXHASH64

#确定组复制恢复时是否应该应用SSL，通常设置为“开”，但默认设置为“关”。此项可选

loose-group_replication_recovery_use_ssl=ON

#节点1

[root@mysql-node1 ~]#vim /etc/my.cnf

[mysqld]

server-id=4

log-bin

datadir=/data/mysql

gtid_mode=on

enforce_gtid_consistency

loose-group_replication_group_name="b2f1a01b-99a9-4527-92b0-2017806b477c"

loose-group_replication_start_on_boot=OFF

loose-group_replication_local_address="10.0.0.4:24901"

loose-group_replication_group_seeds="10.0.0.4:24901,10.0.0.5:24901,10.0.0.6:24901"

loose-group_replication_bootstrap_group=OFF

loose-group_replication_recovery_use_ssl=ON

log-error=/data/mysql/mysql.log

pid-file=/data/mysql/mysql.pid

default_authentication_plugin=mysql_native_password

#节点2

[root@mysql-node2 ~]#vim /etc/my.cnf

[mysqld]

server-id=5

log-bin

datadir=/data/mysql

gtid_mode=on

enforce_gtid_consistency

loose-group_replication_group_name="b2f1a01b-99a9-4527-92b0-2017806b477c"

loose-group_replication_start_on_boot=OFF

loose-group_replication_local_address="10.0.0.5:24901"

loose-group_replication_group_seeds="10.0.0.4:24901,10.0.0.5:24901,10.0.0.6:24901"

loose-group_replication_bootstrap_group=OFF

loose-group_replication_recovery_use_ssl=ON

default_authentication_plugin=mysql_native_password

#节点3

[root@mysql-node3 ~]#vim /etc/my.cnf

[mysqld]

server-id=6

log-bin

gtid_mode=on

enforce_gtid_consistency

datadir=/data/mysql

loose-group_replication_group_name="b2f1a01b-99a9-4527-92b0-2017806b477c"

loose-group_replication_start_on_boot=OFF

loose-group_replication_local_address="10.0.0.4:24901"

loose-group_replication_group_seeds="10.0.0.4:24901,10.0.0.5:24901,10.0.0.6:24901"

loose-group_replication_bootstrap_group=OFF

loose-group_replication_recovery_use_ssl=ON

default_authentication_plugin=mysql_native_password

#重启服务

[root@mysql-node3 ~]#systemctl restart mysqld.service

所有主机创建复制账号

SET SQL_LOG_BIN=0;

CREATEUSER repluser@'%' IDENTIFIED BY '123456';

GRANT REPLICATION SLAVEON*.*TO repluser@'%';

FLUSHPRIVILEGES;

SET SQL_LOG_BIN=1;

在所有主机安装插件

#永久安装插件，下次启动无需再次安装或者写入my.cnf配置文件

mysql> install plugin group_replication soname 'group_replication.so';

Query OK, 0 rows affected (0.03 sec)

mysql> select * from information_schema.plugins where plugin_name='group_replication'\G;

*************************** 1. row ***************************

PLUGIN_NAME: group_replication

PLUGIN_VERSION: 1.1

PLUGIN_STATUS: ACTIVE

PLUGIN_TYPE: GROUP REPLICATION

PLUGIN_TYPE_VERSION: 1.4

PLUGIN_LIBRARY: group_replication.so

PLUGIN_LIBRARY_VERSION: 1.11

PLUGIN_AUTHOR: Oracle Corporation

PLUGIN_DESCRIPTION: Group Replication (1.1.0)

PLUGIN_LICENSE: GPL

LOAD_OPTION: ON

1 row inset (0.00 sec)

mysql> show plugins;

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

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

| daemon_keyring_proxy_plugin | ACTIVE | DAEMON | NULL ........................................................................................

........................................................................................

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

47 rows inset (0.00 sec)

启动第一个节点Primary

#启动第一个节点与启动其于节点不同

mysql> set global group_replication_bootstrap_group=on;

Query OK, 0 rows affected (0.00 sec)

mysql> start group_replication;

Query OK, 0 rows affected (1.22 sec)

mysql> set global group_replication_bootstrap_group=off;

Query OK, 0 rows affected (0.00 sec)

mysql> select @@group_replication_bootstrap_group;

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

| @@group_replication_bootstrap_group |

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

| 0 |

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

1 row inset (0.00 sec)

#验证

mysql> select * from performance_schema.replication_group_members;

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

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

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

1 row inset (0.00 sec)

启动剩余的所有节点Secondary

#设置复制用户信息

mysql>changemasterto master_user='repluser',master_password='123456'for channel 'group_replication_recovery';

Query OK,0 rows affected,5warnings(0.08 sec)

#启动复制

mysql>start group_replication;

Query OK,0 rows affected (1.61 sec)

#查看主从节点复制状态

mysql>select*from performance_schema.replication_group_members;

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

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

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

3 rows inset(0.00 sec)

问题点

#报错提示“此成员执行的事务多于组中存在的事务”

2022-11-17T02:20:32.925684Z 0 [ERROR] [MY-011526] [Repl] Plugin group_replication reported: 'This member has more executed transactions than those present in the group. Local transactions: 0ca61410-661b-11ed-b91d-000c29fbd7c6:1-3 > Group transactions: b2f1a01b-99a9-4527-92b0-2017806b477c:1'

#解决方案，停止应用，然后重置节点，然后重新在主节点和从节点开启通道，最后开启复制

CHANGE MASTER TO MASTER_USER=‘MGR’, MASTER_PASSWORD=‘abcedf!@#’ FOR

CHANNEL ‘group_replication_recovery’;

mysql> stop group_replication;

Query OK, 0 rows affected (0.00 sec)

mysql> reset master;

Query OK, 0 rows affected (0.01 sec)

mysql> sart group_replication;

Query OK, 0 rows affected (0.00 sec)

#Reset Master与Reset Slave差别

Reset Master：删除所有的binglog日志文件，并将日志索引文件清空，重新开始所有新的日志文件；

Rest Slave：用于删除SLAVE数据库的relaylog日志文件，并重新启用新的relaylog文件；

测试复制

#创建一个数据库，可以看到已同步

mysql> create database db1;

Query OK, 1 row affected (0.01 sec)

mysql> show databases;

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

| Database |

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

| db1 |

| information_schema |

| mysql |

| performance_schema |

| sys |

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

5 rows inset (0.00 sec)

#停止主节点服务，查看已切换到node1作为主节点

mysql> select * from performance_schema.replication_group_members;

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

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

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

2 rows inset (0.00 sec)

单主切换多主模式

方法一

#任意节点执行切换多主语句

mysql>select group_replication_switch_to_multi_primary_mode();

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

| group_replication_switch_to_multi_primary_mode()|

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

|Mode switched to multi-primary successfully. |

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

1rowinset(1.01 sec)

#查看主复制模式各节点已切换主节点

mysql>select*from performance_schema.replication_group_members;

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

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

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

3 rows inset(0.00 sec)

#查看个节点读写模式，切换多主后，原有为只读的变量，均已关闭

mysql>showvariableslike'%read_only%';

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

| Variable_name |Value|

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

| innodb_read_only | OFF |

| read_only | OFF |

| super_read_only | OFF |

| transaction_read_only | OFF |

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

4 rows inset(0.01 sec)

方法二

#所有节点

STOP GROUP_REPLICATION;

#是否启用单主模式，默认ON，OFF代表多主

SET global group_replication_single_primary_mode=off;

#是否开启条件检查，因为多主的约束更为严格，不符合要求的直接拒绝,不支持外键的级联操作,不支持隔离级别Serializable

set global group_replication_enforce_update_everywhere_checks=ON;

#主节点

SET GLOBAL group_replication_bootstrap_group=ON;

START GROUP_REPLICATION;

SET GLOBAL group_replication_bootstrap_group=OFF;

SELECT * FROM performance_schema.replication_group_members;

#所有从节点

START GROUP_REPLICATION;

多主切换会单主

MGR 中多主切单主，如果不指定主，切换时遵循以下的规则，前提无延迟情况下，按照优先级先后顺序：

先检查集群内是否存在低版本的 mysql，如果所有成员都是 8.0.17 版本以上的，则按照补丁先后顺序排列，如果有成员是 8.0.17 版本以下的，或者 5.7 的按发行版本的主要版本来排序，忽略补丁编号。

如果集群有任意一个低版本的 mysql。且支持 group_replication_member_weight 这个参数的版本的mysql。则根据 group_replication_member_weight 这个权重参数来，group_replication_member_weight默认都是 50，权重越高优先级越高。

如果集群内有不支持 group_replication_member_weight 参数的 mysql，比如有 mysql5.7 的，并且其中一个以上的成员具有最高的成员权重（或忽略了成员权重），则考虑的第三个因素是生成的服务器 UUID 的词典顺序，由 server_uuid 系统变量指定。服务器 UUID 最低的成员被选为主服务器，这里实际上就是查询 performance_schema.replication_group_members 最靠前的且是活着的MEMBER_ID

方法一

#任意节点执行如下切换单主模式命令

mysql>select group_replication_switch_to_single_primary_mode();

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

| group_replication_switch_to_single_primary_mode()|

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

|Mode switched to single-primary successfully. |

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

1rowinset(0.01 sec)

#查询已切换

mysql>select*from performance_schema.replication_group_members;

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

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

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

3 rows inset(0.00 sec)