登录/ 注册
 找回密码
 立即注册
关闭

CSDN热搜

豆瓜网 精品问答 技术交流 资源下载
返回列表 发新帖
首页 › 业界区 › 业界 ›MySQL高可用之ProxySQL + MGR 实现读写分离实战 ...

MySQL高可用之ProxySQL + MGR 实现读写分离实战

蔬陶 2025-6-2 23:29:00
部署MGR

1、MGR 前置介绍

阿里云RDS集群方案用的就是MGR模式！
1.png

1.1、什么是 MGR


  • MGR（MySQL Group Replication）是MySQL 5.7.17版本诞生的，是MySQL自带的一个插件，可以灵活部署。
  • 保证数据一致性又可以自动切换，具备故障检测功能、支持多节点写入。
  • 集群是多个MySQL Server节点共同组成的分布式集群，每个Server都有完整的副本，它是基于ROW格式的二进制日志文件和GTID特性。
1.2、MGR 优点


  • 强一致性：基于原生复制及paxos协议的组复制技术，并以插件的方式提供，提供一致数据安全保证。
  • 高容错性：只要不是大多数节点坏掉就可以继续工作，有自动检测机制，当不同节点产生资源争用冲突时，不会出现错误，按照先到者优先原则进行处理，并且内置了自动化脑裂防护机制。
  • 高扩展性：节点的新增和移除都是自动的，新节点加入后，会自动从其他节点上同步状态，直到新节点和其他节点保持一致，如果某节点被移除了，其他节点自动更新组信息，自动维护新的组信息。
  • 灵活性：有单主模式和多主模式。单主模式下，会自动选主，所有更新操作都在主上进行；多主模式下，所有server都可以同时处理更新操作。工作中优先使用单主模式！
1.3、MGR 缺点


  • 仅支持InnoDB表，并且每张表一定要有一个主键，用于做write set的冲突检测。
  • 必须打开GTID特性，二进制日志格式必须设置为ROW，用于选主与write set；主从状态信息存于表中（–master-info-repository=TABLE 、–relay-log-inforepository=TABLE），–log-slave-updates打开。
  • MGR不支持大事务，事务大小最好不超过143MB，当事务过大，无法在5秒的时间内通过网络在组成员之间复制消息，则可能会怀疑成员失败了，然后将其驱逐出局。
  • 目前一个MGR集群最多支持9个节点。
  • 不支持外键于save point特性，无法做全局间的约束检测与部分事务回滚。
  • 二进制日志不支持Binlog Event Checksum。
1.4、MGR 适用场景


  • 金融交易、重要数据存储、对主从一致性要求高的场景。
  • 核心数据总量未过亿。
  • 读多写少，如：互联网电商。
2、MySQL MGR 搭建流程

2.1、环境准备

本次集群搭建，我使用3台阿里云ECS服务器（CentOS 7.9，2核2G，20G硬盘），每台服务器都分配公网IP，开放安全组：22（SSH）、3306（MySQL）、24901（MGR）。我的服务器配置如下：
  1. Master服务器（hostname:n0)：172.21.180.98
  2. Slave服务器1（hostname:n1)：172.21.180.99
  3. Slave服务器2（hostname:n2)：172.21.180.100
复制代码
2.png

2.2、搭建流程

2.2.1、配置系统环境

将Hosts文件写入n0/n1/n2节点与内网IP对应关系，后面配置采用域名访问：
  1. # 3台服务器都执行
  2. sudo cat > /etc/hosts <<-'EOF'
  3. 172.21.180.98 n0
  4. 172.21.180.99 n1
  5. 172.21.180.100 n2
  6. EOF
复制代码
在 n0 主节点启用组复制：
  1. # 第1台服务器
  2. hostnamectl set-hostname n0
  4. # 第2台服务器
  5. hostnamectl set-hostname n1
  7. # 第3台服务器
  8. hostnamectl set-hostname n2
