MySQL ProxySQL读写分离使用初探

目的

      在美团点评DBProxy读写分离使用说明文章中已经说明了使用目的，本文介绍ProxySQL的使用方法以及和DBProxy的性能差异。具体的介绍可以看官网的相关说明，并且这个中间件也是percona推的一款中间件。其特性和其他读写分离的中间件差距不大，具体的会在文中介绍。本文大致简单的介绍在使用过程中的一些说明，也可以看官方的wiki获得使用帮助。

环境： 

Distributor ID：    Ubuntu
Description：    Ubuntu 14.04.5 LTS
Release：    14.04
Codename：    trusty

下载

percona站点：

https：//www.percona.com/downloads/proxysql/

github/官网：

https：//github.com/sysown/proxysql/releases

安装

1）编译安装：

1：下载
wget https：//github.com/sysown/proxysql/archive/v1.3.6.tar.gz

2：安装依赖包
apt-get install automake bzip2 cmake make g++ gcc git openssl debconf-utils

3：解压、编译
make
make install
...
 Adding system startup for /etc/init.d/proxysql ...
   /etc/rc0.d/K20proxysql -> ../init.d/proxysql
   /etc/rc1.d/K20proxysql -> ../init.d/proxysql
   /etc/rc6.d/K20proxysql -> ../init.d/proxysql
   /etc/rc2.d/S20proxysql -> ../init.d/proxysql
   /etc/rc3.d/S20proxysql -> ../init.d/proxysql
   /etc/rc4.d/S20proxysql -> ../init.d/proxysql
   /etc/rc5.d/S20proxysql -> ../init.d/proxysql

4：修改非root启动
添加用户：
useradd proxysql
修改相关文件权限：
cd /var/lib/
chown -R proxysql.proxysql proxysql/
cd /etc/
chown -R proxysql.proxysql proxysql.cnf 
修改启动命令
vi /etc/init.d/proxysql ->75行
proxysql $OPTS
改成
sudo -u proxysql /usr/bin/proxysql $OPTS

5：启动&关闭
/etc/init.d/proxysql start
/etc/init.d/proxysql stop

2）deb包安装：

1：下载
wget https：//github.com/sysown/proxysql/releases/download/v1.3.6/proxysql_1.3.6-dbg-ubuntu14_amd64.deb

2：安装
dpkg -i proxysql_1.3.5-1.1.trusty_amd64.deb

3：非root用户启动，参考编译安装部分。

文件说明

通过启动文件 /etc/init.d/proxysql里可以知道ProxySQL安装好的数据目录在 /var/lib/proxysql/，配置文件目录在 /etc/proxysql.cnf。

root@proxysql：/var/lib/proxysql# pwd
/var/lib/proxysql
root@proxysql：/var/lib/proxysql# ls -lh
total 48K
-rw------- 1 proxysql proxysql  33K  5月  9 11：52 proxysql.db
-rw------- 1 proxysql proxysql 7.4K  5月  9 11：56 proxysql.log

root@proxysql：~# ls -lh /etc/proxysql.cnf 
-rw------- 1 proxysql proxysql 4.2K  5月  9 11：45 /etc/proxysql.cnf

启动之后，可以看到这些文件：proxysql.db是SQLITE的数据文件，proxysql配置，如后端数据库的账号、密码、路由等存储在这个数据库里面，proxysql.log是日志文件。proxysql.pid这个pid文件。proxysql.cnf是ProxySQL的一些静态配置项，比如一些启动选项，sqlite的数据目录等等。配置文件只在第一次启动的时候读取进行初始化，后面只读取db文件。

进程说明

root@proxysql：~# ps -ef | grep proxysql
proxysql  35740      1  0 12：20 ?        00：00：00 /usr/bin/proxysql -c /etc/proxysql.cnf -D /var/lib/proxysql
proxysql  35741  35740  0 12：20 ?        00：00：00 /usr/bin/proxysql -c /etc/proxysql.cnf -D /var/lib/proxysql

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

接口说明

和DBProxy一样，ProxySQL也是有管理接口和客户端接口，通过配置文件/etc/proxysql.cnf可以看到管理和客户端接口的信息：

admin_variables=
{
        admin_credentials="admin：admin"
        mysql_ifaces="127.0.0.1：6032;/tmp/proxysql_admin.sock"
#       refresh_interval=2000
#       debug=true
}

mysql_variables=
{
        ...
        ...
        interfaces="0.0.0.0：6033;/tmp/proxysql.sock"
        default_schema="information_schema"        ...
        ...
}

看到管理接口的端口是6032，账号密码是admin(可以动态修改)只能通过本地连接，客户端接口的端口是6033，账号密码通过管理接口去设置。

库、表说明

通过管理接口登录，需要先装好mysql客户端，并且设置prompt

[client]
prompt      = \\u@\\h ： \\d \\r：\\m：\\s>

通过管理接口进入ProxySQL

库：

