proxysql实现读写分离最佳实践

子林super
153 阅读4分钟

注意：

  1. 以下所有slave均配置成 readonly，也就是说slave只能读！
  2. 6032 管理端口； 6033 代理端口

一、环境准备

1. mysql集群信息

# 使用mysql5.7.27版本

172.21.27.30  master

174.21.27.29

176.21.27.31

2. proxysql的版本选择

wget <https://github.com/sysown/proxysql/releases/download/v2.5.4/proxysql-2.5.4-1-centos7.x86_64.rpm>



二、安装与部署

本次用例使用的是2.5.4， 建议使用新版本proxysql。

1. 安装依赖包

# 
yum install -y gnutls libgnutls

2. 安装proxysql2.5.4

# 
rpm -ivh proxysql-2.5.4-1-centos7.x86_64.rpm

3. 启动proxysql


systemctl enable proxysql
systemctl start proxysql



三、配置与管理

1. 登录proxysql的admin后台


# admin后台
mysql -h127.0.0.1 -P6032 -uadmin -padmin

2. 配置后端mysql服务器（mysql_servers表）

# 
use main


# 写库（主库）HostGroup = 10  INSERT  INTO mysql_servers (hostgroup_id, hostname, port, weight) VALUES ( 10 , '172.21.27.30' , 3306 , 100 );  # 读库（两个从库）HostGroup = 20 , 2 个从库的读力量 70 : 30  INSERT  INTO mysql_servers (hostgroup_id, hostname, port, weight) VALUES ( 20 , '172.21.27.29' , 3306 , 70 ),( 20 , '172.21.27.31' , 3306 , 30 );


# load加载到内存， save保存到磁盘
load mysql servers to runtime;
save mysql servers to disk;

3. 配置用户

# 
INSERT INTO mysql_users (username, password, default_hostgroup, active) VALUES ('root', '123', 1, 1);

# # 4、 设置mysql的监控用户
set mysql-monitor_username='proxyMonitor';
set mysql-monitor_password='123456';
#
load mysql variables to runtime;
save mysql variables to disk;


# mysql中创建 proxyMonitor
GRANT SELECT ON *.* TO 'proxyMonitor'@'%' identified by "123456";

4. 读写分离规则

# 
# 事务中带 FOR UPDATE 的 SELECT 强制走写库
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES (1, 1, '^SELECT.*FOR UPDATE', 10, 1);

# 普通 SELECT 走读库（slave组）
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES (2, 1, '^SELECT .*', 20, 1);


#
load mysql query rules to runtime;
save mysql query rules to disk;

5. 观察proxysql的日志

# 
tailf -n 100 /var/lib/proxysql/proxysql.log

6. 验证读写分离

# 6032 管理端口； 6033 代理端口

# 测试读写分离
mysql -h172.21.27.32 -P6033 -uroot -p123

# 读 
select @@server_id; 

# 写 
select @@server_id;begin;select @@server_id;rollback;

或

# ### 

# 验证写的路由

mysql -h192.168.3.188 -uroot -P6033 -p'Yumchina@123' -e "BEGIN;INSERT one_demo.one VALUE(4,4);SELECT @@server_id;commit;"

  


# 验证写 

mysql -h192.168.3.188 -uroot -P6033 -p'Yumchina@123' -e "BEGIN;INSERT one_demo.one VALUE(5,5);SELECT @@server_id;rollback;"

  


# 验证读

mysql -h192.168.3.188 -uroot -P6033 -p'Yumchina@123' -e "SELECT @@server_id;"



四、读写分离配置

略




五、读写分离验证

 # 读
select @@server_id;

# 写
select @@server_id;begin;select @@server_id;rollback;



六、mysql Failover后的主从自动探测


# 1、 配置服务器
-- 写库（主库）hostgroup_id=10
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight, max_connections) VALUES (10, '172.21.27.30', 3306, 100, 1000);

-- 读库（从库）hostgroup_id=20
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight, max_connections) VALUES   (20, '172.21.27.29', 3306, 100, 1000),  (20, '172.21.27.31', 3306, 100, 1000);
# 

# 2、 配置主从关系（启动自动检测）
-- 指定写组为10，从组为20，启用主从探测
INSERT INTO mysql_replication_hostgroups (writer_hostgroup, reader_hostgroup, check_type, comment) VALUES (10, 20, 'read_only', 'MySQL replication setup');


# 3、 配置proxysql用户
INSERT INTO mysql_users (username, password, default_hostgroup, transaction_persistent, active)  VALUES ('root', '123', 10, 1, 1);


# 4、 配置查询规则，实现读写分离
-- SELECT 查询走读库组（20）
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES (1, 1, '^SELECT .*', 20, 1);


# 5、 应用并保存配置
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

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

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
# 如下可能有问题，与proxysql版本有关
LOAD MYSQL REPlication HOSTGROUPS TO RUNTIME;
SAVE MYSQL REPLICATION HOSTGROUPS TO DISK;



# 查询当前主从检测结果
SELECT * FROM runtime_mysql_servers WHERE hostgroup_id IN (10, 20);


# 模拟故障
systemctl stop mysqld  # 在 172.21.27.30 上执行


# 检查proxysql中当前的master
SELECT * FROM runtime_mysql_servers;

说明：

  1. 当master 挂了，发生提备升主后，proxysql会自动将写的请求路由到新主上
  2. 如果主从切换一次，SELECT * FROM runtime_mysql_servers; 中会更新一条信息。

图片.png

  1. 主从切换一次后日志中也会有对应的日志输出。

图片.png




七、补充信息

1. proxysql信息恢复


delete from mysql_replication_hostgroups where 1=1;
delete from mysql_query_rules where 1=1;
delete from mysql_servers where 1=1;
delete from mysql_users where 1=1;

2. proxysql相关表解释

自身的系统库

mian ：ProxySQL主要的配置库，存在于内从中。

disk：main的持久化库，是sqlite3 的数据库文件。

stats：ProxySQL相关的统计数据，存在于内从中。

stats_history：ProxySQL和系统 相关的历史统计信息。

monitor：相关的状态监控信息。

  1. mysql_servers表

  2. mysql_replication_hostgroups表

  3. mysql_users表

  4. mysql_query_rules表

3. proxysql架构

4. 用户连接池配置


SELECT username, max_connections FROM mysql_users WHERE username = 'root';
# 设置最大连接数
UPDATE mysql_users SET max_connections = 50 WHERE username = 'user1';
UPDATE mysql_users SET max_connections = 100 WHERE username = 'user2';

# 加载与保存
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

5. 延迟检测

略



八、文档参考

www.cnblogs.com/keme/p/1229…

www.proxysql.com/

cloud.tencent.com/developer/a…




avatar
文章
阅读
粉丝