复制代码
在 n1，n2 节点上启用组复制：
  1. # 3台服务器都执行
  2. cd /home/
  3. wget --no-check-certificate https://repo.mysql.com/mysql80-community-release-el7-5.noarch.rpm
  4. yum localinstall -y mysql80-community-release-el7-5.noarch.rpm
复制代码
此时，可以看到三台MySQL变成了主从模式：
  1. vi /etc/yum.repos.d/mysql-community.repo
复制代码
3.png

MGR部署参考：https://blog.csdn.net/weixin_46594796/article/details/139321738
部署ProxySQL 实现MGR读写分离

1、ProxySQL 介绍

1.1、ProxySQL 如何工作
  1. # 3台服务器都执行
  2. yum install -y mysql-community-server-8.0.26
复制代码
当我们搭建好了MGR集群，由于组复制的存在，保证每个节点的数据都是强一致的。不会出现主节点有数据，而从节点没有数据的情况。主节点可读可写，从节点只负责读操作，这是经典的MGR集群方案：
4.png

不过，试想这样一种情况，当主节点发生宕机，MGR集群虽然虽然可以自动进行故障转移，重新选举新的主节点，但是Java应用不能感知到数据库发生了宕机现象，依旧会把写操作发送给宕机的主节点，此时就会发生错误。
5.png

针对这种情况，ProxySQL就发挥出了作用，通过ProxySQL自动对MGR集群进行健康状态感知。
6.png

使用ProxySQL时，如果主服务器发生了宕机，会将写入请求转移到故障转移到新晋升的主节点上，并且提供了读写分离功能。
7.png

1.2、ProxySQL 工作原理

ProxySQL对于MGR集群节点，都新增了一个数据库用户，例如：monitor。ProxySQL通过monitor用户定时发送select查询请求，判断当前数据库节点是否可以，如果出现3次不能执行select查询，就认为该节点产生故障，就需要从集群中移除。
8.png

不过此时有个问题，就是ProxySQL如何判断哪个节点是主，哪个节点是从呢？ProxySQL要求每个MGR集群中的节点都在各自服务器上创建视图，作用是收集当前节点运行状态，判断当前节点是主是从。
9.png

当ProxySQL知道了主从之后，此时Java应用进行SQL操作，就会被ProxySQL进行路由分发了！
10.png

2、ProxySQL 安装
  1. # 修改 MySQL 配置
  2. sudo cat >> /etc/my.cnf <<-'EOF'
  4. # 使用mysql_native_password密码策略，防止navicat连不上mysql8
  5. default_authentication_plugin=mysql_native_password
  6. # 设置MySQL插件目录：MGR基于插件，必须设置插件路径
  7. plugin_dir=/usr/lib64/mysql/plugin
  8. # 服务器编号，Master=1
  9. server_id=1
  10. # 开启binlog的GTID模式（MGR强制要求）
  11. gtid_mode=ON
  12. # 开启后MySQL只允许能够保障事务安全，并且能够被日志记录的SQL语句被执行
  13. enforce_gtid_consistency=ON
  14. # 关闭binlog校验（MGR强制要求）
  15. binlog_checksum=NONE
  17. # 定义用于事务期间哈希写入提取的算法，组复制模式下必须设置为 XXHASH64。
  18. transaction_write_set_extraction=XXHASH64
  19. # 确定组复制恢复时是否应该应用 SSL，通常设置为“开”，但默认设置为“关”。
  20. loose-group_replication_recovery_use_ssl=ON
  21. # 服务器实例所在复制组名称，必须是有效的 UUID，所有节点必须相同。
  22. loose-group_replication_group_name="bbbbbbbb-bbbb-cccc-dddd-eeeeeeeeeeee"
  23. # 确定服务器是否应该在服务器启动期间启动组复制。
  24. loose-group_replication_start_on_boot=OFF
  26. # 为复制组中其他的成员提供的网络地址，指定为“主机:端口”的格式化字符串。
  27. # 很多人想当然认为端口应该是3306，起始不然，MGR需要开启新端口24901同步交换
  28. # 所以这里不要写错，同时，前面我们配置了hosts文件做了主机名与IP的映射，这里直接写主机名即可
  29. loose-group_replication_local_address="n0:24901"
  31. # 用于建立新成员到组的连接组成员列表。
  32. # 这个列表指定为由分隔号间隔的组成员网络地址列表，类似 host1:port1、host2:port2 的格式。
  33. # 同样采用n0~n2的主机名替代
  34. loose-group_replication_group_seeds="n0:24901,n1:24901,n2:24901"
  36. # 配置此服务器为引导组，这个选项必须仅在一台服务器上设置，
  37. # 并且仅当第一次启动组或者重新启动整个组时。成功引导组启动后，将此选项设置为关闭。
  38. loose-group_replication_bootstrap_group=OFF
  39. EOF
