通过ProxySQL实现读写分离
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_processlist | MySQL的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_host和srv_port | MySQL后端服务器正在侦听连接的TCP端点的IP和端口 |
status | 后端服务器状态 |
ConnUsed | ProxySQL当前使用多少个连接来向后端服务器发送查询 |
ConnFree | 连接空闲个数 |
ConnOK | 建立成功连接数 |
ConnERR | 建立失败连接数 |
Queries | 路由到此特定后端服务器的查询数 |
Bytes_data_sent | 发送到后端的数据量 |
Bytes_data_recv | 后端接收的数据量 |
Latency_us | monitor报告的当前ping的延迟时间（ms） |
391
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
