proxysql 配置

镜像

cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name= ProxySQL YUM repository
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.0.x/centos/$releasever
gpgcheck=1
gpgkey=https://repo.proxysql.com/ProxySQL/repo_pub_key
EOF

yum安装配置

yum -y install proxysql.x86_64

配置

--创建proxysql数据目录
mkdir -p /data/proxysql

--修改配置目录
vim /etc/proxysql.cnf

datadir="/data/proxysql"
errorlog="/data/proxysql/proxysql.log"

启动

systemctl start proxysql

配置server端

--以admin的方式登录
mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin>'

--添加server
MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,weight,max_connections) values(1,'172.0.0.1',99999,8000),(2,'172.0.0.1',1,8000),(2,'172.0.0.2',99999,8000);
Query OK, 3 rows affected (0.00 sec)

MySQL [(none)]> select hostgroup_id,hostname,port,status,weight,max_connections from mysql_servers;
+--------------+--------------+------+--------+--------+-----------------+
| hostgroup_id | hostname     | port | status | weight | max_connections |
+--------------+--------------+------+--------+--------+-----------------+
| 1            | 172.0.0.1  | 3306 | ONLINE | 99999  | 8000            |
| 2            | 172.0.0.1  | 3306 | ONLINE | 1      | 8000            |
| 2            | 172.0.0.2 | 3306 | ONLINE | 99999  | 8000            |
+--------------+--------------+------+--------+--------+-----------------+
3 rows in set (0.00 sec)

--生效
load mysql servers to runtime;
save mysql servers to disk;

配置user端

insert into mysql_users(username,password,default_hostgroup) values('mysql_user','passwd',1);

load mysql users to runtime;
save mysql users to disk;

主库添加账号

grant all on *.* to 'mysql_user'@'%' identified by 'passwd';
grant replication slave on *.* to 'proxysql_user'@'172.0.0.%' identified by 'proxysql_user';
flush privileges;

修改proxysql账号

set mysql-monitor_username='proxysql_user';
set mysql-monitor_password='proxysql_user';
load mysql variables to runtime;
save mysql variables to disk;

--查看连接状态
select * from mysql_server_connect_log;
select * from mysql_server_ping_log;

读写分离设置

--设置读写分组
insert into mysql_replication_hostgroups values(1,2,'read_only','test_db');
load mysql servers to runtime;
save mysql servers to disk;

--设置读写规则
insert into mysql_query_rules(active,match_digest,destination_hostgroup,apply) values(1,'^SELECT .* FOR UPDATE$',1,1),(1,'^SELECT',2,1);

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

--测试
# mysql -umysql_user -p'passwd' -h 127.0.0.1 -P6033 -e 'SELECT @@server_id'
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|           2 |
+-------------+

# mysql -umysql_user -p'passwd' -h 127.0.0.1 -P6033 -e 'begin;SELECT @@server_id;commit;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|           1 |
+-------------+

参数设置

connection is locked to hostgroup

set mysql-set_query_lock_on_hostgroup=0;
load mysql variables to runtime;
save mysql variables to disk;

参考：https://github.com/sysown/proxysql/pull/2156