root@proxysql：~# mysql -uadmin -padmin -h127.0.0.1 -P6032
...
...
admin@127.0.0.1 ： (none) 12：43：08>show databases;
+-----+---------+-------------------------------+
| seq | name    | file                          |
+-----+---------+-------------------------------+
| 0   | main    |                               |
| 2   | disk    | /var/lib/proxysql/proxysql.db |
| 3   | stats   |                               |
| 4   | monitor |                               |
+-----+---------+-------------------------------+
4 rows in set (0.00 sec)
  • main 内存配置数据库，表里存放后端db实例、用户验证、路由规则等信息。表名以 runtime_开头的表示proxysql当前运行的配置内容，不能通过dml语句修改，只能修改对应的不以 runtime_ 开头的（在内存）里的表，然后 LOAD 使其生效， SAVE 使其存到硬盘以供下次重启加载。
  • disk 是持久化到硬盘的配置，sqlite数据文件。
  • stats 是proxysql运行抓取的统计信息，包括到后端各命令的执行次数、流量、processlist、查询种类汇总/执行时间等等。
  • monitor 库存储 monitor 模块收集的信息，主要是对后端db的健康/延迟检查。

表

1）main库：

admin@127.0.0.1 ： (none) 02：01：54>show tables from main;
+--------------------------------------+
| tables                               |
+--------------------------------------+
| global_variables                     |
| mysql_collations                     |
| mysql_query_rules                    |
| mysql_replication_hostgroups         |
| mysql_servers                        |
| mysql_users                          |
| runtime_global_variables             |
| runtime_mysql_query_rules            |
| runtime_mysql_replication_hostgroups |
| runtime_mysql_servers                |
| runtime_mysql_users                  |
| runtime_scheduler                    |
| scheduler                            |
+--------------------------------------+
13 rows in set (0.00 sec)

global_variables 设置变量，包括监听的端口、管理账号等，详细可参考 https：//github.com/sysown/proxysql/wiki/Global-variables。通过set来设置，如：

set mysql-monitor_ping_interval =10001;

mysql_collations：相关字符集和校验规则。
mysql_query_rules：定义查询路由规则。

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,
    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,
    mirror_flagOUT INT UNSIGNED,
    mirror_hostgroup INT UNSIGNED,
    error_msg VARCHAR,
    log INT CHECK (log IN (0,1)),
    apply INT CHECK(apply IN (0,1)) NOT NULL DEFAULT 0,
    comment VARCHAR) 
  • rule_id： 表主键，自增。规则处理是以 rule_id 的顺序进行。
  • active： 只有 active=1 的规则才会参与匹配。
  • username： 如果非 NULL，只有连接用户是 username 的值才会匹配
  • schemaname： 如果非 NULL，只有查询连接使用的db是 schemaname 的值才会匹配。注意如果是 NULL，不代表连接没有使用schema，而是不伦任何schema都进一步匹配。
  • client_addr： 匹配客户端来源IP
  • proxy_addr, proxy_port： 匹配本地proxysql的IP、端口。
  • digest： 精确的匹配一类查询。
  • match_digest： 正则匹配一类查询。query digest 是指对查询去掉具体值后进行“模糊化”后的查询。
  • match_pattern： 正则匹配查询。
  • negate_match_pattern： 反向匹配，相当于对 match_digest/match_pattern 的匹配取反。
  • re_modifiers： 修改正则匹配的参数，比如默认的：忽略大小写CASELESS、禁用GLOBAL。
    上面都是匹配规则，下面是匹配后的行为：
  • replace_pattern： 查询重写，默认为空，不rewrite。
  • destination_hostgroup： 路由查询到这个 hostgroup。当然如果用户显式 start transaction 且 transaction_persistent=1，那么即使匹配到了，也依然按照事务里第一条sql的路由规则去走。
  • cache_ttl： 查询结果缓存的毫秒数。proxysql这个 Query Cache 与 MySQL 自带的query cache不是同一个。proxysql query cache也不会关心后端数据是否被修改，它所做的就是针对某些特定种类的查询结果进行缓存，比如一些历史数据的count结果。一般不设。
  • timeout： 这一类查询执行的最大时间（毫秒），超时则自动kill。这是对后端DB的保护机制，默认mysql-default_query_timeout给的是10h。
  • retries： 语句在执行时失败时，重试次数。默认由 mysql-query_retries_on_failure变量指定，默认为1 。
  • delay： 查询延迟执行，这是ProxySQL提供的限流机制，会让其它的查询优先执行。
  • error_msg： 默认为NULL，如果指定了则这个查询直接被 block 掉，马上返回这个错误信息。这个功能也很实用，比如线上突然冒出一个 “坏查询”，应用端不方便马上发版解决，我们就可以在这配置一个规则，把查询屏蔽掉，想正常的mysql报错那样抛异常。
  • multiplex： 连接是否复用。
  • log： 是否记录查询日志。
  • flagIN，flagOUT，apply
    • flagIN, flagOUT, apply： 用来定义路由链 chains of rules
    • 首先会检查 flagIN=0 的规则，以rule_id的顺序；如果都没匹配上，则走这个用户的 default_hostgroup
    • 当匹配一条规则后，会检查 flagOUT
    • 如果不为NULL，并且 flagIN != flagOUT ，则进入以flagIN为上一个flagOUT值的新规则链
    • 如果不为NULL，并且 flagIN = flagOUT，则应用这条规则
    • 如果为NULL，或者 apply=1，则结束，应用这条规则
    • 如果最终没有匹配到，则找到这个用户的 default_hostgroup

