• 运维特工,战胜心魔!!
  • 有些事情其实没有那么难,只是我们感觉难,走出第一步!
  • 你所浪费的今天,是昨天死去的人奢望的明天!!!
  • 欢迎访问 unixfbi.com 运维特工社区http://unixfbi.com/
  • 如果您觉得本站非常有看点,那么赶紧使用Ctrl+D 收藏吧
  • 运维特工:http://www.unixfbi.com
  • 人生没有白走的路,每一步都算数!!
  • 空杯心态,沉淀自己!

ProxySQL常用操作

MySQL unixfbi 1年前 (2018-07-21) 3196次浏览 已收录 0个评论 扫描二维码
文章目录[隐藏]

一、查看配置

配置 ProxySQL 之前,我们先确认 ProxySQL 里面是否有配置;

# mysql -uadmin -padmin -P6032 -h127.0.0.1
admin@127.0.0.1 [(none)] 09:24:26> select * from mysql_servers;
Empty set (0.00 sec)

admin@127.0.0.1 [(none)] 09:24:39> select * from mysql_replication_hostgroups;
Empty set (0.00 sec)

admin@127.0.0.1 [(none)] 09:24:52> select * from mysql_query_rules;
Empty set (0.00 sec)

二、增加 backend servers

配置后端 servers 都是在 mysql_servers 中配置的。我们先看看这个表都有哪些字段;

admin@127.0.0.1 [(none)] 10:13:44> 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.00 sec)

具体每个字段的含义,等我在另一篇文章讲解吧。或者看官网。
下面我们来添加 3 个 MySQL server:

admin@127.0.0.1 [(none)] 10:00:51> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'db-node1',3306);
Query OK, 1 row affected (0.00 sec)

admin@127.0.0.1 [(none)] 10:08:04> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'db-node2',3306);
Query OK, 1 row affected (0.00 sec)

admin@127.0.0.1 [(none)] 10:08:22> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'db-node3',3306);
Query OK, 1 row affected (0.00 sec)

admin@127.0.0.1 [(none)] 10:08:40> SELECT * FROM mysql_servers;
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1            | db-node1 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 1            | db-node2 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 1            | db-node3 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)

三、配置监控

ProxySQL 会不间断的监控后端服务器,配置一些监控参数是非常重要的。
ProxySQL 监控后端服务器,首先需要一个监控账号,而且这个账号也必须在 MySQL 数据库里存在。
ProxySQL 里面配置这个账号是在global_variables表中,下面我们来看看这个表的结构:

admin@127.0.0.1 [(none)] 10:14:06> show create table global_variables\G
*************************** 1. row ***************************
       table: global_variables
Create Table: CREATE TABLE global_variables (
    variable_name VARCHAR NOT NULL PRIMARY KEY,
    variable_value VARCHAR NOT NULL)
1 row in set (0.00 sec)

下面我们来创建这个账号,命名为 monitor:
其实 ProxySQL 默认的监控账号就是 monitor。

admin@127.0.0.1 [(none)] 10:26:01> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
Query OK, 1 row affected (0.00 sec)

admin@127.0.0.1 [(none)] 10:31:12> UPDATE global_variables SET variable_value='unixfbi' WHERE variable_name='mysql-monitor_password';       
Query OK, 1 row affected (0.01 sec)

上面监控用户添加好了,我们来配置几个监控项目的监控间隔:

admin@127.0.0.1 [(none)] 10:37:09> UPDATE global_variables SET variable_value='2000' WHERE variable_name IN ('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');
Query OK, 3 rows affected (0.01 sec)

admin@127.0.0.1 [(none)] 10:37:30> SELECT * FROM global_variables WHERE variable_name LIKE 'mysql-monitor_%';
+-----------------------------------------------------+----------------+
| variable_name                                       | variable_value |
+-----------------------------------------------------+----------------+
| mysql-monitor_enabled                               | true           |
| mysql-monitor_connect_timeout                       | 600            |
| mysql-monitor_ping_max_failures                     | 3              |
| mysql-monitor_ping_timeout                          | 1000           |
| mysql-monitor_read_only_max_timeout_count           | 3              |
| mysql-monitor_replication_lag_interval              | 10000          |
| mysql-monitor_replication_lag_timeout               | 1000           |
| mysql-monitor_groupreplication_healthcheck_interval | 5000           |
| mysql-monitor_groupreplication_healthcheck_timeout  | 800            |
| mysql-monitor_replication_lag_use_percona_heartbeat |                |
| mysql-monitor_query_interval                        | 60000          |
| mysql-monitor_query_timeout                         | 100            |
| mysql-monitor_slave_lag_when_null                   | 60             |
| mysql-monitor_wait_timeout                          | true           |
| mysql-monitor_writer_is_also_reader                 | true           |
| mysql-monitor_username                              | monitor        |
| mysql-monitor_password                              | unixfbi        |
| mysql-monitor_history                               | 600000         |
| mysql-monitor_connect_interval                      | 2000           |
| mysql-monitor_ping_interval                         | 2000           |
| mysql-monitor_read_only_interval                    | 2000           |
| mysql-monitor_read_only_timeout                     | 500            |
+-----------------------------------------------------+----------------+
22 rows in set (0.00 sec)

修改完配置后,记得加载到 RUNTIME,并且做持久化:

admin@127.0.0.1 [(none)] 10:39:38> LOAD MYSQL VARIABLES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

admin@127.0.0.1 [(none)] 10:41:32> SAVE MYSQL VARIABLES TO DISK;
Query OK, 95 rows affected (0.07 sec)

四、后端 server 监控检测

上一步我们配置了监控账号和几个项目的监控间隔,现在让我们看看 PrxoySQL 是否能够与这些主机通信。ProxySQL 的 monitor 库有几个表,是专门存储监控信息的。

admin@127.0.0.1 [(none)] 10:41:49> SHOW DATABASES;
+-----+---------------+-------------------------------------+
| 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 |
+-----+---------------+-------------------------------------+
5 rows in set (0.00 sec)

admin@127.0.0.1 [(none)] 10:46:49> 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   |
+------------------------------------+
5 rows in set (0.00 sec)

monitor 库中这些表和监控都有很大的关联。下面我们来查看一些表看看。

admin@127.0.0.1 [(none)] 10:47:30> SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10;
+----------+------+------------------+-------------------------+--------------------------------------------------------------------------+
| hostname | port | time_start_us    | connect_success_time_us | connect_error                                                            |
+----------+------+------------------+-------------------------+--------------------------------------------------------------------------+
| db-node2 | 3306 | 1531968869153141 | 0                       | Access denied for user 'monitor'@'192.168.199.212' (using password: YES) |
| db-node1 | 3306 | 1531968869152588 | 0                       | Access denied for user 'monitor'@'192.168.199.212' (using password: YES) |
| db-node2 | 3306 | 1531968867152541 | 0                       | Access denied for user 'monitor'@'192.168.199.212' (using password: YES) |
| db-node1 | 3306 | 1531968867151533 | 0                       | Access denied for user 'monitor'@'192.168.199.212' (using password: YES) |
| db-node2 | 3306 | 1531968865152372 | 0                       | Access denied for user 'monitor'@'192.168.199.212' (using password: YES) |
| db-node1 | 3306 | 1531968865151133 | 0                       | Access denied for user 'monitor'@'192.168.199.212' (using password: YES) |
| db-node2 | 3306 | 1531968863152463 | 0                       | Access denied for user 'monitor'@'192.168.199.212' (using password: YES) |
| db-node1 | 3306 | 1531968863151586 | 0                       | Access denied for user 'monitor'@'192.168.199.212' (using password: YES) |
| db-node2 | 3306 | 1531968861152596 | 0                       | Access denied for user 'monitor'@'192.168.199.212' (using password: YES) |
| db-node1 | 3306 | 1531968861151651 | 0                       | Access denied for user 'monitor'@'192.168.199.212' (using password: YES) |
+----------+------+------------------+-------------------------+--------------------------------------------------------------------------+
10 rows in set (0.00 sec)

查看 ping 的信息:

admin@127.0.0.1 [(none)] 10:54:30> SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 10;
+----------+------+------------------+----------------------+--------------------------------------------------------------------------+
| hostname | port | time_start_us    | ping_success_time_us | ping_error                                                               |
+----------+------+------------------+----------------------+--------------------------------------------------------------------------+
| db-node2 | 3306 | 1531968959152618 | 0                    | Access denied for user 'monitor'@'192.168.199.212' (using password: YES) |
| db-node3 | 3306 | 1531968959152327 | 0                    | Host '192.168.199.212' is not allowed to connect to this MySQL server    |
| db-node1 | 3306 | 1531968959152203 | 0                    | Access denied for user 'monitor'@'192.168.199.212' (using password: YES) |
| db-node3 | 3306 | 1531968957152489 | 0                    | Host '192.168.199.212' is not allowed to connect to this MySQL server    |
| db-node2 | 3306 | 1531968957152273 | 0                    | Access denied for user 'monitor'@'192.168.199.212' (using password: YES) |
| db-node1 | 3306 | 1531968957152085 | 0                    | Access denied for user 'monitor'@'192.168.199.212' (using password: YES) |
| db-node3 | 3306 | 1531968955152633 | 0                    | Host '192.168.199.212' is not allowed to connect to this MySQL server    |
| db-node1 | 3306 | 1531968955152207 | 0                    | Access denied for user 'monitor'@'192.168.199.212' (using password: YES) |
| db-node2 | 3306 | 1531968955151969 | 0                    | Access denied for user 'monitor'@'192.168.199.212' (using password: YES) |
| db-node3 | 3306 | 1531968953152336 | 0                    | Host '192.168.199.212' is not allowed to connect to this MySQL server    |
+----------+------+------------------+----------------------+--------------------------------------------------------------------------+
10 rows in set (0.00 sec)

从上面两个表的数据可以看出,我们没有在后端 MySQL 数据库上添加 monitor 用户,导致出现Acess denied和连接失败的错误。所以记得在后端 MySQL 服务器上添加 monitor 监控用户。所以一定要在后端 MySQL 实例上创建这 monitor 账号:

mysql> grant USAGE on *.* to monitor@'%' IDENTIFIED  by 'unixfbi';  

这里我们需要明白的是:监控连接和 ping 的监视都是基于表mysql_servers的内容来进行的,尽管我们还没有把添加的 MySQL 服务器配置加载到 RUNTIME。这样的意义就是我们添加完后端 MySQL 服务器后,先检查监控的状态是否正常,然后在加载到 RUNTIME。

