proxysql 使用

1，背景

一款读写分离的数据库中间件，可以根据请求的语句自动将操做转发至后端的数据库服务器。

如下：

功能：

• 最基本的读/写分离，且方式有多种
• 可定制基于用户、基于schema、基于语句的规则对SQL语句进行路由。换句话说，规则很灵活。基于schema和与语句级的规则，可以实现简单的sharding(分库分表)
• 可缓存查询结果。虽然ProxySQL的缓存策略比较简陋，但实现了基本的缓存功能，绝大多数时候也够用了。此外，作者已经打算实现更丰富的缓存策略
• 监控后端节点。ProxySQL可以监控后端节点的多个指标，包括：ProxySQL和后端的心跳信息，后端节点的read-only/read-write，slave和master的数据同步延迟性(replication lag)

高性能中间件特性：

• 连接池，而且是 multiplexing
• 主机和用户的最大连接数限制
• 自动下线后端DB
• 延迟超过阀值
• ping 延迟超过阀值
• 网络不通或宕机

• 强大的规则路由引擎• 实现读写分离
• 查询重写
• sql流量镜像

• 支持prepared statement
• 支持Query Cache
• 支持负载均衡，与gelera结合自动failover

（以上摘自网上）

2，安装

# 下载rpm包

 https://github.com/sysown/proxysql/releases 下载相应的版本rpm
#配置repo或yum安装
cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name= ProxySQL
baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/7
gpgcheck=1
gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key
EOF

  yum  -y install  proxysql 
  yum  -y install  mysql   #proxysql需要通过mysql客户端来连接进入

# 启动并进入查看
service proxysql start
/etc/init.d/proxysql    --help 
$ mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
show databases ;
#配置文件：
/etc/proxysql.cnf，只在第一次启动时候有用。后续所有的配置修改都是对SQLite数据库操作，并且不会更新到proxysql.cnf文件中。
ProxySQL绝大部分配置都可以在线修改，配置存储在/var/lib/proxysql/proxysql.db 中。

3，常用的表

 1 MySQL [(none)]> show  tables ;
 2 +--------------------------------------------+
 3 | tables                                     |
 4 +--------------------------------------------+
 5 | global_variables                           |
 6 | mysql_aws_aurora_hostgroups                |
 7 | mysql_collations                           |
 8 | mysql_galera_hostgroups                    |
 9 | mysql_group_replication_hostgroups         |
10 | mysql_query_rules                          |
11 | mysql_query_rules_fast_routing             |
12 | mysql_replication_hostgroups               |
13 | mysql_servers                              |
14 | mysql_users                                |
15 | proxysql_servers                           |
16 | runtime_checksums_values                   |
17 | runtime_global_variables                   |
18 | runtime_mysql_aws_aurora_hostgroups        |
19 | runtime_mysql_galera_hostgroups            |
20 | runtime_mysql_group_replication_hostgroups |
21 | runtime_mysql_query_rules                  |
22 | runtime_mysql_query_rules_fast_routing     |
23 | runtime_mysql_replication_hostgroups       |
24 | runtime_mysql_servers                      |
25 | runtime_mysql_users                        |
26 | runtime_proxysql_servers                   |
27 | runtime_scheduler                          |
28 | scheduler                                  |
29 +--------------------------------------------+
30 24 rows in set (0.00 sec)

1，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 CHECK (port >= 0 AND port <= 65535) NOT NULL DEFAULT 3306,
    gtid_port INT CHECK (gtid_port <> port AND gtid_port >= 0 AND gtid_port <= 65535) NOT NULL DEFAULT 0,
    status VARCHAR CHECK (UPPER(status) IN ('ONLINE','SHUNNED','OFFLINE_SOFT', 'OFFLINE_HARD')) NOT NULL DEFAULT 'ONLINE',
    weight INT CHECK (weight >= 0 AND weight <=10000000) NOT NULL DEFAULT 1,
    compression INT CHECK (compression IN(0,1)) 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.00 sec)

该表保存所管理的后端服务器，以hostgroup（HG）的形式组织，一个HG代表一个角色，读或写组，与将要介绍的mysql_replication_hostgroups表进行联动。