mysql_replication_hostgroups：监视指定主机组中所有服务器的read_only值，并且根据read_only的值将服务器分配给写入器或读取器主机组

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,
    UNIQUE (reader_hostgroup))

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

mysql_servers：设置后端MySQL的表

CREATE TABLE mysql_servers (
    hostgroup_id INT 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) )
  • hostgroup_id： ProxySQL通过 hostgroup (下称HG) 的形式组织后端db实例。一个 HG 代表同属于一个角色
    该表的主键是 (hostgroup_id, hostname, port)，可以看到一个 hostname：port 可以在多个hostgroup里面，这样可以避免从库全都不可用时，依然可以把读请求发到主库上。一个 HG可以有多个实例，即多个从库，可以通过 weight 分配权重。hostgroup_id 0 是一个特殊的HG，路由查询的时候，没有匹配到规则则默认选择 HG 0
  • status：
    ONLINE： 当前后端实例状态正常
    SHUNNED： 临时被剔除，可能因为后端 too many connections error，或者超过了可容忍延迟阀值 max_replication_lag
    OFFLINE_SOFT： “软离线”状态，不再接受新的连接，但已建立的连接会等待活跃事务完成。
    OFFLINE_HARD： “硬离线”状态，不再接受新的连接，已建立的连接或被强制中断。当后端实例宕机或网络不可达，会出现。
  • max_connections： 允许连接到该后端实例的最大连接数。不要大于MySQL设置的 max_connections，如果后端实例 hostname：port 在多个 hostgroup 里，以较大者为准，而不是各自独立允许的最大连接数。
  • max_replication_lag： 允许的最大延迟，主库不受这个影响，默认0。如果 > 0， monitor 模块监控主从延迟大于阀值时，会临时把它变为 SHUNNED
  • max_latency_ms： mysql_ping 响应时间，大于这个阀值会把它从连接池剔除（即使是ONLINE），默认0。
  • comment： 备注。

mysql_users：配置后端数据库的账号和监控的账号

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 0,
    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))
  • username, password： 连接后端db的用户密码。
    这个密码你可以插入明文，也可以插入hash加密后的密文，proxysql会检查你插入的时候密码是否以 * 开头来判断，而且密文要在其它地方使用 PASSWORD()生成。但到 runtime_mysql_users 里，都统一变成了密文所以可以明文插入，再 SAVE MYSQL USERS TO MEM，此时看到的也是HASH密文。
  • active： 是否生效该用户。
  • default_hostgroup： 这个用户的请求没有匹配到规则时，默认发到这个 hostgroup，默认0
  • default_schema： 这个用户连接时没有指定 database name 时，默认使用的schema注意表面上看默认为NULL，但实际上受到变量 mysql-default_schema 的影响，默认为 information_schema。
  • transaction_persistent： 如果设置为1，连接上ProxySQL的会话后，如果在一个hostgroup上开启了事务，那么后续的sql都继续维持在这个hostgroup上，不伦是否会匹配上其它路由规则，直到事务结束。虽然默认是0。
  • frontend, backend： 目前版本这两个都需要使用默认的1，将来有可能会把 Client -> ProxySQL (frontend) 与 ProxySQL -> BackendDB (backend)的认证分开。从 runtime_mysql_users 表内容看到，记录数比 mysql_users 多了一倍，就是把前端认证与后端认证独立出来的结果。
  • fast_forward： 忽略查询重写/缓存层，直接把这个用户的请求透传到后端DB。相当于只用它的连接池功能，一般不用，路由规则 .* 就行了。

scheduler：调度器是一个类似于cron的实现，集成在ProxySQL中，具有毫秒的粒度。通过脚本检测来设置ProxySQL。

 CREATE TABLE scheduler (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
    interval_ms INTEGER CHECK (interval_ms>=100 AND interval_ms<=100000000) NOT NULL,
    filename VARCHAR NOT NULL,
    arg1 VARCHAR,
    arg2 VARCHAR,
    arg3 VARCHAR,
    arg4 VARCHAR,
    arg5 VARCHAR,
    comment VARCHAR NOT NULL DEFAULT '')
  • id ： 调度器唯一标识，自增主键。
  • active ： 是否开启，默认开启。
  • interval_ms ： 工作的开始频率（以毫秒为单位）。 最小interval_ms为100毫秒
  • filename ： 执行的可执行文件的绝对路径
  • arg1 to arg5 ： 传递给执行文件的参数，最大允许5个参数
  • comment ： 备注

关于main库下的表的说明可以看这篇文章的说明：http：//seanlook.com/2017/04/10/mysql-proxysql-install-config/ 

2）disk库：

show tables from disk;
+------------------------------+
| tables                       |
+------------------------------+
| global_variables             |
| mysql_collations             |
| mysql_query_rules            |
| mysql_replication_hostgroups |
| mysql_servers                |
| mysql_users                  |
| scheduler                    |
+------------------------------+
7 rows in set (0.00 sec)

和上面介绍的main库一样。

3）stats库：

