使用ProxySql实现MySQL的读写分离

ProxySQL简介

1、ProxySQL是一款开源的使用C++编写的MySQL集群代理中间件；

2、用于在MySQL数据库和客户端之间进行负载均衡、查询缓存、故障转移和查询分发；

3、它可以作为中间层插入到应用程序和数据库之间；

4、特点是高效灵活，使用简单，并且性能是所有中间件中比较优秀的。

5、在搭建好mysql的主从复制后，可以利用ProxySQL实现mysql数据库的读写分离；

主要功能

ProxySQL主要支持MySQL的主从集群和组复制集群，其主要功能有以下几个：

1、代理服务：代理后端MySQL服务，进行相关指标的监控

2、负载均衡：后端多节点的访问进行负载均衡

3、高可用：自动识别异常节点，并屏蔽异常节点，保障集群的稳定

4、读写分离：自动动态的识别读写节点，转发SQL至对应节点执行

5、数据分片：通过路由规则，进行SQL分发，达到数据分片的目的

使用原理

ProxySQL兼容MySQL协议，可以像连接MySQL一样连接ProxySQL，执行简单的SQL语句。

ProxySQL本身相关信息数据的存储使用的是sqlite3，部分MySQL语句功能没有完全兼容，所以通过 mysql client 可以正常连接并使用ProxySQL，如果通过dbeaver或者navicat等工具则无法正常使用。

当Proxysql启动后，将监听两个端口：

admin管理接口，默认端口为6032。该端口用于查看、配置ProxySQL
接收SQL语句的接口，默认端口为6033，这个接口类似于MySQL的3306端口

实验：使用ProxySql实现MySQL的读写分离

1、读写分离，简单地说是把对数据库的读和写操作分开，以对应不同的数据库服务器;

2、主数据库提供写操作，从数据库提供读操作，这样能有效地减轻单台数据库的压力;

3、基于mysql主从复制，实现读写分离前需要先配置好主从复制。

IP	角色	软件
192.168.8.100	Master	MySQL,ProxySQL
192.168.8.101	Slave	MySQL

安装proxysql

CentOS7安装proxysql

# 配置官方yum源,或直接下载rpm包（下载地址：https://github.com/sysown/proxysql/releases）
cat > /etc/yum.repos.d/proxysql.repo << EOF
[proxysql]
name=ProxySQL YUM repository
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/centos/\$releasever
gpgcheck=1
gpgkey=https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/repo_pub_key
EOF

#yum安装proxysql
yum makecache
yum install proxysql

Ubuntu22.04安装proxysql

# ubuntu下添加源：

apt install -y --no-install-recommends lsb-release wget apt-transport-https ca-certificates gnupg

wget -O - 'https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/repo_pub_key' | apt-key add - 
echo deb https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/$(lsb_release -sc)/ ./ | tee /etc/apt/sources.list.d/proxysql.list

wget -nv -O /etc/apt/trusted.gpg.d/proxysql-2.5.x-keyring.gpg 'https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/repo_pub_key.gpg'

# 命令下载装：
apt-get update
apt-get install proxysql

启动proxysql服务并加入开机自启

systemctl start proxysql
systemctl enable proxysql

通过管理界面配置ProxySQL

使用mysql客户端，并使用以下admin凭据在本地端口（6032）上进行连接。默认账号密码admin:admin

mysql -uadmin -padmin -h127.0.0.1 -P6032

Admin> show databases;

这些库的含义：

main：内存配置数据库。使用此数据库，可以很容易地以自动化方式查询和更新ProxySQL的配置。使用从内存中加载MYSQL用户和类似命令，可以将存储在此处的配置传播到运行时ProxySQL使用的内存数据结构。
disk："main"的基于磁盘的镜像。在重新启动过程中，“ main”不会保留，而是根据启动标志以及磁盘上是否存在数据库从“磁盘”数据库或从配置文件加载。
stats：包含从代理的内部功能收集的运行时指标。指标示例包括每个查询规则匹配的次数，当前正在运行的查询等。
monitor：包含与ProxySQL连接的后端服务器相关的监视指标。度量标准示例包括连接到后端服务器或对其进行ping操作的最小和最大时间。

