ProxySQL使用 - HelloWorld开发者社区

ProxySQL特点

Query caching
Query Routing
Supports failover
Advanced configuration with 0 downtime
Application layer proxy
Cross platform
Advanced topology support
Firewall
支持group replication https://www.tienle.com/2018/07-29/mysql-high-availability-with-proxysql-and-mysql-group-replication.html
支持sharding

Where Do I Put ProxySQL?

https://www.percona.com/blog/2017/07/20/where-do-i-put-proxysql/

Load balancing with ProxySQL

https://www.percona.com/doc/percona-xtradb-cluster/LATEST/howtos/proxysql.html

安装proxysql

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 -y install perl-DBD-MySQL perl-DBI perl-Time-HiRes perl-IO-Socket-SSL

yum -y install proxysql

默认是service方式，如果制作systemd方式，如下：

如果想要通过systemd管理ProxySQL，可在/usr/lib/systemd/system/proxysql.service中写入如下内容：

[root @xuexi ~]# vim /usr/lib/systemd/system/proxysql.service

[Unit]Description=High Performance Advanced Proxy for MySQL

After=network.target

[Service]

Type=simple

User=mysql

Group=mysql

PermissionsStartOnly=true

LimitNOFILE=102400

LimitCORE=1073741824

ExecStartPre=/bin/mkdir -p /var/lib/proxysqlExecStartPre=/bin/chown mysql:mysql -R /var/lib/proxysql /etc/proxysql.cnf

ExecStart=/usr/bin/proxysql -f

Restart=always

添加用户：useradd proxysql

mkdir /var/lib/proxysql chown -R proxysql.proxysql /var/lib/proxysql chown -R proxysql.proxysql /etc/proxysql.cnf

vi /etc/init.d/proxysql ->84行proxysql $OPTS改成 sudo -u proxysql /usr/bin/proxysql $OPTS

启动：

service proxysql start

启动一个进程，然后fork出一个子进程，父进程负责监控子进程运行状况如果挂了则拉起来，子进程负责执行真正的任务

默认管理端口是6032，客户端服务端口是6033。默认的用户名密码都是 admin。

proxysql 五个数据库简介
+-----+---------------+-------------------------------------+
| seq | name       | file                               |
+-----+---------------+-------------------------------------+
| 0 | main       |                                  |
| 2 | disk       | /var/lib/proxysql/proxysql.db    |
| 3 | stats       |                                  |
| 4 | monitor    |                                  |
| 5 | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+

main 是默认的”数据库”名，表里存放后端db实例、用户验证、路由规则等信息。表名以 runtime_开头的表示proxysql当前运行的配置内容，不能通过dml语句修改，只能修改对应的不以 runtime_ 开头的（在内存）里的表，然后 LOAD 使其生效， SAVE 使其存到硬盘以供下次重启加载。
disk 是持久化到硬盘的配置，sqlite数据文件。
stats 是proxysql运行抓取的统计信息，包括到后端各命令的执行次数、流量、processlist、查询种类汇总/执行时间，等等。
monitor 库存储 monitor 模块收集的信息，主要是对后端db的健康/延迟检查。

global_variables 有80多个变量可以设置，其中就包括监听的端口、管理账号、禁用monitor等，详细可参考 https://github.com/sysown/proxysql/wiki/Global-variables 。

mysql> use main;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

mysql_servers – 后端真实的mysql实例

mysql_users – 连接proxySQL的用户，以及连接后端mysql的用户； 注意两者相同。

mysql_query_rules — 查询规则，支持链式的多条规则。详细看 query rules wiki

global_variables — 如表名，

sqlite3 proxysql.db
sqlite> .databases
seq  name          file
---  ---------------  ----------------------------------------------------------
0 main          /var/lib/proxysql/proxysql.db
sqlite> .tables    #show tables from disk;
global_variables
mysql_servers
mysql_replication_hostgroups
mysql_collations
mysql_group_replication_hostgroups
mysql_users
mysql_query_rules
proxysql_servers
mysql_query_rules_fast_routing
scheduler
sqlite> .schema mysql_servers

MySQL [(none)]> show create table mysql_servers\G
*************************** 1. row ***************************
table: mysql_servers
Create Table: CREATE TABLE mysql_servers (
hostgroup_id INT CHECK (hostgroup_id>=0) NOT NULL DEFAULT 0,
hostname VARCHAR NOT NULL,
port INT NOT NULL DEFAULT 3306,
status VARCHAR CHECK (UPPER(status) IN ('ONLINE','SHUNNED','OFFLINE_SOFT', 'OFFLINE_HARD')) NOT NULL DEFAULT 'ONLINE',
weight INT CHECK (weight >= 0) NOT NULL DEFAULT 1,
compression INT CHECK (compression >=0 AND compression <= 102400) NOT NULL DEFAULT 0,
max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 1000,
max_replication_lag INT CHECK (max_replication_lag >= 0 AND max_replication_lag <= 126144000) NOT NULL DEFAULT 0,
use_ssl INT CHECK (use_ssl IN(0,1)) NOT NULL DEFAULT 0,
max_latency_ms INT UNSIGNED CHECK (max_latency_ms>=0) NOT NULL DEFAULT 0,
comment VARCHAR NOT NULL DEFAULT '',
PRIMARY KEY (hostgroup_id, hostname, port) )
1 row in set (0.000 sec)

