通过ProxySQL实现MySQL读写分离

2 篇文章 0 订阅
1 篇文章 0 订阅

ProxySQL简介

ProxySQL 是一个高性能、面向 MySQL 协议的代理服务器,用于 MySQL、MariaDB 和 Percona 服务器的负载均衡、故障转移和查询路由。以下是 ProxySQL 的一些主要功能和特点:

主要功能和特点

负载均衡

ProxySQL 可以将查询分发到多个后端数据库服务器,优化资源使用,提升应用的整体性能。

高可用性

支持自动故障转移,确保数据库服务的高可用性。在后端服务器出现故障时,ProxySQL 可以自动切换到备用服务器。

查询路由

ProxySQL 能够根据查询的内容、用户、来源 IP 等条件,将查询路由到合适的后端服务器。这对于读写分离和多种复杂场景非常有用。

查询缓存

内置查询缓存功能,可以显著减少数据库服务器的负载,提高查询响应速度。

连接池

支持连接池功能,减少数据库连接的创建和销毁成本,提高连接的复用率。

高级配置和监控

提供丰富的配置选项和详细的监控功能,可以通过 SQL 接口进行管理和监控。

性能优化

ProxySQL 设计为高性能代理,支持大规模并发连接和复杂的查询路由逻辑,能够处理大量的并发请求。

使用场景

读写分离

在读写分离的场景中,ProxySQL 可以将写请求路由到主服务器,将读请求路由到从服务器,实现读写分离,优化资源利用。

多主架构

在多主架构中,ProxySQL 可以管理多个主服务器,提供负载均衡和高可用性支持。

查询优化

ProxySQL 可以根据查询类型、用户等条件对查询进行优化和路由,提高整体系统的性能和响应速度。

搭建过程

1、提前准备mysql主从服务器

主库:192.168.1.10 3306 root 123456
从库:192.168.1.11 3306 root 123456

mkdir /opt/proxysql/
vim /opt/proxysql/proxysql.cnf #编写默认配置文件
datadir="/var/lib/proxysql"

admin_variables=
{
admin_credentials="admin:admin;radmin:radmin"
mysql_ifaces="0.0.0.0:6032"
}

mysql_variables=
{
threads=4
max_connections=2048
default_query_delay=0
default_query_timeout=36000000
have_compress=true
poll_timeout=2000
interfaces="0.0.0.0:6033"
default_schema="information_schema"
stacksize=1048576
server_version="8.0.34"
connect_timeout_server=3000
monitor_username="proxy_user"
monitor_password="proxy_user@123"
monitor_history=60000
monitor_connect_interval=60000
monitor_ping_interval=10000
monitor_read_only_interval=1500
monitor_read_only_timeout=500
ping_interval_server_msec=120000
ping_timeout_server=500
commands_stats=true
sessions_sort=true
connect_retries_on_failure=10
}

2、使用以下命令创建一个docker容器,启动proxysql,并配置自动启动,永久存放数据文件。

docker run --name=proxysql \
	--privileged=true \
	--network=host \
	-d \
	-v /opt/proxysql/proxysql.cnf:/etc/proxysql.cnf \
	-v /opt/proxysql/data/:/var/lib/proxysql/ \
	-v /etc/localtime:/etc/localtime \
	--restart=always \
	proxysql/proxysql

默认控制端口:6032
默认转发端口:6033

3、主库从库新建连接监控账号,监测状态与转发连接(可选)

create user 'proxy_user'@'%' identified by 'proxy_user@123';
grant all privileges on *.* to 'proxy_user'@'%' with grant option;ALTER USER 'proxy_user'@'%' IDENTIFIED WITH mysql_native_password BY 'proxy_user@123';flush privileges;

4、登录 proxysql 管理接口

mysql -uadmin -padmin -h 127.0.0.1 -P6032

5、写入数据库信息组(暂时定义为10为读写组,20为只读组)

INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (10,'192.168.1.10',3306);
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (20,'192.168.1.11',3306);
load mysql servers to runtime;
save mysql servers to disk;

6、配置proxy SQL主从分组

insert into mysql_replication_hostgroups(writer_hostgroup,reader_hostgroup,comment) values(10,20,'proxy');
load mysql servers to runtime;
save mysql servers to disk;

7、设置监控账号(监测状态)

