1. ProxySQL简介
ProxySQL 是一个高性能的SQL 代理。ProxySQL有一个高级多核体系结构。支持数十万并发连接 , 可以多路复用到后端服务器上。作为守护进程运行。而且有一个进程监视这个守护进程 , 并在守护进程崩溃时重启守护进程 , 以减少停机时间。这个守护进程接受来自MySQL客户端的请求 , 并将其转发到后端的MySQL服务器上。ProxySQL被设计为可以连续运行而且不需要重启。大多数配置可以在运行时使用类似SQL语句的查询完成。
ProxySQL是用C++语言开发的, 虽然也是一个轻量级产品, 但性能很好(据测试, 能处理千亿级的数据), 功能也足够, 能满足中间件所需的绝大多数功能, 包括:
最基本的读/写分离, 且方式有多种。
可定制基于用户、基于
schema、基于语句的规则对SQL语句进行路由。换句话说, 规则很灵活。基于schema和与语句级的规则, 可以实现简单的sharding。可缓存查询结果。虽然
ProxySQL的缓存策略比较简陋, 但实现了基本的缓存功能, 绝大多数时候也够用了。此外, 作者已经打算实现更丰富的缓存策略。监控后端节点。
ProxySQL可以监控后端节点的多个指标, 包括:ProxySQL和后端的心跳信息, 后端节点的read-only/read-write,slave和master的数据同步延迟性(replication lag)。
ProxySQL默认管理端口6032 , 默认需要127.0.0.1来进入 , 进入方式和连接MySQL方式一致。
官方站点地址 : ProxySQL - A High Performance Open Source MySQL Proxy
官方github : Home · sysown/proxysql Wiki
ProxySQL中文手册: Home · malongshuai/proxysql Wiki
2. 安装ProxySQL
proxysql的静态配置文件是/etc/proxysql.cnf(只在第一次启动的时候有用 , 后续所有的配置修改都是对SQLite数据库操作 , 并且不会更新到proxysql.cnf文件中。) , 日志文件是/var/lib/proxysql/proxysql.log , SQLITE的数据文件是/var/lib/proxysql/proxysql.db。
安装步骤可以参考链接: Download and Install ProxySQL - ProxySQL
ubuntu安装源配置添加安装源配置
apt-get install -y --no-install-recommends lsb-release wget apt-transport-https ca-certificates gnupg wget -O - 'https://repo.proxysql.com/ProxySQL/proxysql-2.7.x/repo_pub_key' | apt-key add - echo deb https://repo.proxysql.com/ProxySQL/proxysql-2.7.x/$(lsb_release -sc)/ ./ | tee /etc/apt/sources.list.d/proxysql.list可以在不使用
apt密钥的情况下添加存储库密钥wget -nv -O /etc/apt/trusted.gpg.d/proxysql-2.7.x-keyring.gpg 'https://repo.proxysql.com/ProxySQL/proxysql-2.7.x/repo_pub_key.gpg'使用命令安装
proxysqlapt-get update apt-get install proxysqlCentOS安装源配置。Rocky系统使用与CentOS镜像类似[root@rocky9-proxy ~]# cat <<EOF | tee /etc/yum.repos.d/proxysql.repo [proxysql_repo] name=ProxySQL repository baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.7.x/centos/\$releasever gpgcheck=1 gpgkey=https://repo.proxysql.com/ProxySQL/proxysql-2.7.x/repo_pub_key EOF [proxysql_repo] name=ProxySQL repository baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.7.x/centos/$releasever gpgcheck=1 gpgkey=https://repo.proxysql.com/ProxySQL/proxysql-2.7.x/repo_pub_key 之后直接使用
dnf安装proxysql软件包[root@rocky9-proxy ~]# dnf install proxysql -y
另外可以直接通过github下载对应的安装包以及二进制编译包
github下载对应安装包CentOS9下载对应安装包。安装过程中会自动下载对应的依赖包[root@rocky-proxysql ~]# wget https://github.com/sysown/proxysql/releases/download/v3.0.0-alpha/proxysql-3.0.0-1-centos9.x86_64.rpm [root@rocky-proxysql ~]# dnf install proxysql-3.0.0-1-centos9.x86_64.rpm -y Last metadata expiration check: 1:14:47 ago on Thu 02 Jan 2025 11:06:29 PM CST. Ubuntu2204下载对应软件包root@ubuntu2204:~# wget https://github.com/sysown/proxysql/releases/download/v3.0.0-alpha/proxysql_3.0.0-ubuntu22_amd64.deb之后直接使用
dpkg命令安装软件包root@ubuntu2204:~# dpkg -i proxysql_3.0.0-ubuntu22_amd64.deb编译安装
ProxySQapt install make g++ bzip2 cmake libgnutls28-dev libicu-le-hb-dev libevent-dev libtool libssl-dev dnf install make gcc-c++ cmake patch perl gnutls-devel libtool zlib-devel libicu-devel libevent-devel openssl-devel libuuid-devel
3. ProxySQL简单配置
这里通过一个简单的示例实现ProxySQL的读写分离功能 , 算是ProxySQL的快速入门。即使是快速入门 , 需要配置的内容也很多 , 包括: 后端MySQL配置、监控配置、发送SQL语句的用户、SQL语句的路由规则。
环境:
3.1 配置后端的主从复制
注意点: slave节点需要设置read_only=1。如果后端是PXC/MGR/MariaDB Galera , 则无需手动设置 , 因为会自动设置。
三个节点配置文件
#master
[mysqld]
log-bin=master-bin
sync-binlog=1
server-id=7
binlog_format=ROW
skip-name-resolve
#slave01
[mysqld]
log-bin=slave01-bin
relay_log=relay-log
server-id=11
binlog_format=row
log-slave-updates
read-only=on
#slave02
[mysqld]
log-bin=slave02-bin
relay_log=relay-log
server-id=29
binlog_format=row
read-only=on主节点中创建复制用户
[root@master01 ~]# mysql -uroot
MariaDB [(none)]> show master logs;
+-------------------+-----------+
| Log_name | File_size |
+-------------------+-----------+
| master-bin.000001 | 329 |
+-------------------+-----------+
1 row in set (0.002 sec)
MariaDB [(none)]> create user 'repl'@'192.168.71.%' identified by 'P@ssword1!';
Query OK, 0 rows affected (0.014 sec)
MariaDB [(none)]> grant REPLICATION SLAVE on *.* to 'repl'@'192.168.71.%';
Query OK, 0 rows affected (0.010 sec)在两个从节点中的配置连接选项, 并启动从节点复制进程
MariaDB [(none)]> CHANGE MASTER TO
MASTER_HOST='192.168.71.7',
MASTER_USER='repl',
MASTER_PASSWORD='P@ssword1!',
MASTER_PORT=3306,
MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=329;
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.001 sec)如此配置之后 , 3个MySQL节点就保持了同步。
3.2 向ProxySQL中添加MySQL节点
启动
ProxySQLroot@ubuntu2204:~# systemctl start proxysql启动后会监听两个端口 , 默认为
6032和6033。6032端口是ProxySQL的管理端口 ,6033是ProxySQL对外提供服务的端口。root@ubuntu2204:~# ss -tnl State Recv-Q Send-Q Local Address:Port Peer Address:Port Process LISTEN 0 4096 127.0.0.53%lo:53 0.0.0.0:* LISTEN 0 128 0.0.0.0:22 0.0.0.0:* LISTEN 0 128 127.0.0.1:6010 0.0.0.0:* LISTEN 0 1024 0.0.0.0:6033 0.0.0.0:* LISTEN 0 1024 0.0.0.0:6033 0.0.0.0:* LISTEN 0 1024 0.0.0.0:6033 0.0.0.0:* LISTEN 0 1024 0.0.0.0:6033 0.0.0.0:* LISTEN 0 128 0.0.0.0:6032 0.0.0.0:* LISTEN 0 128 [::]:22 [::]:* LISTEN 0 128 [::1]:6010 [::]:*使用
mysql客户端连接到ProxySQL的管理接口(admin interface) , 该接口的默认管理员用户和密码都是admin。root@ubuntu2204:~# mysql -uadmin -padmin -P6032 -h127.0.0.1 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 Admin Module) 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>这里可以使用
--prompt 'admin> '修改提示符, 将mysql>提示符修改为admin >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和monitor数据库中的表。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 | | 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.00 sec) mysql> show tables from monitor; +--------------------------------------+ | tables | +--------------------------------------+ | mysql_server_aws_aurora_check_status | | mysql_server_aws_aurora_failovers | | mysql_server_aws_aurora_log | | mysql_server_connect_log | | mysql_server_galera_log | | mysql_server_group_replication_log | | mysql_server_ping_log | | mysql_server_read_only_log | | mysql_server_replication_lag_log | +--------------------------------------+ 9 rows in set (0.00 sec)runtime_开头的是运行时的配置 , 这些是不能修改的。要修改ProxySQL的配置 , 需要修改了非runtime_表 , 修改后必须执行LOAD ... TO RUNTIME才能加载到RUNTIME生效 , 执行save ... to disk才能将配置持久化保存到磁盘。insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.71.7',3306); insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.71.11',3306); insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.71.29',3306);注: 上面语句中没有先切换到main库也执行成功了 , 因为
ProxySQL内部使用的SQLite3数据库引擎 , 和MySQL的解析方式是不一样的。即使执行了USE main语句也是无任何效果的 , 但不会报错。查看这
3个节点是否插入成功 , 以及它们的状态。mysql> select * from mysql_servers\G *************************** 1. row *************************** hostgroup_id: 10 hostname: 192.168.71.7 port: 3306 gtid_port: 0 status: ONLINE weight: 1 compression: 0 max_connections: 1000 max_replication_lag: 0 use_ssl: 0 max_latency_ms: 0 comment: *************************** 2. row *************************** hostgroup_id: 10 hostname: 192.168.71.11 port: 3306 gtid_port: 0 status: ONLINE weight: 1 compression: 0 max_connections: 1000 max_replication_lag: 0 use_ssl: 0 max_latency_ms: 0 comment: *************************** 3. row *************************** hostgroup_id: 10 hostname: 192.168.71.29 port: 3306 gtid_port: 0 status: ONLINE weight: 1 compression: 0 max_connections: 1000 max_replication_lag: 0 use_ssl: 0 max_latency_ms: 0 comment: 3 rows in set (0.00 sec)修改后 , 加载到
RUNTIME, 并保存到disk。mysql> load mysql servers to runtime; Query OK, 0 rows affected (0.01 sec) mysql> save mysql servers to disk; Query OK, 0 rows affected (0.27 sec)
3.3 监控后端的Mysql节点
添加节点之后 , 还需要监控后端节点。对于后端是主从复制的环境来说 , 这是必须的 , 因为ProxySQL需要通过每个节点的read_only值来自动调整它们是属于读组还是写组。
首先在后端master节点上创建一个用于监控的用户名(只需在master上创建即可 , 因为会复制到slave上) , 这个用户名只需具有USAGE权限即可。如果还需要监控复制结构中slave是否严重延迟于master(先混个眼熟 : 这个俗语叫做"拖后腿" , 术语叫做"replication lag") , 则还需具备replication client权限。这里直接赋予这个权限。
# 在master上执行 :
MariaDB [(none)]> create user monitor@'192.168.71.%' identified by 'P@ssword1!';
Query OK, 0 rows affected (0.011 sec)
MariaDB [(none)]> grant replication client on *.* to monitor@'192.168.71.%';
Query OK, 0 rows affected (0.009 sec)然后回到ProxySQL上配置监控。
mysql> set mysql-monitor_username='monitor';
Query OK, 1 row affected (0.00 sec)
mysql> set mysql-monitor_password='P@ssword1!';
Query OK, 1 row affected (0.00 sec)以上设置实际上是在修改global_variables表 , 它和下面两个语句是等价的 :
UPDATE global_variables SET variable_value='monitor'
WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='P@ssword1!'
WHERE variable_name='mysql-monitor_password';修改后 , 加载到RUNTIME , 并保存到disk。
load mysql variables to runtime;
save mysql variables to disk;验证监控结果 : ProxySQL监控模块的指标都保存在monitor库的log表中。
以下是连接是否正常的监控(对connect指标的监控) : (在前面可能会有很多connect_error , 这是因为没有配置监控信息时的错误 , 配置后如果connect_error的结果为NULL则表示正常)
mysql> select * from mysql_server_connect_log;
mysql> select * from mysql_server_connect_log;
+---------------+------+------------------+-------------------------+------------------------------------------------------------------------+
| hostname | port | time_start_us | connect_success_time_us | connect_error |
+---------------+------+------------------+-------------------------+------------------------------------------------------------------------+
| 192.168.71.7 | 3306 | 1736010835206625 | 0 | Access denied for user 'monitor'@'192.168.71.32' (using password: YES) |
| 192.168.71.29 | 3306 | 1736010835932076 | 0 | Access denied for user 'monitor'@'192.168.71.32' (using password: YES) |
| 192.168.71.11 | 3306 | 1736010836657546 | 0 | Access denied for user 'monitor'@'192.168.71.32' (using password: YES) |
.....
| 192.168.71.11 | 3306 | 1736011016709242 | 0 | Access denied for user 'monitor'@'192.168.71.32' (using password: YES) |
| 192.168.71.7 | 3306 | 1736011330696975 | 1694 | NULL |
| 192.168.71.11 | 3306 | 1736011331458480 | 1975 | NULL |
| 192.168.71.29 | 3306 | 1736011332219633 | 3041 | NULL |
| 192.168.71.11 | 3306 | 1736011390697760 | 1871 | NULL |
| 192.168.71.29 | 3306 | 1736011391193000 | 2043 | NULL |
| 192.168.71.7 | 3306 | 1736011391688212 | 1808 | NULL |
+---------------+------+------------------+-------------------------+------------------------------------------------------------------------+
33 rows in set (0.00 sec)以下是对心跳信息的监控(对ping指标的监控):
mysql> select * from mysql_server_ping_log;
+---------------+------+------------------+----------------------+------------------------------------------------------------------------+
| hostname | port | time_start_us | ping_success_time_us | ping_error |
+---------------+------+------------------+----------------------+------------------------------------------------------------------------+
| 192.168.71.11 | 3306 | 1736010935313479 | 0 | Access denied for user 'monitor'@'192.168.71.32' (using password: YES) |
| 192.168.71.7 | 3306 | 1736010935313898 | 0 | Access denied for user 'monitor'@'192.168.71.32' (using password: YES) |
| 192.168.71.29 | 3306 | 1736010935315610 | 0 | Access denied for user 'monitor'@'192.168.71.32' (using password: YES) |
...
| 192.168.71.11 | 3306 | 1736011325340751 | 0 | Access denied for user 'monitor'@'192.168.71.32' (using password: YES) |
| 192.168.71.7 | 3306 | 1736011325340407 | 0 | Access denied for user 'monitor'@'192.168.71.32' (using password: YES) |
| 192.168.71.29 | 3306 | 1736011325342269 | 0 | Access denied for user 'monitor'@'192.168.71.32' (using password: YES) |
...
| 192.168.71.7 | 3306 | 1736011530862211 | 636 | NULL |
| 192.168.71.11 | 3306 | 1736011530862319 | 563 | NULL |
| 192.168.71.29 | 3306 | 1736011530862091 | 808 | NULL |
+---------------+------+------------------+----------------------+------------------------------------------------------------------------+
183 rows in set (0.00 sec)但是 , read_only和replication_lag的监控日志都为空。
mysql> select * from mysql_server_read_only_log;
Empty set (0.00 sec)
mysql> select * from mysql_server_replication_lag_log;
Empty set (0.00 sec)这是因为还没有对ProxySQL中的节点分组 : writer_hostgroup、reader_hostgroup。设置分组信息 , 需要修改的是main库中的mysql_replication_hostgroups表 , 该表只有3个字段 : 第一个字段名为writer_hostgroup , 第二个字段为reader_hostgroup , 第三个字段为注释字段 , 可随意写。
例如 , 指定写组的id为10 , 读组的id为20。
mysql> insert into mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup) values(10,20);
Query OK, 1 row affected (0.00 sec)在该配置加载到RUNTIME生效之前 , 先查看下各mysql server所在的组。
mysql> select hostgroup_id,hostname,port,status,weight from mysql_servers;
+--------------+---------------+------+--------+--------+
| hostgroup_id | hostname | port | status | weight |
+--------------+---------------+------+--------+--------+
| 10 | 192.168.71.7 | 3306 | ONLINE | 1 |
| 10 | 192.168.71.11 | 3306 | ONLINE | 1 |
| 10 | 192.168.71.29 | 3306 | ONLINE | 1 |
+--------------+---------------+------+--------+--------+
3 rows in set (0.00 sec)3个节点都在hostgroup_id=10的组中。
现在 , 将刚才mysql_replication_hostgroups表的修改加载到RUNTIME生效。
mysql> load mysql servers to runtime;
Query OK, 0 rows affected (0.01 sec)
mysql> save mysql servers to disk;
Query OK, 0 rows affected (0.22 sec)一加载 , Monitor模块就会开始监控后端的read_only值 , 当监控到read_only值后 , 就会按照read_only的值将某些节点自动移动到读/写组。
检查ProxySQL监控后端节点中的read_only节点状态
mysql> SELECT * FROM monitor.mysql_server_read_only_log ORDER BY time_start_us DESC LIMIT 3;
+---------------+------+------------------+-----------------+-----------+-------+
| hostname | port | time_start_us | success_time_us | read_only | error |
+---------------+------+------------------+-----------------+-----------+-------+
| 192.168.71.7 | 3306 | 1736014769174414 | 653 | 0 | NULL |
| 192.168.71.11 | 3306 | 1736014769174310 | 701 | 1 | NULL |
| 192.168.71.29 | 3306 | 1736014769174309 | 782 | 1 | NULL |
+---------------+------+------------------+-----------------+-----------+-------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM monitor.mysql_server_read_only_log ;
+---------------+------+------------------+-----------------+-----------+-------+
| hostname | port | time_start_us | success_time_us | read_only | error |
+---------------+------+------------------+-----------------+-----------+-------+
| 192.168.71.11 | 3306 | 1736014164622081 | 700 | 1 | NULL |
| 192.168.71.29 | 3306 | 1736014164621920 | 995 | 1 | NULL |
| 192.168.71.7 | 3306 | 1736014164622299 | 673 | 0 | NULL |
....例如 , 此处所有节点都在id=10的写组 , slave1和slave2都是slave , 它们的read_only=1 , 这两个节点将会自动移动到id=20的组。如果一开始这3节点都在id=20的读组 , 那么移动的将是Master节点 , 会自动移动到id=10的写组。
mysql> select hostgroup_id, hostname, port , status from runtime_mysql_servers;
+--------------+---------------+------+--------+
| hostgroup_id | hostname | port | status |
+--------------+---------------+------+--------+
| 10 | 192.168.71.7 | 3306 | ONLINE |
| 20 | 192.168.71.11 | 3306 | ONLINE |
| 20 | 192.168.71.29 | 3306 | ONLINE |
+--------------+---------------+------+--------+
3 rows in set (0.00 sec)这里需要注意每个版本配置到这一步的时候 , 显示不一样。
这里安装配置的是3.0版本。mysql_servers库中 , 在配置到这里的时候, 不会有什么改变。
mysql> select hostgroup_id,hostname,port,status,weight from mysql_servers;
+--------------+---------------+------+--------+--------+
| hostgroup_id | hostname | port | status | weight |
+--------------+---------------+------+--------+--------+
| 10 | 192.168.71.7 | 3306 | ONLINE | 1 |
| 10 | 192.168.71.11 | 3306 | ONLINE | 1 |
| 10 | 192.168.71.29 | 3306 | ONLINE | 1 |
+--------------+---------------+------+--------+--------+
3 rows in set (0.00 sec)在安装proxysql-2.0.2版本的时候,这里会直接显示为以下内容。主节点会同时进去10、20两个组
MySQL [(none)]> select hostgroup_id,hostname,port,status,weight from runtime_mysql_servers;
+--------------+---------------+------+--------+--------+
| hostgroup_id | hostname | port | status | weight |
+--------------+---------------+------+--------+--------+
| 10 | 192.168.71.7 | 3306 | ONLINE | 1 |
| 20 | 192.168.71.11 | 3306 | ONLINE | 1 |
| 20 | 192.168.71.29 | 3306 | ONLINE | 1 |
| 20 | 192.168.71.7 | 3306 | ONLINE | 1 |
+--------------+---------------+------+--------+--------+
4 rows in set (0.00 sec)
MySQL [(none)]> select hostgroup_id,hostname,port,status,weight from mysql_servers;
+--------------+---------------+------+--------+--------+
| hostgroup_id | hostname | port | status | weight |
+--------------+---------------+------+--------+--------+
| 10 | 192.168.71.7 | 3306 | ONLINE | 1 |
| 20 | 192.168.71.11 | 3306 | ONLINE | 1 |
| 20 | 192.168.71.29 | 3306 | ONLINE | 1 |
| 20 | 192.168.71.7 | 3306 | ONLINE | 1 |
+--------------+---------------+------+--------+--------+
4 rows in set (0.00 sec)在proxysql-1.4.9版本中, 会直接将mysql_servers库中的内容修改
MySQL [(none)]> select hostgroup_id,hostname,port,status,weight from runtime_mysql_servers;
+--------------+---------------+------+--------+--------+
| hostgroup_id | hostname | port | status | weight |
+--------------+---------------+------+--------+--------+
| 10 | 192.168.71.7 | 3306 | ONLINE | 1 |
| 20 | 192.168.71.29 | 3306 | ONLINE | 1 |
| 20 | 192.168.71.11 | 3306 | ONLINE | 1 |
+--------------+---------------+------+--------+--------+
3 rows in set (0.01 sec)
MySQL [(none)]> select hostgroup_id,hostname,port,status,weight from mysql_servers;
+--------------+---------------+------+--------+--------+
| hostgroup_id | hostname | port | status | weight |
+--------------+---------------+------+--------+--------+
| 10 | 192.168.71.7 | 3306 | ONLINE | 1 |
| 20 | 192.168.71.11 | 3306 | ONLINE | 1 |
| 20 | 192.168.71.29 | 3306 | ONLINE | 1 |
+--------------+---------------+------+--------+--------+
3 rows in set (0.00 sec)每个版本只要runtime_mysql_servers运行时的库中能按照组划分即可。只是mysql_servers库不一样
3.4 配置mysql_users
上面的所有配置都是关于后端MySQL节点的 , 现在可以配置关于SQL语句的 , 包括 : 发送SQL语句的用户、SQL语句的路由规则、SQL查询的缓存、SQL语句的重写等等。
本小节是SQL请求所使用的用户配置 , 例如root用户。这要求我们需要先在后端MySQL节点添加好相关用户。这里以root和sqlsender两个用户名为例。
首先 , 在master节点上执行 : (只需master执行即可 , 会复制给两个slave)
grant all on *.* to root@'192.168.71.%' identified by 'P@ssword1!';
grant all on *.* to sqlsender@'192.168.71.%' identified by 'P@ssword1!';然后回到ProxySQL , 配置mysql_users表 , 将刚才的两个用户添加到该表中。
insert into mysql_users(username,password,default_hostgroup) values('root','P@ssword1!',10);
insert into mysql_users(username,password,default_hostgroup) values('sqlsender','P@ssword1!',10);
load mysql users to runtime;
save mysql users to disk;mysql_users表有不少字段 , 最主要的三个字段为username、password和default_hostgroup :
username: 前端连接ProxySQL, 以及ProxySQL将SQL语句路由给MySQL所使用的用户名。password: 用户名对应的密码。可以是明文密码 , 也可以是hash密码。如果想使用hash密码 , 可以先在某个MySQL节点上执行select password(PASSWORD), 然后将加密结果复制到该字段。default_hostgroup: 该用户名默认的路由目标。例如 , 指定root用户的该字段值为10时 , 则使用root用户发送的SQL语句默认情况下将路由到hostgroup_id=10组中的某个节点。
mysql> select * from mysql_users\G
*************************** 1. row ***************************
username: root
password: P@ssword1!
active: 1 # 注意本行
use_ssl: 0
default_hostgroup: 10
default_schema: NULL
schema_locked: 0
transaction_persistent: 1 # 注意本行
fast_forward: 0
backend: 1
frontend: 1
max_connections: 10000
attributes:
comment:
*************************** 2. row ***************************
username: sqlsender
password: P@ssword1!
active: 1
use_ssl: 0
default_hostgroup: 10
default_schema: NULL
schema_locked: 0
transaction_persistent: 1
fast_forward: 0
backend: 1
frontend: 1
max_connections: 10000
attributes:
comment:
2 rows in set (0.00 sec)active=1的用户才是有效的用户。
transaction_persistent字段 , 当它的值为1时 , 表示事务持久化 : 当某连接使用该用户开启了一个事务后 , 那么在事务提交/回滚之前 , 所有的语句都路由到同一个组中 , 避免语句分散到不同组(更进一步的 , 它会自动禁用multiplexing , 让同一个事务的语句从同一个连接路由出去 , 保证路由到同一个组的同一个节点)。在以前的版本中 , 默认值为0 , 不知道从哪个版本开始 , 它的默认值为1。我们期望的值为1 , 所以在继续下面的步骤之前 , 先查看下这个值 , 如果为0 , 则执行下面的语句修改为1。
update mysql_users set transaction_persistent=1 where username='root';
update mysql_users set transaction_persistent=1 where username='sqlsender';
load mysql users to runtime;
save mysql users to disk;然后 , 另开一个终端 , 分别使用root用户和sqlsender用户测试下它们是否能路由到默认的hostgroup_id=10(它是一个写组)读、写数据。
root@ubuntu2204:~# mysql -uroot -pP@ssword1! -P6033 -h127.0.0.1 -e "select @@server_id"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
| 7 |
+-------------+
root@ubuntu2204:~# mysql -uroot -pP@ssword1! -P6033 -h127.0.0.1 -e "create database proxy_test"
mysql: [Warning] Using a password on the command line interface can be insecure.
root@ubuntu2204:~# mysql -uroot -pP@ssword1! -P6033 -h127.0.0.1 -e "show databases;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| proxy_test |
| sys |
| test |
+--------------------+
root@ubuntu2204:~# mysql -usqlsender -pP@ssword1! -P6033 -h127.0.0.1 -e '\
use proxy_test;\
create table t(id int);'
mysql: [Warning] Using a password on the command line interface can be insecure.
root@ubuntu2204:~# mysql -usqlsender -pP@ssword1! -P6033 -h127.0.0.1 -e 'show tables from proxy_test;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------------------+
| Tables_in_proxy_test |
+----------------------+
| t |
+----------------------+3.5 读写分离: 配置路由规则
ProxySQL的路由规则非常灵活 , 可以基于用户、基于schema以及基于每个语句实现路由规则的定制。
这里实现一个最简单的语句级路由规则 , 从而实现读写分离。必须注意 , 这只是实验 , 实际的路由规则绝不应该仅根据所谓的读、写操作进行分离 , 而是从各项指标中找出压力大、执行频繁的语句单独写规则、做缓存等等。
和查询规则有关的表有两个：mysql_query_rules和mysql_query_rules_fast_routing , 后者是前者的扩展表 , 1.4.7之后才支持该快速路由表。本文只介绍第一个表。
插入两个规则 , 目的是将select语句分离到hostgroup_id=20的读组 , 但由于select语句中有一个特殊语句SELECT...FOR UPDATE它会申请写锁 , 所以应该路由到hostgroup_id=10的写组。
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)
VALUES (1,1,'^SELECT.*FOR UPDATE$',10,1),
(2,1,'^SELECT',20,1);
load mysql query rules to runtime;
save mysql query rules to disk;select ... for update规则的rule_id必须要小于普通的select规则的rule_id , 因为ProxySQL是根据rule_id的顺序进行规则匹配的。
再来测试下 , 读操作是否路由给了hostgroup_id=20的读组。
root@ubuntu2204:~# mysql -uroot -pP@ssword1! -P6033 -h127.0.0.1 -e 'select @@server_id'
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
| 11 |
+-------------+
root@ubuntu2204:~# mysql -uroot -pP@ssword1! -P6033 -h127.0.0.1 -e 'select @@server_id'
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
| 29 |
+-------------+
读操作已经路由给读组 , 再看看写操作。这里以事务持久化进行测试。
root@ubuntu2204:~# mysql -uroot -pP@ssword1! -P6033 -h127.0.0.1 -e '\
start transaction;\
select @@server_id;\
commit;\
select @@server_id;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
| 7 |
+-------------+
+-------------+
| @@server_id |
+-------------+
| 29 |
+-------------+显然 , 一切都按照预期进行。
最后 , 如果想查看路由的信息 , 可查询stats库中的stats_mysql_query_digest表。以下是该表的一个输出格式示例(和本文无关)。
mysql> SELECT hostgroup hg,
sum_time,
count_star,
digest_text
FROM stats_mysql_query_digest
ORDER BY sum_time DESC;
+----+----------+------------+----------------------------------+
| hg | sum_time | count_star | digest_text |
+----+----------+------------+----------------------------------+
| 10 | 37109 | 1 | create table t(id int) |
| 10 | 26963 | 1 | create database proxy_test |
| 20 | 7463 | 3 | select @@server_id |
| 10 | 2710 | 1 | start transaction |
| 10 | 2489 | 1 | show tables from proxy_test |
| 10 | 2487 | 2 | select @@server_id |
| 10 | 2240 | 1 | show databases |
| 10 | 2188 | 1 | SELECT DATABASE() |
| 10 | 497 | 1 | commit |
| 10 | 0 | 2 | select @@version_comment limit ? |
| 10 | 0 | 6 | select @@version_comment limit ? |
+----+----------+------------+----------------------------------+
11 rows in set (0.01 sec)参考链接