stats_mysql_commands_counters：统计各种SQL类型的执行次数和时间，通过参数mysql-commands_stats控制开关，默认是ture。

CREATE TABLE stats_mysql_commands_counters (
    Command VARCHAR NOT NULL PRIMARY KEY,
    Total_Time_us INT NOT NULL,
    Total_cnt INT NOT NULL,
    cnt_100us INT NOT NULL,
    cnt_500us INT NOT NULL,
    cnt_1ms INT NOT NULL,
    cnt_5ms INT NOT NULL,
    cnt_10ms INT NOT NULL,
    cnt_50ms INT NOT NULL,
    cnt_100ms INT NOT NULL,
    cnt_500ms INT NOT NULL,
    cnt_1s INT NOT NULL,
    cnt_5s INT NOT NULL,
    cnt_10s INT NOT NULL,
    cnt_INFs)
  • command：已执行的SQL命令的类型。 如：FLUSH，INSERT，KILL，SELECT FOR UPDATE等。
  • Total_Time_us：执行该类型命令的总时间（以微秒为单位）。
  • total_cnt：执行该类型的命令的总数。
  • cnt_100us, cnt_500us, ..., cnt_10s, cnt_INFs：在指定的时间限制内执行的给定类型的命令总数和前一个命令的总数。

stats_mysql_connection_pool：连接后端MySQL的连接信息。

CREATE TABLE stats_mysql_connection_pool (
    hostgroup VARCHAR,
    srv_host VARCHAR,
    srv_port VARCHAR,
    status VARCHAR,
    ConnUsed INT,
    ConnFree INT,
    ConnOK INT,
    ConnERR INT,
    Queries INT,
    Bytes_data_sent INT,
    Bytes_data_recv INT,
    Latency_us INT)
  • hostgroup：后端服务器所属的主机组。请注意，单个后端服务器可以属于多个主机组。
  • srv_host，srv_port：mysqld后端服务器正在侦听连接的TCP端点的IP和Port。
  • status：后端服务器的状态。可以有ONLINE，SHUNNED，OFFLINE_SOFT，OFFLINE_HARD。有关每个状态意味着什么的详细信息，请参阅上面的mysql_servers表的描述。
  • ConnUsed：ProxySQL当前使用多少个连接来向后端服务器发送查询。
  • ConnFree：目前有多少个连接是空闲。
  • ConnOK：成功建立了多少个连接。
  • ConnERR：没有成功建立多少个连接。
  • Queries：路由到此特定后端服务器的查询数。
  • Bytes_data_sent：发送到后端的数据量。
  • Bytes_data_recv：从后端接收的数据量。
  • Latency_ms：从Monitor报告的当前ping以毫秒为单位的延迟时间。

stats_mysql_global：代表与MySQL相关的代理级别的全局统计，如：

  • Client_Connections_aborted：由于无效凭据或max_connections而导致的前端连接数已达到。
  • Client_Connections_connected - 当前连接的前端连接数。
  • Client_Connections_created - 到目前为止创建的前端连接数。
  • Questions ：从前端发送的查询总数。
  • Slow_queries：在全局变量中定义的运行时间超过阈值的毫秒数的查询数mysql-long_query_time
  • 等等...

stats_mysql_processlist：类似MySQL的show processlist的命令，查看各线程的状态。

CREATE TABLE stats_mysql_processlist (
    ThreadID INT NOT NULL,
    SessionID INTEGER PRIMARY KEY,
    user VARCHAR,
    db VARCHAR,
    cli_host VARCHAR,
    cli_port VARCHAR,
    hostgroup VARCHAR,
    l_srv_host VARCHAR,
    l_srv_port VARCHAR,
    srv_host VARCHAR,
    srv_port VARCHAR,
    command VARCHAR,
    time_ms INT NOT NULL,
    info VARCHAR)
  • ThreadID：ProxySQL内线程的内部ID。
  • SessionID：ProxySQL会话ID。通过这个ID可以进行kill操作。
  • user：与MySQL客户端连接到ProxySQL的用户。
  • db：当前选择的数据库。
  • cli_host，cli_port：连接ProxySQL的IP和TCP端口。
  • hostgroup：当前主机组。如果正在处理查询，则是查询已被路由或将要路由的主机组，或默认主机组。可以通过这个查看该SQL到底是到哪个HG里。
  • l_srv_host，l_srv_port：ProxySQL的IP和TCP端口。
  • srv_host，srv_port - 后端MySQL服务器的IP和端口。
  • command：正在执行的MySQL查询的类型。
  • time_ms：命令执行的时间（以毫秒为单位）。
  • info：正在执行的SQL。

stats_mysql_query_digest：表示SQL的执行次数、时间消耗等。通过变量mysql-query_digests控制开关，默认是开。 