UPDATE global_variables SET variable_value='proxy_user' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='proxy_user@123' WHERE variable_name='mysql-monitor_password';
load mysql servers to runtime;
save mysql servers to disk;

8、配置对外访问账号,并对该用户开启事务持久化保护(账密需与目标数据库一致)

insert into mysql_users(username,password,default_hostgroup,transaction_persistent) values('proxy_user','proxy_user@123',10,1);
load mysql users to runtime;
save mysql users to disk;

9、读写分离策略配置(其余负载规则待完善)

insert into mysql_query_rules(active,match_pattern,destination_hostgroup,apply) values(1,'^SELECT.*FOR UPDATE$',10,1); #写
insert into mysql_query_rules(active,match_pattern,destination_hostgroup,apply) values(1,'^SELECT',20,1); #读
load mysql query rules to runtime;
save mysql query rules to disk;

10、设置其他项

#目前,已确认以下值不会同步,需手动设置:
set mysql-default_charset='utf8mb4';
set mysql-default_collation_connection='utf8mb4_general_ci';

#ProxySQL能处理前端请求的最大连接数
set mysql-max_connections=10000;

#proxysql响应给客户端的MySQL版本号
set mysql-server_version='8.0.34';

#set语法兼容
set mysql-set_query_lock_on_hostgroup=0;

load mysql servers to runtime;
save mysql servers to disk;

读写分离测试

mysql -u proxy_user -p'proxy_user@123' -h 127.0.0.1 -P 6033 #登陆proxysql所在机器登陆数据库
select * from mysql.user; #读操作
create database zhangyu ; #写操作

再次登录管理口

mysql -uadmin -padmin -h 127.0.0.1 -P6032

查看路由日志

mysql > select * from stats_mysql_query_digest;

调整权重,让某个机器承受更多读操作

update mysql_servers set weight=10 where hostname='192.168.1.106';
load mysql servers to runtime;
save mysql servers to disk;

更多知识:

ProxySQL 内置数据库结构

ProxySQL自身共有5个 库,分别为3个保存在内存中的库,和三个保存在磁盘的SQLite库。
通过6032管理端口登入后,默认就是main库,所有的配置更改都必须在这个库中进行,disk存档库不会直接受到影响。接下来看下

mysql -uadmin -padmin -h127.0.0.1 -P6032
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: 8.0.34 (ProxySQL Admin Module)
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)

main:内存配置数据库,表里存放后端db实例、用户验证、路由规则等信息。表名以 runtime_开头的表示proxysql当前运行的配置内容,不能通过dml语句修改,只能修改对应的不以 runtime_ 开头的(在内存)里的表,然后 LOAD 使其生效, SAVE 使其存到硬盘以供下次重启加载。
disk:是持久化到硬盘的配置,sqlite数据文件。SQLite3 数据库,默认位置为 $(DATADIR)/proxysql.db,在重新启动时,未保留的内存中配置将丢失。因此,将配置保留在 DISK 中非常重要。(SQLite是一个进程内的库,实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎)
stats:proxysql运行抓取的统计信息,包括到后端各命令的执行次数、流量、processlist、查询种类汇总/执行时间等等。
monitor:库存储 monitor 模块收集的信息,主要是对后端db的健康/延迟检查。
stats_history:统计信息历史库

核心表功能说明

一、核心表功能说明

表名说明
stat_mysql_processlistMySQL的show processlist命令,查看各线程的状态
stats_mysql_commands_counters统计各种SQL类型的执行次数和时间
stats_mysql_connection_pool连接后端MySQL的连接信息
mysql_servers包含要连接ProxySQL的后端服务器列表
mysql_users包含ProxySQL将用于向后端服务器进行身份验证的用户列表
mysql_query_rules包含用于缓存、路由或重写发送到ProxySQL的SQL查询的规则
global_variables包含在服务器初始配置期间定义的MySQL变量和管理变量
debug_levels仅用于调试ProxySQL的手动构建

二、mysql_servers

字段名说明
hostgroup_id自增ID,后端服务器所属的主机组的ID
hostname数据库IP
port数据端口
status状态说明,默认ONLINE
weight权重,默认值1
max_connections最大连接数
max_replication_lag检查主从延迟(0:默认不检查)
comment注释说明

三、mysql_users

