ProxySQL 结合 Group Replication 构建多主 RDS 集群应用，实现多节点写及小于 1 秒节点维护影响

2023 年 11 月 17 日，亚马逊托管的 RDS for MySQL 8.0.35 版本开始支持 Group Replication 插件。采用 Group Replication 插件，我们可以构建出一个多主的 MySQL 集群。那么我们应该如何在此功能基础上构建一个成熟的多主高可用集群呢？首先，构建一个多主的高可用 MySQL 集群有几个目的：

多主的集群架构，可以通过多实例节点均衡写负载。同时应用端利用 sql 代理层来实现灵活的读写节点分配。
利用多主的集群架构，提升数据库层的高可用性，数据库的单节点故障，数据库小版本的升级可以做到应用侧的零影响。

在本文中，我们选择使用 proxysql 中间件，利用对 Group Replication 插件的支持能力，构建了 3 个节点的多主集群，并测试了集群中一个节点重启及小版本升级的情况下，可做到对应用测影响低于 1 秒。目标参考架构如下：

具体的实现步骤如下。

创建 Group Replication 集群

首先，我们需要先构建一个 3 节点的多主 Group Replication 集群，详细步骤参见推出用于 Amazon RDS for MySQL 上主动/主动复制的组复制插件，本文不再赘述。创建完成后，我们在任意的 RDS MySQL 实例采用管理账户登陆后可看到 Group Replication 集群的状态。

ProxySQL 的配置

下面我们会通过如下几个步骤完成 ProxySQL 来管理 Replication Group。

收集 Amazon RDS 多可用区数据库集群中所有实例的终端节点和端口

使用控制台查找实例终端节点和端口

使用控制台，请完成以下步骤：

在 Amazon RDS 控制台上，选择创建数据库集群的区域。
在导航窗格中，选择数据库以显示所有数据库实例的列表。

选择数据库实例的名称。
选择名称，数据库集群的名称，以显示其所有详细信息。

重复这些步骤，获取 gr-2 和 gr-3 数据库集群的端点和端口信息。

在此示例中我们的 Amazon RDS MySQL Group Replication 具有以下实例端点和端口。

	A	B
1	实例端点	端口
2	gr-1.cyuvj1mfoudy.us-east-1.rds.amazonaws.com	3306
3	gr-2.cyuvj1mfoudy.us-east-1.rds.amazonaws.com	3306
4	gr-3.cyuvj1mfoudy.us-east-1.rds.amazonaws.com	3306

使用实例终端节点将 Amazon RDS Group Replication 端点加载到 ProxySQL 上

使用您在上一步中检索到的实例终端节点和端口信息将 Amazon RDS 多可用区数据库集群载入 ProxySQL。

连接到运行 ProxySQL 的 EC2 实例。

通过管理界面连接到 ProxySQL 进行配置：

mysql -uadmin -padmin -h 127.0.0.1 -P6032 --prompt='ProxySQL Admin>'

将您之前检索到的 Amazon RDS Group Replication 终端节点和端口插入到 ProxySQL 配置中的 MySQL_servers 表中。在以下示例中，我们使用以下命令载入了集群的所有实例 hostgroup_id 定义为 10。

INSERT INTO MySQL_servers (hostname,hostgroup_id,port,weight,max_connections) VALUES ('gr-1.cyuvj1mfoudy.us-east-1.rds.amazonaws.com',10,3306,1000,2000);
INSERT INTO MySQL_servers (hostname,hostgroup_id,port,weight,max_connections) VALUES ('gr-2.cyuvj1mfoudy.us-east-1.rds.amazonaws.com',10,3306,1000,2000);
INSERT INTO MySQL_servers (hostname,hostgroup_id,port,weight,max_connections) VALUES ('gr-3.cyuvj1mfoudy.us-east-1.rds.amazonaws.com',10,3306,1000,2000);