admin@127.0.0.1 [(none)] 10:57:59> LOAD MYSQL SERVERS TO RUNTIME;
Query OK, 0 rows affected (0.01 sec)

admin@127.0.0.1 [(none)] 11:06:19> SELECT * FROM mysql_servers;
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1            | db-node1 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 1            | db-node2 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 1            | db-node3 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)

五、MySQL replication hostgroups

这里主要是对 master 和 Slave 进行读写分组
我们来看一下read_only_log信息,这些信息是在这个表里monitor.mysql_server_read_only_log

admin@127.0.0.1 [(none)] 11:06:33> SELECT * FROM monitor.mysql_server_read_only_log ORDER BY time_start_us DESC LIMIT 10;
Empty set (0.00 sec)

这个表怎么是空的呢?原因是 ProxySQL 只对在mysql_replication_hostgroups中配置的主机组中服务器监控read_only的值。这个表目前也是空的。

admin@127.0.0.1 [(none)] 11:37:18> SELECT * FROM mysql_replication_hostgroups;
Empty set (0.00 sec)

这个表的功能是什么呢?在此表中,可以配置writer_hostgroupreader_hostgroup。ProxySQL 将会监视read_only在指定主机组中的所有服务器的值,并且根据read_only的值,将把服务器分配给相应的writer_hostgroupreader_hostgroup 主机组中。
下面举个例子:
先查看一下这个表结构:

admin@127.0.0.1 [(none)] 11:37:36> 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.00 sec)

下面我们来设置writer_hostgroup的值为 1,reader_hostgroup的值为 2。
如果 MySQL 实例的read_only=0 那么这个实例就会被移动到 hostgroup 1 里,如果read_only=1那么这实例就会被移动到 hostgroup 2 中。

admin@127.0.0.1 [(none)] 11:46:17> INSERT INTO mysql_replication_hostgroups VALUES (1,2,'group comment');
Query OK, 1 row affected (0.00 sec)

admin@127.0.0.1 [(none)] 11:46:26> SELECT * FROM mysql_replication_hostgroups;                                                         
+------------------+------------------+---------------+
| writer_hostgroup | reader_hostgroup | comment       |
+------------------+------------------+---------------+
| 1                | 2                | group comment |
+------------------+------------------+---------------+
1 row in set (0.00 sec)

加载配置到 RUNTIME:

LOAD MYSQL SERVERS TO RUNTIME;  

此时会把mysql_servers and mysql_replication_hostgroups 两个表都加载到 RUNTIME;
下面我们来看一下结果:

admin@127.0.0.1 [(none)] 12:10:14> SELECT * FROM monitor.mysql_server_read_only_log ORDER BY time_start_us DESC LIMIT 10; 
+----------+------+------------------+-----------------+-----------+-------+
| hostname | port | time_start_us    | success_time_us | read_only | error |
+----------+------+------------------+-----------------+-----------+-------+
| db-node3 | 3306 | 1531973431390080 | 3901            | 1         | NULL  |
| db-node2 | 3306 | 1531973431389544 | 2190            | 1         | NULL  |
| db-node1 | 3306 | 1531973431389018 | 2435            | 0         | NULL  |
| db-node3 | 3306 | 1531973429389986 | 3814            | 1         | NULL  |
| db-node2 | 3306 | 1531973429389472 | 2196            | 1         | NULL  |
| db-node1 | 3306 | 1531973429388945 | 2443            | 0         | NULL  |
| db-node3 | 3306 | 1531973427389923 | 3823            | 1         | NULL  |
| db-node2 | 3306 | 1531973427389406 | 2166            | 1         | NULL  |
| db-node1 | 3306 | 1531973427388882 | 2435            | 0         | NULL  |
| db-node3 | 3306 | 1531973425389862 | 4286            | 1         | NULL  |
+----------+------+------------------+-----------------+-----------+-------+
10 rows in set (0.00 sec)

到现在,ProxySQL 正在监视服务器的read_only值。并且还创建了一个新组 hostgroup2 ,这个新组是只读组,read_only=1的实例都会被移动到 hostgroup2 组里。

admin@127.0.0.1 [(none)] 12:10:31> SELECT * FROM mysql_servers;
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1            | db-node1 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 2            | db-node2 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 2            | db-node3 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)

现在把配置持久化到硬盘:

admin@127.0.0.1 [(none)] 12:14:10> SAVE MYSQL SERVERS TO DISK;
Query OK, 0 rows affected (0.50 sec)

admin@127.0.0.1 [(none)] 12:19:46> SAVE MYSQL VARIABLES TO DISK;
Query OK, 95 rows affected (0.09 sec)

六、MySQL Users

在我们配置了mysql_servers 后,我们还需要配置mysql users 存储在mysql_users表中

admin@127.0.0.1 [(none)] 12:21:22> SELECT * FROM mysql_users;
Empty set (0.00 sec)

admin@127.0.0.1 [(none)] 14:13:03> 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.00 sec)

这个表默认是空的,让我们来配置 users 吧

admin@127.0.0.1 [(none)] 14:13:18> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('proxysql_user1','',1);
Query OK, 1 row affected (0.00 sec)

