骏马金龙 (新博客：www.junmajinlong.com)

网名骏马金龙，钟情于IT世界里的各种原理和实现机制，强迫症重症患者。爱研究、爱翻译、爱分享。特借此一亩三分田记录自己成长点滴！！！

MySQL中间件之ProxySQL(6)：管理后端节点

返回ProxySQL系列文章：http://www.cnblogs.com/f-ck-need-u/p/7586194.html

 

1.配置后端节点前的说明

为了让ProxySQL能够找到后端的MySQL节点，需要将后端的MySQL Server加入到ProxySQL中。ProxySQL的一切配置行为都是在修改main库中的对应的表，所以添加节点到ProxySQL中实际上也是通过修改相关表来实现的。

管理后端节点有几个过程：

  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才新增该表)。

本文暂时只解释mysql_servers和mysql_replication_hostgroups，组复制相关的表在在后面介绍"ProxySQL+组复制"的文章中再介绍。

完成了上面的过程后，节点就一切正常了，然后就可以去配置ProxySQL的路由规则、查询缓存、SQL语句重写等功能。这些在后面的文章会详细介绍。

在开始本文下面的配置之前，请确保已经明白如何使用Admin管理接口，以及ProxySQL的多层配置系统。

2.添加新节点：mysql_servers表

假如后端有3个节点，使用的是传统的异步复制(1 master, 2 slave)。这3个节点的IP地址为：

192.168.100.22
192.168.100.23
192.168.100.24

要加入3个后端MySQL节点，只需向mysql_servers表插入3行对应的记录即可。以下是使用了一大堆默认值的insert语句：

insert into mysql_servers(hostgroup_id,hostname,port) 
values(10,'192.168.100.22',3306);

insert into mysql_servers(hostgroup_id,hostname,port) 
values(10,'192.168.100.23',3306);

insert into mysql_servers(hostgroup_id,hostname,port) 
values(10,'192.168.100.24',3306);

load mysql servers to runtime;
save mysql servers to disk;

上面的insert语句表示将后端MySQL节点192.168.100.xx:3306加入到hostgroup_id=10的组中。

其实mysql_servers表有很多字段，很多字段都有默认值，正如上面的insert语句，除了3个字段，其它使用的全是字段的默认值。

以下是mysql_servers表的字段属性。

|       字段          | 数据类型  | 允许null  | 默认值 |
|---------------------|----------|-----------|--------|
| hostgroup_id  (pk)  | int      | not null  | 0      |
| hostname      (pk)  | varchar  | not null  | 无     |
| port          (pk)  | int      | not null  | 3306   |
| status              | varchar  | not null  | online |
| weight              | int      | not null  | 1      |
| compression         | int      | not null  | 0      |
| max_connections     | int      | not null  | 1000   |
| max_replication_lag | int      | not null  | 0      |
| use_ssl             | int      | not null  | 0      |
| max_latency_ms      | int      | not null  | 0      |
| comment             | varchar  | not null  | ''     |

可见，新添加一个节点时，唯一必须指定的字段就是hostname值。如果不指定hostgroup_id，那么节点将自动加入到hostgroup_id=0的组中。

