MYSQL集群部署方案

type

status

date

slug

summary

目的

MySQL 集群各节点数据同步

MySQL 集群拥有故障转移的机制

MySQL 集群拥有一个固定出口 JDBC 连接，拥有读写分离的功能

架构

采用 MGR 进行数据同步；ProxySQL 集群进行 SQL 分发，读写分离；Keepalived 进行真机故障转移。

MGR 数据同步 && 故障转移

MySQL Group Replication（简称MGR）是MySQL官方于2016年12月12日推出的一款高可用与高扩展的解决方案，它提供了高可用、高扩展、高可靠的MySQL集群服务，具有以下特点：

强一致性：基于原生复制和 paxos 协议，保证数据传输的一致性和原子性

高容错性：只要大多数节点没有出现故障，集群就可对外提供服务

高扩展性：节点的加入和移除都是自动的，不需要人为过多干预

高灵活性：具有单主模式和多主模式，单主模式在主宕机后自动选主，多主模式多节点写入

组复制的使用需要满足如下要求：

InnoDB 存储引擎

每个表必须有显式主键

网络性能要求低延时、高带宽

server_id 要唯一

开启 GTID

事务隔离级别建议使用 RC

不建议使用外键级联约束

组成员最大数目为 9

MySQL 集群测试环境启动

坑一：default-authentication-plugin 使用 mysql_native_password，如果使用 caching_sha2_password 会导致 ProxySQL 连不上 MySQL

坑二：如果不在同一台机器上部署机器，不然会在添加子节点到 MGR 集群里的时候报连不上 xxx:33061，xxx 为 docker 容器 Id；应该在 docker-compose 里的服务编排里添加 hostname 指定为本机 IP，command 添加 -report-host=<本机IP> ，network_mode 为 host

本文示例 MySQL 示例

host	port	remark
192.168.124.92	33061	master
192.168.124.92	33062	slave1
192.168.124.92	33063	slave2

通过 MySQL Shell 环境创建 MGR 集群

MySQL Shell 中可以用 Python 或 JavaScript 语法来执行这些方法，默认是 JavaScript 语法

进入 MySQL 容器

创建名为 Cluster 的集群

添加节点，定数据同步的方式为 clone，意为全量复制，增量为 incremental

查看集群状态

重启集群

如果 This function is not available through a session to a standalone instance (metadata exists, instance belongs to that metadata, but GR is not active) 这个问题，重启集群，这个问题一般是重启 MySQL 导致的

清空集群元数据

如果整个集群重启，使用 dba.rebootClusterFromCompleteOutage() 重启，一直卡在 Cancelling active GR auto-initialization at xxx 不动；可以尝试清空所有节点的集群元数据，再重新创建集群后，子节点加入集群。

MGR 集群其他操作

查看当前集群的全部节点

单主切换多主（默认为单主）

多主切换单主

ProxySQL 中间代理

ProxySQL 是一个高性能的、高可用性 MySQL 中间件，优点如下：

几乎所有的配置均可在线更改（其配置数据基于SQLite存储），无需重启 ProxySQL

详细的状态统计，相当于有了统一的查看 SQL 性能和 SQL 语句统计的入口

自动重连和重新执行机制，若一个请求在链接或执行过程中意外中断，ProxySQL 会根据其内部机制重新执行该操作

query cache 功能：比 MySQL 自带QC更灵活，可多维度控制哪类语句可以缓存

支持连接池（connection pool）

支持分库、分表

支持负载均衡

自动下线后端DB，根据延迟超过阀值、ping 延迟超过阀值、网络不通或宕机都会自动下线节点

概念

ProxySQL 具有零停机时间变更功能，它是通过3层配置来实现的，3层配置包括：Runtime、Memory、Disk

Runtime 层表示 ProxySQL 工作线程使用的内存数据结构

Memory 层经由一个 MySQL 兼容接口露出的内存数据库，用户可以使用 MySQL 客户端连接到管理界面，查看、编辑 ProxySQL 配置表

Disk 层是一个存放在磁盘上的 SQLite3 数据库，Disk 层可将内存中的配置信息保存到磁盘，以便 ProxySQL 重新启动后配置还可用

各层之间数据通过 load/save 命令来实现同步

以上 xxx 表示要加载/保存的是哪类配置，目前 ProxySQL 支持以下配置

mysql users 用户信息

mysql servers 服务信息

mysql variables 系统变量

mysql query rules 服务规则

admin variables 管理员变量

Docker 启动 ProxySQL

Docker

https://hub.docker.com/r/proxysql/proxysql

admin 管理接口，端口为 16032，该端口用于查看、配置 ProxySQL