复制代码
首先，下载ProxySQL安装包，进行安装操作：
  1. sudo cat >> /etc/my.cnf <<-'EOF'
  2. default_authentication_plugin=mysql_native_password
  3. plugin_dir=/usr/lib64/mysql/plugin
  5. # 设置唯一的服务器编号
  6. server_id=2
  7. gtid_mode=ON
  8. enforce_gtid_consistency=ON
  9. binlog_checksum=NONE
  11. # 这个参数决定primary节点到secondary节点的请求是否为基于 RSA 密钥对的密码交换所需的公钥
  12. loose-group_replication_recovery_get_public_key=ON
  13. loose-group_replication_recovery_use_ssl=ON
  14. loose-group_replication_group_name="bbbbbbbb-bbbb-cccc-dddd-eeeeeeeeeeee"
  15. loose-group_replication_start_on_boot=OFF
  17. # 设置本机地址n1:24901
  18. loose-group_replication_local_address="n1:24901"
  19. loose-group_replication_group_seeds="n0:24901,n1:24901,n2:24901"
  20. loose-group_replication_bootstrap_group=OFF
  21. EOF
复制代码
启动 ProxySQL 服务，可以发现启动完毕：
  1. sudo cat >> /etc/my.cnf <<-'EOF'
  2. default_authentication_plugin=mysql_native_password
  3. plugin_dir=/usr/lib64/mysql/plugin
  5. #设置唯一的服务器编号
  6. server_id=3
  7. gtid_mode=ON
  8. enforce_gtid_consistency=ON
  9. binlog_checksum=NONE
  11. #这个参数决定primary节点到secondary节点的请求是否为基于 RSA 密钥对的密码交换所需的公钥
  12. loose-group_replication_recovery_get_public_key=ON
  13. loose-group_replication_recovery_use_ssl=ON
  14. loose-group_replication_group_name="bbbbbbbb-bbbb-cccc-dddd-eeeeeeeeeeee"
  15. loose-group_replication_start_on_boot=OFF
  17. #设置本机地址n2:24901
  18. loose-group_replication_local_address="n2:24901"
  19. loose-group_replication_group_seeds="n0:24901,n1:24901,n2:24901"
  20. loose-group_replication_bootstrap_group=OFF
  21. EOF
复制代码
11.png

安装MySQL YUM仓库源：
  1. # 3台服务器都执行
  2. systemctl start mysqld
复制代码
将 MySQL 8 下载地址调整为腾讯云镜像（否则下载速度KB维度）：
  1. # 获取数据库密码
  2. grep 'temporary password' /var/log/mysqld.log
复制代码
  1. # 连接数据库
  2. mysql -uroot -p'密码'
复制代码
12.png