各字段的意义如下：

  • hostgroup_id：该后端MySQL实例所在的主机组。注意，同一MySQL节点可属于多个主机组。
  • hostname,port：后端MySQL监听的地址和端口。就是默认3306端口的意义
  • status：
    • ONLINE：该后端MySQL节点完全正常。
    • SHUNNED：该后端MySQL节点将暂时被ProxySQL自动避开(忽略)，原因可能是一个较短时间内发生了大量连接错误，也可能是该slave与master的数据延迟太大(replication lag)。
    • OFFLINE_SOFT：当某后端MySQL被设置为 OFFLINE_SOFT 时，ProxySQL将不会向它发送新的请求，但该节点正在执行的事务会继续执行，直到所有事务执行完毕后会进入非激活状态。也就是说，和该后端的连接会保持到事务执行完毕。这可以实现后端节点的graceful停止、重启。
    • OFFLINE_HARD：当某后端MySQL被设置为 OFFLINE_HARD 时，ProxySQL将不会向它发送新的请求，该节点正在执行的事务也会立即中断。也就是直接将该后端杀掉。等价于删除该节点，或临时将其移除出组(例如出于维护的目的)。
  • weight：节点在组中的权重值越高，ProxySQL会发送越多请求给它们。
  • compression：如果该字段的值设置为大于0，ProxySQL和该后端新建的连接中，ProxySQL将会先压缩数据再传输。
  • max_connections：和该后端允许建立的最大连接数。当达到最大数量时，即使该后端的权重很大，也不会和它新建连接。默认值为1000，表示每个后端最多能同时接受1000个连接。请确保该后端的max_connections值是合理的，以避免MySQL超负荷时ProxySQL继续向其发送请求。
  • max_replication_lag：如果值大于0，ProxySQL的Monitor模块将会定期检查该slave的复制是否延后于master，如果延迟的值大于该字段的值，ProxySQL将会暂时避开该节点，直到该slave赶上master。
  • use_ssl：如果设置为1，则和该后端MySQL建立SSL连接。
  • max_latency_ms：Monitory模块定期向该后端发起ping检查，如果该节点的ping时间大于该字段的值，则将其排除在连接池之外(尽管该节点仍处于ONLINE状态)。
  • comment：该表的说明信息，可随便定义。

关于该表各字段的用法，请参见官方手册(我已翻译)。

以下是上面插入数据成功后的结果：

admin> select * from mysql_servers\G
*************************** 1. row ***************************
       hostgroup_id: 10
           hostname: 192.168.100.22
               port: 3306
             status: ONLINE
             weight: 1
        compression: 0
    max_connections: 1000
max_replication_lag: 0
            use_ssl: 0
     max_latency_ms: 0
            comment: 
*************************** 2. row ***************************
       hostgroup_id: 10
           hostname: 192.168.100.23
               port: 3306
             status: ONLINE
             weight: 1
        compression: 0
    max_connections: 1000
max_replication_lag: 0
            use_ssl: 0
     max_latency_ms: 0
            comment: 
*************************** 3. row ***************************
       hostgroup_id: 10
           hostname: 192.168.100.24
               port: 3306
             status: ONLINE
             weight: 1
        compression: 0
    max_connections: 1000
max_replication_lag: 0
            use_ssl: 0
     max_latency_ms: 0
            comment:

需要注意的是，同一个节点是可以同时存在于多个组中的。最典型的情形是master既充当写节点，也充当读节点，这时它就同时存在于写组和读组。

3.监控后端节点

ProxySQL通过Monitor模块监控后端MySQL Server的read_only值来自动调整节点所属的组。所以，在配置读、写组之前，必须先配置好监控。

本文只简单介绍该模块的监控类型，以及如何配置监控。对于Monitor模块理论的详细介绍，请参见以下两篇文章：

  1. Monitor模块官方手册翻译：关于Monitor模块和相关变量。
  2. Monitor模块使用的线程池、连接池

3.1 Monitor模块可以监控什么东西

首先看下Monitor库中的表：

admin> show tables from monitor;
+------------------------------------+
| tables                             |
+------------------------------------+
| mysql_server_connect_log           |
| mysql_server_group_replication_log |
| mysql_server_ping_log              |
| mysql_server_read_only_log         |
| mysql_server_replication_lag_log   |
+------------------------------------+

Monitor监控4种指标：connect、ping、read_only和replication lag。下面稍作介绍。

1.connect监控

ProxySQL连接到各后端是否成功，成功/失败的连接将记录到表mysql_server_connect_log中。

2.ping监控

这是一种心跳检测。Monitor模块向所有后端MySQL节点发起ping检查，ping成功/失败的情况将记录到表mysql_server_ping_log中。当ping某后端的失败次数达到了mysql-monitor_ping_max_failures时表示失去心跳，将发送一个信号给MySQL的主机组管理器来杀掉和该后端节点的所有连接。

请和connect监控区分开，connect监控是通过建立连接来检测和后端节点连接的连通性。ping监控是心跳检测，ProxySQL通过MySQL的一个ping API发送给后端MySQL服务上，然后等待ping回复，虽然是ping检测，但也是需要建立连接的。

