mysql5.7使用ProxySQL实现读写分离

 

 

ProxySql是MySQL的一款中间件产品，是灵活强大的mysql代理层，可以实现读写分离，支持query路由功能，支持动态指定某个sql进行缓存，支持动态加载配置。故障切换和一些sql过滤功能。

 

实战演练

环境

centos7.5 mysql5.7

ip：10.106.145.181（从） 10.106.145.182（主） 10.106.145.183（从）

 

注意：另个从库设置为read_only=on

 

我们可以将proxysql单独安装在一台机器上，这里因为机器不够我们就先将它安装在10.106.145.183上，下面我们开始操作

 

1. 安装并配置好mysq主从

从库的配置文件中要加入read_only=1，不然在启动之后查询mysql_servers表会出现从库也会被自动加入写入的组中。proxysql区别主从的唯一方式就是read_only状态值。 如果是测试则先将从库临时设置为只读

vi /etc/my.cnf

添加 read_only=1

临时设置：

set global read_only=on

 

2. 安装ProxySql

vim /etc/yum.repos.d/proxysql.repo

[proxysql_repo]

name= ProxySQL YUM repository

baseurl=http://repo.proxysql.com/ProxySQL/proxysql-2.0.x/centos/$releasever

gpgcheck=1

gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key

 

3.安装ProxySQL和mariadb客户端(如果有该机器上有mysql客户端则不需要安装mariadb)

ProxySQL内置了一个轻量级的数据库，所以需要有MySQL客户端连上去对其进行配置

yum install proxysql mariadb -y

这里因为该机器上有mysql客户端了，所有我使用指令如下

yum install proxysql -y

4.启动ProxySQL服务

systemctl start proxysql

systemctl status proxysql

设置开机启动

system enable proxysql

 

netstat -anlp| grep proxysql

 

proxysql --version

 

5.连接管理端口

6032是proxysql的管理端口号6033是对外服务的端口，用户名密码都是admin

登录命令

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

show databases;

 

6.四个库的说明

main：内存配置数据库，即memory，表里存放后端db实例 用户验证 路由规则等信息，该库信息如 下

 

 

其中主要表

mysql_servers：后端可以连接mysql的服务器列表

mysql_users:后端数据库账户和监控账户

mysql_query_rules: query路由到后端不同服务器的规则列表

disk库：持久化磁盘的配置

stats库：统计信息的汇总

monitor库：一些监控收集的信息，包括数据库的健康状态等

 

7.在主库上配置监控账户和对外访问账户

create user monitor@'10.106.145.%' identified by 'monitor';

grant all privileges on *.* to monitor@'10.106.145.%' with grant option;

create user zsprox@'10.106.145.%' identified by 'zsprox';

grant all privileges on *.* to zsprox@'10.106.145.%' with grant option;

主库的账户会被同步到另外两个从库上，如果屏蔽了则需要分别到从库上创建上面两个账户

8.Proxysql的多层配置系统

proxysql有一套完整的配置系统 ，方便dba对线上操作，整套配置系统分为三层，顶层为runtime，中间层为memory，底层也就是持久层为disk 和config file，配置结构如下

 

 

runtime:代表当前生效的正在使用的配置，无法直接修改这里的配置，必须从下一层load进来

memory：上层连runtime下面连接持久化，这层是可以正常操作proxysql的配置，随便修改，不会影响生产环境，修改这个配置一般是先在memory中完成的，确认正常后在加载到runtime和持久化到磁盘上

disk 和config file：持久化信息，重启后内存的信息会丢失，所有需要持久化

 

9. 添加主从服务器列表

使用超管用户登录

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

use main；

insert into mysql_servers(hostgroup_id,hostname,port) values(10,'10.106.145.181',3306);

insert into mysql_servers(hostgroup_id,hostname,port) values(10,'10.106.145.182',3306);

insert into mysql_servers(hostgroup_id,hostname,port) values(10,'10.106.145.183',3306);

load mysql servers to runtime;

save mysql servers to disk;

 

查询状态

select *from mysql_servers;

 

10. 添加监控账户

set mysql-monitor_username='monitor';

set mysql-monitor_password='monitor';

load mysql variables to runtime;

save mysql variables to disk;

验证

select *from monitor.mysql_server_connect_log;

 

监控正常，没有报错信息

11.配置主从分组信息

show create table mysql_replication_hostgroups;

 

里面write_hostgroup 是写入组编号，reader_hostgroup是读取组编号，我们这里使用10 作为写入组，20为读取组编号

insert into mysql_replication_hostgroups(writer_hostgroup,reader_hostgroup,comment) values(10,20,'proxy');

load mysql servers to runtime;

save mysql servers to disk;

select *from mysql_replication_hostgroups;

 

proxysql 会根据server的read_only的取值来将服务器进行分组， 只读的分配到20（读取组），不是只读的分配到10（写入组）

 

12.配置对外访问账户

对方访问账户默认指定主库，并对该用户开启事务持久化保护

mysql_users表中的transaction_persistent 的字段默认是0，建议在创建完用户之后设置为1，避免发生脏读幻读现象，命令如下

insert into mysql_users(username,password,default_hostgroup) values('zsprox','zsprox',10);

update mysql_users set transaction_persistent=1 where username='zsprox';

load mysql users to runtime;

save mysql users to disk;

验证登录的服务器就是主库（这里登录ip就是我们安装proxysql的服务器地址，一般建议单独安装一台 ，不要和mysql集群中的机器使用一台）

/usr/local/mysql/bin/mysql -uzsprox -pzsprox -h 10.106.145.183 -P 6033 -e "select @@hostname"

 

注意对外端口是6033

13 配置读写分离策略

配置读写分离需要使用mysql_query_rules 表，表中的match_pattern字段就是代表设置的规则，destination_hostgroup字段代表默认指定的分组，Apply代表真正执行的应用规则。

把所有以select开头的语句全部分配到20的读组中，其他都分配到写组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;

 

14 测试读写分离

通过创建对外账户zsproxy连接proxysql登录数据库

命令如下

/usr/local/mysql/bin/mysql -u zsprox -p -h 10.106.145.183 -P 6033

查询一个表

select * from test.b

 

在登录管理端口 查询监控状态

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

select *from stats_mysql_query_digest;

 

 

 

在6033端口执行插入操作

insert into b values(20);

再次查询状态

 

 

15 调整权重

读写分离成功后，我们可以调整权重，让某台机器承受更多的读操作，在表mysql_servers上进行设置

update mysql_servers set weight=10 where hostname='10.106.145.181';

load mysql servers to runtime;

save mysql servers to disk;

 

到此读写分离正式配置完毕了