配置好镜像后，安装好MySQL YUM源，就可以下载MySQL客户端了：
  1. # 修改root密码为asAS123456!
  2. ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'asAS123456!';
  3. # 创建rpl_user账户，此账户用于实现主从数据同步
  4. CREATE USER rpl_user@'%' IDENTIFIED BY 'asAS123456!';
  5. # 赋予主从同步权限
  6. GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
  8. # 创建一个远程连接用户，这个用户用在Navcate、JDBC登录的时候，直接用root登录不好
  9. create user 'remote'@'%' identified with mysql_native_password by 'asAS123456!';
  10. # 为remote用户赋予所有数据库资源的访问权限，熟悉grant的小伙伴可以自己调整
  11. grant all privileges on *.* to remote@'%';
  13. # 让刚才的修改生效
  14. FLUSH PRIVILEGES;
  16. # 删除已产生的Binlog
  17. # 一定要RESET MASTER，它会删除刚才已产生的Binlog
  18. # 因为刚才Binglog包含创建用户这种高权限操作，用于主从同步的rpl_user账户是没有权限执行的
  19. # 这就会导致RelayLog重放无法正确执行，导致从属服务器卡死在"RECEVERING"状态
  20. # 利用RESET MASTER删除这些无法执行的binlog，就没问题了
  21. RESET MASTER;
复制代码
3、ProxySQL + MGR 读写分离

3.1、读写分离配置

通过上述操作，前置准备已完成，此时链接上ProxySQL：
  1. # 3台服务器都执行
  2. INSTALL PLUGIN group_replication SONAME 'group_replication.so';
复制代码
13.png

接着，将MGR集群节点配置到ProxySQL中，使用的是MGR节点的内网ip：
  1. # 注意：只在主服务器上运行
  2. # 我们在 primary.cnf 配置文件中把 group_replication_bootstrap_group 参数设置成 OFF
  3. # 在 primary 服务器启动时并不会直接启动复制组，通过下面的命令动态的开启复制组使我们的集群更安全
  4. SET GLOBAL group_replication_bootstrap_group=ON;
  5. START GROUP_REPLICATION;
  6. SET GLOBAL group_replication_bootstrap_group=OFF;
复制代码
14.png

接下来这步需要在MGR主节点上进行，配置会通过主节点同步到其他从属节点：
  1. # 指定主从账户与指定通信频道
  2. CHANGE MASTER TO MASTER_USER="rpl_user", MASTER_PASSWORD="asAS123456!" FOR CHANNEL 'group_replication_recovery';
  4. # 开启组网数据同步
  5. START GROUP_REPLICATION;
复制代码
从这里使用Navicat进入sys数据库，连接到MGR主节点上，创建视图（一段一段执行，用Navicat是因为避免控制台出现ERROR）：
15.png
  1. SELECT * FROM performance_schema.replication_group_members;
复制代码
  1. systemctl stop mysqld;
复制代码
  1. ·优先低版本节点
  2. ·版本一样，优先权重大的节点
  3. ·版本与权重一样，按照 server uuid 的字母顺序选主
复制代码
此时，回到ProxySQL服务器控制台上，设置监控账号：
  1. # 查看MySQL日志
  2. tail -n 50 /var/log/mysqld.log
复制代码
设置读写组：
  1. # n0:3306（主节点n0）从组中被移除掉
  2. [Warning] [MY-011499] [Repl] Plugin group_replication reported: 'Members removed from the group: n0:3306'
  4. # 重新选举新的 Primary 主节点
  5. [System] [MY-011500] [Repl] Plugin group_replication reported: 'Primary server with address n0:3306 left the group. Electing new Primary.'
  7. # n1:3306（从节点n1）被选举为主节点，执行之前未完成的事务处理
  8. [System] [MY-011507] [Repl] Plugin group_replication reported: 'A new primary with address n1:3306 was elected. The new primary will execute all previous group transactions before allowing writes.'
  10. # 组成员目前只剩 n1:3306, n2:3306
  11. [System] [MY-011503] [Repl] Plugin group_replication reported: 'Group membership changed to n1:3306, n2:3306 on view 17172171443362674:4.'
  13. # 关闭 n1 节点的只读状态
  14. [System] [MY-013731] [Repl] Plugin group_replication reported: 'The member action "mysql_disable_super_read_only_if_primary" for event "AFTER_PRIMARY_ELECTION" with priority "1" will be run.'
  16. # 设置 super_read_only=OFF
  17. [System] [MY-011566] [Repl] Plugin group_replication reported: 'Setting super_read_only=OFF.'
  19. # 当前节点（n1）以主节点身份工作
  20. [System] [MY-011510] [Repl] Plugin group_replication reported: 'This server is working as primary member.'