所以，有两个确定连接不可用公式：

  • mysql-monitor_ping_max_failures * mysql-monitor_connect_timeout
  • mysql-monitor_ping_max_failures * mysql-monitor_ping_timeout

3.read_only监控

检查mysql_replication_hostgroups表中所有节点的read_only值，并记录到mysql_server_read_only_log。如果read_only=1，表示只读，是一个slave，这样的节点将会自动移入reader_hostgroup中，如果read_only=0，表示可写，可能是master，这样的节点将会自动移入writer_hostgroup中。

4.replication lag监控

对mysql_servers表中所有配置了max_replication_lag的后端slave节点都检查复制延迟，通过show slave status返回结果中的Seconds_Behind_Master字段，判断slave和master之间的延迟程度，并记录到mysql_server_replication_lag_log表中。

如果Seconds_Behind_Master > max_replication_lag，表示该slave延迟很严重，ProxySQL会自动避开这种slave节点，直到Seconds_Behind_Master < max_replication_lag。

Monitor监控上述指标时，会使用MySQL节点上的用户连接到后端节点，所以，需要先在后端节点上创建负责监控的用户。监控connect、ping和read_only这3项指标时，该用户只需具有USAGE权限，如果要监控replication lag指标，则需要replication client权限。

3.2 配置connect和ping监控

首先，在后端节点上创建用于监控的用户，顺便为其授予replication client权限。只需在一个写节点(例如master)上创建即可，它会复制到其它节点上。

create user monitor@'192.168.100.%' identified by 'P@ssword1!';
grant replication client on *.* to monitor@'192.168.100.%';

然后，在ProxySQL上配置这个监控用户，配置方式是修改全局变量。

set mysql-monitor_username='monitor';
set mysql-monitor_password='P@ssword1!';

由于ProxySQL上所有的配置修改都是在修改main库中对应的表，上面的变量在main.global_variables中，所以上面两个set语句和下面两个update语句是等价的。

update global_variables set variable_value='monitor'
 where variable_name='mysql-monitor_username';

update global_variables set variable_value='P@ssword1!'
 where variable_name='mysql-monitor_password';

在将配置load到runtime之前，可以先查看下connect和ping对应的log表。

admin> select * from mysql_server_connect_log;
+----------------+------+------------------+-------------------------+---------------+
| hostname       | port | time_start_us    | connect_success_time_us | connect_error |
+----------------+------+------------------+-------------------------+---------------+
| 192.168.100.22 | 3306 | 1530968712977867 | 4174                    | NULL          |
| 192.168.100.23 | 3306 | 1530968712988986 | 4908                    | NULL          |
| 192.168.100.24 | 3306 | 1530968713000074 | 3044                    | NULL          |
| 192.168.100.22 | 3306 | 1530968772978982 | 3407                    | NULL          |
| 192.168.100.23 | 3306 | 1530968772989627 | 3404                    | NULL          |
| 192.168.100.24 | 3306 | 1530968773000778 | 3444                    | NULL          |
+----------------+------+------------------+-------------------------+---------------+

admin> select * from mysql_server_ping_log;
+----------------+------+------------------+----------------------+-------------+
| hostname       | port | time_start_us    | ping_success_time_us | ping_error  |
+----------------+------+------------------+----------------------+-------------+
| 192.168.100.22 | 3306 | 1530968712666540 | 452                  | NULL        |
| 192.168.100.23 | 3306 | 1530968712668779 | 458                  | NULL        |
| 192.168.100.24 | 3306 | 1530968712671541 | 324                  | NULL        |
| 192.168.100.22 | 3306 | 1530968722667071 | 1190                 | NULL        |
| 192.168.100.23 | 3306 | 1530968722669574 | 1162                 | NULL        |
| 192.168.100.24 | 3306 | 1530968722673162 | 1380                 | NULL        |
+----------------+------+------------------+----------------------+-------------+

不难发现，监控操作在load到runtime之前就已经生效了。这是有意为之的：通过这种方式，可以在节点添加到生产环境之前执行一些基本的健康检查。

