proxysql读写分离

[jamesBan]

准备工作搭建主从复制

安装

#run docker container
docker run -p 16032:6032 -p 16033:6033 -d --network=mysql-network --name proxysql proxysql/proxysql

#install mysql client
docker exec -ti proxysql bash
apt update && apt install  -ymysql-client

mysql -u admin -padmin -h 127.0.0.1 -P6032

####添加MySQL节点

#config mysql servers
insert into mysql_servers(hostgroup_id, hostname, port, max_connections, comment) 
values(1, "mysql-master", 3306, 100, "master-server");

insert into mysql_servers(hostgroup_id, hostname, port, max_connections, comment) 
values(2, "mysql-slave1", 3306, 100, "slave-1-server");

insert into mysql_servers(hostgroup_id, hostname, port, max_connections, comment) 
values(2, "mysql-slave2", 3306, 100, "slave-2-server");

#save config
load mysql servers to runtime;
save mysql servers to disk;

监控后端MySQL节点

#master/slave add  monitor user
mysql> create user monitor@'%' identified by 'P@ssword1!';
mysql> grant replication client on *.* to monitor@'%';

#proxysql
mysql>set mysql-monitor_username='monitor';
mysql>set mysql-monitor_password='P@ssword1!';

#save config
msyql>LOAD MYSQL VARIABLES TO RUNTIME;SAVE MYSQL VARIABLES TO DISK;

#show log
mysql>select * from mysql_server_connect_log;
mysql>select * from mysql_server_ping_log; 

配置mysql_users

#master/slave上添加用户 root
mysql>grant all  privileges on *.* to root@'%' identified by 'P@ssword1!';

#proxysql
mysql>INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('root','P@ssword1!',1);
msyql>LOAD MYSQL USERS TO RUNTIME;SAVE MYSQL USERS TO DISK;

配置路由

#update
INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT.*FOR UPDATE$',1,1);

#read
INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT',2,1);

#save config
mysql>LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK;

test

mysql> mysql -h127.0.0.1 -uroot -pP@ssword1! -P6033
mysql> create database proxy_test;
mysql> create table t(id int);
mysql> insert into t values(1);
mysql> select * from t;
mysql> select * from stats_mysql_connection_pool;#连接池
mysql> select * from stats_mysql_query_digest;#sql执行

参考

ProxySQL Read Write Split (HOWTO)

MySQL中间件：ProxySQL

[MHA+ProxySQL实现读写分离高可用]