ProxySQL运行机制

runtime：运行中使用的配置文件
memory：提供用户动态修改配置文件
disk：将修改的配置保存到磁盘SQLit表中（即：proxysql.db）

proxysql

ProxySQL账号

ProxySQL默认的管理员账户和密码为admin:admin，但是这个默认的用户只能在本地使用。如果想要远程连接到ProxySQL，例如用windows上的navicat连接Linux上的ProxySQL管理接口，必须自定义一个管理员账户。

例如：添加一个root:root123的用户和密码

Admin> select @@admin-admin_credentials;
+---------------------------+
|@@admin-admin_credentials  |
+---------------------------+
|admin:admin                |
+---------------------------+
1 row in set (0.001 sec)
Admin> set admin-admin_credentials='admin:admin;root:root123';
Query OK, 1 row affected (0.000 sec)
Admin> select @@admin-admin_credentials;
+---------------------------+
|@@admin-admin_credentials  |
+---------------------------+
| admin:admin;root:root123  |
+---------------------------+
1 row in set (0.001 sec)
Admin> load admin variables to runtime; # 使修改立即生效
Query OK, 0 rows affected (0.000 sec)
Admin> save admin variables to disk;  # 使修改永久保存到磁盘
Query OK, 35 rows affected (0.004 sec)

配置内容

使用proxysql，主要需要完成以下几项内容的配置：

1、配置监控账号。监控账号用于检测后端mysql实例是否健康（是否能连接、复制是否正常、复制是否有延迟等）;

2、到后端mysql实例创建监控账号;

3、配置后端mysql实例连接信息。实例连接信息存储在mysql_servers表;

4、配置连接proxysql和后端实例的账号。账号信息存储在mysql_users表;

5、配置查询路由信息。路由信息存储在mysql_query_rules表;

6、配置后端mysql集群信息。根据后端mysql集群架构，配置分别存储在mysql_replication_hostgroups、mysql_group_replication_hostgroups、runtime_mysql_galera_hostgroups、runtime_mysql_aws_aurora_hostgroups等表中;

7、根据具体需要，调优相关参数。参数存储在global_variables表;

1.在主节点的MySQL中创建监控用户

CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor';
GRANT USAGE, REPLICATION CLIENT ON *.* TO 'monitor'@'%';

2.将监控用户加入到proxysql中

UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_password';

3.将Master和slave节点添加到mysql_servers表中

注意：是在proxysql中操作

-- Master主节点
INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight,comment) VALUES (1,'192.168.8.100',3306,1,'Write group');

-- slave节点
INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight,comment) VALUES (2,'192.168.8.101',3306,1,'Read group');

-- 保存
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

4.查看添加的mysql集群

SELECT * FROM mysql_servers;

5.在MySQL主从节点上创建用户，赋予权限，并将其存储到代理服务器中。

-- 在Master节点和slave节点上创建adm用户,设置为管理员权限。
CREATE USER 'adm'@'%' IDENTIFIED BY '123456';
GRANT ALL PRIVILEGES ON *.* TO 'adm'@'%';

-- 在Master节点和slave节点上创建用户read,并设置权限为只读。
CREATE USER 'read'@'%' IDENTIFIED BY '123456';
GRANT SELECT ON *.* TO 'read'@'%';
FLUSH PRIVILEGES;

6.在proxysql上添加用户

-- 在proxysql上添加用户
INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('adm','123456',1);
INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('read','123456',2);

-- 保存
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

select hostgroup_id, hostname, port,status from runtime_mysql_servers;

7.在proxysql上配置读写规则

参数介绍：