ProxySQL 在 xxx 版本之后原生支持 MySQL Group Replication。新增配置表 MySQL_group_replication_hostgroups，支持灵活的 Group Replication 集群配置。在本例中我们有 3 个可读/写节点，我们定义 hostgroup_id 10 为写节点组，20 为备份的写节点组，30 为读节点组，40 为离线的节点组，最大写节点个数为 2 个，写节点也允许读请求，最大事务延迟数量为 100（可根据实际情况调整）。
```
# 10 for writer group 20 for reader group
insert into MySQL_group_replication_hostgroups (writer_hostgroup,backup_writer_hostgroup,
reader_hostgroup, offline_hostgroup,active,max_writers,writer_is_also_reader,max_transactions_behind) 
values (10,20,30,40,1,2,1,100);
```

加载 MySQL_servers 表配置到运行时并将其保存到磁盘：

ProxySQL Admin> LOAD MySQL SERVERS TO RUNTIME;
ProxySQL Admin> SAVE MySQL SERVERS TO DISK;

为 Amazon RDS 多可用区数据库集群配置 ProxySQL 监控

管理 Amazon RDS MySQL Group Replication 实例，ProxySQL 监控配置是必须的步骤，ProxySQL 需要根据 Group Replication 的元数据信息获取到 Group Replication 各个节点的状态，以便了解写入器中的更改，在 1 秒或更短时间内检测到新写入器，并将流量重新路由到新写入器。

在本例中，我们使用默认的 ProxySQL 监控用户 monitor 和密码 monitor 配置监控。如果需要，您可以使用不同的监视器用户和密码。

先决条件

更改 Amazon RDS MySQL Group Replication 实例所对应的参数组，通过控制台选择对应的参数组 MySQL-group-replication-3。

点击编辑按钮，找到 log_bin_trust_function_creators 更改为 1。允许在开启 Binlog 的状态下创建 function。

首先，您在 Amazon RDS Group Replication 上配置 ProxySQL 监控用户。

使用管理账户登录 Amazon RDS for MySQL Group Replication 的其中一个端点。

创建 ProxySQL 监控用户并授予其监控权限：

MySQL> CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor';
MySQL> GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'monitor'@'%';
MySQL> GRANT select ON sys.* TO 'monitor'@'%';
MySQL> GRANT select ON performance_schema.* TO 'monitor'@'%';

执行以下脚本，在 sys 用户下创建相关统计使用的函数及视图。

SET @TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;
SET @TEMP_READ_ONLY = @@GLOBAL.READ_ONLY;
SET @TEMP_SUPER_READ_ONLY = @@GLOBAL.SUPER_READ_ONLY;
SET @@GLOBAL.READ_ONLY = 0;
USE sys;


DROP VIEW IF EXISTS gr_member_routing_candidate_status;

DROP FUNCTION IF EXISTS IFZERO;
DROP FUNCTION IF EXISTS LOCATE2;
DROP FUNCTION IF EXISTS GTID_NORMALIZE;
DROP FUNCTION IF EXISTS GTID_COUNT;
DROP FUNCTION IF EXISTS gr_applier_queue_length;
DROP FUNCTION IF EXISTS gr_member_in_primary_partition;
DROP FUNCTION IF EXISTS gr_transactions_to_cert;

DELIMITER $$

CREATE FUNCTION IFZERO(a INT, b INT)
RETURNS INT
DETERMINISTIC
RETURN IF(a = 0, b, a)$$

CREATE FUNCTION LOCATE2(needle TEXT(10000), haystack TEXT(10000), offset INT)
RETURNS INT
DETERMINISTIC
RETURN IFZERO(LOCATE(needle, haystack, offset), LENGTH(haystack) + 1)$$

CREATE FUNCTION GTID_NORMALIZE(g TEXT(10000))
RETURNS TEXT(10000)
DETERMINISTIC
RETURN GTID_SUBTRACT(g, '')$$

