service proxysql start
mysql -uadmin -padmin -P6032 -h127.0.0.1
查看mysql_servers的表结构
select*from sqlite_master where name='mysql_servers'\G
添加mysql节点信息,加载生效并保存
insertinto mysql_servers(hostgroup_id,hostname,port)values(10,'192.168.160.129',3306);insertinto mysql_servers(hostgroup_id,hostname,port)values(10,'192.168.160.140',3306);load mysql servers to runtime;save mysql servers todisk;
ProxySQL上配置监控用的用户和密码
set mysql-monitor_username='monitor';set mysql-monitor_password='centos';load mysql variables to runtime;save mysql variables todisk;
查看监控连接是否正常的,NULL为正常
select*from mysql_server_connect_log;
查看监控心跳(测ping)
select*from mysql_server_ping_log;
设置分组信息,10代表写,20代表读
insertinto mysql_replication_hostgroups values(10,20,"test");load mysql servers to runtime;save mysql servers todisk;
insertinto mysql_users(username,password,default_hostgroup)values('sqluser','centos',10);load mysql users to runtime;save mysql users todisk;
添加路由规则,只是查询的语句发送给20的读组,其他语句发送给10组
insertinto mysql_query_rules
(rule_id,active,match_digest,destination_hostgroup,apply)VALUES(1,1,'^SELECT.*FOR UPDATE$',10,1),(2,1,'^SELECT',20,1);load mysql query rules to runtime;save mysql query rules todisk;