ProxySQL!像C罗一样的强大!

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: 甦哥我最喜欢的球队是葡萄牙——最爱C罗,喜欢他那种在比赛中好强不服输的精神。我们做技术也是一样,不要因为一点困难,就放弃了当初的梦想。只有不断努力,提升自己,才能在更好的平台上实现自我价值。今儿,甦哥给大家介绍一款MySQL的一款中间件的产品——ProxySQL,它是灵活强大的MySQL代理层。

甦哥我最喜欢的球队是葡萄牙——最爱C罗,喜欢他那种在比赛中好强不服输的精神。我们做技术也是一样,不要因为一点困难,就放弃了当初的梦想。只有不断努力,提升自己,才能在更好的平台上实现自我价值。
今儿,甦哥给大家介绍一款MySQL的一款中间件的产品——ProxySQL,它是灵活强大的MySQL代理层。像C罗一样的强大,可以实现读写分离,支持Query路由功能,支持动态指定某个SQL进行cache,支持动态加载配置、故障切换和一些SQL的过滤功能。还有一些同类产品比如DBproxy、MyCAT、OneProxy等。但经过反复对比和测试之后,决定给大家介绍一款性能不谙的MySQL中间件产品ProxySQL。
有关ProxySQL更多的详细信息可访问:
link
接下来通过实战来全面了解一下ProxySQL的特性和使用场景,先介绍一下环境,我们的系统是CentOS6.7,MySQL版本是5.7.14,准备一主两从架构来配合ProxySQL

环境配置:
192.168.56.100  Master(node1)   server-id:3306100
192.168.56.101  Slave1(node2)   server-id:3306101
192.168.56.102  Slave2(node3)   server-id:3306102
192.168.56.103  Proxysql中间件 server-id:3306103

注:两个从库都要开启read_only=on。

实验架构:
image

ProxySQL的安装与启动
首先要安装一些依赖的软件包,配置好Yum源进行安装即可。
在192.168.56.103上执行如下操作:

yum -y install perl-DBD-MySQL
yum -y install perl-DBI
yum -y install perl-Time-HiRes
yum -y install perl-IO-Socket-SSL

ProxySQL软件包的两个下载地址。
GitHub官网:link
percona官网:link
安装ProxySQL:

rpm -ivh proxysql-1.3.9-1-centos67.x86_64.rpm

配置文件路径为/etc/proxysql.cnf
启动ProxySQL:

service proxysql start

image
注:6032是ProxySQL的管理端口号,6033是对外服务的端口号
管理用户名和密码都是默认的admin。

关闭ProxySQL:
service proxysql stop

查看安装版本:
image

管理员登录命令:

/usr/local/mysql/bin/mysql -uadmin -padmin -h 127.0.0.1 -P 6032

image
可见有四个库:main,disk,stats,monitor。分别说明一下这四个库的作用
main:是内存配置数据库即MEMORY,表里存放后端db实例、用户验证、路由规则等信息。main库中有如下表信息:
image

库下的主要表:
mysql_servers—后端可以连接MySQL服务器的列表。
mysql_users—配置后端数据库的账号和监控的账号。
mysql_query_rules—指定Query路由到后端不同服务器的规则列表。

注:表名以runtime_开头的表示ProxySQL当前运行的配置内容,不能通过DML语句修改。只能修改对应的不以 runtime开头的表,然后“LOAD”使其生效,“SAVE”使其存到硬盘以供下次重启加载。

disk库—持久化磁盘的配置。
stats库—统计信息的汇总。
monitor库—一些监控的收集信息,包括数据库的健康状态等

配置ProxySQL监控
首先在master(192.168.56.100)上创建ProxySQL的监控账户和对外访问账户并赋予权限。
命令如下:

create user 'monitor'@'192.168.56.%' identified by 'monitor';
grant all privileges on *.* to 'monitor'@'192.168.56.%' with grant option;
create user 'zs'@'192.168.56.%' identified by 'zs';
grant all privileges on *.* to 'zs'@'192.168.56.%' with grant option;
flush privileges;

ProxySQL的多层配置系统
ProxySQL有一套很完整的配置系统,方便DBA对线上的操作。整套配置系统分为三层,最顶层为RUNTIME,中间层为MEMORY和最底层,也就是持久层的DISK和CONFIG FILE
image

RUNTIME:代表ProxySQL当前生效的正在使用的配置,无法直接修改这里的配置,必须要从下一层“load”进来。
MEMORY:MEMORY这一层上面连接RUNTIME层,下面连接持久化层。在这层可以正常操作ProxySQL配置,随便修改,不会影响生产环境。修改一个配置一般都是先在MEMORY层完成,然后确认正常之后再加载到RUNTIME和持久化到磁盘上。
DISK和CONFIG FILE:持久化配置信息,重启后内存中的配置信息会丢失,所以需要将配置信息保留在磁盘中。重启时,可以从磁盘快速加载回来。
介绍完这三层配置系统之后,用超管用户登录ProxySQL来添加主从服务器列表。
命令如下:

insert into mysql_servers(hostgroup_id,hostname,port) values (10,'192.168.56.100',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values (10,'192.168.56.101',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values (10,'192.168.56.102',3306);
load mysql servers to runtime;
save mysql servers to disk;

登录ProxySQL之后,凡是进行任何操作,都需要运行load to runtime,从memory加载到runtime。然后再执行save to disk持久化到磁盘
image
加载完成之后,三台机器都是ONLINE状态。
接下来继续为ProxySQL配置监控账号,命令如下:

set mysql-monitor_username='monitor';
set mysql-monitor_password='monitor';
load mysql variables to runtime;
save mysql variables to disk;

之后验证监控信息:
image
image

监控信息都已正常,没有任何报错。

配置ProxySQL主从分组信息
这里会用到一张表mysql_replication_hostgroups:
image
里面的writer_hostgroup是写入组的编号,reader_hostgroup是读取组的编号。实验中使用10作为写入组,20作为读取组编号。

insert into mysql_replication_hostgroups values (10,20,'proxy');
load mysql servers to runtime;
save mysql servers to disk;

image
proxysql会根据server的read_only的取值,将服务器进行分组。read_only=0的server,master被分到编号为10的写组,read_only=1的server,slave则被分到编号为20的读组。
image

配置对外访问账号,默认指定主库,并需要对该用户开启事务持久化保护;
注:mysql_users表中的transaction_persistent字段默认为0,建议在创建完用户之后设置为1,避免脏读,幻读等现象的发生
命令如下:
insert into mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT.*FOR UPDATE$',10,1);
insert into mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT',20,1);
load mysql query rules to runtime;
save mysql query rules to disk;

验证登录的服务器就是主库:
image
image
注:对外端口号需要指定为6033。

配置读写分离策略
配置读写分离策略需要使用mysql_query_rules表。表中的match_pattern字段就是代表设置的规则,destination_hostgroup字段代表默认指定的分组,apply代表真正执行应用规则。
把所有以select开头的语句全部分配到编号为20的读组中。select for update 会产生一个写锁,对数据查询的实效性要求高,把它分配到编号为10的写组中,其他所有操作都会默认路由到写组中。
命令如下:
insert into mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT.*FOR UPDATE$',10,1);
insert into mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT',20,1);
load mysql query rules to runtime;
save mysql query rules to disk;

测试读写分离
通过创建的对外账户zs,连接proxysql登录数据库
命令如下:
/usr/local/mysql/bin/mysql -uzs -pzs -h 192.168.56.103 -P 6033
查看zs库下,tt的数据;
image
然后再登录管理端口,通过查询stats_mysql_query_digest这张表,来监控查询状态 命令如下:
select * from stats_mysql_query_digest;
image
可见这条select语句自动路由到编号为20的读组,即slave库上。
然后继续测试,通过proxysql登录到数据库
/usr/local/mysql/bin/mysql -uzs -pzs -h 192.168.56.103 -P 6033
执行select * from zs.tt for update和update tt set name='ff' where score=100的语句操作;
image
image
这时再去登录管理端口,监控查询状态,发现都已经成功路由到了编号为10的写组,即主库,证明读写分离设置成功。
image
image
读写分离设置成功之后,我们可以调整权重,让某台机器承受更多的读操作。这些都可以用在我们运维proxysql上面。
我们调整192.168.56.102 node2节点的查询权重,让更多的读请求路由到这台机器上面;
命令如下:

update mysql_servers set weight=10 where hostname='192.168.56.102';
load mysql servers to runtime;
save mysql servers to disk;

image
作为苦逼DBA的我们,无论是初学者还是已经从业多年的“老司机”,都不要急于去把每个MySQL集群架构搭建出来。在学习的过程中,一些同学总是存在一个误区,就是觉得我会搭建所有的数据库架构就非常厉害了。其实并不是这样的,架构搭建并不是我们的最终目的,作为DBA要先了解清楚自己公司的现有业务,看看公司的业务场景适合什么样的架构,要做好相应的数据库架构设计。了解好该架构的优缺点,以及在今后应用中可能出现的问题,提前做好能解决问题的预案。知己知彼,注重细节,才能避免没日没夜地加班熬夜处理那些不该发生的问题。
下面总结了五条MySQL架构设计中的经验。
(1)根据公司现有业务设计合理架构。
(2)选择成熟的架构方案。
(3)因地制宜,根据实际设备情况做出选择。
(4)考虑方案的可行性。
(5)越简单越好,越适合公司越好。

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
关系型数据库 MySQL 移动开发
MYSQL數據庫亂碼轉碼問題
事情有些複雜,是這樣的: 現狀: 數據庫:mysql,默認編碼為latin1,建庫用的默認編碼,所以是:latin1_swedish_ci; site後臺為:ISO-8859-1; site前臺為:UTF-8; 輸入:繁...
762 0
|
SQL 存储 Oracle
【B站老杜】mysql详解(上)
【B站老杜】mysql详解
【B站老杜】mysql详解(上)
|
存储 SQL Oracle
【B站老杜】mysql详解(下)
【B站老杜】mysql详解
【B站老杜】mysql详解(下)
|
SQL 存储 Oracle
【B站老杜】mysql详解(中)
【B站老杜】mysql详解
|
关系型数据库 MySQL 程序员
让人敬佩的白发程序员——MySQL/MariaDB之父Monty阿里交流会
承蒙中国MySQL用户组(CMUG, China MySQL User Group)这次邀请了MySQL之父Monty进行一次中国行活动,作为CMUG的主席团成员和MariaDB基金会的Maria Captains(基金会的Committor),也利用“职务之便”顺手将Monty邀请到了阿里进行一次
14358 0
|
SQL 关系型数据库 MySQL
MYSQL 導入數據的幾種不同
from://http://blog.csdn.net/starluck/archive/2009/03/20/4008621.aspx //============================   Source  C://sql.txt ;    // 這種是用來執行標准的SQL 語句。
823 0
|
监控 关系型数据库 MySQL
|
Oracle 关系型数据库 数据库
Sqli学习笔记系列---一次艰难的Sqli
正文 这里记录一下一些小技巧: 替换 路径/参数 在以下接口的的“idNumber”参数上找到注入点: /foo/?theName=YAP&idNumber=248001[注入点] 识别是否执行成功true/false的payload /foo/?theName=YAP&idNumber=248001'+AND+'1'='1 TRUE /foo/?theName=YAP&idNumber=248001'+AND+'2'='1 FALSE 也可以使用管道操作:
58 0