MySQL [(none)]> show create table mysql_users\G
*************************** 1. row ***************************
table: mysql_users
Create Table: CREATE TABLE mysql_users (
username VARCHAR NOT NULL,
password VARCHAR,
active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
use_ssl INT CHECK (use_ssl IN (0,1)) NOT NULL DEFAULT 0,
default_hostgroup INT NOT NULL DEFAULT 0,
default_schema VARCHAR,
schema_locked INT CHECK (schema_locked IN (0,1)) NOT NULL DEFAULT 0,
transaction_persistent INT CHECK (transaction_persistent IN (0,1)) NOT NULL DEFAULT 1,
fast_forward INT CHECK (fast_forward IN (0,1)) NOT NULL DEFAULT 0,
backend INT CHECK (backend IN (0,1)) NOT NULL DEFAULT 1,
frontend INT CHECK (frontend IN (0,1)) NOT NULL DEFAULT 1,
max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 10000,
PRIMARY KEY (username, backend),
UNIQUE (username, frontend))
1 row in set (0.000 sec)

MySQL [(none)]> show create table mysql_replication_hostgroups\G
*************************** 1. row ***************************
table: mysql_replication_hostgroups
Create Table: CREATE TABLE mysql_replication_hostgroups (
writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY,
reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND reader_hostgroup>0),
comment VARCHAR NOT NULL DEFAULT '', UNIQUE (reader_hostgroup))
1 row in set (0.000 sec)

MySQL [(none)]> show create table mysql_query_rules\G
*************************** 1. row ***************************
table: mysql_query_rules
Create Table: CREATE TABLE mysql_query_rules (
rule_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 0,
username VARCHAR,
schemaname VARCHAR,
flagIN INT NOT NULL DEFAULT 0,
client_addr VARCHAR,
proxy_addr VARCHAR,
proxy_port INT,
digest VARCHAR,
match_digest VARCHAR,
match_pattern VARCHAR,
negate_match_pattern INT CHECK (negate_match_pattern IN (0,1)) NOT NULL DEFAULT 0,
re_modifiers VARCHAR DEFAULT 'CASELESS',
flagOUT INT,
replace_pattern VARCHAR,
destination_hostgroup INT DEFAULT NULL,
cache_ttl INT CHECK(cache_ttl > 0),
reconnect INT CHECK (reconnect IN (0,1)) DEFAULT NULL,
timeout INT UNSIGNED,
retries INT CHECK (retries>=0 AND retries <=1000),
delay INT UNSIGNED,
next_query_flagIN INT UNSIGNED,
mirror_flagOUT INT UNSIGNED,
mirror_hostgroup INT UNSIGNED,
error_msg VARCHAR,
OK_msg VARCHAR,
sticky_conn INT CHECK (sticky_conn IN (0,1)),
multiplex INT CHECK (multiplex IN (0,1,2)),
log INT CHECK (log IN (0,1)),
apply INT CHECK(apply IN (0,1)) NOT NULL DEFAULT 0,
comment VARCHAR)
1 row in set (0.001 sec)

这些字段具有以下语义：