stats_mysql_query_digest
Create Table： CREATE TABLE stats_mysql_query_digest (
    hostgroup INT,
    schemaname VARCHAR NOT NULL,
    username VARCHAR NOT NULL,
    digest VARCHAR NOT NULL,
    digest_text VARCHAR NOT NULL,
    count_star INTEGER NOT NULL,
    first_seen INTEGER NOT NULL,
    last_seen INTEGER NOT NULL,
    sum_time INTEGER NOT NULL,
    min_time INTEGER NOT NULL,
    max_time INTEGER NOT NULL,
    PRIMARY KEY(hostgroup, schemaname, username, digest))
  • hostgroup：发送查询的主机组。值-1表示查询查询缓存。
  • schemaname：查询的数据库。
  • user：连接ProxySQL的用户名。
  • digest：一个十六进制散列，表示其参数剥离的SQL。
  • digest_text：参数剥离的实际SQL文本。
  • count_star：执行查询的总次数（参数的值不同）。
  • first_seen：unix时间戳，是通过代理路由查询的第一时刻。
  • last_seen：unix时间戳，当查询通过代理路由时的最后一刻（到目前为止）。
  • sum_time：执行此类查询的总时间（以微秒为单位）。这对于确定应用程序工作负载中花费的最多时间在哪里是非常有用的，并为改进的地方提供了一个良好的起点。
  • min_time，max_time - 执行此类查询时期望的持续时间范围。 min_time是到目前为止所看到的最小执行时间，而max_time表示最大执行时间，以微秒为单位。

stats_mysql_query_digest_reset和stats_mysql_query_digest结构一样，stats_mysql_query_digest_reset可以清空stats_mysql_query_digest的数据，如执行：

select * from stats_mysql_query_digest_reset;
show create table stats.stats_mysql_query_digest_reset;

执行上面的任意一条就可以清空stats_mysql_query_digest表。

stats_mysql_query_rules：路由命中次数统计。

 CREATE TABLE stats_mysql_query_rules (
    rule_id INTEGER PRIMARY KEY,
    hits INT NOT NULL)
  • rule_id：路由规则的ID与main.mysql_query_rules的id对应。
  • hits：此路由规则的匹配总数。 如果当前传入的查询符合规则，则会记录一次命中。

4）monitor库： 对后端MySQL的健康检查。由变量mysql-monitor_enabled来确定是否开启Monitor模块。

  • mysql_server_connect/mysql_server_connect_log：连接到所有MySQL服务器以检查它们是否可用，该表用来存放检测连接的日志。由变量mysql-monitor_connect_interval来控制其检测的时间间隔，由参数mysql-monitor_connect_timeout控制连接是否超时（默认200毫秒）。
  • mysql_server_ping/mysql_server_ping_log：使用mysql_ping API ping后端MySQL服务器检查它们是否可用，该表用来存放ping的日志。由变量mysql-monitor_ping_interval控制ping的时间间隔，默认值：10000（毫秒，相当于10秒）。
  • mysql_server_replication_lag_log：后端MySQL服务主从延迟的检测。由参数mysql-monitor_replication_lag_interval控制检测间隔时间， 如果复制滞后太大，可以暂时关闭从。由mysql_servers.max_replication_lag列控制。默认值：10000（毫秒，相当于10秒）。

配置

ProxySQL有一个完备的配置系统，持配置修改之后的在线保存、应用，不需要重启之后生效。整个配置系统分三层设计。配置系统分为三层的目的有三个:
(1).自动更新
(2).尽可能的不重启proxysql就可以修改配置
(3).方便回滚错误配置
整个配置系统分为三层，如下所示:

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

RUNTIME 代表的是ProxySQL当前生效的正在使用的配置，包括 global_variables, mysql_servers, mysql_users, mysql_query_rules。无法直接修改这里的配置，必须要从下一层load进来。也就是说RUNTIME这个顶级层，就是proxysql运行过程中实际采用的那一份配置，这一份配置就是要影响实际生产的，所以将你的配置加进RUNTIME层时需要三思而行。

MEMORY 用户可以将MySQL客户端连接到此接口（admin接口），并查询不同的表和数据库是在mysql命令行修改的 main 里头配置，可以认为是SQLite数据库在内存的镜像。也就是说MEMORY这个中间层，上面接着生产配置项RUNTIME，下面接着持久化层DISK,CONFIG FILE。MEMORY也是我们修改proxysql的唯一正常入口。一般的，我们修改一个配置，先改Memory，确认无误后再接入生产(RUNTIME)，和持久化到磁盘(DISK)。也就是说memeory里面的配置随便改，不影响生产，也不影响磁盘中保存的数据。通过此接口可以修改mysql_servers、mysql_users、mysql_query_rules、global_variables等。

DISK / CONFIG FILE 持久存储的那份配置，一般在$(DATADIR)/proxysql.db，在重启的时候会从硬盘里加载。 /etc/proxysql.cnf文件只在第一次初始化的时候用到，完了后，如果要修改监听端口，还是需要在管理命令行里修改，再 save 到硬盘。 也就是说DISK和CONFIG FILE这一层是持久化层，我们做的任何配置更改，如果不持久化下来，重启后，配置都将丢失。

说明：proxysql的每一个配置项在三层中都存在，但是这三层是独立的，也就是说，proxysql可以同时拥有三份配置，每层都是独立的，可能三份都不一样，可能三份都一样。

