proxysql压测

【1】测试架构

（1.1）机器架构

IP　　　　　　　　server-id　　　　db-version　　desc

192.168.148.149　　1493306　　　　8.0.22　　　　master(gtid)、proxysql 2.2.2-11

192.168.148.176　　1793306　　　　8.0.22　　　　slave(gtid)

192.168.148.185　　1853306　　　　8.0.22　　　　slave(gtid)

（1.2）mysql 与 proxysql配置

mysql主库执行：

-- mysql主库执行
create user repl@'192.168.191.%' identified by '123456';
grant replication client,replication slave on *.* to repl@'192.168.191.%';

create user proxysql@'192.168.191.%' identified by '123456';
grant all privileges on *.* to proxysql@'192.168.191.%';

create user monitor@'192.168.191.%' identified by '123456';
grant all privileges on *.* to monitor@'192.168.191.%';

mysql从库执行：

change master to
master_host='192.168.191.149',
master_user='repl',
master_password='123456',
master_auto_position=1;

start slave;
set global read_only=1;

proxysql 执行：

# （1）监控
set mysql-monitor_username='monitor';
set mysql-monitor_password='123456';
set mysql-monitor_enabled='true';

UPDATE global_variables SET variable_value='2000' 
WHERE variable_name IN ('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');

load mysql variables to runtime; 
save mysql variables to disk;

# （2）用户
insert into mysql_users(username,password,active,default_hostgroup) values('proxysql','123456',1,1);
load mysql users to runtime; 
save mysql users to disk;

【2】1主1从压测

（2.1）proxysql配置

监控和连接相关信息已经在 （1.2）中配置好；

# 服务器
insert into mysql_servers(hostgroup_id,hostname,port) values(1,'192.168.191.149',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values(2,'192.168.191.176',3306); 

# 复制组
insert into mysql_replication_hostgroups(writer_hostgroup,reader_hostgroup) values(1,2);

# 查询规则
insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values (1,1,'^select',2,1);

load mysql servers to runtime;
load mysql query rules to runtime;
save mysql servers to disk;
save mysql query rules to disk;