admin@127.0.0.1 [(none)] 14:16:41> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('proxysql_user2','unixfbi',1);
Query OK, 1 row affected (0.00 sec)

admin@127.0.0.1 [(none)] 14:18:04> SELECT * FROM mysql_users;
+----------------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| username       | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
+----------------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| proxysql_user1 |          | 1      | 0       | 1                 | NULL           | 0             | 1                      | 0            | 1       | 1        | 10000           |
| proxysql_user2 | unixfbi  | 1      | 0       | 1                 | NULL           | 0             | 1                      | 0            | 1       | 1        | 10000           |
+----------------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
2 rows in set (0.00 sec)

这个表中很多都是默认值,我们只是配置了username,password,default_hostgroup字段。
default_hostgroup是一个主机组,如果一个查询没有匹配查询规则,那么将会用于发送特定用户生成的流量到该hostgroup上。

把配置加载到 RUNTIME,并且持久化到硬盘

admin@127.0.0.1 [(none)] 14:41:13> LOAD MYSQL USERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

admin@127.0.0.1 [(none)] 14:41:24> SAVE MYSQL USERS TO DISK;
Query OK, 0 rows affected (0.17 sec)

该账户还要添加到后端 MySQL 实例上。

root@localhost:mysql3306.sock [(none)]> grant all privileges on *.* to proxysql_user2@'%' identified by 'unixfbi';
Query OK, 0 rows affected, 1 warning (0.03 sec)

root@localhost:mysql3306.sock [(none)]> flush privileges;
Query OK, 0 rows affected (0.04 sec)

下面我们来测试一下:

[root@db-node4 ~]# mysql -uproxysql_user2 -punixfbi -h127.0.0.1 -P6033 -e "select 1"   
mysql: [Warning] Using a password on the command line interface can be insecure.
+---+
| 1 |
+---+
| 1 |
+---+
[root@db-node4 ~]# mysql -uproxysql_user2 -punixfbi -h127.0.0.1 -P6033 -e "select @@hostname"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| db-node1   |
+------------+

可以看出查询是发送到了hostgroup1组里的db-node1机器上。

我们利用 sysbench 测试一下:
首先需要在 MySQL 实例中创建 sbtest1 数据库:

root@localhost:mysql3306.sock [(none)]> create database sbtest1;

直接通过 ProxySQL 访问后端 MySQL 实例:

$ sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-host=192.168.199.212 --mysql-port=6033  --mysql-user=proxysql_user2 --mysql-password='unixfbi' --mysql-db=sbtest1 --db-driver=mysql --tables=10 --table-size=10000  --threads=32  prepare

$ sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-host=192.168.199.212 --mysql-port=6033  --mysql-user=proxysql_user2 --mysql-password='unixfbi' --mysql-db=sbtest1 --db-driver=mysql --tables=10 --table-size=10000  --threads=32  run

七、ProxySQL 信息统计

ProxySQL 收集了大量实时统计信息存放在 stats 库中。

admin@127.0.0.1 [(none)] 15:09:08> SHOW DATABASES;
+-----+---------------+-------------------------------------+
| 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 |
+-----+---------------+-------------------------------------+
5 rows in set (0.00 sec)

admin@127.0.0.1 [(none)] 15:09:15> SHOW TABLES FROM stats;
+--------------------------------------+
| tables                               |
+--------------------------------------+
| global_variables                     |
| stats_memory_metrics                 |
| stats_mysql_commands_counters        |
| stats_mysql_connection_pool          |
| stats_mysql_connection_pool_reset    |
| stats_mysql_global                   |
| stats_mysql_prepared_statements_info |
| stats_mysql_processlist              |
| stats_mysql_query_digest             |
| stats_mysql_query_digest_reset       |
| stats_mysql_query_rules              |
| stats_mysql_users                    |
| stats_proxysql_servers_checksums     |
| stats_proxysql_servers_metrics       |
| stats_proxysql_servers_status        |
+--------------------------------------+
15 rows in set (0.00 sec)

我们来看几个表中的统计信息:

admin@127.0.0.1 [(none)] 15:09:40> SELECT * FROM stats.stats_mysql_connection_pool;
+-----------+----------+----------+--------------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
| hostgroup | srv_host | srv_port | status       | ConnUsed | ConnFree | ConnOK | ConnERR | Queries | Bytes_data_sent | Bytes_data_recv | Latency_us |
+-----------+----------+----------+--------------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
| 1         | db-node1 | 3306     | ONLINE       | 0        | 44       | 45     | 524     | 20585   | 20148418        | 27507456        | 386        |
| 1         | db-node3 | 3306     | OFFLINE_HARD | 0        | 0        | 0      | 0       | 0       | 0               | 0               | 606        |
| 2         | db-node2 | 3306     | ONLINE       | 0        | 0        | 0      | 0       | 0       | 0               | 0               | 374        |
| 2         | db-node3 | 3306     | ONLINE       | 0        | 0        | 0      | 0       | 0       | 0               | 0               | 606        |
+-----------+----------+----------+--------------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
4 rows in set (0.00 sec)

这个表包含了很多流量发送到每个 server 的详细信息。发现所有的流量都发送到 db-node1 实例上面,这符合我们的预期。