当proxysql启动时，首先读取配置文件CONFIG FILE(/etc/proxysql.cnf)，然后从该配置文件中获取datadir，datadir中存储的是sqlite的数据目录。如果该目录存在，且sqlite数据文件存在，那么正常启动，将sqlite中的配置项读进内存，并且加载进RUNTIME，用于初始化proxysql的运行。如果datadir目录下没有sqlite的数据文件，proxysql就会使用config file中的配置来初始化proxysql，并且将这些配置保存至数据库。

修改配置：需要修改配置时，直接操作的是 MEMORAY（main）

以下命令可用于加载或保存 users（mysql_users）： （序号对应上图）

[1]: LOAD MYSQL USERS TO RUNTIME / LOAD MYSQL USERS FROM MEMORY --将修改后的配置(在memory层)用到实际生产
[2]: SAVE MYSQL USERS TO MEMORY / SAVE MYSQL USERS FROM RUNTIME --将生产配置拉一份到memory中
[3]: LOAD MYSQL USERS TO MEMORY / LOAD MYSQL USERS FROM DISK    --将磁盘中持久化的配置拉一份到memory中来
[4]: SAVE MYSQL USERS TO DISK / SAVE MYSQL USERS FROM MEMORY    --将memoery中的配置保存到磁盘中去
[5]: LOAD MYSQL USERS FROM CONFIG                               --将配置文件中的配置加载到memeory中

以下命令加载或保存servers（mysql_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（mysql_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（global_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

以下命令加载或保存admin variables（select * from global_variables where variable_name like 'admin-%'）:

[1]: load admin variables to runtime
[2]: save admin variables to memory
[3]: load admin variables to memory
[4]: save admin variables to disk
[5]: load admin variables from config

到此，PrxoySQL的说明已经大致介绍完，更多的信息可以看官方文档介绍和官方wiki说明，后面接着开始测试ProxySQL的相关功能。

测试说明

1）读写分离

环境：

APP：192.168.200.25、192.168.200.64
DB：
   M：192.168.200.202
   S：192.168.200.132
ProxySQL：192.168.200.24

① 配置后端MySQL。登入ProxySQL，把MySQL主从的信息添加进去。将主库master也就是做写入的节点放到HG 100中，salve节点做读放到HG 1000。

admin@127.0.0.1 : (none) 12:37:05>insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(100,'192.168.200.202',3306,1,1000,10,'test proxysql');
Query OK, 1 row affected (0.00 sec)

admin@127.0.0.1 : (none) 12:37:13>insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(1000,'192.168.200.132',3306,1,1000,10,'test proxysql');
Query OK, 1 row affected (0.00 sec)

admin@127.0.0.1 : (none) 12:37:19>select * from mysql_servers;     
+--------------+-----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------------+
| hostgroup_id | hostname        | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment       |
+--------------+-----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------------+
| 100          | 192.168.200.202 | 3306 | ONLINE | 1      | 0           | 1000            | 10                  | 0       | 0              | test proxysql |
| 1000         | 192.168.200.132 | 3306 | ONLINE | 1      | 0           | 1000            | 10                  | 0       | 0              | test proxysql |
+--------------+-----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------------+
2 rows in set (0.00 sec)

② 配置后端MySQL用户。这个用户需要先在后端MySQL(202,132)里真实存在，一个是监控账号、一个是程序账号：

-- 监控
dba@192.168.200.202 : 12:38:21>GRANT USAGE ON *.* TO 'proxysql'@'192.168.200.24' IDENTIFIED BY 'proxysql';
Query OK, 0 rows affected (0.00 sec)

-- 程序
dba@192.168.200.202 : 12:38:22>GRANT SELECT, INSERT, UPDATE, DELETE ON `sbtest`.* TO 'sbuser'@'192.168.200.24' identified by 'sbuser';
Query OK, 0 rows affected (0.00 sec)

在后端MySQL里添加完之后再配置ProxySQL：这里需要注意，default_hostgroup需要和上面的对应。

admin@127.0.0.1 : (none) 12:39:52>insert into mysql_users(username,password,active,default_hostgroup,transaction_persistent) values('sbuser','sbuser',1,100,1);
Query OK, 1 row affected (0.00 sec)

admin@127.0.0.1 : (none) 12:41:07>select * from mysql_users;
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| sbuser   | sbuser   | 1      | 0       | 100               | NULL           | 0             | 1                      | 0            | 1       | 1        | 10000           |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
1 row in set (0.00 sec)

上面看到是一个明文密码，也可以用password函数加密后的密码进行代替。

③ 设置健康监测账号：

admin@127.0.0.1 : (none) 12:42:38>UPDATE global_variables SET variable_value='proxysql' WHERE variable_name='mysql-monitor_username';
Query OK, 1 row affected (0.00 sec)

admin@127.0.0.1 : (none) 12:42:44>UPDATE global_variables SET variable_value='proxysql' WHERE variable_name='mysql-monitor_password';
Query OK, 1 row affected (0.00 sec)

或则还行下面的：

admin@127.0.0.1 : (none) 12:44:24>set mysql-monitor_username='proxysql';
Query OK, 1 row affected (0.01 sec)

admin@127.0.0.1 : (none) 12:45:14>set mysql-monitor_password='proxysql';
Query OK, 1 row affected (0.01 sec)