监控的节点一切正常后(error=NULL)，将配置load到runtime。

load mysql variables to runtime;
save mysql variables to disk;

3.3 配置read_only监控和读/写组

目前read_only和replication_lag的监控日志还是空。

admin> select * from mysql_server_read_only_log;
Empty set (0.00 sec)

admin> select * from mysql_server_replication_lag_log;
Empty set (0.00 sec)

这是因为还没有对ProxySQL中的节点分组：writer_hostgroup、reader_hostgroup。

设置分组信息，需要修改的是main库中的mysql_replication_hostgroups表(组复制则是mysql_group_replication_hostgroups)，该表只有3个字段：第一个字段名为writer_hostgroup，第二个字段为reader_hostgroup，第三个字段为注释字段，可随意写。

例如，指定写组的id为10，读组的id为20。

insert into mysql_replication_hostgroups values(10,20);

admin> select * from mysql_replication_hostgroups;
+------------------+------------------+----------+
| writer_hostgroup | reader_hostgroup | comment  |
+------------------+------------------+----------+
| 10               | 20               | cluster1 |
+------------------+------------------+----------+

在该配置加载到RUNTIME生效之前，先查看下各mysql server所在的组。

admin> select hostgroup_id,hostname,port,status,weight from mysql_servers; 
+--------------+----------------+------+--------+--------+
| hostgroup_id | hostname       | port | status | weight |
+--------------+----------------+------+--------+--------+
| 10           | 192.168.100.22 | 3306 | ONLINE | 1      |
| 10           | 192.168.100.23 | 3306 | ONLINE | 1      |
| 10           | 192.168.100.24 | 3306 | ONLINE | 1      |
+--------------+----------------+------+--------+--------+

3个节点都在hostgroup_id=10的组中。

现在，将刚才mysql_replication_hostgroups表的修改加载到RUNTIME生效。

load mysql servers to runtime;
save mysql servers to disk;

一加载，Monitor模块就会开始监控后端的read_only值，当监控到read_only值后，就会按照read_only的值将某些节点自动移动到读/写组。

例如，此处所有节点都在id=10的写组，slave1和slave2都是slave，它们的read_only=1，这两个节点将会移动到id=20的组。如果一开始这3节点都在id=20的读组，那么移动的将是Master节点，会移动到id=10的写组。

看结果：

admin> select hostgroup_id,hostname,port,status,weight from mysql_servers;
+--------------+----------------+------+--------+--------+
| hostgroup_id | hostname       | port | status | weight |
+--------------+----------------+------+--------+--------+
| 10           | 192.168.100.22 | 3306 | ONLINE | 1      |
| 20           | 192.168.100.23 | 3306 | ONLINE | 1      |
| 20           | 192.168.100.24 | 3306 | ONLINE | 1      |
+--------------+----------------+------+--------+--------+

配置好read_only的监控后，Monitor模块会每隔一段时间监控一次read_only值。

admin> select * from mysql_server_read_only_log;
+----------------+------+------------------+-----------------+-----------+--------+
| hostname       | port | time_start_us    | success_time_us | read_only | error  |
+----------------+------+------------------+-----------------+-----------+--------+
| 192.168.100.22 | 3306 | 1530970372197917 | 8487            | 0         | NULL   |
| 192.168.100.23 | 3306 | 1530970372198992 | 7907            | 1         | NULL   |
| 192.168.100.24 | 3306 | 1530970372199835 | 8064            | 1         | NULL   |
| 192.168.100.22 | 3306 | 1530970373698824 | 10078           | 0         | NULL   |
| 192.168.100.23 | 3306 | 1530970373699825 | 9845            | 1         | NULL   |
| 192.168.100.24 | 3306 | 1530970373700786 | 10745           | 1         | NULL   |
+----------------+------+------------------+-----------------+-----------+--------+

3.4 配置replication lag监控

Monitor模块会监控后端主机组中各slave的数据是否延迟于master，这个延迟行为称为replication lag，俗称拖后腿。

