1、在mysql_replication_hostgroups中配置读写组
mysql -uadmin -padmin -h 127.0.0.1 -P 6032
insert into mysql_replication_hostgroups(writer_hostgroup,reader_hostgroup,comment) values(10,20,'proxy');
load mysql servers to runtime;
save mysql servers to disk;
2、后端从节点添加只读参数
set global read_only=1;
3、创建监控用户 主库创建监控用户:
create user monitor@'%' identified by '123';
grant replication client on *.* to monitor@'%';
proxySQL中修改variables表:
set mysql-monitor_username='monitor';
set mysql-monitor_password='123';
或者:
update global_variables set variable_value = 'monitor' where variable_name = 'mysql-monitor_username';
update global_variables set variable_value = '123' where variable_name = 'mysql-monitor_password';
生效:
load mysql variables to runtime;
save mysql variables to disk;
注:默认主库也是可以读的,可以设置权重或者从mysql_servers中删除
4、添加主机到proxySQL
insert into mysql_servers(hostgroup_id,hostname,port) values (10,'10.0.0.100',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values (20,'10.0.0.101',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values (20,'10.0.0.102',3306);
load mysql servers to runtime;
save mysql servers to disk;
4、查看监控日志
select * from mysql_server_connect_log;
select * from mysql_server_ping_log;
select * from mysql_server_read_only_log;
select * from mysql_server_replication_lag_log;
5、创建应用用户
主库:
create user dev@'%' identified by '123';
grant all on *.* to dev@'%';
proxySQL中:
insert into mysql_users(username,password,default_hostgroup) values('dev','123',10);
load mysql users to runtime;
save mysql users to disk;
注:早期版本mysql_users 表中的 transaction_persistent值为0 需要修改为1,保证事务完整性
6、读写规则
insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values(1,1,'^select.*for update$',10,1);
insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values(2,1,'^select',20,1);
load mysql query rules to runtime;
save mysql query rules to disk;
注:select.... for update 的rule_id必须小于普通select语句的rule_id,proxySQL是根据rule_id顺序进行规则匹配的。
7、测试
mysql -udev -p123 -P6033 -h127.0.0.1 -e "begin;select @@server_id;commit;"
mysql -udev -p123 -P6033 -h127.0.0.1 -e "select @@server_id;"
select *select * from stats_mysql_query_digest;