其他变量的修改，登入管理接口按照需要可以自行调整： 

set mysql-default_charset='utf8mb4';
set mysql-query_retries_on_failure=0;
set mysql-ping_timeout_server=500;
set mysql-monitor_connect_timeout=1000;
set mysql-default_max_latency_ms=2000;
set mysql-monitor_replication_lag_interval=500;
set mysql-ping_interval_server_msec=3000;
set mysql-monitor_ping_interval=5000;
set mysql-connect_timeout_server_max=3000;

④ 加载配置和变量：因为修改了servers、users和variables，所以加载的时候要执行： 

-- 应用到线上
admin@127.0.0.1 : (none) 12:51:28>load mysql servers to runtime;
Query OK, 0 rows affected (0.00 sec)

admin@127.0.0.1 : (none) 12:56:27>load mysql users to runtime;
Query OK, 0 rows affected (0.00 sec)

admin@127.0.0.1 : (none) 12:56:31>load mysql variables to runtime;
Query OK, 0 rows affected (0.01 sec)

-- 持久化
admin@127.0.0.1 : (none) 12:56:37>save mysql servers to disk;
Query OK, 0 rows affected (0.01 sec)

admin@127.0.0.1 : (none) 12:56:45>save mysql users to disk;
Query OK, 0 rows affected (0.00 sec)

admin@127.0.0.1 : (none) 12:56:50>save mysql variables to disk;
Query OK, 72 rows affected (0.00 sec)

要是是用明文密码设置mysql_users，在这里可以用save命令来转换成了hash值的密码：

admin@127.0.0.1 : (none) 04:25:23>save mysql users to mem;
Query OK, 0 rows affected (0.00 sec)

admin@127.0.0.1 : (none) 04:25:33>select username,password from mysql_users;
+----------+-------------------------------------------+
| username | password                                  |
+----------+-------------------------------------------+
| sbuser   | *CA96E56547F43610DDE9EB7B12B4EF4C51CDDFFC |
+----------+-------------------------------------------+
1 row in set (0.00 sec)

⑤ 连接数据库，通过proxysql的客户端接口访问(6033)：

~$ mysql -usbuser -psbuser -h192.168.200.24 -P6033
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.33 (ProxySQL)
...
sbuser@192.168.200.24 : (none) 04:41:34>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| sbtest             |
+--------------------+
2 rows in set (0.00 sec)

写入测试数据：

