ProxySQL 是一个高性能、高可用的 MySQL 代理软件,旨在提升 MySQL 数据库的可扩展性和性能。它可以在应用程序和 MySQL 服务器之间充当中间层,提供强大的路由、负载均衡和查询优化功能。
ProxySQL 的主要功能:
查询路由:
- ProxySQL 可以根据用户定义的规则,将 SQL 查询路由到不同的 MySQL 服务器或集群。例如,可以将写操作路由到主服务器,将读操作路由到从服务器,从而实现读写分离。
负载均衡:
- ProxySQL 可以在多个 MySQL 实例之间分配查询流量,实现负载均衡。它支持多种负载均衡策略,如轮询、最少连接等。
连接池:
- ProxySQL 提供了高级连接池功能,通过复用 MySQL 连接来减少 MySQL 服务器的连接开销。它还能保持客户端和 MySQL 服务器之间的持久连接,提高整体性能。
查询缓存:
- ProxySQL 内置了查询缓存,可以缓存常见的查询结果,从而减少数据库负载和响应时间。
自动故障切换:
- 当检测到 MySQL 实例故障时,ProxySQL 能自动将流量切换到其他可用的实例,确保高可用性。
监控和统计:
- ProxySQL 提供了丰富的监控和统计信息,帮助用户分析查询性能和流量分布,并支持动态调整配置而无需重启服务。
SSL/TLS 支持:
- ProxySQL 支持 SSL/TLS 加密,确保客户端和 MySQL 服务器之间的数据传输安全。
ProxySQL 的应用场景:
- 读写分离:通过将写操作路由到主库,读操作路由到从库,减少主库压力,提高读操作性能。
- 高可用性架构:在主从复制、MHA、Galera Cluster 等高可用架构中作为中间层,提高整体系统的容错能力。
- 跨数据中心部署:在多数据中心或多区域环境下,优化数据库流量分配,减少跨地域的延迟。
- 查询优化:通过查询规则重写和查询缓存,提高复杂查询的执行效率。
环境准备
主机名 | IP地址 | 系统版本 | 安装应用 |
proxy | 192.168.226.10 | rocky_linux-9.4mini | ProxySQL version 2.6.3 |
master | 192.168.226.11 | rocky_linux-9.4mini | MySQL 8.0.39 |
slave | 192.168.226.12 | rocky_linux-9.4mini | MySQL 8.0.39 |
准备三台虚拟机,关闭防火墙和selinux,进行时间同步。
curl -s https://raw.gitcode.com/Lzcsfg/Shell_Script/raw/main/rocky-linux/rocky-mini9.4.sh | sed 's/\r$//' > rocky-mini9.4.sh && sh rocky-mini9.4.sh
安装mysql8.0 (该脚本会先清楚mysql环境再安装,仅适合测试环境)
curl -s https://raw.gitcode.com/Lzcsfg/Shell_Script/raw/main/mysql/mysql8-rocky9.4.sh | sed 's/\r$//' > mysql8-rocky9.4.sh && sh mysql8-rocky9.4.sh
安装proxysql
官方网站:https://proxysql.com/documentation/installing-proxysql/
cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name=ProxySQL 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
dnf install -y proxysql
systemctl enable proxysql
systemctl start proxysql
[root@proxy ~]# ss -tnlp
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 128 0.0.0.0:6032 0.0.0.0:* users:(("proxysql",pid=20315,fd=29))
LISTEN 0 1024 0.0.0.0:6033 0.0.0.0:* users:(("proxysql",pid=20315,fd=25))
LISTEN 0 1024 0.0.0.0:6033 0.0.0.0:* users:(("proxysql",pid=20315,fd=24))
LISTEN 0 1024 0.0.0.0:6033 0.0.0.0:* users:(("proxysql",pid=20315,fd=23))
LISTEN 0 1024 0.0.0.0:6033 0.0.0.0:* users:(("proxysql",pid=20315,fd=22))
LISTEN 0 128 0.0.0.0:22 0.0.0.0:* users:(("sshd",pid=12917,fd=3))
LISTEN 0 70 *:33060 *:* users:(("mysqld",pid=22755,fd=21))
LISTEN 0 128 [::]:22 [::]:* users:(("sshd",pid=12917,fd=4))
LISTEN 0 151 *:3306 *:* users:(("mysqld",pid=22755,fd=24))
- 管理操作:使用
6032
端口,通过 ProxySQL 的admin
用户连接,执行管理和监控相关的操作。 - 应用程序操作:使用
6033
端口,通过应用程序用户连接,测试实际的读写分离功能。
配置MySQL主从复制
对master主机操作
[root@master ~]# vim /etc/my.cnf.d/mysql-server.cnf # 在该配置文件最后追加下述配置
server-id = 1 # 服务器唯一标识,每个服务器在复制环境中应有唯一的ID,用于标识不同的复制实例log-bin = mysql-bin # 启用二进制日志,指定二进制日志文件的基名,MySQL会在此基名后添加数字和扩展名来创建日志文件binlog-format = ROW # 设置二进制日志格式为ROW,记录每一行数据的变化,有助于减少数据不一致的风险,也便于从库的并行复制
重启MySQL
systemctl restart mysqld
以下需要登录mysql执行sql语句
登录mysql创建远程用户
-- 创建主从复制用户
CREATE USER 'replica'@'%' IDENTIFIED WITH mysql_native_password BY 'admin';
GRANT ALL PRIVILEGES ON *.* TO 'replica'@'%'; -- 创建ProxySQL监控用户
CREATE USER 'monitor'@'%' IDENTIFIED WITH mysql_native_password BY 'monitor';
GRANT PROCESS, REPLICATION CLIENT, SHOW DATABASES ON *.* TO 'monitor'@'%';
-- FLUSH PRIVILEGES;(这一行是可选的,因为GRANT已经自动更新了权限)
查看主库上的binlog日志
mysql> show master status; # 显示主服务器状态,包括二进制日志文件名和位置
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 663 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
对slave主机操作
[root@slave ~]# vim /etc/my.cnf.d/mysql-server.cnf # 在该配置文件最后追加下述配置
# 从库的唯一标识,与主库和其他从库不同server-id = 2# 设置二进制日志格式为ROW,有助于减少数据不一致的风险binlog-format = ROW# 设置从库为只读模式,防止在从库上直接写入数据导致的数据不一致read-only = 1
重启MySQL
systemctl restart mysqld
以下需要登录mysql执行sql语句
登录mysql创建远程用户
-- 创建主从复制用户
CREATE USER 'replica'@'%' IDENTIFIED WITH mysql_native_password BY 'admin';
GRANT ALL PRIVILEGES ON *.* TO 'replica'@'%'; -- 创建ProxySQL监控用户
CREATE USER 'monitor'@'%' IDENTIFIED WITH mysql_native_password BY 'monitor';
GRANT PROCESS, REPLICATION CLIENT, SHOW DATABASES ON *.* TO 'monitor'@'%';
-- FLUSH PRIVILEGES; (这一行是可选的,因为GRANT已经自动更新了权限)
配置从库以连接到主库
CHANGE REPLICATION SOURCE TOSOURCE_HOST='192.168.226.11',SOURCE_USER='replica',SOURCE_PASSWORD='admin',SOURCE_LOG_FILE='mysql-bin.000001',SOURCE_LOG_POS=663;
启动复制进程
START REPLICA;
查看复制状态
SHOW REPLICA STATUS\G;
配置 ProxySQL
1. 登录到 ProxySQL 管理界面
[root@proxy ~]# mysql -u admin -padmin -h 127.0.0.1 -P 6032 --prompt='Admin> '
2. 在 ProxySQL 中配置 MySQL 服务器
-- 添加主服务器(读写)
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight) VALUES (0, '192.168.226.11', 3306, 100);-- 添加从服务器(只读)
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight) VALUES (1, '192.168.226.12', 3306, 100);
主服务器(读写):hostgroup_id 是 0,表示主服务器组。
hostname 是 192.168.226.11,这是主服务器的 IP 地址。
port 是 3306,这是 MySQL 的默认端口。
weight 是 100,表示该服务器的权重。权重值用于负载均衡,值越大,分配到它的流量越多。从服务器(只读):hostgroup_id 是 1,表示从服务器组。
hostname 是 192.168.226.12,这是从服务器的 IP 地址。
port 和 weight 同主服务器的设置。
3. 配置 MySQL 用户
-- 添加 ProxySQL 用于连接 MySQL 服务器的用户
INSERT INTO mysql_users (username, password, default_hostgroup) VALUES ('replica', 'admin', 0);-- 添加监控 monitor 用户的密码
INSERT INTO mysql_users (username, password, default_hostgroup) VALUES ('monitor', 'monitor', 0);
username 是 replica,用于连接 MySQL 服务器的用户名。
password 是 admin,这是用户的密码。
default_hostgroup 是 0,表示默认使用主服务器组。对于只读查询,代理将会路由到从服务器组,具体取决于路由规则。
4. 配置读写分离的路由规则
-- 配置 SELECT 语句到从服务器
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES (1, 1, '^SELECT', 1, 1);-- 配置 INSERT, UPDATE, DELETE 语句到主服务器
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES (2, 1, '^INSERT|^UPDATE|^DELETE', 0, 1);
SELECT 语句路由规则:rule_id 是 1,这是规则的唯一标识符。
active 是 1,表示规则是激活的。
match_pattern 是 '^SELECT',正则表达式,用于匹配以 SELECT 开头的 SQL 查询。
destination_hostgroup 是 1,表示查询将路由到从服务器组。
apply 是 1,表示规则将应用于查询。INSERT, UPDATE, DELETE 语句路由规则:rule_id 是 2。
match_pattern 是 '^INSERT|^UPDATE|^DELETE',正则表达式,用于匹配 INSERT、UPDATE 或 DELETE 操作。
destination_hostgroup 是 0,表示这些操作将路由到主服务器组。
apply 是 1,表示规则将应用于查询。
5. 应用更改
-- 重新加载 ProxySQL 配置
LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL USERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;-- 保存配置到磁盘
SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL USERS TO DISK;
SAVE MYSQL QUERY RULES TO DISK;
LOAD MYSQL SERVERS TO RUNTIME:将服务器配置加载到运行时配置中。
LOAD MYSQL USERS TO RUNTIME:将用户配置加载到运行时配置中。
LOAD MYSQL QUERY RULES TO RUNTIME:将查询规则加载到运行时配置中。
SAVE MYSQL SERVERS TO DISK:将服务器配置保存到磁盘,以便重启后仍然有效。
SAVE MYSQL USERS TO DISK:将用户配置保存到磁盘。
SAVE MYSQL QUERY RULES TO DISK:将查询规则保存到磁盘。
这些操作确保 ProxySQL 配置在运行时被应用,并且在重新启动 ProxySQL 后仍然有效。
-- 查看存储的服务器配置
SELECT * FROM mysql_servers;-- 查看存储的用户配置
SELECT * FROM mysql_users;-- 查看存储的查询规则
SELECT * FROM mysql_query_rules;-- 查看 ProxySQL 服务器状态
SELECT * FROM stats_mysql_connection_pool;
配置文件存储位置:
[root@proxy ~]# grep -E 'datadir|admin_variables' /etc/proxysql.cnf
# On startup, ProxySQL reads its config file (if present) to determine its datadir.
# datadir (i.e. "/var/lib/proxysql/proxysql.db").
datadir="/var/lib/proxysql"
admin_variables=
[root@proxy ~]# ls -l /var/lib/proxysql
total 428
-rw-r--r-- 1 root root 1082 Aug 23 10:15 proxysql-ca.pem
-rw-r--r-- 1 root root 1086 Aug 23 10:15 proxysql-cert.pem
-rw-r--r-- 1 root root 1675 Aug 23 10:15 proxysql-key.pem
-rw-r--r-- 1 root root 225280 Aug 23 11:29 proxysql.db
-rw-r----- 1 root root 15710 Aug 23 11:37 proxysql.log
-rw-r--r-- 1 root root 184320 Aug 23 11:37 proxysql_stats.db
下载SQLite 工具(选做)
dnf install -y sqlite
使用 sqlite3
工具打开数据库文件
-
打开 SQLite 数据库:
使用 SQLite 工具打开
proxysql.db
文件:sqlite3 /var/lib/proxysql/proxysql.db
-
查看数据库中的表格:
在 SQLite 提示符下,列出所有表格:
.tables
-
查询表格中的数据:
例如,要查看
mysql_servers
表中的所有数据,可以执行:SELECT * FROM mysql_servers;
类似地,你可以查看其他表格,比如
mysql_users
或mysql_query_rules
,以获取你在 ProxySQL 中配置的数据:SELECT * FROM mysql_users; SELECT * FROM mysql_query_rules;
-
退出 SQLite 提示符:
使用以下命令退出 SQLite 提示符:
.quit
查看 proxysql.log
文件
proxysql.log
文件记录了 ProxySQL 的运行日志。你可以使用 cat
、less
或 tail
命令来查看其内容。
查看 proxysql_stats.db
文件
类似于 proxysql.db
文件,proxysql_stats.db
也是一个 SQLite 数据库文件。你可以用相同的方式查看其内容:
sqlite3 /var/lib/proxysql/proxysql_stats.db
在 SQLite 提示符下,列出所有表格和查询数据的方法与 proxysql.db
相同。
总结
- 查看
proxysql.db
:使用sqlite3
工具来查询配置和状态数据。 - 查看
proxysql.log
:使用cat
、less
或tail
命令来查看日志文件。 - 查看
proxysql_stats.db
:使用sqlite3
工具来查询统计数据。
测试 ProxySQL 配置
测试一 :
在测试读写分离的功能时,应使用你配置的应用程序用户(如 replica
用户),并连接到 ProxySQL 的客户端接口(默认端口 6033)。这个用户应该是应用程序使用的用户,用于执行实际的读写操作,从而验证 ProxySQL 的路由规则是否正确生效。
[root@proxy ~]# mysql -u replica -padmin -h 127.0.0.1 -P 6033
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 11
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>
这样就可以执行sql语句了
首先在ProxySQL这里执行写操作,然后去master和slave查看验证数据同步,正确情况都是同步了的。
CREATE DATABASE test_db;
USE test_db;CREATE TABLE test_table (id INT PRIMARY KEY,data VARCHAR(255)
);
-- 执行写操作
INSERT INTO test_table (id, data) VALUES (1, 'test data');
测试二 :
现在去slave主机中的mysql操作一个写操作,验证slave中的sql语句不会同步到master里
-- 插入测试数据到 test_table 表
INSERT INTO test_table (id, data) VALUES (2, 'test data');
现在去proxysql主机和master中查看,正确情况是proxysql中能查询到slave插入的数据,而master的数据库中查不到。
测试三 :
模拟master宕机,将master的MySQL服务关闭,然后进行读写验证
正确情况下是可以执行读操作的,但是写操作就会提示连接不上而失败。
然后将master的MySQL恢复,执行插入操作就会立即成功了。