如果某个slave节点上的数据比master落后很多(临界值见下文)，表示这个slave节点处理速度慢，数据较旧。ProxySQL采用一种称为自动避开(automatic shunned)的方式，临时避开这个落后的节点。当ProxySQL避开某节点后，ProxySQL不会把SQL语句路由给这个节点。

ProxySQL有几种情况可能会触发自动避开节点的行为：

  1. 和后端的连接断开。
  2. slave落后于master过多。
  3. 和后端建立连接时，错误次数过多。
  4. second_behind_master=null时，即slave的SQL线程未运行，或者slave未连接到master。(不过这种自动避开的情况是可控的，见全局变量mysql-monitor_slave_lag_when_null

本文介绍关于replication lag的内容。

Monitor模块会每隔一段时间(mysql-monitor_replication_lag_interval)去检查一次拖后腿情况，检测的方式是获取show slave status中的Seconds_Behind_Master字段值，然后和mysql_servers表中max_replication_lag字段的值比较：

  • Seconds_Behind_Master < max_replication_lag：表示落后程度尚在允许范围内。
  • Seconds_Behind_Master > max_replication_lag：表示落后太多，这样的节点应该避开。

只有传统复制结构的slave节点才需要设置max_replication_lag字段，master无需设置，组复制和galera也无需设置，因为这两种复制结构中show slave status的结果为空。例如，将读组中的所有节点都设置最多落后于master 10秒钟。

update mysql_servers set max_replication_lag=10 where hostgroup_id=20;
load mysql servers to runtime;
save mysql servers to disk;

需要注意的是，Seconds_Behind_Master的值并不总是可靠的，见 https://dev.mysql.com/doc/refman/5.7/en/show-slave-status.html 。

4.关于主机组和read_only

前面为了将主机组分为读组和写组，特地开启了monitor模块的read_only监控功能，根据read_only的值以及mysql_replication_hostgroup表中定义的读、写组自动调整节点。

在简单的环境下，这没什么问题。但是想要实现复杂一点的读、写分离，比如写操作路由到hostgroup_id=10的组，读操作分多种情况路由，select1语句路由到hostgroup_id=20的读组，select2语句路由到hostgroup_id=30的组，...。这种情况，想通过monitor模块和mysql_replication_hostgroup表来实现是不可能的，因为mysql_replication_hostgroup表的writer_hostgroup字段是主键，reader_hostgroup字段具有唯一性，它们都是int类型，且不可重复。

admin> show create table main.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.00 sec)

换句话说，每一个写组只能且必须对应单个读组(经测试，reader_hostgroup字段可以为空)，无法实现hg_id=10是写组，而hg_id=20、hg_id=30同时作为读组。

显然ProxySQL不可能限制的这么死。实际上，完全可以不用定义mysql_replication_hostgroup表，也不用定义monitor模块的read_only监控(如果没有定义mysql_replication_hostgroup，read_only监控会自动停止)，这时只能人为控制不要把写操作路由到后端的slave上。

例如：

delete from mysql_replication_hostgroup;
delete from mysql_servers;

insert into mysql_servers(hostgroup_id,hostname,port) 
values(10,'192.168.100.22',3306),
      (20,'192.168.100.23',3306),
      (30,'192.168.100.24',3306);

load mysql servers to runtime;
save mysql servers to disk;

事实上，在实现ProxySQL复杂、高级的路由功能时，都不会通过monitor模块去自动控制读、写组。

5.向ProxySQL添加MySQL用户：mysql_users表

配置好后端MySQL节点、监控、分组后，接下来要配置的是MySQL的用户。