sbuser@192.168.200.24 : sbtest 04:42:35>insert into sb values(1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

sbuser@192.168.200.24 : sbtest 04:44:12>select * from sb;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

sbuser@192.168.200.24 : sbtest 04:44:14>begin;
Query OK, 0 rows affected (0.00 sec)

sbuser@192.168.200.24 : sbtest 04:44:17>insert into sb values(11),(22),(33);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

sbuser@192.168.200.24 : sbtest 04:44:26>commit;
Query OK, 0 rows affected (0.00 sec)

sbuser@192.168.200.24 : sbtest 04:44:29>select * from sb;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|   11 |
|   22 |
|   33 |
+------+
6 rows in set (0.00 sec)

通过proxysql接口正常操作数据，从管理接口看看ProxySQL的统计信息：

-- 查看各类命令的执行情况
admin@127.0.0.1 : (none) 04:52:59>select Command,Total_Time_us,Total_cnt from stats_mysql_commands_counters where Total_cnt >0;
+---------+---------------+-----------+
| Command | Total_Time_us | Total_cnt |
+---------+---------------+-----------+
| BEGIN   | 314           | 1         |
| COMMIT  | 493           | 1         |
| INSERT  | 5021          | 2         |
| SELECT  | 2224          | 9         |
| SHOW    | 4577          | 5         |
+---------+---------------+-----------+
-- 查看各类SQL的执行情况
admin@127.0.0.1 : (none) 04:54:47>select * from stats_mysql_query_digest;

通过上面看到，ProxySQL已经正常启动，但是在stats_mysql_query_digest的hostgroup中发现，读和写全部都是走100这个Master的HG，没有用到从库。主要原因就是ProxySQL的核心mysql_query_rules路由表没有配置。proxysql是通过自定义sql路由规则就可以实现读写分离。

⑥ 定义路由规则，如：除select * from tb for update的select全部发送到slave，其他的的语句发送到master。

-- 发送到M
admin@127.0.0.1 : (none) 04:58:11>INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT.*FOR UPDATE$',100,1);
Query OK, 1 row affected (0.00 sec)
-- 发送到S
admin@127.0.0.1 : (none) 05:08:17>INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT',1000,1);
Query OK, 1 row affected (0.00 sec)

-- 加载
admin@127.0.0.1 : (none) 05:09:37>load mysql query rules to runtime;
Query OK, 0 rows affected (0.00 sec)
admin@127.0.0.1 : (none) 05:09:57>save mysql query rules to disk;
Query OK, 0 rows affected (0.00 sec)

说明：active表示是否启用这个sql路由项，match_pattern就是我们正则匹配项，destination_hostgroup表示我们要将该类sql转发到哪些mysql上面去，apply为1表示该正则匹配后，将不再接受其他匹配，直接转发。具体的信息看上面的表介绍说明。路由规则添加完成，现在来测试下读写分离，先清空stats_mysql_query_digest统计表：

admin@127.0.0.1 : (none) 05:16:21>select * from stats_mysql_query_digest_reset;

写入测试数据： 

sbuser@192.168.200.24 : sbtest 10:20:27>insert into sb values(1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

sbuser@192.168.200.24 : sbtest 10:20:29>select * from sb;
...

sbuser@192.168.200.24 : sbtest 10:20:34>start transaction;
Query OK, 0 rows affected (0.01 sec)

sbuser@192.168.200.24 : sbtest 10:20:41>insert into sb values(11),(22),(33);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

sbuser@192.168.200.24 : sbtest 10:20:46>commit;
Query OK, 0 rows affected (0.00 sec)

sbuser@192.168.200.24 : sbtest 10:20:51>select * from sb;
...

sbuser@192.168.200.24 : sbtest 10:20:56>select * from sb for update;
..

sbuser@192.168.200.24 : sbtest 10:21:11>insert into sb values(111),(222),(333);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

sbuser@192.168.200.24 : sbtest 10:21:20>select * from sb;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|   11 |
|   22 |
|   33 |
|    1 |
|    2 |
|    3 |
|   11 |
|   22 |
|   33 |
|  111 |
|  222 |
|  333 |
+------+
15 rows in set (0.01 sec)

查看统计信息：

select * from stats_mysql_query_digest;

看到读写分离已经成功。为了更直观一点，使用sysbench测试读写分离是否正常：

./bin/sysbench --test=./share/sysbench/oltp_read_write.lua --mysql-host=192.168.200.24 --mysql-port=6033 --mysql-user=sbuser --mysql-password=sbuser --mysql-db=sbtest  --report-interval=10  --max-requests=0 --time=300 --threads=4 --tables=1  --table-size=500000 --skip-trx=on --db-ps-mode=disable --mysql-ignore-errors=1062 prepare/run/clean

为了更好的验证，可以在sysbench运行期间，可以到后端的从MySQL上查看是否有查询进来。执行完之后来看下统计表：

admin@127.0.0.1 : (none) 10:31:52>select hostgroup,schemaname,username,substr(digest_text,120,-120),count_star from stats_mysql_query_digest;
+-----------+------------+----------+--------------------------------------------------------------------+------------+
| hostgroup | schemaname | username | substr(digest_text,120,-120)                                       | count_star |
+-----------+------------+----------+--------------------------------------------------------------------+------------+
| 100       | sbtest     | sbuser   | show processlist                                                   | 15         |
| 100       | sbtest     | sbuser   | INSERT INTO sbtest1 (id, k, c, pad) VALUES (?, ?, ?, ?)            | 123932     |
| 100       | sbtest     | sbuser   | DELETE FROM sbtest1 WHERE id=?                                     | 123932     |
| 100       | sbtest     | sbuser   | UPDATE sbtest1 SET c=? WHERE id=?                                  | 123932     |
| 100       | sbtest     | sbuser   | UPDATE sbtest1 SET k=k+? WHERE id=?                                | 123932     |
| 1000      | sbtest     | sbuser   | SELECT SUM(k) FROM sbtest1 WHERE id BETWEEN ? AND ?                | 123932     |
| 100       | sbtest     | sbuser   | show tables                                                        | 1          |
| 100       | sbtest     | sbuser   | select * from sb for update                                        | 1          |
| 100       | sbtest     | sbuser   | start transaction                                                  | 1          |
| 1000      | sbtest     | sbuser   | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ? ORDER BY c          | 123932     |
| 1000      | sbtest     | sbuser   | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?                     | 123932     |
| 1000      | sbtest     | sbuser   | SELECT c FROM sbtest1 WHERE id=?                                   | 1239320    |
| 1000      | sbtest     | sbuser   | select * from sb                                                   | 3          |
| 1000      | sbtest     | sbuser   | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ? ORDER BY c | 123932     |
| 100       | sbtest     | sbuser   | drop table sbtest1                                                 | 1          |
| 100       | sbtest     | sbuser   | commit                                                             | 1          |
| 100       | sbtest     | sbuser   | insert into sb values(?),(?),(?)                                   | 3          |
+-----------+------------+----------+--------------------------------------------------------------------+------------+

从上面的结果可知，路由规则已经生效，select语句均到从库上执行了。

总结：

本文初步介绍了ProxySQL的安装、相关表说明和读写分离。 后续还有ProxySQL其本身的特性说明：延迟读取主库、重写查询以及和DBProxy对比等，由于篇幅原因，放到下一篇文章里介绍。

参考文档：

https://github.com/sysown/proxysql/wiki

http://proxysql.blogspot.jp/2015/09/proxysql-tutorial-setup-in-mysql.html

ProxySQL之安装及配置详解  推荐

ProxySQL之读写分离与分库路由演示 推荐

MySQL中间件之ProxySQL安装部署篇

MySQL中间件之ProxySQL_读写分离/查询重写配置

 

posted @ 2017-05-10 22:54  jyzhou  阅读(24405)  评论(1)  编辑  收藏  举报