CREATE FUNCTION GTID_COUNT(gtid_set TEXT(10000))
RETURNS INT
DETERMINISTIC
BEGIN
  DECLARE result BIGINT DEFAULT 0;
  DECLARE colon_pos INT;
  DECLARE next_dash_pos INT;
  DECLARE next_colon_pos INT;
  DECLARE next_comma_pos INT;
  SET gtid_set = GTID_NORMALIZE(gtid_set);
  SET colon_pos = LOCATE2(':', gtid_set, 1);
  WHILE colon_pos != LENGTH(gtid_set) + 1 DO
     SET next_dash_pos = LOCATE2('-', gtid_set, colon_pos + 1);
     SET next_colon_pos = LOCATE2(':', gtid_set, colon_pos + 1);
     SET next_comma_pos = LOCATE2(',', gtid_set, colon_pos + 1);
     IF next_dash_pos < next_colon_pos AND next_dash_pos < next_comma_pos THEN
       SET result = result +
         SUBSTR(gtid_set, next_dash_pos + 1,
                LEAST(next_colon_pos, next_comma_pos) - (next_dash_pos + 1)) -
         SUBSTR(gtid_set, colon_pos + 1, next_dash_pos - (colon_pos + 1)) + 1;
     ELSE
       SET result = result + 1;
     END IF;
     SET colon_pos = next_colon_pos;
  END WHILE;
  RETURN result;
END$$

CREATE FUNCTION gr_applier_queue_length()
RETURNS INT
DETERMINISTIC
BEGIN
  RETURN (SELECT sys.gtid_count( GTID_SUBTRACT( (SELECT
Received_transaction_set FROM performance_schema.replication_connection_status
WHERE Channel_name = 'group_replication_applier' ), (SELECT
@@global.GTID_EXECUTED) )));
END$$

CREATE FUNCTION gr_member_in_primary_partition()
RETURNS VARCHAR(3)
DETERMINISTIC
BEGIN
  RETURN (SELECT IF( MEMBER_STATE='ONLINE' AND ((SELECT COUNT(*) FROM
performance_schema.replication_group_members WHERE MEMBER_STATE != 'ONLINE') >=
((SELECT COUNT(*) FROM performance_schema.replication_group_members)/2) = 0),
'YES', 'NO' ) FROM performance_schema.replication_group_members JOIN
performance_schema.replication_group_member_stats rgms USING(member_id) WHERE rgms.MEMBER_ID=@@SERVER_UUID )  ;
END$$

CREATE FUNCTION gr_transactions_to_cert() RETURNS int(11)
    DETERMINISTIC
BEGIN
  RETURN (select  performance_schema.replication_group_member_stats.COUNT_TRANSACTIONS_IN_QUEUE AS transactions_to_cert
    FROM
        performance_schema.replication_group_member_stats where MEMBER_ID=@@SERVER_UUID );
END$$

CREATE VIEW gr_member_routing_candidate_status AS
    SELECT
        IFNULL(sys.gr_member_in_primary_partition(),'NO') AS viable_candidate,
        IF((SELECT
                    ((SELECT
                                GROUP_CONCAT(performance_schema.global_variables.VARIABLE_VALUE
                                        SEPARATOR ',')
                            FROM
                                performance_schema.global_variables
                            WHERE
                                (performance_schema.global_variables.VARIABLE_NAME IN ('read_only' , 'super_read_only'))) <> 'OFF,OFF')
                ),
            'YES',
            'NO') AS read_only,
        IFNULL(sys.gr_applier_queue_length(),0) AS transactions_behind,
        IFNULL(sys.gr_transactions_to_cert(),0) AS transactions_to_cert;$$

DELIMITER ;
SET @@SESSION.SQL_LOG_BIN = @TEMP_LOG_BIN;
SET @@GLOBAL.READ_ONLY = @TEMP_READ_ONLY;
SET @@GLOBAL.SUPER_READ_ONLY = @TEMP_SUPER_READ_ONLY;

以上函数及视图完成创建后，可以执行 sql 验证，您将看到下图类似结果。