当服务器被删除(完全删除或者从主机组移出)时,这个服务器在 ProxySQL 内部被标记为OFFLINE_HARD,而不是真正删除。这也就是为什么 db-node3 被标记为 hostgroup1 的OFFLINE_HARD

查看stats_mysql_commands_counters表信息:
这个表中主要是统计都是执行了一些什么语句,以及每个类型的语句所花费的时间。

admin@127.0.0.1 [(none)] 15:12:37> SELECT * FROM stats_mysql_commands_counters WHERE Total_cnt;
+--------------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+-----------+-----------+--------+--------+---------+----------+
| Command      | Total_Time_us | Total_cnt | cnt_100us | cnt_500us | cnt_1ms | cnt_5ms | cnt_10ms | cnt_50ms | cnt_100ms | cnt_500ms | cnt_1s | cnt_5s | cnt_10s | cnt_INFs |
+--------------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+-----------+-----------+--------+--------+---------+----------+
| BEGIN        | 2397824       | 736       | 2         | 143       | 289     | 192     | 39       | 70       | 1         | 0         | 0      | 0      | 0       | 0        |
| COMMIT       | 169788498     | 736       | 20        | 5         | 7       | 0       | 0        | 0        | 0         | 696       | 8      | 0      | 0       | 0        |
| CREATE_INDEX | 40304306      | 10        | 0         | 0         | 0       | 0       | 0        | 0        | 0         | 0         | 0      | 10     | 0       | 0        |
| CREATE_TABLE | 20629090      | 30        | 0         | 0         | 1       | 0       | 0        | 19       | 0         | 0         | 2      | 8      | 0       | 0        |
| DELETE       | 23796525      | 1024      | 271       | 14        | 145     | 352     | 92       | 50       | 27        | 69        | 4      | 0      | 0       | 0        |
| INSERT       | 184818553     | 1064      | 296       | 7         | 117     | 445     | 75       | 49       | 14        | 20        | 9      | 22     | 0       | 10       |
| SELECT       | 36546296      | 11481     | 1349      | 1034      | 3593    | 3153    | 1702     | 617      | 18        | 15        | 0      | 0      | 0       | 0        |
| UPDATE       | 66737206      | 2048      | 556       | 17        | 132     | 633     | 272      | 203      | 49        | 167       | 19     | 0      | 0       | 0        |
| SHOW         | 15299         | 1         | 0         | 0         | 0       | 0       | 0        | 1        | 0         | 0         | 0      | 0      | 0       | 0        |
+--------------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+-----------+-----------+--------+--------+---------+----------+
9 rows in set (0.00 sec)

查看stats_mysql_query_digest
这个表中可以查看到更详细的执行语句的信息。

admin@127.0.0.1 [(none)] 16:00:25> SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest   LIMIT 9;                 
+----+----------+------------+---------------------------------------------------------+
| hg | sum_time | count_star | digest_text                                             |
+----+----------+------------+---------------------------------------------------------+
| 1  | 462207   | 86         | INSERT INTO sbtest7 (id, k, c, pad) VALUES (?, ?, ?, ?) |
| 1  | 422425   | 91         | INSERT INTO sbtest6 (id, k, c, pad) VALUES (?, ?, ?, ?) |
| 1  | 1370558  | 116        | INSERT INTO sbtest2 (id, k, c, pad) VALUES (?, ?, ?, ?) |
| 1  | 4771450  | 108        | DELETE FROM sbtest10 WHERE id=?                         |
| 1  | 1800472  | 112        | DELETE FROM sbtest8 WHERE id=?                          |
| 1  | 1672294  | 101        | DELETE FROM sbtest1 WHERE id=?                          |
| 1  | 1054634  | 85         | UPDATE sbtest10 SET c=? WHERE id=?                      |
| 1  | 995734   | 89         | UPDATE sbtest8 SET c=? WHERE id=?                       |
| 1  | 2187623  | 124        | UPDATE sbtest7 SET c=? WHERE id=?                       |
+----+----------+------------+---------------------------------------------------------+
9 rows in set (0.00 sec)

八、配置查询规则

规则配置主要是在mysql_query_rules表中。查看下该表结构:

admin@127.0.0.1 [(none)] 15:33:18> 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.00 sec)

下面我们来配置两个查询规则,去 Slave 查询的规则。其他的语句全部去 Master。

admin@127.0.0.1 [(none)] 15:36:39> INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (10,1,'proxysql_user2','^SELECT c FROM sbtest1 WHERE id=\?$',2,1);
Query OK, 1 row affected (0.00 sec)

admin@127.0.0.1 [(none)] 16:07:48> INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (20,1,'proxysql_user2','DISTINCT c FROM sbtest1',2,1);             
Query OK, 1 row affected (0.00 sec)

有几个点需要说明一下:

  • 查询规则是按照rule_id排序的;
  • active=1表示该规则启用该规则。查询规则是一种非常强大的工具,如果配置不当,调试是比较麻烦的。默认 active=0,在启用规则之前,请认真检查规则语句。
  • 第一个规则的例子使用了正则开始符^和结束符$表示开始和结束的标记。在这种情况下,它意味着match_digest或者match_pattern应该完整匹配查询。
  • 第二例子里没有使用正则符,表示在该规则在查询语句的任何地方都能匹配。
  • 有些特殊字符需要进行转义。
  • apply=1,意味着如果存在匹配了,那么不会再去检查其他规则进行匹配了。