字段名说明
username用户名
password密码(明文或者密文)
active是否激活,1:激活
default_hostgroup代理后端对应的hostgroup_id
transaction_persistent事务持久性

四、mysql_query_rules

字段名说明
rule_id自增ID,规则ID
active是否启用SQL路由项,1:启用;0:不启用(默认)
username如果非空,只有连接用户名是username的值才会匹配
schemaname如果非空,只有查询连接使用的DB是schemaname的值才会匹配,默认是information_schema
flagIN
client_addr匹配客户端IP
proxy_addr匹配本地ProxySQL的IP
proxy_port匹配本地ProxySQL的端口
digest精确匹配的一类查询
match_digest正则匹配一类查询
match_pattern正则表达式匹配查询
negate_match_pattern反向匹配,相当于match_digest
flogOUT
replace_pattern查询重写,默认为空
destination_hostgroup路由查询指向的hostgroup
cache_ttl查询结果缓存的毫秒数
timeout查询执行的最大时间ms,超时则自动kill,默认10小时
retries执行失败时,重试次数,默认1
delay查询延时执行
error_msg返回的指定的错误信息
OK_msg连接成功返回的信息
multiplex连接是否复用
log是否记录查询日志
apply是否应用规则,1:应用
comment注释

五、stats_mysql_connection_pool

字段名说明
hostgroup后端服务器所属主机组
srv_hostsrv_portMySQL后端服务器正在侦听连接的TCP端点的IP和端口
status后端服务器状态
ConnUsedProxySQL当前使用多少个连接来向后端服务器发送查询
ConnFree连接空闲个数
ConnOK建立成功连接数
ConnERR建立失败连接数
Queries路由到此特定后端服务器的查询数
Bytes_data_sent发送到后端的数据量
Bytes_data_recv后端接收的数据量
Latency_usmonitor报告的当前ping的延迟时间(ms)
  • 10
    点赞
  • 9
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
要使用 ProxySQL 配置 MySQL读写分离,可以按照以下步骤进行操作: 1. 安装 ProxySQL:首先,你需要安装 ProxySQL 的软件。你可以从 ProxySQL 的官方网站下载适用于你的操作系统的安装程序,并按照官方文档中的说明进行安装。 2. 配置主服务器和从服务器:在主服务器和从服务器上进行以下配置: - 确保主服务器和从服务器都已正确配置和运行,并且数据库实例正常工作。 - 确保主服务器和从服务器都可以通过网络连接到 ProxySQL。 3. 连接到 ProxySQL:使用以下命令连接到 ProxySQL 的命令行界面: ``` mysql -u admin -p -h proxy_server_ip -P proxy_server_port --prompt='ProxySQLAdmin>' ``` 将 "admin" 替换为 ProxySQL 的管理员用户名,将 "proxy_server_ip" 替换为 ProxySQL 服务器的 IP 地址,将 "proxy_server_port" 替换为 ProxySQL 服务器的端口号。 4. 添加主服务器和从服务器:在 ProxySQL 的命令行界面中,使用以下命令添加主服务器和从服务器: ``` INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (1, 'master_server_ip', 3306); INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (2, 'slave_server_ip', 3306); ``` 将 "master_server_ip" 替换为主服务器的 IP 地址,将 "slave_server_ip" 替换为从服务器的 IP 地址。 5. 添加主从关系:在 ProxySQL 的命令行界面中,使用以下命令添加主从关系: ``` INSERT INTO mysql_replication_hostgroups (writer_hostgroup, reader_hostgroup) VALUES (1, 2); ``` 这将指定主服务器和从服务器的关系,其中 "writer_hostgroup" 是主服务器的组 ID,"reader_hostgroup" 是从服务器的组 ID。 6. 保存更改并重新加载配置:在 ProxySQL 的命令行界面中,使用以下命令保存更改并重新加载配置: ``` LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK; LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK; ``` 7. 配置应用程序:在应用程序的数据库连接配置中,将读操作的请求连接到 ProxySQL 服务器的 IP 地址和端口号。 请注意,上述步骤仅提供了一个基本的配置示例,你可能需要根据你的环境和需求进行调整。此外,ProxySQL 还提供了许多其他的功能和配置选项,如负载均衡、故障转移等。你可以参考 ProxySQL 的官方文档以了解更多详细信息。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值