AI 助理
备案控制台
开发者社区 数据库 文章 正文

ProxySQL+MGR组复制实现“自动故障恢复“和“读写分离“（一）

2022-11-28 360
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
RDS MySQL Serverless 高可用系列，价值2615元额度，1个月
简介： ProxySQL+MGR组复制实现“自动故障恢复“和“读写分离“（一）

大神连接，点这里


首先你先得有个部署好的MGR组复制集群（mgr部署详情点我）

实验中所有要用的资源包下载地址点我

这里我的IP地址就继续用我部署的mgr集群了


主机分配：

主机名

 IP地址

MGR-node1

 192.168.182.100

MOR-node2

 192.168.182.101
MGR-node3 192.168.182.102
proxysql-node 192.168.182.120

重点再说一遍奥：

三台mysql集群一定要添加hosts解析和正确的主机名


我的三台MGRmysql版本（Server version: 5.7.31）

我的proxysql机器的Mariadb的版本（Server version: 5.5.30）

以及我的系统版本（CentOS Linux release 7.6.1810 ）


如果保证和我所有版本都一样那么一定是能成功的。

[root@mgr-node1 ~]# mysql -p123456
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 8af18ff4-c8c3-11ea-bb9d-000c29e59105 | mgr-node1   |        3306 | ONLINE       |
| group_replication_applier | 8af6f48d-c8c3-11ea-8105-000c2908dcc4 | mgr-node3   |        3306 | ONLINE       |
| group_replication_applier | b9bfc6e9-c8c7-11ea-b043-000c29c3f2a0 | mgr-node2   |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)


这里可以到我的mgr组复制状态是正常的


一切准备就绪，开始正题


一 部署 ProxySQL

1.编辑下载mariadb的repo

[root@proxysql-node ~]# cat /etc/yum.repos.d/mariadb.repo
[mariadb]
name = MariaDB
baseurl = https://mirrors.ustc.edu.cn/mariadb/yum/10.1/centos7-amd64/
gpgkey=https://mirrors.ustc.edu.cn/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck=1


记得重建yum缓存，生效repo


[root@ProxySQL-node ~]# yum install -y MariaDB-client


============================================================================

如果遇到报错：

Error: MariaDB-compat conflicts with 1:mariadb-libs-5.5.60-1.el7_5.x86_64
 You could try using --skip-broken to work around the problem
 You could try running: rpm -Va --nofiles --nodigest


解决办法：

[root@ProxySQL-node ~]# rpm -qa|grep mariadb
mariadb-libs-5.5.60-1.el7_5.x86_64
[root@ProxySQL-node ~]# rpm -e mariadb-libs-5.5.60-1.el7_5.x86_64 --nodeps
[root@ProxySQL-node ~]# yum install -y MariaDB-client


2.安装proxysql

proxysql的rpm包下载地址: https://pan.baidu.com/s/1S1_b5DKVCpZSOUNmtCXrrg

提取密码: 5t1c

[root@ProxySQL-node ~]# yum install -y perl-DBI perl-DBD-MySQL
[root@ProxySQL-node ~]# rpm -ivh proxysql-1.4.8-1-centos7.x86_64.rpm --force


启动proxysql