MySQL> select * from sys.gr_member_routing_candidate_status;
+------------------+-----------+---------------------+----------------------+
| viable_candidate | read_only | transactions_behind | transactions_to_cert |
+------------------+-----------+---------------------+----------------------+
| YES              | NO        |                   0 |                    0 |
| YES              | NO        |                   0 |                    0 |
| YES              | NO        |                   0 |                    0 |
+------------------+-----------+---------------------+----------------------+
3 rows in set (0.00 sec)

现在，我们来配置 ProxySQL 来监控 Amazon RDS MySQL Group Replication。

连接到运行 ProxySQL 的 EC2 实例。

通过管理界面连接到 ProxySQL 进行配置：

mysql -uadmin -padmin -h 127.0.0.1 -P6032 --prompt='ProxySQL Admin> '

配置监控用户和密码：

ProxySQL Admin> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='MySQL-monitor_username';

ProxySQL Admin> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='MySQL-monitor_password';

配置监视间隔通过将 MySQL-monitor_read_only_interval 设置为 100 毫秒来。这有助于 ProxySQL 在 100 毫秒内检测到新写入器的更改。
```
ProxySQL Admin> UPDATE global_variables SET variable_value=100 WHERE variable_name='MySQL-monitor_read_only_interval';
```

将配置加载到运行时并将其保存到磁盘：

Proxy Admin> LOAD MySQL VARIABLES TO RUNTIME;
Proxy Admin> SAVE MySQL VARIABLES TO DISK;

此时我们来查看 runtime_MySQL_servers 的配置，会发现类似下图的结果。gr-2，gr-3 两个节点被分配到写节点组 10，gr-1 被分配到备份写节点组 20，因前面定义了写节点都可以被作为读节点使用，所以三个节点都同时被分配给 30 节点组。

ProxySQL Admin> select hostgroup_id, hostname, port, status from runtime_MySQL_servers;
+--------------+-----------------------------------------------+------+--------+
| hostgroup_id | hostname                                      | port | status |
+--------------+-----------------------------------------------+------+--------+
| 10           | gr-2.cyuvj1mfoudy.us-east-1.rds.amazonaws.com | 3306 | ONLINE |
| 10           | gr-3.cyuvj1mfoudy.us-east-1.rds.amazonaws.com | 3306 | ONLINE |
| 20           | gr-1.cyuvj1mfoudy.us-east-1.rds.amazonaws.com | 3306 | ONLINE |
| 30           | gr-1.cyuvj1mfoudy.us-east-1.rds.amazonaws.com | 3306 | ONLINE |
| 30           | gr-2.cyuvj1mfoudy.us-east-1.rds.amazonaws.com | 3306 | ONLINE |
| 30           | gr-3.cyuvj1mfoudy.us-east-1.rds.amazonaws.com | 3306 | ONLINE |
+--------------+-----------------------------------------------+------+--------+

至此，我们便已经有了高可用的多主的 RDS MySQL 集群。下面我们模拟 RDS 实例宕机场景开展测试，看这种模式下对应用的影响时间是否可以达到 1 秒以下。

模拟 RDS 实例宕机的测试

测试准备

采用 golang 编写如下一段简单的测试程序 MySQLtest。每 100ms 对测试表 pub_dict 执行一次更新操作。

package main

import (
    "database/sql"
    "fmt"
    "strconv"
    "time"
    _ "github.com/go-sql-driver/MySQL"
)
func main() {

    // MySQL服务器IP地址
    ip := "10.0.5.xxx"
    // ProxySQL服务器端口号
    port := 6033
    // DSN格式 -> "用户名:密码@tcp(IP:端口)/数据库名"
    dsn := "common:password@tcp(" + ip + ":" + strconv.Itoa(port) + ")/common"
    db, err := sql.Open("MySQL", dsn)
    if err != nil {
        panic(err)
    }
    defer db.Close()

    for {
        //err := dbExec(db, dsn, "UPDATE pub_dict SET dict_value=? WHERE dict_id=?", "test update", 1)
        _, err := db.Exec("UPDATE pub_dict SET dict_value=? WHERE dict_id=?", "test update", 1)
        t := time.Now()
        fmt.Printf("[%v.%03d] update success...\n", t.Format("2006-01-02 15:04:05"), t.Nanosecond()/1000000)
        if err != nil {
            fmt.Printf("[%v.%03d] update failure...\n", t.Format("2006-01-02 15:04:05"), t.Nanosecond()/1000000)
            time.Sleep(100 * time.Millisecond)
            continue
        }

        time.Sleep(100 * time.Millisecond)
    }
}

