ProxySQL 读写分离配置

devtools/2024/12/22 21:10:00/

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地址系统版本安装应用
proxy192.168.226.10rocky_linux-9.4miniProxySQL version 2.6.3
master192.168.226.11rocky_linux-9.4miniMySQL 8.0.39
slave192.168.226.12rocky_linux-9.4miniMySQL 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 工具打开数据库文件

  1. 打开 SQLite 数据库

    使用 SQLite 工具打开 proxysql.db 文件:

    sqlite3 /var/lib/proxysql/proxysql.db
  2. 查看数据库中的表格

    在 SQLite 提示符下,列出所有表格:

    .tables

  3. 查询表格中的数据

    例如,要查看 mysql_servers 表中的所有数据,可以执行:

    SELECT * FROM mysql_servers;

    类似地,你可以查看其他表格,比如 mysql_usersmysql_query_rules,以获取你在 ProxySQL 中配置的数据:

    SELECT * FROM mysql_users; SELECT * FROM mysql_query_rules;

  4. 退出 SQLite 提示符

    使用以下命令退出 SQLite 提示符:

    .quit

查看 proxysql.log 文件

proxysql.log 文件记录了 ProxySQL 的运行日志。你可以使用 catlesstail 命令来查看其内容。

查看 proxysql_stats.db 文件

类似于 proxysql.db 文件,proxysql_stats.db 也是一个 SQLite 数据库文件。你可以用相同的方式查看其内容:

sqlite3 /var/lib/proxysql/proxysql_stats.db

在 SQLite 提示符下,列出所有表格和查询数据的方法与 proxysql.db 相同。

总结

  • 查看 proxysql.db:使用 sqlite3 工具来查询配置和状态数据。
  • 查看 proxysql.log:使用 catlesstail 命令来查看日志文件。
  • 查看 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恢复,执行插入操作就会立即成功了。


http://www.ppmy.cn/devtools/99352.html

相关文章

《黑神话·悟空》是用什么编程语言开发的?

最近国内出了一款3A大作的游戏&#xff0c;凭借牛b的画面、文化底蕴、剧情&#xff0c;已经吸引了很多人入局&#xff0c;当然小孟也在第一时间尝鲜。 最直接的感受&#xff1a;效果牛b&#xff0c;画面牛b&#xff0c;游戏好玩。但是时间太忙&#xff0c;准备过年的放假的时候…

Linux数据相关第1个服务_备份服务rsync

1、备份服务概述 备份服务&#xff1a;需要使用到脚本&#xff0c;打包备份&#xff0c;定时任务 备份服务&#xff1a;rsyncd 服务&#xff0c;不同主机之间数据传输 特点: rsync是个服务也是命令使用方便&#xff0c;具有多种模式传输数据的时候是增量传输 增量与全量&am…

写一个githubDemo

1.List组件 <template><div class"container"><!-- 展示用户列表 --><div class"row"><divv-show"info.users.length"v-for"(item, index) in info.users":key"item.id"><div class"…

问答泛单页目录站群通用程序——码山侠

功能特性&#xff1a; 内置ASCII码转换功能&#xff1a; 可通过简易开关控制文章中ASCII码的添加。 内置缓存管理功能&#xff1a; 提供程序缓存开关&#xff0c;以优化性能和响应速度。 打开&#xff1a;i5i.net 使用指南&#xff1a;本程序无需依赖数据库&#xff0c;仅需…

[LeetCode]48.旋转图像(python)

1.代码 class Solution:def rotate(self, matrix: List[List[int]]) -> None:"""Do not return anything, modify matrix in-place instead."""n len(matrix)for i in range(n//2):for j in range((n1)//2):temp matrix[i][j]for count in…

SQL,给连续的行加上标识序号

postgresql 数据库的表 tmp 有 2 个分组字段&#xff0c;source_id 和 event_user&#xff0c;将该表按 source_id 分组&#xff0c;组内按 event_date 排序后&#xff0c;event_user 相同的值会形成有序的小组&#xff1a; idsource_idevent_userevent_date11A05-03-201421A0…

RFID光触发标签在多行业的应用与效益差异

在当今数字化和智能化的浪潮下&#xff0c;RFID技术已成为众多行业优化运营、提升竞争力的关键手段。RFID光触发标签作为这一技术的创新成果&#xff0c;以其独特的性能特点&#xff0c;正逐渐在各个领域发挥着重要作用。 一、RFID光触发标签的特点与参数 &#xff08;一&…

spark client mode cluster mode 区别 与选择

1、在我们使用spark-submit 提交spark 任务一般有以下参数 /bin/spark-submit \--class <main-class> \--master <master-url> \--deploy-mode <deploy-mode> \--conf <key><value> \... # other options<application-jar> \[application…