需要注意，ProxySQL中有3套用户：

  1. 连接Admin管理接口(默认6032端口)使用一套用户名/密码。
    • 分为管理员和普通用户，管理员用户具有写权限，能配置ProxySQL，普通用户只有几个库的读权限。
    • 管理员用户/密码通过变量admin-admin_credentials设置，普通用户/密码通过变量admin-stats_credentials设置。
    • 这部分用户名不能出现在mysql_users表中。
  2. Monitor模块监控后端时，需要使用一个MySQL用户连接后端进行监控。
    • 需要先在后端MySQL组中创建好这个用户监控的用户。
    • 监控connect、ping和read_only时，只需USAGE权限，监控replication lag时需要replication client权限。
    • 通过变量mysql-monitor_username和mysql-monitor_password将监控用户加入到ProxySQL中。
  3. 前端连接ProxySQL(默认6033端口)、ProxySQL连接后端MySQL时使用一套用户名/密码。
    • 当前端app发送SQL请求时，需要使用这套用户。然后ProxySQL将SQL语句路由给某后端节点，需要使用同一个用户和后端建立连接并将SQL语句发送出去。
    • 需要现在后端MySQL组中创建好这套用户，因为ProxySQL需要连接后端，所以一般授权所有权限，例如root。
    • 通过mysql_users表将这些用户加入到ProxySQL中，每一行对应一个用户。

5.1 添加MySQL users到ProxySQL

例如，使用root用户来处理SQL请求。先在后端的写节点(如master节点)上授权root，该操作会复制给其它节点。

grant all on *.* to root@'192.168.100.%' identified by 'P@ssword1!';

然后，向ProxySQL的mysql_users插入这个用户即可。这个表的字段很多，大多数字段都有默认值，以下是大部分使用默认值的插入语句：

insert into mysql_users(username,password,default_hostgroup) 
values ('root','P@ssword1!',10);

load mysql users to runtime;
save mysql users to disk;

上面指定了root用户的用户名、密码以及该用户默认的路由目标组。

ProxySQL有多种粒度的路由规则，每个用户都有默认的路由目标组，当使用该用户发送的SQL语句没有能够匹配的语句级路由规则，则会将该SQL语句路由给该用户默认的路由目标组。

例如，navicat工具使用root用户连接到了ProxySQL，发送了一个select语句，如果没有配置select语句的路由规则，那么这个select语句将默认路由给root用户的默认组。

下面先介绍一下mysql_users表中的密码相关内容，然后再详细介绍mysql_users表。

5.2 mysql_users表中用户的密码管理

ProxySQL向mysql_users表添加用户时，支持明文密码和hash加密密码。这个hash密码和mysql的password()的算法是一样的。

但是，ProxySQL内部使用的是SQLite3引擎，不支持password()。所以，想要向ProxySQL中使用hash加密密码，可以先通过mysql的password()函数创建一个hash密码，然后copy到mysql_users表中。

例如：

[root@s4 ~]#  mysql -uroot -pP@ssword1! -e 'select password("P@ssword1!");' 
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------------------------------------+
| password("P@ssword1!")                    |
+-------------------------------------------+
| *50572A5FABC7DA9CEE5EB5977EDDE59E38967422 |
+-------------------------------------------+

然后插入到ProxySQL的mysql_users表：

insert into mysql_users(username,password,default_hostgroup) 
values ('root','*50572A5FABC7DA9CEE5EB5977EDDE59E38967422',10);

ProxySQL和MySQL对密码的处理方式都是：以"*"开头的密码，表示是hash加密密码。

在MySQL和ProxySQL中，使用SHA1(SHA1('clear_password'))对clear_password进行加密。无法根据加密的hash密码推导回原始的明文密码。

当客户端连接到ProxySQL时，ProxySQL将使用该加密的密码对用户进行认证。如果该客户端是第一次认证，则ProxySQL会推导出一部分的hash密码SHA1('clear_password')。推导出的信息会存储到runtime的内部数据结构中，以便ProxySQL连接后端MySQL时使用。

从1.2.3版本开始，引入了一个布尔类型的全局变量admin-hash_passwords，默认为true。该变量表示，内存数据库的mysql_users表中的明文密码，在load mysql users to runtime时，对明文密码进行hash加密，并保存到runtime数据结构中。有了这个特性，可以以另一种方式保存加密密码：只需将其刷回内存数据库即可。

例如：

Admin> SELECT username,password FROM mysql_users;
+----------+-----------+
| username | password  |
+----------+-----------+
| user1    | password1 |     # 明文密码
| user2    | password2 |
+----------+-----------+

Admin> LOAD MYSQL USERS TO RUNTIME;    # 加载到runtime数据结构

