ProxySQL使用

原创
2019/01/16 14:08
阅读数 748
ProxySQL特点
Where Do I Put ProxySQL?

 

Load balancing with ProxySQL

 

安装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

Database changed
mysql> show tables from main;
+--------------------------------------------+
| tables                                     |
+--------------------------------------------+
| global_variables                           |
| mysql_collations                           |
| mysql_group_replication_hostgroups         |
| mysql_query_rules                          |
| mysql_query_rules_fast_routing             |
| mysql_replication_hostgroups               |
| mysql_servers                              |
| mysql_users                                |
| proxysql_servers                           |
| runtime_checksums_values                   |
| runtime_global_variables                   |
| runtime_mysql_group_replication_hostgroups |
| runtime_mysql_query_rules                  |
| runtime_mysql_query_rules_fast_routing     |
| runtime_mysql_replication_hostgroups       |
| runtime_mysql_servers                      |
| runtime_mysql_users                        |
| runtime_proxysql_servers                   |
| runtime_scheduler                          |
| scheduler                                  |
+--------------------------------------------+

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
展开阅读全文
加载中
点击引领话题📣 发布并加入讨论🔥
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部