Proxysql读写分离配置

2018/3/8 18:55:01 人评论 次浏览 分类:MYSQL

ProxySQL是Percona主推的读写分离中间件,下载地址为:

https://www.percona.com/downloads/proxysql/
一、安装
1:下载
wget https://www.percona.com/downloads/proxysql/proxysql-1.4.5/binary/redhat/6/x86_64/proxysql-1.4.5-1.1.el6.x86_64.rpm
2:安装
rpm -ivh proxysql-1.4.5-1.1.el6.x86_64.rpm
3:启动
service proxysql start/stop/status

二、配置

--登录Proxysql管理和监控界面(也是使用Mysql协议,端口是6032,不加-h会自动使用本地sock连到3306的mysql端口,所以必须要加上-h参数)
mysql -uadmin -padmin -P6032 -h127.0.0.1
6032为管理和监控端口,6033为对外提供数据库服务的端口,admin/amin为默认的管理监控的账号密码,可以在proxysql.conf文件的admin_variables找到。

main

    是默认的数据库名,表里存放后端db实例、用户验证、路由规则等信息。表名以 runtime_开头的表示proxysql当前运行的配置内容,不能通过dml语句修改,只能修改对应的不以 runtime_ 开头的(在内存)里的表,然后 LOAD 使其生效, SAVE 使其存到硬盘以供下次重启加载。

disk

    是持久化到硬盘的配置,sqlite数据文件。

stats

    是proxysql运行抓取的统计信息,包括到后端各命令的执行次数、流量、processlist、查询种类汇总/执行时间,等等。

monitor 

    存储 monitor 模块收集的信息,主要是对后端db的健康/延迟检查。

Proxysql登录管理和监控界面后,不需要使用use database_name;的命令,直接查询下列表即可。

主要的表信息:

其中最重要的是mysql_servers,mysql_users,mysql_query_rules这3个表,其他表很少修改。这3个最重要的表的内容也可以在/etc/proxysql.conf中修改,但只是第一次初始化时有用,以后的修改和初始化信息都是存储在sqllite文件中的,建议不要对/etc/proxysql.conf做任何修改。

  • mysql_servers:

定义所有可用的mysql数据库IP,hostgroup_id可以自定义,只需将读和写的组设为不一样的hostgroup_id即可。

本例中是在MHA环境下配置的,59、60、61为MHA的3个节点,其中59目前为master,200为MHA的VIP(公共IP)。

insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) 
values(100,'10.0.1.59',3306,1,1000,10,'test my proxysql');
insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) 
values(100,'10.0.1.60',3306,1,1000,10,'test my proxysql');
insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) 
values(100,'10.0.1.61',3306,1,1000,10,'test my proxysql');
insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) 
values(1000,'10.0.1.200',3306,1,1000,10,'test my proxysql');

这里把MHA的3个节点的IP--59,60,61的hostgroup_id设为100,将MHA的VIP--200的hostgroup_id设置为1000,实现读写分离和读的负载均衡。

  • mysql_users:

定义连接proxysql的数据库账户和密码,首先需要此账号在mysql库中存在,这里使用已经存在的账leo,可以看到这个账号既能连接mysql也能连接proxysql的6033端口。default_hostgroup设为上边定义的1000,这样proxysql自动连接到MHA的VIP上,即始终默认连接到master库。

insert into mysql_users(username,password,active,default_hostgroup,transaction_persistent)
values('leo','leo',1,1000,1);

建议再定义一个监控账号,如下:

mysql> GRANT SUPER,REPLICATION CLIENT ON *.* TO 'proxysql' IDENTIFIED BY 'proxysql';
然后mysql -uadmin -padmin -h127.0.0.1 -P6032进入管理和监控界面:
set mysql-monitor_username='proxysql';
set mysql-monitor_password='proxysql';
--此账号也可以不配置直接使用默认的admin:admin,区别是默认的admin:admin权限很大,自己设置的proxysql账户只能看到监控信息。

最后再load并在sqlite中保存上述各种修改:

load mysql servers to runtime;
load mysql users to runtime;
load mysql variables to runtime;
save mysql servers to disk;
save mysql users to disk;
save mysql variables to disk;
  • mysql_query_rules:

定义读写分离规则,可以看到是通过正则实现的,下列定义表示除了select for update,其他的select一律转发到hostgroup_id为100的虚拟节点上,也就说读操作负载均衡的分配到59、60、61三个库上。其他操作使用默认的hostgroup_id为1000的库,也就是MHA的master库。

INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) 
VALUES(1,'^SELECT.*FOR UPDATE$',1000,1);
INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) 
VALUES(1,'^SELECT',100,1);

--load并在sqlite中保存上述修改。
load mysql query rules to runtime;
save mysql query rules to disk;
  • 查看相关的统计信息
在进行一系列增删改查后,可以通过如下视图查看统计信息,需要使用监控账号连接proxysql的6032端口.
本例中连接方式为mysql -uproxysql -pproxysql -P6032 -h127.0.0.1 (127.0.0.1为proxsql所在服务器,我这里直接在本地查的)
select * from stats_mysql_query_digest_reset;
三、总结
本例使用Proxysql实现了MHA的读写分离,把VIP设置为了可读写的hostgroup,真实的3台MHA服务器IP设置为了可读的hostgroup,但是在MHA同步延迟较大时可能出现读取不到最新数据的情况,需要注意。

相关知识

  • 低成本和高性能MySQL云数据的架构探索

    PPT这里下载: MySQL作为一个低成本、高性能、可靠性好而且开源的数据库产品,在互联网企业应用非常广泛,例如淘宝网有数千台MySQL服务器的规模。虽然近两年来NoSQL的发展很快,新产品层出不穷,但在业务中应用NoSQL对开发者来说要求比较高,而MySQL拥有成熟的中间件、运…

    2017/7/19 11:09:00

共有访客发表了评论 网友评论

验证码: 看不清楚?