rule_id - 规则的唯一ID。规则按rule_id顺序处理
active - 查询处理模块将仅考虑active = 1的规则，并且仅将活动规则加载到运行时。
username - 过滤标准匹配用户名。如果为非NULL，则仅当使用正确的用户名建立连接时，查询才会匹配
schemaname - 过滤标准匹配schemaname。如果为非NULL，则只有在连接使用schemaname默认模式时才会匹配查询（在mariadb / mysql schemaname中等效于databasename）
flagIN，flagOUT，apply-这使我们能够创建得到应用一前一后“的规则链”。输入标志值设置为0，并且在开始时仅考虑flagIN = 0的规则。当为特定查询找到匹配规则时，将评估flagOUT，如果为NOT NULL，则将使用flagOUT中的指定标志标记查询。如果flagOUT与flagIN不同，则查询将退出当前链并输入一个新的规则链，其中flagIN作为新的输入标志。如果flagOUT与flagIN匹配，则将针对具有所述flagIN的第一个规则再次重新评估查询。这种情况会发生，直到没有更多匹配规则，或者apply设置为1（这意味着这是要应用的最后一条规则）
client_addr - 匹配来自特定来源的流量
proxy_addr - 匹配特定本地IP上的传入流量
proxy_port - 匹配特定本地端口上的传入流量
digest- 将查询与特定摘要匹配，如返回stats_mysql_query_digest。digest
match_digest - 与查询摘要匹配的正则表达式。另请参见mysql-query_processor_regex
match_pattern - 与查询文本匹配的正则表达式。另请参见mysql-query_processor_regex
negate_match_pattern - 如果将其设置为1，则只有与查询文本不匹配的查询才会被视为匹配项。这在与match_patternor 匹配的正则表达式前面充当NOT运算符match_digest
re_modifiers - 逗号分隔的选项列表，用于修改RE引擎的行为。随着CASELESS比赛是不区分大小写。随着GLOBAL替换是全球（替换所有的比赛，而不仅仅是第一个）。为了向后兼容，仅CASELESS默认启用。有关更多详细信息，另请参见mysql-query_processor_regex。
replace_pattern - 这是替换匹配模式的模式。它是使用RE2 :: Replace完成的，所以值得一看有关的在线文档：https：//github.com/google/re2/blob/master/re2/re2.h#L378。请注意，这是可选的，当缺少此选项时，查询处理器将仅缓存，路由或设置其他参数而不进行重写。
destination_hostgroup - 将匹配的查询路由到此主机组。除非存在已启动的事务且登录用户将transaction_persistent标志设置为1（请参阅mysql_users表），否则会发生这种情况。
cache_ttl - 缓存查询结果的毫秒数。注意：在ProxySQL 1.1中，cache_ttl只需几秒钟
reconnect - 功能未使用
timeout - 应执行匹配或重写查询的最大超时（以毫秒为单位）。如果查询运行的时间超过特定阈值，则会自动终止查询。如果未指定超时，则mysql-default_query_timeout应用全局变量
retries - 在执行查询期间检测到故障时需要重新执行查询的最大次数。如果未指定重试，则mysql-query_retries_on_failure应用全局变量
delay - 延迟执行查询的毫秒数。这本质上是一种限制机制和QoS，允许优先考虑某些查询而不是其他查询。此值将添加到mysql-default_query_delay适用于所有查询的全局变量中。未来版本的ProxySQL将提供更高级的限制机制。
mirror_flagOUT和mirror_hostgroup- 与镜像相关的设置。
error_msg- 将阻止查询，并将指定的error_msg内容返回给客户端
sticky_conn - 尚未实施
multiplex - 如果为0，则禁用Multiplex。如果为1，如果没有任何其他条件阻止此操作（如用户变量或事务），则可以重新启用Multiplex。如果为2，则不会仅针对当前查询禁用多路复用。请参阅wiki默认值NULL，因此不会修改多路复用策略
log - 将记录查询
apply- 设置为1不匹配和处理此规则后，将评估其他查询（注意：mysql_query_rules_fast_routing之后不会评估规则）
comment - 自由格式文本字段，可用于查询规则的描述性注释

sqlite3 proxysql_stats.db
SQLite version 3.7.17 2013-05-20 00:56:22
sqlite> .databases
seq  name          file
---  ---------------  ----------------------------------------------------------
0 main          /var/lib/proxysql/proxysql_stats.db
sqlite> .tables
mysql_connections
mysql_query_cache_day
system_cpu_hour
mysql_connections_day
mysql_query_cache_hour
system_memory
mysql_connections_hour
system_cpu
system_memory_day
mysql_query_cache
system_cpu_day
system_memory_hour

控制台操作添加主机
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (0,'serv10.bigdata.com',3306);
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (1,'serv11.bigdata.com',3306);
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (1,'serv12.bigdata.com',3306);

INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('bizuser','1q2w.3E4R',0);

UPDATE global_variables SET variable_value='proxysql' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='1q2w.3E4R' WHERE variable_name='mysql-monitor_password';

read&write split
insert into mysql_query_rules(rule_id, active, match_digest, destination_hostgroup,apply) values (10,1,'^SELECT',1,1);

insert into mysql_query_rules( rule_id, active, match_digest, apply,flagOUT) values(49, 1,'^select\s.*\sfor update',0,21);

insert into mysql_query_rules( rule_id, active, match_digest, apply,flagOUT) values(49, 1,'^select\s.*\sLOCK IN SHARE MODE',0,21);

/*将配置应用于proxysql运行环境*/
load mysql users to runtime;
LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL QUERY RULES to RUNTIME;
load mysql variables to runtime;
/*将配置存储到sqlite数据库中*/
SAVE mysql users to DISK;
SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL QUERY RULES TO DISK;
SAVE mysql variables to DISK;
SAVE ADMIN VARIABLES TO DISK;

每一个后台数据库，创建以下账号
drop user proxysql;
drop user bizuser;
CREATE USER 'proxysql'@'%' IDENTIFIED WITH mysql_native_password BY '1q2w.3E4R';
GRANT USAGE ON *.* TO 'proxysql'@'%';
CREATE USER 'bizuser'@'%' IDENTIFIED WITH mysql_native_password BY '1q2w.3E4R';
GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'bizuser'@'%';
flush privileges;

登录管理后端

mysql -uadmin -padmin -P6032 -h127.0.0.1 --prompt 'admin> '

查看是否有monitor连接出错：

SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10;

select * from mysql_server_ping_log;

select * from mysql_server_replication_lag_log;

select *from stats_mysql_query_digest;

访问前端

mysql -ubizuser -p1q2w.3E4R -h127.0.0.1 -P6033