Admin> SELECT username,password FROM mysql_users;
+----------+-----------+
| username | password  |
+----------+-----------+
| user1    | password1 |    # 还是明文密码
| user2    | password2 |
+----------+-----------+

这个时候，runtime数据结构中的密码是加密密码，而内存数据库中的密码是明文密码。

将runtime数据结构数据刷回内存数据库，即可将加密密码保存到内存数据库中，然后还可以将加密的密码持久化到disk。

Admin> save mysql users to memory;

Admin> SELECT username,password FROM mysql_users;
+----------+-------------------------------------------+
| username | password                                  |
+----------+-------------------------------------------+
| user1    | *668425423DB5193AF921380129F465A6425216D0 |
| user2    | *DC52755F3C09F5923046BD42AFA76BD1D80DF2E9 |
+----------+-------------------------------------------+

Admin> save mysql users to disk;

唯一需要注意的是，admin-hash-passwords变量是admin-变量而非mysql-变量，这意味着修改了这个变量后(虽然基本不会去修改)，load/save操作的目标是"admin variables"，而非"mysql variables"。

load admin variables to runtime;
save admin variables to disk;

5.3 mysql_users表详细解释

以下是mysql_users表的属性。

|     字段名             | 数据类型 | 可为空?  | 默认值 |
|-----------------------|---------|----------|-------|
|username    (pk,uk)    | VARCHAR | NOT NULL |       |
|password               | VARCHAR | NULL     |       |
|active                 | INT     | NOT NULL | 1     |
|use_ssl                | INT     | NOT NULL | 0     |
|default_hostgroup      | INT     | NOT NULL | 0     |
|default_schema         | VARCHAR | NULL     |       |
|schema_locked          | INT     | NOT NULL | 0     |
|transaction_persistent | INT     | NOT NULL | 1     |
|fast_forward           | INT     | NOT NULL | 0     |
|backend      (pk)      | INT     | NOT NULL | 1     |
|frontend     (uk)      | INT     | NOT NULL | 1     |
|max_connections        | INT     | NOT NULL | 10000 |

各字段的意义：

  • username, password：前端连接到ProxySQL以及ProxySQL连接到后端时使用的用户凭据。
  • active：active=0的用户会保留在库中，但不会加载到runtime数据结构中，只有active=1用户才是有效用户。该字段默认值为1。
  • default_hostgroup：如果该用户发送的查询语句无法匹配任何规则，则该查询会路由到该字段指定的默认组中。
  • default_schema：建立连接时默认将切换到该schema。
  • schema_locked：目前还不支持该功能。
  • transaction_persistent：如果正在和ProxySQL建立连接的客户端使用的用户设置了该字段，那么当该用户开启一个事务后，该客户端后续的请求都将路由到同一主机组中(无视查询规则)，使得事务在这个组中是持久的，避免同一事务分散到其它组，直到事务结束才可能会路由到其它主机组。注意，有些老版本中，这个字段默认值为0，强烈建议修改为1。
  • fast_forward：如果设置了该字段，ProxySQL将绕过查询处理层(重写语句、缓存)，直接将原请求语句转发给后端节点。
  • frontend：如果设置为1，前端将可以使用该用户(username,password)连接到ProxySQL。
  • backend：如果设置为1，ProxySQL将可以使用该用户(username,password)连接到后端节点。
  • max_connections：使用该用户"建立到ProxySQL的连接"的最大数量。默认值为10000，所以每个用户最多和ProxySQL建立10000个连接。注意，这是前端到ProxySQL的连接限制，ProxySQL和某个后端节点的最大连接数量是通过mysql_servers中的max_connections字段控制的。

注意，当前版本的ProxySQL要求所有的用户均设置frontend和backend为1(即所有用户都可以进行frontend --> ProxySQL以及ProxySQL --> backend的连接认证)。将来版本中，ProxySQL将分离这两部分连接的用户凭据。这样前端将永远不知道后端的用户凭据，它们只能通过中间的ProxySQL发送请求，无法直接和后端节点建立连接，从而提高安全性。