主键为(hostgroup_id, hostname, port) 。

• 一个 HG 可以有多个实例，即多个从库，可以通过 weight 分配权重
• hostgroup_id 0 是一个特殊的HG，路由查询的时候，没有匹配到规则则默认选择 HG 0
hostname,port：后端MySQL监听的地址和端口。就是默认3306端口的意义
• status:

• ONLINE: 当前后端实例状态正常
• SHUNNED: 临时被剔除，可能因为后端 too many connections error，或者超过了可容忍延迟阀值 max_replication_lag
• OFFLINE_SOFT: “软离线”状态，不再接受新的连接，但已建立的连接会等待活跃事务完成。
• OFFLINE_HARD: “硬离线”状态，不再接受新的连接，已建立的连接或被强制中断。当后端实例宕机或网络不可达，会出现。

2，mysql_users表

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,
    comment VARCHAR NOT NULL DEFAULT '',
    PRIMARY KEY (username, backend),
    UNIQUE (username, frontend))
1 row in set (0.00 sec)

连接后端DB的账号密码，使用该信息向后端服务器进行查询或修改操做。

3，mysql_replication_hostgroups

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),
    check_type VARCHAR CHECK (LOWER(check_type) IN ('read_only','innodb_read_only','super_read_only')) NOT NULL DEFAULT 'read_only',
    comment VARCHAR NOT NULL DEFAULT '', UNIQUE (reader_hostgroup))

定义 hostgroup 的主从关系。ProxySQL monitor 模块会监控 HG 后端所有servers 的 read_only 变量，如果发现从库的 read_only 变为0、主库变为1，则认为角色互换了，自动改写 mysql_servers 表里面 hostgroup 关系，达到自动 Failover 效果。

在对mysql_servers 表操做时指定了后端服务器所属的角色，根据这个角色判断该服务器为写还是只读，但是peroxysql,还以后端的read_only 为判断依据，若过read_only为1，表示为从服务器，在mysql_servers里的服务器角色会自动切换为读角色

4，表 mysql_query_rules

ProxySQL非常核心一个表，定义查询路由规则

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 CHECK (flagIN >= 0) NOT NULL DEFAULT 0,
    client_addr VARCHAR,
    proxy_addr VARCHAR,
    proxy_port INT CHECK (proxy_port >= 0 AND proxy_port <= 65535), 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 CHECK (flagOUT >= 0), replace_pattern VARCHAR CHECK(CASE WHEN replace_pattern IS NULL THEN 1 WHEN replace_pattern IS NOT NULL AND match_pattern IS NOT NULL THEN 1 ELSE 0 END),
    destination_hostgroup INT DEFAULT NULL,
    cache_ttl INT CHECK(cache_ttl > 0),
    cache_empty_result INT CHECK (cache_empty_result IN (0,1)) DEFAULT NULL,
    cache_timeout INT CHECK(cache_timeout >= 0),
    reconnect INT CHECK (reconnect IN (0,1)) DEFAULT NULL,
    timeout INT UNSIGNED CHECK (timeout >= 0),
    retries INT CHECK (retries>=0 AND retries <=1000),
    delay INT UNSIGNED CHECK (delay >=0),
    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)),
    gtid_from_hostgroup INT UNSIGNED,
    log INT CHECK (log IN (0,1)),
    apply INT CHECK(apply IN (0,1)) NOT NULL DEFAULT 0,
    comment VARCHAR)

4，proxysql的多层配置设计

ProxySQL采用多层配置的设计来达到以下目的

• 允许在线应用配置项，而不需要重启proxysql
• 使用MySQL接口风格，来操作配置项，自定更新
• 如果配置有误，可以轻易回滚

软件第一次启动时只读取依次配置文件，以后的配置将存储在自身数据内，不在使用配置文件。

+-------------------------+
|         RUNTIME         |
+-------------------------+
       /|\          |
        |           |
    [1] |       [2] |
        |          \|/
+-------------------------+
|         MEMORY          |
+-------------------------+ _
       /|\          |      |\
        |           |        \
    [3] |       [4] |         \ [5]
        |          \|/         \
+-------------------------+  +-------------------------+
|          DISK           |  |       CONFIG FILE       |
+-------------------------+  +-------------------------+