查看我们刚才配置的规则:

admin@127.0.0.1 [(none)] 16:08:41> SELECT match_digest,destination_hostgroup FROM mysql_query_rules WHERE active=1 AND username='proxysql_user2' ORDER BY rule_id;
+-------------------------------------+-----------------------+
| match_digest                        | destination_hostgroup |
+-------------------------------------+-----------------------+
| ^SELECT c FROM sbtest1 WHERE id=\?$ | 2                     |
| DISTINCT c FROM sbtest1             | 2                     |
+-------------------------------------+-----------------------+
2 rows in set (0.00 sec)

匹配这两个规则的查询都会请求到 slaves 实例上。如果没有匹配上,会把请求发送到default_hostgroup,proxysql_user2用户默认hostgroup 是 1;

下面我们来重置一下stats_mysql_query_digest 这个表的统计信息。使用如下命令:


admin@127.0.0.1 [(none)] 16:34:15> SELECT * FROM stats_mysql_query_digest_reset LIMIT 1;

使配置的规则生效:

admin@127.0.0.1 [(none)] 16:34:15> LOAD MYSQL QUERY RULES TO RUNTIME;

我们来测试一下:

$ sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-host=192.168.199.212 --mysql-port=6033  --mysql-user=proxysql_user2 --mysql-password='unixfbi' --mysql-db=sbtest1 --db-driver=mysql --tables=1 --table-size=10000  --threads=32 run

查看一下统计信息:

admin@127.0.0.1 [(none)] 16:59:13> SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;
+----+----------+------------+--------------------------------------------------------------------+
| hg | sum_time | count_star | digest_text                                                        |
+----+----------+------------+--------------------------------------------------------------------+
| 1  | 92175875 | 440        | UPDATE sbtest1 SET k=k+? WHERE id=?                                |
| 1  | 78550084 | 435        | DELETE FROM sbtest1 WHERE id=?                                     |
| 1  | 75527262 | 440        | UPDATE sbtest1 SET c=? WHERE id=?                                  |
| 1  | 68730876 | 431        | COMMIT                                                             |
| 2  | 2519370  | 4112       | SELECT c FROM sbtest1 WHERE id=?                                   |
| 2  | 2226335  | 440        | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ? ORDER BY c |
| 1  | 1974314  | 440        | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ? ORDER BY c          |
| 1  | 1517441  | 440        | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?                     |
| 1  | 1066374  | 440        | SELECT SUM(k) FROM sbtest1 WHERE id BETWEEN ? AND ?                |
| 1  | 745190   | 431        | INSERT INTO sbtest1 (id, k, c, pad) VALUES (?, ?, ?, ?)            |
| 1  | 248455   | 440        | BEGIN                                                              |
+----+----------+------------+--------------------------------------------------------------------+
11 rows in set (0.00 sec)

可以看出我们配置的规则已经生效了。
stats_mysql_query_digest 可以查看汇聚的结果。

admin@127.0.0.1 [(none)] 17:00:12> SELECT hostgroup hg, SUM(sum_time), SUM(count_star) FROM stats_mysql_query_digest GROUP BY hostgroup;
+----+---------------+-----------------+
| hg | SUM(sum_time) | SUM(count_star) |
+----+---------------+-----------------+
| 1  | 320535871     | 3937            |
| 2  | 4745705       | 4552            |
+----+---------------+-----------------+
2 rows in set (0.00 sec)

九、查询缓存(Query Caching)

ProxySQL 的一个常用用法是可以当做查询缓存。默认情况下,查询是不被缓存,但是可以在mysql_query_rules中启用设置cache_ttl(单位是毫秒)

admin@127.0.0.1 [(none)] 17:05:00> UPDATE mysql_query_rules set cache_ttl=5000 WHERE active=1 AND destination_hostgroup=2;
Query OK, 2 rows affected (0.00 sec)

查看一下配置:

admin@127.0.0.1 [(none)] 17:47:14>  SELECT match_digest,destination_hostgroup,cache_ttl FROM mysql_query_rules WHERE active=1 AND username='proxysql_user2' ORDER BY rule_id;     
+-------------------------------------+-----------------------+-----------+
| match_digest                        | destination_hostgroup | cache_ttl |
+-------------------------------------+-----------------------+-----------+
| ^SELECT c FROM sbtest1 WHERE id=\?$ | 2                     | 5000      |
| DISTINCT c FROM sbtest1             | 2                     | 5000      |
+-------------------------------------+-----------------------+-----------+
2 rows in set (0.00 sec)

让配置生效:

admin@127.0.0.1 [(none)] 17:47:49> LOAD MYSQL QUERY RULES TO RUNTIME;

把统计的计数器清零:

admin@127.0.0.1 [(none)] 17:50:25> SELECT 1 FROM stats_mysql_query_digest_reset LIMIT 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

测试一下:

$ sysbench /usr/share/sysbench/oltp_read_only.lua --mysql-host=192.168.199.212 --mysql-port=6033  --mysql-user=proxysql_user2 --mysql-password='unixfbi' --mysql-db=sbtest1 --db-driver=mysql  --tables=1 --table-size=100000  --threads=32 run

然后执行:

proxysql_user2@192.168.199.212:6033 [sbtest1]> SELECT c FROM sbtest1 WHERE id=5;
proxysql_user2@192.168.199.212:6033 [sbtest1]> SELECT c FROM sbtest1 WHERE id=1;
proxysql_user2@192.168.199.212:6033 [sbtest1]> SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN 3 AND 2+1 ORDER BY c;

查看结果:

admin@127.0.0.1 [(none)] 18:14:11> SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;
+----+------------+------------+----------------------------------------------------------------------+
| hg | sum_time   | count_star | digest_text                                                          |
+----+------------+------------+----------------------------------------------------------------------+
| 2  | 2501368616 | 4213544    | SELECT c FROM sbtest1 WHERE id=?                                     |
| 1  | 429964465  | 421498     | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ? ORDER BY c            |
| 1  | 408570864  | 421498     | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?                       |
| 1  | 389473145  | 421498     | SELECT SUM(k) FROM sbtest1 WHERE id BETWEEN ? AND ?                  |
| 1  | 319757439  | 421498     | COMMIT                                                               |
| 1  | 313606326  | 421498     | BEGIN                                                                |
| 2  | 309793914  | 421498     | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ? ORDER BY c   |
| 2  | 33350      | 3          | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c |
| 2  | 2043       | 1          | SELECT c FROM sbtest1 WHERE id=?                                     |
| 1  | 1134       | 1          | SELECT DATABASE()                                                    |
| -1 | 0          | 3          | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c |
| -1 | 0          | 8          | SELECT c FROM sbtest1 WHERE id=?                                     |
| 1  | 0          | 1          | select USER()                                                        |
| 1  | 0          | 1          | select @@version_comment limit ?                                     |
+----+------------+------------+----------------------------------------------------------------------+
14 rows in set (0.00 sec)

总结:

  • 如果出现被缓存了,后面的查询就不会被发送到任何hostgroup了,并把缓存查询的hostgroup 标记为 -1

  • 缓存查询的总执行时间为 0;

十、查询重写(Query Rewrite)

ProxySQL 支持多种方式匹配一个查询,例如flagIN,username,schemaname

最常见的匹配查询的方法是编写与查询本身的 SQL 语句相匹配的正则表达式。要匹配查询的 SQL 语句,ProxySQL 提供两个机制,使用两个不同的字段:

  • match_digest:它再次匹配查询摘要的正则表达式,如stats_mysql_query_digest.query_digest所示。

  • match_pattern:它再次匹配查询的未修改文本的正则表达式。

这些机制为啥不同?一个查询摘要比查询语句本身要小的多(一个包含若干 MB 数据的INSERT语句)。因此,运行一个较小字符串的正则表达式肯定会更快。因此,如果你没有尝试在一个查询语句中匹配一个指定的字符的话,那么推荐你使用match_digest这样会更快。

如果你想要重写查询,你必匹配查询的原始语句(使用match_pattern),因为原始查询语句需要被重写。

举个例子:
我查看一下之前的执行语句的统计信息:

admin@127.0.0.1 [(none)] 14:26:53> SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;
+----+------------+------------+----------------------------------------------------------------------+
| hg | sum_time   | count_star | digest_text                                                          |
+----+------------+------------+----------------------------------------------------------------------+
| 2  | 2501368616 | 4213544    | SELECT c FROM sbtest1 WHERE id=?                                     |
| 1  | 429964465  | 421498     | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ? ORDER BY c            |
| 1  | 408570864  | 421498     | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?                       |
| 1  | 389473145  | 421498     | SELECT SUM(k) FROM sbtest1 WHERE id BETWEEN ? AND ?                  |
| 1  | 319757439  | 421498     | COMMIT                                                               |
| 1  | 313606326  | 421498     | BEGIN                                                                |
| 2  | 309793914  | 421498     | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ? ORDER BY c   |
| 2  | 33350      | 3          | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c |
| 2  | 2043       | 1          | SELECT c FROM sbtest1 WHERE id=?                                     |
| 1  | 1134       | 1          | SELECT DATABASE()                                                    |
| -1 | 0          | 3          | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c |
| -1 | 0          | 8          | SELECT c FROM sbtest1 WHERE id=?                                     |
| 1  | 0          | 1          | select USER()                                                        |
| 1  | 0          | 1          | select @@version_comment limit ?                                     |
+----+------------+------------+----------------------------------------------------------------------+
14 rows in set (0.01 sec)

我们现在把SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ? ORDER BY c 语句改写为: SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ? 就是把ORDER BY c去掉。

我们首先看看 ProxySQL 现在都有哪些规则:

admin@127.0.0.1 [(none)] 14:34:21> SELECT rule_id, match_digest, match_pattern, replace_pattern, cache_ttl, apply FROM mysql_query_rules ORDER BY rule_id;
+---------+-------------------------------------+---------------+-----------------+-----------+-------+
| rule_id | match_digest                        | match_pattern | replace_pattern | cache_ttl | apply |
+---------+-------------------------------------+---------------+-----------------+-----------+-------+
| 10      | ^SELECT c FROM sbtest1 WHERE id=\?$ | NULL          | NULL            | 50000     | 1     |
| 20      | DISTINCT c FROM sbtest1             | NULL          | NULL            | 50000     | 1     |
+---------+-------------------------------------+---------------+-----------------+-----------+-------+
2 rows in set (0.00 sec)

