mysql5.7使用ProxySQL实现读写分离( 二 ) _mysql5.7

/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;

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