关于快速转发fast_forward：

  • 不要求用一个不同的端口：正常的ProxySQL逻辑和"fast forward"的逻辑使用的是完全相同的代码/模块。
  • fast forward是基于每用户的：根据连接到ProxySQL用户的设置，决定该用户是否启用、禁用fast forward功能。
  • fast forward算法的启用是在用户认证之后：ProxySQL仍然需要先对客户端使用的用户进行认证，尽管客户端的请求会直接原样转发给后端，但ProxySQL仍然会和前端先建立好连接。这意味着，如果前端和ProxySQL的连接发生错误，也会被处理。
  • fast forward不支持SSL连接。
  • 如果使用压缩功能，必须在两端都启用压缩。

5.4 事务持久化：transaction_persistent

mysql_users表中的transaction_persistent字段，当它的值为1时，表示事务持久化：当某连接使用该用户开启了一个事务后，那么在事务提交/回滚之前，所有的语句都路由到同一个组中，避免语句分散到不同组。在以前的版本中，默认值为0，不知道从哪个版本开始，它的默认值为1。我们期望的值为1，所以强烈建议插入用户后先检查下这个字段的值是否为1，如果为0，则执行下面的语句修改为1。

update mysql_users set transaction_persistent=1 where username='root';
update mysql_users set transaction_persistent=1 where username='sqlsender';
load mysql users to runtime;
save mysql users to disk;

6.总结

添加后端节点、监控后端节点、添加MySQL用户是使用ProxySQL所必须完成的步骤。这3个步骤虽然需要操作的过程不多，但是涉及的内容还是比较多的。

强烈建议将mysql_servers、mysql_users、mysql_replication_hostgroups这3个表的所有字段都了解一遍。不仅如此，要熟练使用ProxySQL，还应该对main库中的表的各个字段都比较熟悉，至少要知道它们什么意思。

下面，将添加后端节点、监控后端节点、添加MySQL用户的操作过程抽取出来，算是个步骤总结：

######### 1.添加后端节点
#
#
insert into mysql_servers(hostgroup_id,hostname,port) values
    (10,'192.168.100.22',3306),
    (10,'192.168.100.23',3306),
    (10,'192.168.100.24',3306);

load mysql servers to runtime;
save mysql servers to disk;

# 查看下各节点是否都是 ONLINE 
select * from mysql_servers\G


######### 2.在后端MySQL上创建监控用户和处理SQL语句的用户
#
# 在后端master节点上执行以下语句
#
create user monitor@'192.168.100.%' identified by 'P@ssword1!';
grant replication client on *.* to monitor@'192.168.100.%';
grant all on *.* to root@'192.168.100.%' identified by 'P@ssword1!';


######### 3.在ProxySQL中配置监控用户
#
#
set mysql-monitor_username='monitor';
set mysql-monitor_password='P@ssword1!';

# 以上两个set语句等价于下面两个update：
update global_variables set variable_value='monitor'
 where variable_name='mysql-monitor_username';

update global_variables set variable_value='P@ssword1!'
 where variable_name='mysql-monitor_password';

load mysql variables to runtime;
save mysql variables to disk;

# 查看下connect和ping的监控是否正常
select * from mysql_server_connect_log order by time_start_us desc limit 6;
select * from mysql_server_ping_log order by time_start_us desc limit 6;


######### 4.配置读、写组
#
#
insert into mysql_replication_hostgroups values(10,20);
load mysql servers to runtime;
save mysql servers to disk;


######### 5.在ProxySQL中配置MySQL用户
#
#
insert into mysql_users(username,password,default_hostgroup) 
values ('root','P@ssword1!',10);
load mysql users to runtime;
save mysql users to memory;
save mysql users to disk;

update mysql_users set transaction_persistent=1 where username='root';
load mysql users to runtime;
save mysql users to disk;

在后面的文章中，我会介绍以下percona版本的ProxySQL，percona ProxySQL提供了一个管理工具，它能通过额外的配置文件自动化配置上面一大堆的操作，大大简化了初始搭建使用ProxySQL的过程。

posted @ 2018-07-10 03:34  骏马金龙  阅读(5206)  评论(0)  编辑  收藏  举报