我需要先设置rule_id=20规则的apply=0

admin@127.0.0.1 [(none)] 14:37:31> UPDATE mysql_query_rules SET apply=0 WHERE rule_id=20;
Query OK, 1 row affected (0.00 sec)

admin@127.0.0.1 [(none)] 14:39:28> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

好了。准备工作就绪了,我们来编写查询重写语句的规则。

admin@127.0.0.1 [(none)] 14:39:47> INSERT INTO mysql_query_rules (rule_id,active,username,match_pattern,replace_pattern,apply) VALUES (30,1,'proxysql_user2','DISTINCT(.*)ORDER BY c','DISTINCT\1',1);
Query OK, 1 row affected (0.00 sec)

查看一下配置的规则:

admin@127.0.0.1 [(none)] 14:41:32> SELECT rule_id, match_digest, match_pattern, replace_pattern, cache_ttl, apply FROM mysql_query_rules ORDER BY rule_id;
+---------+-------------------------------------+------------------------+-----------------+-----------+-------+
| rule_id | match_digest                        | match_pattern          | replace_pattern | cache_ttl | apply |
+---------+-------------------------------------+------------------------+-----------------+-----------+-------+
| 10      | ^SELECT c FROM sbtest1 WHERE id=\?$ | NULL                   | NULL            | 50000     | 1     |
| 20      | DISTINCT c FROM sbtest1             | NULL                   | NULL            | 50000     | 0     |
| 30      | NULL                                | DISTINCT(.*)ORDER BY c | DISTINCT\1      | NULL      | 1     |
+---------+-------------------------------------+------------------------+-----------------+-----------+-------+
3 rows in set (0.00 sec)

让配置生效:

admin@127.0.0.1 [(none)] 14:42:25> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

测试一下查询重写语句的规则是否生效:
统计计数器清零:

admin@127.0.0.1 [(none)] 14:46:23> SELECT 1 FROM stats_mysql_query_digest_reset LIMIT 1;
$ sysbench /usr/share/sysbench/oltp_read_only.lua --mysql-host=192.168.199.212 --mysql-port=6033  --mysql-user=proxysql_user2 --mysql-password='unixfbi' --mysql-db=sbtest1 --db-driver=mysql  --tables=1 --table-size=100000  --threads=32 run
admin@127.0.0.1 [(none)] 16:25:14> SELECT hits, mysql_query_rules.rule_id, match_digest, match_pattern, replace_pattern, cache_ttl, apply FROM mysql_query_rules NATURAL JOIN stats.stats_mysql_query_rules ORDER BY mysql_query_rules.rule_id;
+------+---------+-------------------------------------+------------------------+-----------------+-----------+-------+
| hits | rule_id | match_digest                        | match_pattern          | replace_pattern | cache_ttl | apply |
+------+---------+-------------------------------------+------------------------+-----------------+-----------+-------+
| 32   | 10      | ^SELECT c FROM sbtest1 WHERE id=\?$ | NULL                   | NULL            | 50000     | 1     |
| 32   | 20      | DISTINCT c FROM sbtest1             | NULL                   | NULL            | 50000     | 0     |
| 32   | 30      | NULL                                | DISTINCT(.*)ORDER BY c | DISTINCT\1      | NULL      | 1     |
+------+---------+-------------------------------------+------------------------+-----------------+-----------+-------+
3 rows in set (0.00 sec)

可以看出 hits 了 32 次;
看看SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ? ORDER BY c语句是否改写成了SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ? :

admin@127.0.0.1 [(none)] 16:27:19> SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;
+----+----------+------------+-----------------------------------------------------------+
| hg | sum_time | count_star | digest_text                                               |
+----+----------+------------+-----------------------------------------------------------+
| 2  | 76608156 | 20322      | SELECT c FROM sbtest1 WHERE id=?                          |
| 1  | 20246646 | 2061       | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ? ORDER BY c |
| 1  | 17582272 | 2061       | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?            |
| 2  | 17411621 | 2061       | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ?   |
| 1  | 12860666 | 2061       | COMMIT                                                    |
| 1  | 11207050 | 2061       | BEGIN                                                     |
| 1  | 11195520 | 2061       | SELECT SUM(k) FROM sbtest1 WHERE id BETWEEN ? AND ?       |
+----+----------+------------+-----------------------------------------------------------+
7 rows in set (0.00 sec)

从上面的结果可以看出查询重写已经成功了。
总结:
rule_id=20rule_id=30规则可以合并为一个规则。它们分离描述apply字段的重要性,不仅多个规则可以匹配同一个查询,而且多个规则可以转换并应用到同一个查询。

参考文档

https://github.com/sysown/proxysql/blob/master/doc/configuration_howto.md
https://github.com/sysown/proxysql/wiki

本文出自 “运维特工” 博客,转载请务必保留原文链接 和 http://unixfbi.com


UnixFBI 运维特工 , 版权所有丨如未注明 , 均为原创丨 转载请务必注明原文链接http://www.unixfbi.com/539.html
喜欢 (0)
[支付宝]
分享 (0)
unixfbi
关于作者:
运维工程师一枚,做有价值的事情!
发表我的评论
取消评论
表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址