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;
到此读写分离正式配置完毕了
