proxysql学习

一.安装

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

yum install -y proxysql

 

二.概念及基本操作

 

1.runtime:内存中的参数配置，不可直接修改

2.memory 通过mysql客户端6032访问到的参数配置数据库

3.disk  存在sqllite3内的参数配置  默认地址/var/lib/proxysql/proxysql.db

4.config file 存在文件内的参数配置/etc/proxysql.cnf


 

启动：当启动proxysql的时候，首先会找到参数文件，读取到proxysql.db位置，如果存在，则通过db内的配置启动，如果不存在，则会解析参数文件

--initial 备份现有proxysql.db 并生成新的proxysql.db

--reload 合并proxysql.db和配置文件的信息到proxysql.db,有冲突可能会报错

 

参数在文件内的传递

LOAD <item> FROM MEMORY / LOAD <item> TO RUNTIME  　　 memory-runtime

SAVE <item> TO MEMORY / SAVE <item> FROM RUNTIME   　　 runtime-memory

LOAD <item> TO MEMORY / LOAD <item> FROM DISK　　　　　disk-memory

SAVE <item> FROM MEMORY / SAVE <item> TO DISK 　　　　　memory-disk

LOAD <item> FROM CONFIG　　　　　　　　　　　　　　　　   config-memory

 

添加servers

添加一个实例

INSERT INTO mysql_servers (hostname) VALUES ('172.16.0.1');

添加一组实例

INSERT INTO mysql_servers (hostgroup_id, hostname) VALUES (1, '172.16.0.2'), (1,'172.16.0.3');

LOAD MYSQL SERVERS TO RUNTIME;

SAVE MYSQL SERVERS TO DISK;

参数：

hostname：实例 ip

hostnamegroup_id:实例组

status：online,offline_soft,offline_hard

weight:权重

max_replication_lag:如果设置了，当达到这个阈值，则会访问回避这个实例

 

monitor

 

添加监控

UPDATE global_variables SET variable_value='qa_root' WHERE variable_name='mysql-monitor_username';

UPDATE global_variables SET variable_value='Vso0ViKRU=' WHERE variable_name='mysql-monitor_password';

UPDATE global_variables SET variable_value='200' 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

connect　　　　　　 mysql_server_connect_log

ping　　     　  mysql_server_ping_log

replication lag    mysql_server_replication_lag_log 

read only　　　    mysql_server_read_only_log

 

replication hostgroups

INSERT INTO mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,comment) VALUES (1,2,'cluster1');

LOAD MYSQL SERVERS TO RUNTIME;

SAVE MYSQL SERVERS TO DISK;

SELECT * FROM monitor.mysql_server_read_only_log ORDER BY time_start_us DESC LIMIT 3;

 

stats

stats_mysql_connection_pool   连接健康监测

stats_mysql_commands_counters  执行命令的次数

stats_mysql_query_digest     类似查询统计

 

quary rules

INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (10,1,'stnduser','^SELECT c FROM sbtest1 WHERE id=\?$',2,1);

INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (20,1,'stnduser','DISTINCT c FROM sbtest1',2,1);

apply=1 means that no further rules should be evaluated if the current rule was matched

LOAD MYSQL QUERY RULES TO RUNTIME;

 

query caching

UPDATE mysql_query_rules set cache_ttl=5000 WHERE active=1 AND destination_hostgroup=2;

LOAD MYSQL QUERY RULES TO RUNTIME;

 

Query Rewrite (通过match_pattern replace_pattern)改写sql

INSERT INTO mysql_query_rules (rule_id,active,username,match_pattern,replace_pattern,apply) VALUES (30,1,'stnduser','DISTINCT(.*)ORDER BY c','DISTINCT\1',1);

 

三.读写分离

端口方式（不实用）：

需重启proxysql

SET mysql-interfaces='0.0.0.0:6401;0.0.0.0:6402';

## save it on disk and restart proxysql

SAVE MYSQL VARIABLES TO DISK;

PROXYSQL RESTART;

INSERT INTO mysql_query_rules (rule_id,active,proxy_port,destination_hostgroup,apply)

VALUES (1,1,6401,1,1), (2,1,6402,2,1);

LOAD MYSQL QUERY RULES TO RUNTIME;

SAVE MYSQL QUERY RULES TO DISK;

 

正则匹配方式（生产也推荐）

INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply)

VALUES

(1,1,'^SELECT.*FOR UPDATE$',1,1),

(2,1,'^SELECT',2,1);

LOAD MYSQL QUERY RULES TO RUNTIME;

SAVE MYSQL QUERY RULES TO DISK;

 

Intelligent Read/Write split using regex and digest （生产推荐）

INSERT INTO mysql_query_rules (rule_id,active,digest,destination_hostgroup,apply)

VALUES

(1,1,'0x38BE36BDFFDBE638',20,1);

INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply)

VALUES

(1,1,'^SELECT COUNT\(\*\)',20,1);

 

sharding （感觉用这个配置sharding会比较麻烦，所以省略）

 

其他功能

1.升级功能，需手动配置及开启

2.通过 mysql_query_rules 配置是否记录日志