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

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

2022-11-28 294
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
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
数据库
缓存
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境，其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
大鹅i
目录
相关文章
chen2ha
|
7月前
|
关系型数据库 MySQL
MySQL基于GTID的组复制（MGR）
MySQL基于GTID的组复制（MGR）
chen2ha
39 0
架构师研究会
|
存储 关系型数据库 Java
【Postgres扩展】pg_auto_failover支持高可用性和自动故障转移
【Postgres扩展】pg_auto_failover支持高可用性和自动故障转移
架构师研究会
197 0
大鹅i
|
SQL 关系型数据库 MySQL
ProxySQL+MGR组复制实现“自动故障恢复“和“读写分离“（二）
ProxySQL+MGR组复制实现“自动故障恢复“和“读写分离“（二）
大鹅i
184 0
chen2ha
|
关系型数据库 MySQL
MySQL 基于 GTID 的组复制（MGR）
MySQL 基于 GTID 的组复制（MGR）
chen2ha
136 0
遥想公瑾
|
关系型数据库 网络安全 数据库
PGPool-II+PG流复制实现HA主备切换
基于PG的流复制能实现热备切换，但是是要手动建立触发文件实现，对于一些HA场景来说，需要当主机down了后，备机自动切换，经查询资料知道pgpool-II可以实现这种功能。
遥想公瑾
2920 0
知洲
|
SQL 前端开发 关系型数据库
MySQL主从复制--单库复制搭建
MySQL主从复制的基本步骤大致一致，这里通过一次业务操作的库级别复制，大致梳理下主从复制搭建的核心过程
知洲
360 0
优惠码优惠
|
SQL 关系型数据库 MySQL
MySQL MGR集群单主模式的自动搭建和自动化故障修复
MySQL MGR集群单主模式的自动搭建和自动化故障修复/*the waiting game:尽管人生如此艰难，不要放弃；不要妥协；不要失去希望*/ 随着MySQL MGR的版本的升级以及技术成熟，在把MHA拉下神坛之后， MGR越来越成为MySQL高可用的首选方案。
优惠码优惠
1206 0
梓杰
|
关系型数据库 MySQL 网络安全
MySQL8.0MGR单主/多主安装与切换
MySQL8.0 MGR 单主 多主
梓杰
2802 0
科技探索者
|
关系型数据库 MySQL 测试技术
Mysql组复制故障恢复测试
科技探索者
1165 0
技术小胖子
|
监控 关系型数据库 MySQL
replication监控及自动故障切换
技术小胖子
1063 0