复制代码
启用规则：
  1. # 查看MySQL日志
  2. tail -n 50 /var/log/mysqld.log
复制代码
状态校验，编号为10是主节点，编号为30是从节点：
  1. # n0:3306（主节点n0）从组中被移除掉
  2. [Warning] [MY-011499] [Repl] Plugin group_replication reported: 'Members removed from the group: n0:3306'
  4. # 重新选举新的 Primary 主节点
  5. [System] [MY-011500] [Repl] Plugin group_replication reported: 'Primary server with address n0:3306 left the group. Electing new Primary.'
  7. # n1:3306（从节点n1）被选举为主节点，执行之前未完成的事务处理
  8. [System] [MY-011507] [Repl] Plugin group_replication reported: 'A new primary with address n1:3306 was elected. The new primary will execute all previous group transactions before allowing writes.'
  10. # 组成员目前只剩 n1:3306, n2:3306
  11. [System] [MY-011503] [Repl] Plugin group_replication reported: 'Group membership changed to n1:3306, n2:3306 on view 17172171443362674:4.'
  13. # 当前节点（n2）作为主节点（n1）的从成员身份工作
  14. [System] [MY-011511] [Repl] Plugin group_replication reported: 'This server is working as secondary member with primary member address n1:3306.'
复制代码
16.png

进行读写分离配置：
  1. systemctl stop mysqld;
复制代码
  1. SELECT * FROM performance_schema.replication_group_members;
复制代码
3.2、读写分离测试

这里注意，进行读写分离实战测试时，要连接ProxySQL 6033端口，6032是Admin，6033才是客户端（Java程序也连6033！）：
  1. # 3台服务器都执行
  2. systemctl restart mysqld;
复制代码
可以看到，读写分离成功！
17.png

3.3、SpringBoot 整合
  1. # 停止组复制
  2. stop group_replication;
  4. # 是否启用单主模式，默认ON，OFF代表多主
  5. set global group_replication_single_primary_mode=OFF;
  7. # 是否开启条件检查，因为多主的约束更为严格，不符合要求的直接拒绝
  8. # 不支持外键的级联操作
  9. # 不支持“串行化Serializable”
  10. set global group_replication_enforce_update_everywhere_checks=ON;
复制代码
只需要将SpringBoot的yml配置文件中配置连接到ProxySQL就可以了
  1. # 只在 n0 上执行
  2. SET GLOBAL group_replication_bootstrap_group=ON;
  3. START GROUP_REPLICATION;
  4. SET GLOBAL group_replication_bootstrap_group=OFF;
复制代码
如果运行期间出现以下错误，要么mysql驱动版本号不对，要么就是ProxySQL需要调整了：
  1. # 只在 n1, n2 上执行
  2. START GROUP_REPLICATION;
复制代码
如果要调整ProxySQL，请执行如下命令，即可解决上述问题：
  1. SELECT * FROM performance_schema.replication_group_members;
复制代码
ProxySQL搭建参考：https://xuzhibin.blog.csdn.net/article/details/139408078?spm=1001.2014.3001.5502
其他资料：https://www.cnblogs.com/kebibuluan/p/18328618
 

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！
MySQL高可用之ProxySQLMGR

使用道具 举报

相关推荐
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册
发帖
蔬陶
2025-6-2 23:29:00

0

粉丝关注

17

主题发布

板块介绍填写区域，请于后台编辑
微信扫一扫