ProxySQL 读写分离

概念：

proxySQL 是一个单独的类似mysql 的数据库管理程序，它的读写分离实现逻辑是：

1. 把主从数据库的信息存在proxySQL中
2. 对主库设置只写， 从库设置只读
3. 在主从库中分别创建一个可以操作的账号 proxySQL（监听账号） 来进行操作
4. 把 proxySQL账号信息存入proxySQL中
5. 在proxySQL中创建读写（分离）匹配规则

PS：

1. 更新后的数据只在运行时有效， 重启后会丢失，所以需要进行持久化
2. 更新配置时在 更新区 --> load to 运行时 --> save to 磁盘

# 加载数据到运行时
LOAD MYSQL VARIABLES TO RUNTIME;
# 进行持久化到磁盘
SAVE MYSQL VARIABLES TO DISK;
# 从磁盘加载数据到运行时
LOAD MYSQL SERVERS TO RUNTIME;

 

环境：

1. ubuntu 24
2. proxysql_3.0.2-ubuntu24_amd64.deb

安装:

在下载时你可能需要配置代理：export https_proxy=http://127.0.0.1:20171

1. wget https://github.com/sysown/proxysql/releases/download/v3.0.2/proxysql_3.0.2-ubuntu24_amd64.deb
2. sudo dpkg -i proxysql_3.0.2-ubuntu24_amd64.deb

# 启动
    systemctl start proxysql
# 查看状态
    systemctl status proxysql
# 关闭
    systemctl stop proxysql

PS:

1. 配置文件位置： /etc/proxysql.cnf，此配置文件只在第一次启动时生效，如果已经生成了数据库文件，则后续所有的配置都是从数据中进行加载。
2. 端口6032 为admin 进行配置
3. 端口6033 为业务端口， 接受SQL 语句

 

部署流程

1、登录admin账户 # prompt 参数是指定命令行title

mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt 'ProxySQL Admin> '

 

2、添加后端

# 添加主机
INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight,comment) VALUES (10,'172.17.0.2',3306,1,'Write Group');

# 添加从机
INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight,comment) VALUES (20,'172.17.0.3',3306,1,'Read Group');
INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight,comment) VALUES (20,'172.17.0.4',3306,1,'Read Group');

# 加载与持久化
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;


# 查看：
SELECT * FROM mysql_servers;

 

4、账号信息保存进proxySQL 中，这也是业务中使用的账号

#插入
INSERT INTO mysql_users(username,password,default_hostgroup,transaction_persistent) VALUES ('tfdata', '123456', 10, 1);

# 更新：
- LOAD MYSQL USERS TO RUNTIME;
- SAVE MYSQL USERS TO DISK;

# 查看：
SELECT * FROM mysql_users;

 

5、添加健康检测账号

监控账号是proxySQL 用来监听主和从库是否宕机的

如果你只需要监听主库， 那么你的从库可以不创建monitor 账号

# 在主机上创建 proxySQL 监听账号
CREATE USER 'monitor'@'%' IDENTIFIED BY '123456';
GRANT SELECT ON *.* TO 'monitor'@'%';
FLUSH PRIVILEGES;

 

6、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';

# 变量赋值
- set mysql-monitor_username='monitor';
- set mysql-monitor_password='123456';

 # 配置监听间隔
 - UPDATE global_variables SET variable_value='2000' WHERE variable_name IN ('mysql-monitor_connect_interval','mysql-monitor_ping_interval');

# 持久化
- LOAD MYSQL VARIABLES TO RUNTIME;
- SAVE MYSQL VARIABLES TO DISK;

 

 # 查看监听用户
 - SELECT * FROM global_variables WHERE variable_name LIKE 'mysql-monitor_%';

 # 查看间隔
 - SELECT * FROM global_variables WHERE variable_name LIKE 'mysql-monitor_%interval%';

 

 # 验证监控状态， 如果有问题在最后一个 *_error 字段会有显示
 - SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 3;
 - SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 3;

 

7、添加读写分离规则

INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES (1, 1, '^SELECT.*FOR UPDATE', 10, 1);
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES (2, 1, '^SELECT', 20, 1);
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES (3, 1, '^SHOW', 20, 1);
            
# 查看
SELECT rule_id, active, match_pattern, destination_hostgroup, apply from mysql_query_rules;
#持久化
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

 

验证

# 如果连接超时， 说明配置有问题或没有加载最新配置：
- LOAD MYSQL SERVERS TO RUNTIME;

# 查看select 语句执行在那台机器
- mysql -u tfdata -p123456 -h 127.0.0.1 -P6033 -Ne "select @@hostname"

# 在proxySQL 中执行
select * from stats_mysql_query_digest \G;