首页 > 数据库 >ProxySQL 读写分离配置

ProxySQL 读写分离配置

时间:2024-08-25 16:24:20浏览次数:13  
标签:0.0 -- ProxySQL 读写 分离 proxysql mysql 服务器

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 TO
    SOURCE_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 命令来查看其内容。

  • 查看最新的日志条目

    tail -f /var/lib/proxysql/proxysql.log

查看 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恢复,执行插入操作就会立即成功了。

标签:0.0,--,ProxySQL,读写,分离,proxysql,mysql,服务器
From: https://blog.csdn.net/Lzcsfg/article/details/141448922

相关文章

  • Java毕业设计作品(113):基于thymeleaf前后端分离 棋牌室会员预约管理系统设计与实现
      博主介绍:黄菊华老师《Vue.js入门与商城开发实战》《微信小程序商城开发》图书作者,CSDN博客专家,在线教育专家,CSDN钻石讲师;专注大学生毕业设计教育和辅导。所有项目都配有从入门到精通的基础知识视频课程,学习后应对毕业设计答辩。项目配有对应开发文档、开题报告、任务书......
  • C#串口读写,遇到数据粘连怎么处理
    在读取串口数据时,可能会出现"粘连"的情况,即一次读取的数据中包含了多个数据包。这通常是由于数据的传输速度和读取速度不匹配导致的。解决这种情况的常见方法有以下几种:一、使用缓冲区可以使用一个缓冲区来暂时存储读取的数据,并对缓冲区中的数据进行分析,找出完整的......
  • C#串口读写,如果是按位读取,遇到有结束符号“0D0A”16进制终止符号的时候,怎么处理,并且保
    对于串口数据存在以0D0A(回车换行)作为结束符号的情况,可以按照以下步骤设计相应的处理逻辑:一、定义数据包结构首先定义一个数据包结构,包含数据包长度和实际数据内容两个部分。示例代码:publicstructSerialPacket{publicintLength;publicbyte[]Data;......
  • Java毕业设计作品(119):基于thymeleaf前后端分离 驾校会员报名和管理系统设计与实现
      博主介绍:黄菊华老师《Vue.js入门与商城开发实战》《微信小程序商城开发》图书作者,CSDN博客专家,在线教育专家,CSDN钻石讲师;专注大学生毕业设计教育和辅导。所有项目都配有从入门到精通的基础知识视频课程,学习后应对毕业设计答辩。项目配有对应开发文档、开题报告、任务书......
  • 达梦读写分离集群的配置与测试
    操作系统版本CentOS7.6数据库版本DM8_20240712数据守护集群版本V4.0目录1.环境准备2.dm_svc.conf配置3.实现原理4.测试1.环境准备        配置读写分离集群,需要先搭建即时归档或实时归档模式的主备集群。我们使用上篇文档《在一台主机上搭建达梦主备集群......
  • Java毕业设计作品(104):基于thymeleaf前后端分离 校园学校社团网站系统设计与实现
      博主介绍:黄菊华老师《Vue.js入门与商城开发实战》《微信小程序商城开发》图书作者,CSDN博客专家,在线教育专家,CSDN钻石讲师;专注大学生毕业设计教育和辅导。所有项目都配有从入门到精通的基础知识视频课程,学习后应对毕业设计答辩。项目配有对应开发文档、开题报告、任务书......
  • 双主双从读写分离
    目录介绍准备搭建主库配置从库配置两台主库相互复制 双主双从测试双主双从读写分离schema.xml配置server.xml配置测试介绍一个主机Master1用于处理所有写请求,它的从机Slave1和另一台主机Master2还有它的从机Slave2负责所有读请求。当Master1主机宕......
  • 前后端分离Nginx配置
    #usernobody;worker_processesauto;events{worker_connections1024;}http{includemime.types;default_typeapplication/octet-stream;#log_formatmain'$remote_addr-$remote_user[$time_local]"$request"......
  • JS 读写剪贴板
    document.execCommandAPI复制操作复制时,先选中文本,然后调用document.execCommand('copy'):constinputElement=document.querySelector("#input");inputElement.select();document.execCommand("copy");注意:复制操作最好由用户触发,如果脚本自主执行,可能某些浏览器会报......
  • Java毕业设计作品(98):基于thymeleaf前后端分离 体育赛事购票系统设计与实现
      博主介绍:黄菊华老师《Vue.js入门与商城开发实战》《微信小程序商城开发》图书作者,CSDN博客专家,在线教育专家,CSDN钻石讲师;专注大学生毕业设计教育和辅导。所有项目都配有从入门到精通的基础知识视频课程,学习后应对毕业设计答辩。项目配有对应开发文档、开题报告、任务书......