[root@ProxySQL-node ~]# /etc/init.d/proxysql start
Starting ProxySQL: DONE!
[root@ProxySQL-node ~]# ss -lntup|grep proxy   
tcp    LISTEN     0      128       *:6080                  *:*                   users:(("proxysql",pid=29931,fd=11))
tcp    LISTEN     0      128       *:6032                  *:*                   users:(("proxysql",pid=29931,fd=28))
tcp    LISTEN     0      128       *:6033                  *:*                   users:(("proxysql",pid=29931,fd=27))
tcp    LISTEN     0      128       *:6033                  *:*                   users:(("proxysql",pid=29931,fd=26))
tcp    LISTEN     0      128       *:6033                  *:*                   users:(("proxysql",pid=29931,fd=25))
tcp    LISTEN     0      128       *:6033                  *:*                   users:(("proxysql",pid=29931,fd=24))
[root@ProxySQL-node ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032
............
............
MySQL [(none)]> show databases;
+-----+---------------+-------------------------------------+
| seq | name          | file                                |
+-----+---------------+-------------------------------------+
| 0   | main          |                                     |
| 2   | disk          | /var/lib/proxysql/proxysql.db       |
| 3   | stats         |                                     |
| 4   | monitor       |                                     |
| 5   | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+
5 rows in set (0.000 sec)
接着初始化Proxysql，将之前的proxysql数据都删除
MySQL [(none)]> delete from scheduler ;
Query OK, 0 rows affected (0.000 sec)
MySQL [(none)]> delete from mysql_servers;
Query OK, 3 rows affected (0.000 sec)
MySQL [(none)]> delete from mysql_users;
Query OK, 1 row affected (0.000 sec)
MySQL [(none)]> delete from mysql_query_rules;
Query OK, 0 rows affected (0.000 sec)
MySQL [(none)]> delete from mysql_group_replication_hostgroups ;
Query OK, 1 row affected (0.000 sec)
MySQL [(none)]> LOAD MYSQL VARIABLES TO RUNTIME;
Query OK, 0 rows affected (0.000 sec)
MySQL [(none)]> SAVE MYSQL VARIABLES TO DISK;
Query OK, 94 rows affected (0.175 sec)
MySQL [(none)]> LOAD MYSQL SERVERS TO RUNTIME;
Query OK, 0 rows affected (0.003 sec)
MySQL [(none)]> SAVE MYSQL SERVERS TO DISK;
Query OK, 0 rows affected (0.140 sec)
MySQL [(none)]> LOAD MYSQL USERS TO RUNTIME;
Query OK, 0 rows affected (0.000 sec)
MySQL [(none)]> SAVE MYSQL USERS TO DISK;
Query OK, 0 rows affected (0.050 sec)
MySQL [(none)]> LOAD SCHEDULER TO RUNTIME;
Query OK, 0 rows affected (0.000 sec)
MySQL [(none)]> SAVE SCHEDULER TO DISK;
Query OK, 0 rows affected (0.096 sec)
MySQL [(none)]> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.000 sec)
MySQL [(none)]> SAVE MYSQL QUERY RULES TO DISK;
Query OK, 0 rows affected (0.156 sec)


3.在数据库端建立proxysql登入需要的帐号 （在三个MGR任意一个节点上操作，会自动同步到其他节点）

[root@MGR-node1 ~]# mysql -p123456
.........
mysql> CREATE USER 'proxysql'@'%' IDENTIFIED BY 'proxysql';   
Query OK, 0 rows affected (0.07 sec)
mysql> GRANT ALL ON * . * TO  'proxysql'@'%';
Query OK, 0 rows affected (0.06 sec)
mysql> create user 'sbuser'@'%' IDENTIFIED BY 'sbpass';   
Query OK, 0 rows affected (0.05 sec)
mysql> GRANT ALL ON * . * TO 'sbuser'@'%'; 
Query OK, 0 rows affected (0.08 sec)
mysql> FLUSH PRIVILEGES;   
Query OK, 0 rows affected (0.07 sec)


4.创建检查MGR节点状态的函数和视图 （在三个MGR任意一个节点上操作，会自动同步到其他节点）

在MGR-node1节点上，创建系统视图sys.gr_member_routing_candidate_status，该视图将为ProxySQL提供组复制相关的监控状态指标。

下载addition_to_sys.sql脚本，在MGR-node1节点执行如下语句导入MySQL即可 (在mgr-node1节点的mysql执行后，会同步到其他两个节点上)。


下载地址: https://pan.baidu.com/s/1bNYHtExy2fmqwvEyQS3sWg

提取密码：wst7


导入addition_to_sys.sql文件数据

[root@MGR-node1 ~]# mysql -p123456 < /root/addition_to_sys.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
在三个mysql节点上可以查看该视图：
[root@MGR-node1 ~]# mysql -p123456
............
mysql> select * from sys.gr_member_routing_candidate_status;
+------------------+-----------+---------------------+----------------------+
| viable_candidate | read_only | transactions_behind | transactions_to_cert |
+------------------+-----------+---------------------+----------------------+
| YES              | NO        |                   0 |                    0 |
+------------------+-----------+---------------------+----------------------+
1 row in set (0.01 sec)


