Proxysql 读写分离中间件

读写分离的，翻译的中文GitHub：https://github.com/malongshuai/proxysql/wiki

官方文档地址：https://github.com/sysown/proxysql/wiki

我的机器是centos6

安装启动

cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name= ProxySQL YUM repository
baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/\$releasever
gpgcheck=1
gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key
EOF
yum install proxysql OR yum install proxysql-version
service proxysql start
service proxysql stop
proxysql --version //查看版本
mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> ' //链接本地proxsql控制台数据库

我的机器列表，每台都同样的安装即可

101.209.33.1
101.209.33.2
101.209.33.3
101.209.33.4
101.209.33.5
101.209.33.6
101.209.33.7

我的配置

set mysql-default_charset='utf8mb4';
insert into mysql_servers  (hostgroup_id, hostname,port,weight,max_connections) values ('0','101.143.153.141', '3306', '1', '20000'),('1','101.143.153.142', '3306', '1', '20000'),('1','101.206.250.143', '3306', '1', '20000');
insert into mysql_users(username, password, default_hostgroup,transaction_persistent, active, default_schema) values ('xxx', 'xxx', '0', 1, 1, 'xxx');
INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) VALUES (1,1,'SELECT.*COUNT\(',1,1);
INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) VALUES (2,1,'SELECT.*SUM\(',1,1);
set mysql-monitor_username='xxx_r';
set mysql-monitor_password='xxx_r';
set admin-admin_credentials = 'admin:admin;admin2:admin2';
set admin-cluster_username = 'admin2';
set admin-cluster_password = 'admin2';
insert into proxysql_servers(hostname, port,weight) values 
('101.209.33.1', '6032','1'),
('101.209.33.2', '6032','1'),
('101.209.33.3', '6032','1'),
('101.209.33.4', '6032','1'),
('101.209.33.5', '6032','1'),
('101.209.33.6', '6032','1'),
('101.209.33.7', '6032','1');
set mysql-monitor_enabled='false';
set mysql-default_schema='xxx';
load mysql servers to runtime;
save mysql servers to disk;
load mysql users to runtime;
save mysql users to disk;
load mysql variables to runtime;
save mysql variables to disk;
load proxysql servers to runtime;
save proxysql servers to disk;
load admin variables to runtime;
save admin variables to disk;
load mysql query rules to runtime;
save mysql query rules to disk;

测试是否生效，能链接上并能查询那么则生效

mysql -ujxxx -pxxx -P6033 -h127.0.0.1

查看规则命中情况

select * from stats_mysql_query_rules;

查看集群规则等同步情况

select * from stats.stats_proxysql_servers_checksum

设置连接池，参数理解请看（https://blog.csdn.net/qinglianluan/article/details/126997229?spm=1001.2014.3001.5502）

set mysql-connection_max_age_ms=30000;
set  mysql-ping_interval_server_msec=120000;
set  mysql-free_connections_pct=10;
set mysql-max_connections=100;
load mysql variables to runtime;
save mysql variables to disk;