一般目前配置为RUNTIME 运行时，这是当前生效的，，当前修改的配置保存在memeory内，但没有加载到runtime ，不会生效，但是加载到TUNTIME层后，重启服务配置丢失，所以还需要保存配置到DISK.根据以下命令加载

[1]: LOAD MYSQL USERS TO RUNTIME / LOAD MYSQL USERS FROM MEMORY  -- 常用
[2]: SAVE MYSQL USERS TO MEMORY / SAVE MYSQL USERS FROM RUNTIME
[3]: LOAD MYSQL USERS TO MEMORY / LOAD MYSQL USERS FROM DISK
[4]: SAVE MYSQL USERS TO DISK /  SAVE MYSQL USERS FROM MEMORY    -- 常用
[5]: LOAD MYSQL USERS FROM CONFIG

以下命令加载或保存servers:
1]: LOAD MYSQL SERVERS TO RUNTIME  -- 常用，让修改的配置生效
[2]: SAVE MYSQL SERVERS TO MEMORY
[3]: LOAD MYSQL SERVERS TO MEMORY
[4]: SAVE MYSQL SERVERS TO DISK     -- 常用，将修改的配置持久化
[5]: LOAD MYSQL SERVERS FROM CONFIG

以下命令加载或保存query rules:
[1]: load mysql query rules to run    -- 常用
[2]: save mysql query rules to mem
[3]: load mysql query rules to mem
[4]: save mysql query rules to disk   -- 常用
[5]: load mysql query rules from config
以下命令加载或保存 mysql variables:
[1]: load mysql variables to runtime
[2]: save mysql variables to memory
[3]: load mysql variables to memory
[4]: save mysql variables to disk
[5]: load mysql variables from config

5，配置

启动后会监听两个默认端口6032：ProxySQL的管理端口6033：ProxySQL对外提供服务的端口
mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
1,后端主从服务器已配置完全
（slave 设置read_only=1, proxysql 通过其判断写组和读组）

2，proxysql 添加节点
show tables ；
insert into mysql_servers(hostgroup_id,hostname,port)values(10,'192.168.8.17',3306);
insert into mysql_servers(hostgroup_id,hostname,port)values(10,'192.168.8.27',3306);
load mysql servers to runtime;
save mysql servers to disk;

3，添加管理用户（监控后端数据库的read_only值）：
proxysql 通过节监控点的read_only 自动调整读组或写组
在master节点服务器执行（同时slave 也执行了）
grant replication client on *.* to monitor@'192.168.36.%' identified by "" ; #管里用户

进入 proxysql 设置监控用户
set mysql_monitor_username="monitor";
set mysql_monitor_password='';
加载到runtime 并保存到disk
load mysql variables to runtime ;
save mysql variables to disk;

查看监控连接是否正常的 (对connect指标的监控)：(如果connect_error的结果为NULL则表示正常)MySQL> select * from mysql_server_connect_log;
查看监控心跳信息 (对ping指标的监控)：
MySQL> select * from mysql_server_ping_log;
查看read_only和replication_lag的监控日志MySQL> select * from mysql_server_read_only_log;MySQL> select * from mysql_server_replication_lag_log;

4 配置发送sql语句的用户（proxysql 通过此用户在后端执行sql 语句）
master节点操做：
grant all on *.* to sqluser@'' identified by "" ;（因为已经配置好主从复制模式，从库自动添加同样的用户）

proxysql将用户添加至mysql_user 表中，defaults_group默认组设置为写组10 ，当读写分离的路由规则不符合时
会访问默认数据库；

insert into mysql_users(username,password,default_hostgroup)
values('sqluser','redhat',10)
load mysql users to runtime ;
save mysql users to disk ;
username：前端连接ProxySQL，以及ProxySQL将SQL语句路由给MySQL所使用的用户名
password：用户名对应的密码。可以是明文密码，也可以是hash密码。如果想使用hash密码，可以先在某个MySQL节点上执行select password(PASSWORD)，然后将加密结果复制到该字段
default_hostgroup：该用户名默认的路由目标。例如，指定root用户的该字段值为10时，则使用root用户发送的SQL语句默认情况下将路由到hostgroup_id=10组中的某个节点。