5.在proxysql中增加帐号

root@ProxySQL-node ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032
...........
MySQL [(none)]> INSERT INTO MySQL_users(username,password,default_hostgroup) VALUES ('proxysql','proxysql',1);
Query OK, 1 row affected (0.000 sec)
MySQL [(none)]> UPDATE global_variables SET variable_value='proxysql' where variable_name='mysql-monitor_username';  
Query OK, 1 row affected (0.001 sec)
MySQL [(none)]> UPDATE global_variables SET variable_value='proxysql' where variable_name='mysql-monitor_password';
Query OK, 1 row affected (0.002 sec)
MySQL [(none)]> LOAD MYSQL SERVERS TO RUNTIME;
Query OK, 0 rows affected (0.006 sec)
MySQL [(none)]> SAVE MYSQL SERVERS TO DISK;
Query OK, 0 rows affected (0.387 sec)


测试一下能否正常登入数据库

[root@ProxySQL-node ~]# mysql -uproxysql -pproxysql -h 127.0.0.1 -P6033 -e"select @@hostname"
+------------+
| @@hostname |
+------------+
| MGR-node1  |
+------------+


===================================================================

如果上面测试登录时报错:

[root@ProxySQL-node ~]#  mysql -uproxysql -pproxysql -h 127.0.0.1 -P6033 -e"select @@hostname"
ERROR 1045 (28000): ProxySQL Error: Access denied for user 'proxysql'@'127.0.0.1' (using password: YES)


但是检查发现，明明用户名和密码已经修改成proxysql:proxysql了

MySQL [(none)]> select * from global_variables;  
..........
| mysql-interfaces                                    | 0.0.0.0:6033       |
| mysql-default_schema                                | information_schema |
| mysql-stacksize                                     | 1048576            |
| mysql-server_version                                | 5.5.30             |
| mysql-connect_timeout_server                        | 3000               |
| mysql-monitor_username                              | proxysql           |
| mysql-monitor_password                              | proxysql           |
解决办法: 依次执行下面的命令
MySQL [(none)]> LOAD MYSQL VARIABLES TO RUNTIME;
MySQL [(none)]> SAVE MYSQL VARIABLES TO DISK;
MySQL [(none)]> LOAD MYSQL SERVERS TO RUNTIME;
MySQL [(none)]> SAVE MYSQL SERVERS TO DISK;
MySQL [(none)]> LOAD MYSQL USERS TO RUNTIME;
MySQL [(none)]> SAVE MYSQL USERS TO DISK;
MySQL [(none)]> LOAD SCHEDULER TO RUNTIME;
MySQL [(none)]> SAVE SCHEDULER TO DISK;
MySQL [(none)]> LOAD MYSQL QUERY RULES TO RUNTIME;
MySQL [(none)]> SAVE MYSQL QUERY RULES TO DISK;


=========================================================

如果测试登录再出现：

[root@ProxySQL-node ~]# mysql -uproxysql -pproxysql -h 127.0.0.1 -P6033 -e"select @@hostname"
ERROR 9001 (HY000) at line 1: Max connect timeout reached while reaching hostgroup 1 after 10000ms
这是因为后端三个mysql的MGR节点还没有加入到proxysql中的原因，再进行完下面的步骤"配置proxysql"后就可以了
[root@ProxySQL-node ~]# mysql -uproxysql -pproxysql -h 127.0.0.1 -P6033 -e"select @@hostname"
+------------+
| @@hostname |
+------------+
| MGR-node1  |
+------------+


6.配置proxysql