测试步骤

构建完成后启动执行，启动完成后您可以看到正常的日志记录输出

[2024-01-31 16:04:29.483] update success...
[2024-01-31 16:04:29.584] update success...
[2024-01-31 16:04:29.686] update success...
[2024-01-31 16:04:29.787] update success...
[2024-01-31 16:04:29.888] update success...
[2024-01-31 16:04:29.990] update success...

登陆 RDS 控制台，选择其中任意一个 Group Replication 实例执行手工停机操作，如下图所示

停机完成后，可通过如下命令搜索关键字“update failure”来了解在 Group Replication 中的其中一个节点停机时，对应用侧的影响时间。
```
grep "update failure" nohup.out
```
如上图，我们可以观察到，通过 ProxySQL + RDS for MySQL 的 Group Replication 多主模式，当一个节点停机维护时，通过 RroxySQL 可快速将下一次 sql 写操作路由到可用节点，最终做到客户端读写影响在 1 秒以下，甚至达到百毫秒级别。

模拟 RDS 实例从 8.0.35 升级到 8.0.36 的测试

测试准备

测试准备的过程与测试一相同。

测试步骤

构建完成后启动执行，启动完成后您可以看到正常的日志记录输出

[2024-03-07 06:24:12.587] update success...
[2024-03-07 06:24:12.689] update success...
[2024-03-07 06:24:12.791] update success...
[2024-03-07 06:24:12.892] update success...
[2024-03-07 06:24:12.994] update success...
[2024-03-07 06:24:13.095] update success...

登陆 RDS 控制台，选择其中任意一个 Group Replication 实例执行数据库升级操作，如下图所示

选择立即执行升级，然后等待升级完成后，登陆 gr-2 实例，查看 Group replication 集群的状态。可以看到其中一个实例已经升级到 8.0.36 版本。

从 mysql-go 程序运行的主机，通过如下命令查看到升级过程对客户端的实际影响。
```
grep -C20 "update failure" nohup.out
```
如上图，我们可以观察到，通过 ProxySQL + RDS for MySQL 的 Group Replication 多主模式，也同时适用于 Mysql 小版本升级的场景，对于重要的 workload，采用此类 Mysql 集群架构，客户可以不再担心小版本升级频繁影响到线上应用系统。

总结

使用 ProxySQL+RDS MySQL Group Replication 可以构建出高可用的多主数据库集群。
ProxySQL 可以基于 Group Replication 的元数据信息实时感知每个节点的状态，从而在 1 秒内完成主节点切换。
在本例中采用了 ProxySQL 单机部署模式，在实际生产中可考虑采用 proxySQL Cluster 模式提升中间层的高可用性。

亚马逊AWS官方博客

ProxySQL 结合 Group Replication 构建多主 RDS 集群应用，实现多节点写及小于 1 秒节点维护影响

创建 Group Replication 集群

ProxySQL 的配置

收集 Amazon RDS 多可用区数据库集群中所有实例的终端节点和端口

使用控制台查找实例终端节点和端口

使用实例终端节点将 Amazon RDS Group Replication 端点加载到 ProxySQL 上

为 Amazon RDS 多可用区数据库集群配置 ProxySQL 监控

先决条件

模拟 RDS 实例宕机的测试

测试准备

测试步骤

模拟 RDS 实例从 8.0.35 升级到 8.0.36 的测试

测试准备

测试步骤

总结

参考链接

本篇作者