接收 SQL 语句的接口，端口为 16033，这个接口类似于 MySQL 的 3306 端口

配置文件 proxysql.cnf

配置远程用户 radmin@radmin

配置 MySQL 版本为 8.0.25（解决连接 druid 连接池 Unknown system variable 'query_cache_size' 异常）

配置 proxysql_servers 集群，只有在 master 节点执行 load mysql servers to runtime; 时，其天节点才会同步配置，因为数据差异检查是根据 runtime 进行检查的，只对 memory 和 disk 进行更改，并不触发同步操作。

ProxySQL 服务只有在第一次启动时才会去读取 proxysql.cnf 文件并解析；后面启动不会读取 proxysql.cnf 文件，如果想要让 proxysql.cnf 文件里的配置在重启 proxysql 服务后生效，则需要先删除 /var/lib/proxysql/proxysql.db 数据库文件，然后再重启 proxysql 服务（之前的相关服务器配置会被删除，需重新手动配置）

ProxySQL 配置 MGR

以下通过 SQL 的方式配置 ProxySQL

登录 ProxySQL 管理控制台

添加主机到 ProxySQL

监控MGR节点状态

创建 sys.gr_member_routing_candidate_status 视图 ProxySQL 监控 MGR 状态时是通过视图 sys.gr_member_routing_candidate_status 实现的，所以首先需要在MGR集群创建视图，master 节点执行

MGR 创建监控用户

一定要给 proxysql_monitor 赋予足够多的权限，不然 ProxySQL 无法监控 MGR 集群，导致使用时Max connect timeout reached while reaching hostgroup 1000 after 10000ms 异常

ProxySQL 监控配置

录入 MGR hostgroups 信息

Galera Configuration - ProxySQL

ProxySQL support for Galera ProxySQL provides the following parameters and configuration tables specifically for Galera: Health […]

https://proxysql.com/documentation/galera-configuration/

MRG 组信息，分配各读写配置对应的组，读写配置释义如下

字段	释义
`write_hostgroup`	写的流量应该发送到该组；read_only 和 super_read_only OFF的节点。
`backup_writer_hostgroup`	如果集群有多个写节点(read_only=0)且超过了max_writers规定数量，则会把多出来的写节点放到备用写组里面
`reader_hostgroup`	读取的流量应该发送到该组；read_only 或 super_read_only ON 的节点。
`offline_hostgroup`	当ProxySQL监视到某个节点不正常时，会被放入该组
`active`	是否启用主机组，当启用时，ProxySQL将监视主机在各族之间移动
`max_writers`	最大写节点的数量，超过该值的节点应该被放入 `backup_write_hostgroup`
`writer_is_also_reader`	如果设置为0，则只有只读节点加入 `reader_hostgroup` 如果设置成1，则 `writer_hostgroup` 中的节点会同时加入 `reader_hostgroup` 如果设置为2，则只会把 `backup_writer_hostgroup` 中的节点加入 `reader_hostgroup`
ㅤ	ㅤ

以下 SQL 配置，添加编号为 1000 的组为只写组，10002 的组为只读组。

配置 mysql_users

添加 MGR 集群用户，ProxySQL 会通过这个用户间接操作 MGR 集群

ProxySQL 配置

查看间接操作 MRG 集群 SQL 日志

通过 rules 配置读写分离路由

mysql_query_rules 的规则较 mysql_group_replication_hostgroups 的优先级高

mysql_query_rules 表中有多个字段，它们能够方便的控制每个查询请求如何通过 ProxySQL 进行路由。较重要的用 username ，schemaname ，match_digest 等。其中 match_digest 是对 digest 做正则匹配，但注意 match_pattern 字段中给的规则不是 hash 值，而是 SQL 语句的文本匹配规则

读写分离测试

Keepalived 真机故障转移

Keepalived 是运行在 lvs 之上，是一个用于做双机热备（HA）的软件，它的主要功能是实现真实机的故障隔离及负载均衡器间的失败切换，提高系统的可用性；因为在本文中 ProxySQL 作为 MGR 的出口，如果 ProxySQL 出现问题，会导致所有高可用集群无法使用；所以本文在 ProxySQL 集群上层加一层 Keepalived 作真机故障转移。

运行原理

keepalived 通过选举（看服务器设置的权重）挑选出一台热备服务器做 MASTER 机器，MASTER 机器会被分配到一个指定的虚拟 IP，外部程序可通过该 IP 访问这台服务器，如果这台服务器出现故障（断网，重启，或者本机器上的 keepalived crash 等），keepalived 会从其他的备份机器上重选（还是看服务器设置的权重）一台机器做 MASTER 并分配同样的虚拟 IP，充当前一台主服务器的角色。