5 设置分组信息（确定哪个分组为读，哪个为写）
需要修改的是main库中的mysql_replication_hostgroups表，该表有3个字段：writer_hostgroup，reader_hostgroup，comment, 指定写组的id为10，读组的id为20
insert into mysql_replication_hostgroups（writer_hostgroup ，reader_hostgroup） values(10,20,"test");
将mysql_replication_hostgroups表的修改加载到RUNTIME生效
MySQL> load mysql servers to runtime;
MySQL> save mysql servers to disk;
Monitor模块监控后端的read_only值，按照read_only的值将节点自动移动到读/写组
select hostgroup_id,hostname,port,status,weight from mysql_servers;

6 使用用户测试是否能够路由至默认的10 组实现读写
mysql -usqluser -p -P6033 -h127.0.0.1 -e'SELECT @@server_id'
mysql -usqluser -p -P6033 -h127.0.0.1 -e'select databases testdb'
mysql -usqluser -p -P6033 -h127.0.0.1 -e'create table t(id int )'

7 配置路由规则，实现读写分离
插入路由规则：将select语句分离到20的读组，select语句中有一个特殊语句SELECT...FOR UPDATE它会申请写锁，应路由到10的写组
MySQL> insert into mysql_query_rules
(rule_id,active,match_digest,destination_hostgroup,apply)VALUES
(1,1,'^SELECT.*FOR UPDATE$',10,1),(2,1,'^SELECT',20,1);
#比较严格的转发语句放前面
MySQL> load mysql query rules to runtime;
MySQL> save mysql query rules to disk;
因ProxySQL根据rule_id顺序进行规则匹配，select ... for update规则的rule_id必须要小于普通的select规则的rule_id

以事务方式测试：
mysql -usqluser -pmagedu -P6033 -h127.0.0.1 \-e 'start transaction;select @@server_id;commit;select @@server_id'
mysql -usqluser -pmagedu -P6033 -h127.0.0.1 -e 'insert testdb.t values (1)'mysql -usqluser -pmagedu -P6033 -h127.0.0.1 -e 'select id from testdb.t'

路由的信息：查询stats库中的stats_mysql_query_digest表
MySQL > SELECT hostgroup hg,sum_time, count_star, digest_textFROM stats_mysql_query_digest ORDER BY sum_time DESC;

管理后端节点：

管理后端节点有几个过程：
1. 将后端MySQL Server的主从复制结构配置好。

2. 将后端MySQL Server节点加入到ProxySQL中。相关的表为mysql_servers。

3. 监控后端节点。相关的表是全局变量表global_vairbles，相关的变量为mysql-monitor_开头的变量。

4. 配置读、写组。相关的表为mysql_replication_hostgroups。

5. 配置MySQL用户，用于发送SQL语句。相关的表为mysql_users。
几个注意点：
• (1).ProxySQL是通过监控后端节点的read_only值来自动调整节点所属组的，例如read_only=1的节点会移动到读组，read_only=0的节点会移动到写组。所以，在配置读、写组之前，需要先监控后端节点。ProxySQL也支持手动管理后端节点，这种模式不会根据read_only的值自动调整，在后面的文章中会介绍这种模式。

• (2).对于传统的主从复制，默认的read_only=0，所以在第一步中，各slave节点的配置文件中需要加上read_only=1。对于组复制、Galera，因为会自动强制设置非写节点的read_only=1，所以无需额外配置该属性。

• (3).ProxySQL支持传统主从复制结构(即异步、半同步、gtid复制)的后端，读、写组相关的表是mysql_replication_hostgroups。还支持MySQL组复制结构的后端，相关的表是mysql_group_replication_hostgroups，还支持Galera(如percona XtraDB cluster)结构的后端，不过ProxySQL是通过scheduler调度proxysql_galera_checker.sh脚本来支持Galera的，而且目前还没有mysql_galera_hostgroups(ProxySQL 2.0才新增该表)。

posted @ 2019-05-12 14:54 Lust4Life 阅读(393) 评论(0) 收藏举报

刷新页面返回顶部

Loading

𝖑𝖚𝖘𝖙4𝖑𝖎𝖋𝖊