9. proxy sql实现读写分离

于 2024-06-24 20:47:15 发布
阅读量601 收藏 4
点赞数 6
分类专栏： 1、MySQL数据库 文章标签： 数据库 mysql proxy sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u010198709/article/details/139937621
版权

proxy sql实现读写分离

一、proxysql介绍

  • 读写分离
  • 基于用户、schema、语句对SQL进行路由
  • 缓存结果，减轻后端数据库服务器压力
  • 健康状态检测

二、proxysql实现读写分离

1、环境描述

192.168.140.10 主库 MySQL 8.0
192.168.140.11 从库 MySQL 8.0
192.168.140.12 proxysql

2、两台数据库配置读写分离，从库添加read_only参数

3、安装proxysql

cat > /etc/yum.repos.d/proxysql.repo << EOF
[proxysql]
name=ProxySQL YUM repository
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.6.x/centos/\$releasever
gpgcheck=1
gpgkey=https://repo.proxysql.com/ProxySQL/proxysql-2.6.x/repo_pub_key
EOF

[root@proxysql ~]# yum install -y proxysql mariadb

[root@proxysql ~]# systemctl enable --now proxysql
[root@proxysql ~]# netstat -tunlp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
tcp        0      0 0.0.0.0:6032            0.0.0.0:*               LISTEN      16544/proxysql      
tcp        0      0 0.0.0.0:6033            0.0.0.0:*               LISTEN      16544/proxysql 

6032端口: proxysql管理端口，用于对Proxysql进行配置、状态查看
6033端口: proxysql业务连接端口

3.1 proxysql内置库说明

mysql> 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.00 sec)

说明：
1、main库，最主要的库，对proxysql进行配置大多数要修改main库中的表，为内存数据库，需要持久化保存
2、disk库， 和main库一致，是main库的持久化
3、stats库，统计信息库
4、monitor库，对后端服务器做健康状态检查的信息库
5、stats_history库，历史状态信息

3.2 main库中主要的表说明

mysql> show tables from main;
+----------------------------------------------------+
| tables                                             |
+----------------------------------------------------+
| coredump_filters                                   |
| global_variables                                   |
| mysql_aws_aurora_hostgroups                        |
| mysql_collations                                   |
| mysql_firewall_whitelist_rules                     |
| mysql_firewall_whitelist_sqli_fingerprints         |
| mysql_firewall_whitelist_users                     |
| mysql_galera_hostgroups                            |
| mysql_group_replication_hostgroups                 |
| mysql_hostgroup_attributes                         |
| mysql_query_rules                                  |     // 路由SQL语句的表
| mysql_query_rules_fast_routing                     |
| mysql_replication_hostgroups                       |
| mysql_servers                                      |	  // 存储后端数据库服务器连接信息的表
| mysql_servers_ssl_params                           |    
| mysql_users                                        |    // 存储连接后端数据库用户的表
| proxysql_servers                                   |
| restapi_routes                                     |
| runtime_checksums_values                           |
| runtime_coredump_filters                           |
| runtime_global_variables                           |
| runtime_mysql_aws_aurora_hostgroups                |
| runtime_mysql_firewall_whitelist_rules             |
| runtime_mysql_firewall_whitelist_sqli_fingerprints |
| runtime_mysql_firewall_whitelist_users             |
| runtime_mysql_galera_hostgroups                    |
| runtime_mysql_group_replication_hostgroups         |
| runtime_mysql_hostgroup_attributes                 |
| runtime_mysql_query_rules                          |
| runtime_mysql_query_rules_fast_routing             |
| runtime_mysql_replication_hostgroups               |
| runtime_mysql_servers                              |
| runtime_mysql_servers_ssl_params                   |
| runtime_mysql_users                                |
| runtime_proxysql_servers                           |
| runtime_restapi_routes                             |
| runtime_scheduler                                  |
| scheduler                                          |
+----------------------------------------------------+
38 rows in set (0.01 sec)

4、在后端主库创建允许proxysql连接的用户

mysql> CREATE USER 'proxyuser'@"192.168.183.12" identified by 'WWW.1.com';
Query OK, 0 rows affected (0.02 sec)

mysql> GRANT all ON *.* TO 'proxyuser'@'192.168.183.12';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

5、在proxysql上添加后端服务器信息、并指定读、写组

[root@proxysql ~]# mysql -uadmin -padmin -h 127.0.0.1 -P 6032
 
mysql> 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.00 sec)

mysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(1, "192.168.183.10",3306,1,"write group");
Query OK, 1 row affected (0.00 sec)

mysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(2, "192.168.183.11",3306,1,"read group");
Query OK, 1 row affected (0.00 sec)

mysql> select * from mysql_servers;
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+
| hostgroup_id | hostname       | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment     |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+
| 1            | 192.168.183.10 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              | write group |
| 2            | 192.168.183.11 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              | read group  |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+
2 rows in set (0.00 sec)

说明：
数字1代表写组
数字2代表读组


mysql> load mysql servers to runtime;
Query OK, 0 rows affected (0.00 sec)

mysql> save mysql servers to disk;
Query OK, 0 rows affected (0.02 sec)

6、在Proxysql上添加连接后端数据库的用户

mysql> insert into mysql_users(username,password,default_hostgroup,transaction_persistent) values("proxyuser","WWW.1.com",1,1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from mysql_users;
+-----------+-----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+
| username  | password  | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | attributes | comment |
+-----------+-----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+
| proxyuser | WWW.1.com | 1      | 0       | 1                 | NULL           | 0             | 1                      | 0            | 1       | 1        | 10000           |            |         |
+-----------+-----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+
1 row in set (0.00 sec)

mysql> load mysql users to runtime;
Query OK, 0 rows affected (0.00 sec)

mysql> save mysql users to disk;
Query OK, 0 rows affected (0.01 sec)

7、在proxysql添加健康状态检查的用户

7.1 在后端主库创建健康状态检查用户

mysql> CREATE USER 'healthuser'@"%" IDENTIFIED BY 'WWW.1.com';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT select ON *.* TO 'healthuser'@"%";
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

7.2 proxysql上添加该用户

mysql> set mysql-monitor_username="healthuser";
Query OK, 1 row affected (0.00 sec)

mysql> set mysql-monitor_password="WWW.1.com";
Query OK, 1 row affected (0.01 sec)

mysql> load mysql variables to runtime;
Query OK, 0 rows affected (0.01 sec)

mysql> save mysql variables to disk;
Query OK, 162 rows affected (0.00 sec)

8、添加读写分离的路由规则

mysql> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) values(1,1,"^SELECT.*FOR UPDATE$
",1,1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) values(2,1,"^SELECT",2,1);
Query OK, 1 row affected (0.00 sec)

mysql> select rule_id, active, match_digest, destination_hostgroup, apply from mysql_query_rules;
+---------+--------+----------------------+-----------------------+-------+
| rule_id | active | match_digest         | destination_hostgroup | apply |
+---------+--------+----------------------+-----------------------+-------+
| 1       | 1      | ^SELECT.*FOR UPDATE$ | 1                     | 1     |
| 2       | 1      | ^SELECT              | 2                     | 1     |
+---------+--------+----------------------+-----------------------+-------+
2 rows in set (0.00 sec)

mysql> load mysql query rules to runtime;
Query OK, 0 rows affected (0.00 sec)

mysql> load admin variables to runtime;
Query OK, 0 rows affected (0.00 sec)

mysql> save mysql query rules to disk;
Query OK, 0 rows affected (0.01 sec)

mysql> save admin variables to disk;
Query OK, 49 rows affected (0.01 sec)

9、测试读写分离

9.1 登录Proxysql业务端，任意执行读写操作

[root@proxysql ~]# mysql -uproxyuser -pWWW.1.com -h 127.0.0.1 -P6033
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.30 (ProxySQL)

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

mysql> create database test1;
Query OK, 1 row affected (0.01 sec)

mysql> create database test2;
Query OK, 1 row affected (0.01 sec)

mysql> select user,host from mysql.user;
+------------------+----------------+
| user             | host           |
+------------------+----------------+
| healthuser       | %              |
| repluser         | %              |
| proxyuser        | 192.168.183.12 |
| mysql.infoschema | localhost      |
| mysql.session    | localhost      |
| mysql.sys        | localhost      |
| root             | localhost      |
+------------------+----------------+
7 rows in set (0.00 sec)

mysql>

9.2 登录proxysql管理端，通过hostgroup_id查看读写分离效果

mysql> select * from stats_mysql_query_digest;
+-----------+--------------------+-----------+----------------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+-------------------+---------------+
| hostgroup | schemaname         | username  | client_address | digest             | digest_text                      | count_star | first_seen | last_seen  | sum_time | min_time | max_time | sum_rows_affected | sum_rows_sent |
+-----------+--------------------+-----------+----------------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+-------------------+---------------+
| 2         | information_schema | proxyuser |                | 0xf02b330c823d739  | select user,host from mysql.user | 1          | 1719230970 | 1719230970 | 5030     | 5030     | 5030     | 0                 | 7             |
| 1         | information_schema | proxyuser |                | 0x5b29ed96765bad6c | create database test2            | 1          | 1719230951 | 1719230951 | 7884     | 7884     | 7884     | 1                 | 0             |
| 1         | information_schema | proxyuser |                | 0xec77b5d4dcabfb9f | create database test1            | 1          | 1719230948 | 1719230948 | 4983     | 4983     | 4983     | 1                 | 0             |
| 1         | information_schema | proxyuser |                | 0x2033e45904d3df0  | show databases                   | 1          | 1719230934 | 1719230934 | 12909    | 12909    | 12909    | 0                 | 4             |
| 1         | information_schema | proxyuser |                | 0x226cd90d52a2ba0b | select @@version_comment limit ? | 1          | 1719230929 | 1719230929 | 0        | 0        | 0        | 0                 | 0             |
+-----------+--------------------+-----------+----------------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+-------------------+---------------+
5 rows in set (0.02 sec)
Martin_wjc
关注
  • 6
    点赞
  • 踩
  • 4
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
proxysql读写分离
qq_44784155的博客
08-14 515
1.不同类型的读写分离方案解析 数据库中间件最基本的功能就是实现读写分离， ProxySQL 当然也支持。而且 ProxySQL 支持的路由规则非常灵活，不仅可以实现最简单的读写分离，还可以将读/写都分散到多个不同的组，以及实现分库 sharding (分表sharding的规则比较难写，但也能实现)。 本文只描述通过规则制定的语句级读写分离，不讨论通过 ip/port, client, username, schemaname 实现的读写分离。 下面描述了ProxySQL能实现的常见读写分离类型 1.1
proxy SQL实现MySQL主从读写分离
小啊宇的博客
09-23 1266
proxy SQL实现MySQL主从读写分离proxy SQL简介同类型产品proxy SQL功能安装环境MySQL主从搭建proxy SQL安装下载安装 ProxySQL启动 ProxySQL查看 ProxySQL 的版本管理员登录 ProxySQL配置 ProxySQL 所需账户ProxySQL 的多层配置系统在不同层次间移动配置管理配置小结 proxy SQL简介 ProxySQL 是一款可以实际用于生产环境的 MySQL 中间件，它有官方版和 percona 版两种。percona版是在官方版的基础
MSSQLProxy：数据库安全监测的新守护者
gitblog_00006的博客
04-25 270
MSSQLProxy：数据库安全监测的新守护者 项目地址:https://gitcode.com/blackarrowsec/mssqlproxy 项目简介 MSSQLProxy 是一个由 BlackArrowSec 团队开发的开源项目，它是一个透明代理工具，专为 Microsoft SQL Server 提供安全审计和数据过滤功能。通过在数据库连接之间插入 MSSQLProxy，您可以实时监控、...
Proxy实现mysql读写分离
ss810540895的博客
03-13 588
1. mysql实现读写分离的方式 mysql 实现读写分离的方式有以下几种： 程序修改mysql操作，直接和数据库通信，简单快捷的读写分离和随机的方式实现的负载均衡，权限独立分配，需要开发人员协助。 amoeba，直接实现读写分离和负载均衡，不用修改代码，有很灵活的数据解决方案，自己分配账户，和后端数据库权限管理独立，权限处理不够灵活。 mysql-proxy，直接实现读写分离和负载均衡，不用...
通过ProxySQL实现MySQL读写分离
最新发布
id_is_null的博客
05-20 821
ProxySQL 是一个高性能、面向 MySQL 协议的代理服务器，用于 MySQL、MariaDB 服务器的读写分离、负载均衡、故障转移和查询路由。
ProxySQL读写分离
08-01 282
ProxySQL读写分离查询路由是proxysql的核心特性之一。读/写分离可能是最常用的查询路由之一，而另一种最常用的查询路由是分片。一、使用不同的端口进行读写分离如果使用像HAProxy这样的代理，可以将其配置为侦听两个端口:一个端口作为写入端，而第二个端口作为读取端。人们经常询问如何使用相同的方法配置proxysql，以及基于传入端口查询路由。下面是一个关于如何实现基于...
利用ProxySQL实现读写分离
jeffery_love的博客
06-17 511
环境准备： 准备三台主机，一台ProxySQL服务器:192.168.8.7，另外两台主机实现主从复制192.168.8.17,27 注意：slave节点需要设置read_only=1 安装ProxySQL，并向ProxySQL中添加MySQL节点，以下操作不需要use main也可成功 MySQL> show tables; MySQL > select * from sqlite_master where name='mysql_servers'\G MySQL > sel..
mysql-proxy实现读写分离1
08-04
MySQL Proxy 是一个开源工具，主要用于实现数据库的读写分离，以优化MySQL服务器的负载。在本文中，我们将探讨如何利用MySQL Proxy实现一个简单的读写分离环境，并关注在配置过程中的一些关键点。 首先，读写分离是...
通过mysql-proxy完成mysql读写分离
09-10
MySQL Proxy是一款轻量级的代理服务器，它位于客户端和MySQL服务器之间，可以动态地处理SQL语句，实现读写分离、负载均衡等功能。以下是使用MySQL Proxy完成MySQL读写分离的具体步骤： 1. 安装Lua环境： Lua是...
MySQL Proxy 快速实现读写分离以及负载均衡
03-31
### MySQL Proxy 快速实现读写分离以及负载均衡 #### 一、概述 在数据库管理领域，MySQL Proxy 是一个开源的代理服务器，它为 MySQL 提供了一层中间件，能够帮助用户实现数据库的读写分离、负载均衡等功能，进而...
使用mysql-proxy实现mysql读写分离
04-11
### 使用mysql-proxy实现MySQL读写分离 随着业务规模的不断扩大，数据库系统往往成为整个系统中的瓶颈之一。为了提高系统的响应速度以及数据处理能力，一种常用的技术手段就是采用**读写分离**的方式。读写分离的...
mysql中间件proxysql实现mysql读写分离
weixin_44437026的博客
03-13 989
proxysql1. mysql实现读写分离的方式2. ProxySQL简介3. ProxySQL安装ySQL的Admin管理接口5. 和admin管理接口相关的变量5.1 admin-admin_credentials5.2 admin-stats_credentials5.3 admin-mysql_ifaces6. 多层配置系统6.1 proxysql中的库6.2 ProxySQL多层配置系...
ProxySQL实现mysql读写分离
夸父的博客
12-09 1163
环境说明 IP 角色 应用 系统平台 192.168.118.222 读写分离解析主机 proxysql rhel7.4 192.168.118.112 master mariadb rhel7.4 192.168.118.100 slave mariadb rhel7.4 关闭防火墙,关闭selinux [root@server01 ~]# s...
proxysql mysql_利用ProxySQL实现MySQL的读写分离
weixin_35705450的博客
02-19 135
年前最后一场技巧盛宴 | 1月27日与京东、日记易技巧大年夜咖畅聊智能化运维成长趋势！一、Proxysql 的基本常识ProxySQL是一个可以实现MySQL读写分别的轻量级对象。ProxySQL的特点将所有设备保存写入到SQLit表中。支撑动态加载设备，即一般可以在线修改设备，但有少部分参数照样须要重启来生效。支撑query cache。支撑对query的路由，可以针对某个语句进行分派去哪个实例...
ProxySQL源码分析1-ProxySQL功能介绍
阿尔卑斯LOVE
08-31 261
上图中展示了流量从客户端发出 → ProxySQL进行处理转发 → 后端处理 → ProxySQL的前端连接 → 返回客户端的基本流程。下面我们简单梳理下ProxySQL的核心功能如下图。 读写分离 简单Sharding 连接池管理 多路复用 流量管控 高可用 查询缓存 动态配置标准化 流量镜像 SQL改写 ...
ProxySQL实现读写分离
08-12 143
环境： 192.168.205.37: as ProxySQL server 192.168.205.47: as Master server 192.168.205.57: as Slave server 版本： OS: centos 7 1810 with mini install mariadb-server 5.5.60 proxysql-1.4.15-1-centos7.x86_...
ProxySQL配置读写分离
十五十六
11-05 744
简介 ProxySQL是具有GPL许可证的高性能MySQL代理。作为监视进程监视的守护进程运行。该进程监视守护进程并在发生崩溃时重新启动它以最大限度地减少停机时间。 守护进程接受来自MySQL客户端的传入流量，并将其转发到后端MySQL服务器。 代理设计为连续运行而无需重新启动。大多数配置可以在运行时使用类似于SQL语句的查询来完成。其中包括运行时参数，服务器分组和与流量相关的设置。 官方文档：h...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值