rule_id为1，表示规则的唯一标识符。
active为1，表示规则处于激活状态。
match_digest为'^SELECT.*FROM UPDATE$'，表示匹配查询语句，以SELECT开头，中间可以有任意字符，以FROM UPDATE结尾。
destination_hostgroup为1，表示匹配成功后，将查询请求发送到hostgroup 1。
apply为1，表示应用该规则。

INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) VALUES (1,1,'^SELECT.*FROM UPDATE$',1,1);
INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) VALUES (2,1,'^SELECT',2,1);
INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) VALUES (3,1,'^SHOW',2,1);
-- 保存规则
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
-- 查看规则
select rule_id,match_digest,destination_hostgroup,apply from mysql_query_rules order by rule_id;

8.测试能否正常读取

直接在ProxySQL所在服务器的Linux命令行执行下列命令

mysql -uread -p123456 -h 127.0.0.1 -P6033 -e "SELECT @@hostname,@@port"

# 在代理服务器上执行读操作
mysql -uread -p123456 -h 127.0.0.1 -P6033 -e "show databases;"

# 执行创建操作，测试效果
mysql -uadm -p123456 -h 127.0.0.1 -P6033 -e "create database test2;"

# 再次查询
mysql -uread -p123456 -h 127.0.0.1 -P6033 -e "show databases;"

在代理服务器中查询执行记录

# 可以看到proxysql将不同的请求分别发送给了不同的服务器处理，读取发送给slave节点，写入和删除发送给Master节点。
-- 在proxysql服务器上执行
mysql -uadmin -padmin -h127.0.0.1 -P6032
select hostgroup,digest_text from stats_mysql_query_digest\G;

## ProxySQL保存配置：

-- 修改后重新加载服务并保存配置，防止重启服务器后配置丢失。

-- 重新加载并保存服务器设置
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

-- 重新加载并保存查询设置
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

-- 重新加载并保存用户设置
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

-- 重新加载并保存变量设置
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

重置配置方法

如果你遇到 ProxySQL 配置错误，需要删除或重置配置，通常可以按照以下步骤操作：

1. 删除错误配置

1.连接到 ProxySQL 数据库：

使用 mysql 命令行工具或其他 MySQL 客户端连接到 ProxySQL 实例。默认情况下，ProxySQL 的管理界面监听在 3306 端口（也可能是其他端口，取决于你的配置）。

mysql -u admin -p -h 127.0.0.1 -P 6032

这里 6032 是 ProxySQL 默认的管理端口，admin 是默认的管理用户。

2.查看当前配置：在管理控制台中，你可以使用以下命令查看当前配置：

SELECT * FROM mysql_servers;
SELECT * FROM mysql_users;

3.删除或修改错误配置：假设你要删除某个错误的服务器配置：

DELETE FROM mysql_servers WHERE hostname='错误的主机名';

对于用户配置，可以用类似的方式删除：

DELETE FROM mysql_users WHERE username='错误的用户名';

4.应用更改：删除或修改配置后，记得应用更改到 ProxySQL 配置：

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

对于用户配置：

LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

2. 重置 ProxySQL 配置

方法一：可以执行初始化命令：

proxysql --initial

方法二：也可以删除所有配置并重新加载：

1.删除所有配置：

-- 删除所有的 MySQL 服务器配置
DELETE FROM mysql_servers;

-- 删除所有的 MySQL 用户配置
DELETE FROM mysql_users;

-- 删除所有的 MySQL 规则配置
DELETE FROM mysql_query_rules;

-- 删除所有的 MySQL 监控配置
DELETE FROM stats_mysql_status;

2.重新加载配置：

-- 将删除的配置应用到运行时
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;

## 退出重启服务
sudo systemctl restart proxysql

3. 其他检查

确保 ProxySQL 配置文件（通常是 proxysql.cnf）中没有错误，并检查任何可能的日志文件来确认问题的具体细节。

这些步骤应该能帮助你解决 ProxySQL 配置错误。如果问题依然存在，可能需要更详细地检查 ProxySQL 的日志或配置文档。

Z笔记

左老师的课堂笔记