[root@ProxySQL-node ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032 
.............
MySQL [(none)]> delete from mysql_servers;
Query OK, 3 rows affected (0.000 sec)
MySQL [(none)]> insert into mysql_servers (hostgroup_id, hostname, port) values(1,'192.168.182.100',3306);
Query OK, 1 row affected (0.001 sec)
MySQL [(none)]> insert into mysql_servers (hostgroup_id, hostname, port) values(1,'192.168.182.101',3306);
Query OK, 1 row affected (0.000 sec)
MySQL [(none)]> insert into mysql_servers (hostgroup_id, hostname, port) values(1,'192.168.182.102',3306);
Query OK, 1 row affected (0.000 sec)
MySQL [(none)]> insert into mysql_servers (hostgroup_id, hostname, port) values(2,'192.168.182.100',3306);
Query OK, 1 row affected (0.000 sec)
MySQL [(none)]> insert into mysql_servers (hostgroup_id, hostname, port) values(2,'192.168.182.101',3306);
Query OK, 1 row affected (0.000 sec)
MySQL [(none)]> insert into mysql_servers (hostgroup_id, hostname, port) values(2,'192.168.182.102',3306);
Query OK, 1 row affected (0.000 sec)
MySQL [(none)]> select * from  mysql_servers ;
+--------------+-----------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname        | port | status       | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+-----------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1            | 192.168.182.100 | 3306 | ONLINE       | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 1            | 192.168.182.101 | 3306 | ONLINE       | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 1            | 192.168.182.102 | 3306 | ONLINE       | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 2            | 192.168.182.100 | 3306 | ONLINE       | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 2            | 192.168.182.101 | 3306 | ONLINE       | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 2            | 192.168.182.102 | 3306 | ONLINE       | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+-----------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
6 rows in set (0.00 sec)


hostgroup_id = 1代表write group，针对我们提出的限制，这个地方只配置了一个节点；

hostgroup_id = 2代表read group，包含了MGR的所有节点,目前只是Onlinle的，等配置过scheduler后，status就会有变化 。


对于上面的hostgroup配置，默认所有的写操作会发送到hostgroup_id为1的online节点，也就是发送到写节点上。

所有的读操作，会发送为hostgroup_id为2的online节点。


需要确认一下没有使用proxysql的读写分离规则（因为之前测试中配置了这个地方，所以需要删除，以免影响后面的测试）。

MySQL [(none)]> delete from mysql_query_rules;
Query OK, 2 rows affected (0.000 sec)
MySQL [(none)]> commit;
Query OK, 0 rows affected (0.000 sec)


最后需要将global_variables，mysql_servers、mysql_users表的信息加载到RUNTIME，更进一步加载到DISK:

MySQL [(none)]> LOAD MYSQL VARIABLES TO RUNTIME;
Query OK, 0 rows affected (0.001 sec)
MySQL [(none)]> SAVE MYSQL VARIABLES TO DISK;
Query OK, 94 rows affected (0.080 sec)
MySQL [(none)]> LOAD MYSQL SERVERS TO RUNTIME;
Query OK, 0 rows affected (0.003 sec)
MySQL [(none)]> SAVE MYSQL SERVERS TO DISK;
Query OK, 0 rows affected (0.463 sec)
MySQL [(none)]> LOAD MYSQL USERS TO RUNTIME;
Query OK, 0 rows affected (0.001 sec)
MySQL [(none)]> SAVE MYSQL USERS TO DISK;
Query OK, 0 rows affected (0.134 sec)
再次验证proxysql登录
[root@ProxySQL-node ~]# mysql -uproxysql -pproxysql -h 127.0.0.1 -P6033 -e"select @@hostname"
+------------+
| @@hostname |
+------------+
| MGR-node1  |
+------------+


文章标签：
云数据库 RDS MySQL 版
关系型数据库
监控
Linux
数据安全/隐私保护
MySQL
数据库
缓存
相关实践学习
如何在云端创建MySQL数据库
开始实验后，系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
大鹅i
目录
相关文章
一只牛博
|
5月前
|
运维 负载均衡 监控
解析ProxySQL的故障转移机制
解析ProxySQL的故障转移机制
一只牛博
188 0
chen2ha
|
关系型数据库 MySQL
MySQL基于GTID的组复制（MGR）
MySQL基于GTID的组复制（MGR）
chen2ha
63 0
架构师研究会
|
存储 关系型数据库 Java
【Postgres扩展】pg_auto_failover支持高可用性和自动故障转移
【Postgres扩展】pg_auto_failover支持高可用性和自动故障转移
架构师研究会
351 0
技术课堂的搬运工~
|
SQL 关系型数据库 MySQL
只读实例（slave主从）延迟排查
本文分享的方法适用于实时查看只读延迟(主从延迟)，即需要在延迟发生的时候查看才能确认问题，历史延迟不适用，以下环境已经开启并行复制。
技术课堂的搬运工~
1907 0
只读实例（slave主从）延迟排查
大鹅i
|
SQL 关系型数据库 MySQL
ProxySQL+MGR组复制实现“自动故障恢复“和“读写分离“（二）
ProxySQL+MGR组复制实现“自动故障恢复“和“读写分离“（二）
大鹅i
245 0
chen2ha
|
关系型数据库 MySQL
MySQL 基于 GTID 的组复制（MGR）
MySQL 基于 GTID 的组复制（MGR）
chen2ha
184 0
余二五
|
SQL 关系型数据库 MySQL
MySQL主从（介绍，配置主机，配置从机，测试主从同步）
余二五
1436 0
遥想公瑾
|
关系型数据库 网络安全 数据库
PGPool-II+PG流复制实现HA主备切换
基于PG的流复制能实现热备切换，但是是要手动建立触发文件实现，对于一些HA场景来说，需要当主机down了后，备机自动切换，经查询资料知道pgpool-II可以实现这种功能。
遥想公瑾
3034 0
优惠码优惠
|
SQL 关系型数据库 MySQL
MySQL MGR集群单主模式的自动搭建和自动化故障修复
MySQL MGR集群单主模式的自动搭建和自动化故障修复/*the waiting game:尽管人生如此艰难，不要放弃；不要妥协；不要失去希望*/ 随着MySQL MGR的版本的升级以及技术成熟，在把MHA拉下神坛之后， MGR越来越成为MySQL高可用的首选方案。
优惠码优惠
1257 0
梓杰
|
关系型数据库 MySQL 网络安全
MySQL8.0MGR单主/多主安装与切换
MySQL8.0 MGR 单主 多主
梓杰
2837 0

热门文章

最新文章

  • 1
    Java 生成 UUID
  • 2
    一文读懂 Serverless 的起源、发展和落地实践
  • 3
    Fiddler抓包：下载、安装及使用
  • 4
    用CodeViz绘制函数调用关系图(call graph)
  • 5
    阿里AI上岗双11服装车间，能识别头发丝直径十分之一的瑕疵
  • 6
    SpringBoot开发案例之整合Dubbo提供者(一)
  • 7
    Dubbo架构介绍
  • 8
    警惕垃圾邮件借加沙新闻进行钓鱼攻击
  • 9
    美国博客刊文发布NetApp StoreVault停产通知
  • 10
    IntelliJ IDEA 12 创建Web项目 教程 超详细版
  • 1
    通义灵码与云计算平台的融合：基础与概述
    36
  • 2
    《深度解析：死锁的“前世今生”与防范之道》
    29
  • 3
    《探秘程序崩溃：核心转储（Core Dump）分析全攻略》
    34
  • 4
    今日 AI 开源｜共 12 项｜开源的DIY健康追踪项目，基于低成本的智能戒指构建私人的健康监测应用
    37
  • 5
    Open Interpreter：AI 赋能终端！在终端中对话AI模型进行编程，通过运行代码来完成各种计算机操作任务
    30
  • 6
    「Mac畅玩鸿蒙与硬件15」鸿蒙UI组件篇5 - Slider 和 Progress 组件
    40
  • 7
    ai断点怎么做?
    16
  • 8
    AI Shell：在命令行里“对话” AI ，微软推出将 AI 助手引入命令行的 CLI 工具，打造对话式交互命令行
    19
  • 9
    ai怎么设计迷宫字体效果?
    15
  • 10
    「Mac畅玩鸿蒙与硬件14」鸿蒙UI组件篇4 - Toggle 和 Checkbox 组件
    29

    • 相关电子书

    更多
  • PostgreSQL复制原理及高可用集群
  • MySQL MGR 8.0高可用实战
  • PolarDB-X 2.0 全局 Binlog 与备份恢复能

    • 相关实验场景

    更多
  • 体验PolarDB MySQL多主集群
  • PolarDB MySQL自动SQL限流
  • Tair for Redis数据闪回：任意时间点数据恢复
  • PolarDB MySQL从表回收站中恢复误删的表
  • 如何开通SQL Server的专属集群
    • 下一篇
    阿里云OSS设置跨域访问