ProxySQL是一款强大的MySQL代理，它可以在你的数据库应用程序和MySQL服务器之间添加一层中间层，以提高查询的性能，提供高可用性和提高查询路由的灵活性。本篇会逐步讲解怎样配置基础信息，前提是你已经下载并正常运行proxysql
1. 安装ProxySQL
在Ubuntu系统上，你可以使用以下命令安装ProxySQL：
bash
sudo apt-get update sudo apt-get install proxysql
在CentOS系统上，你可以使用以下命令安装ProxySQL：
bash
sudo yum update sudo yum install proxysql
2. 配置ProxySQL
-
确认初始配置
ProxySQL的配置文件通常位于/etc/proxysql.cnf，通常在服务第一次安装启动时加载，后续修改配置一般不通过直接修改配置文件的方式，官方提供的admin接口(默认是6032端口)：
$ mysql -u admin -padmin -h 10.10.0.1 -P6032 --prompt 'ProxySQL Admin> '官方建议通过这种方式配置你的MySQL服务器和其他相关的设置。
通过以下语句，确定这几个表是否为空：mysql_servers, mysql_replication_hostgroups and mysql_query_rules
ProxySQL Admin> SELECT * FROM mysql_servers;
Empty set (0.00 sec)
ProxySQL Admin> SELECT * from mysql_replication_hostgroups;
Empty set (0.00 sec)
ProxySQL Admin> SELECT * from mysql_query_rules;
Empty set (0.00 sec)-
新增后端配置
新增3台mysql服务器到后端配置表：mysql_servers，默认端口为3306
ProxySQL Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'10.0.0.1',3306);
Query OK, 1 row affected (0.01 sec)
ProxySQL Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'10.0.0.2',3306);
Query OK, 1 row affected (0.01 sec)
ProxySQL Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'10.0.0.3',3306);
Query OK, 1 row affected (0.00 sec)
Admin> SELECT * FROM mysql_servers;
+--------------+-----------+------+--------+--------+-------------+-----------------+---------------------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag |
+--------------+-----------+------+--------+--------+-------------+-----------------+---------------------+
| 1 | 10.10.0.1 | 3306 | ONLINE | 1 | 0 | 1000 | 0 |
| 1 | 10.10.0.1 | 3306 | ONLINE | 1 | 0 | 1000 | 0 |
| 1 | 10.10.0.1 | 3306 | ONLINE | 1 | 0 | 1000 | 0 |
+--------------+-----------+------+--------+--------+-------------+-----------------+---------------------+
3 rows in set (0.00 sec)注意：如果mysql服务器配置read_only = 1 ，proxysql将认为此mysql是只读的，所以你应该确认主库的read_only为0来保证正常的功能，请设置好mysql实例中my.cnf文件的read_only值。
-
配置监控
proxysql持续监控mysql实例的健康状况，在主库创建相关账号：
mysql> CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor';
Query OK, 1 row affected (0.00 sec)授予权限：
用户需要USAGE权限来连接、ping和检查read_only
如果需要监控同步延迟，用户还需要REPLICATION CLIENT权限，
mysql> GRANT USAGE, REPLICATION CLIENT ON *.* TO 'monitor'@'%';
Query OK, 0 rows affected (0.00 sec)在proxysql中添加监控用户：
ProxySQL Admin> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
Query OK, 1 row affected (0.00 sec)
ProxySQL Admin> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_password';
Query OK, 1 row affected (0.00 sec)以下是详细监控项：
ProxySQL Admin> 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.00 sec)
ProxySQL Admin> SELECT * FROM global_variables WHERE variable_name LIKE 'mysql-monitor_%';
+----------------------------------------+---------------------------------------------------+
| variable_name | variable_value |
+----------------------------------------+---------------------------------------------------+
| mysql-monitor_history | 600000 |
| mysql-monitor_connect_interval | 2000 |
| mysql-monitor_connect_timeout | 200 |
| mysql-monitor_ping_interval | 2000 |
| mysql-monitor_ping_timeout | 100 |
| mysql-monitor_read_only_interval | 2000 |
| mysql-monitor_read_only_timeout | 100 |
| mysql-monitor_replication_lag_interval | 10000 |
| mysql-monitor_replication_lag_timeout | 1000 |
| mysql-monitor_username | monitor |
| mysql-monitor_password | monitor |
| mysql-monitor_query_variables | SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES |
| mysql-monitor_query_status | SELECT * FROM INFORMATION_SCHEMA.GLOBAL_STATUS |
| mysql-monitor_query_interval | 60000 |
| mysql-monitor_query_timeout | 100 |
| mysql-monitor_timer_cached | true |
| mysql-monitor_writer_is_also_reader | true |
+----------------------------------------+---------------------------------------------------+
17 rows in set (0.00 sec)在表global_variables中对MySQL Monitor进行的更改将在执行LOAD MYSQL VARIABLES TO RUNTIME语句后应用。要在重启后保持配置更改，还必须执行SAVE MYSQL VARIABLES TO DISK
Admin> LOAD MYSQL VARIABLES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
Admin> SAVE MYSQL VARIABLES TO DISK;
Query OK, 54 rows affected (0.02 sec)-
mysql同步主机组配置
集群的拓扑结构变动监控是基于表mysql_replication_hostgroups的配置来变动的，会根据mysql实例配置read_only的值分配，例如：下面语句指定一组读写的标识
Admin> SHOW CREATE TABLE mysql_replication_hostgroupsG
*************************** 1. row ***************************
table: mysql_replication_hostgroups
Create Table: CREATE TABLE mysql_replication_hostgroups (
writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY,
reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND reader_hostgroup>0),
check_type VARCHAR CHECK (LOWER(check_type) IN ('read_only','innodb_read_only','super_read_only','read_only|innodb_read_only','read_only&innodb_read_only')) NOT NULL DEFAULT 'read_only',
comment VARCHAR,
UNIQUE (reader_hostgroup))
1 row in set (0.00 sec)
Admin> INSERT INTO mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,comment) VALUES (1,2,'cluster1');
Query OK, 1 row affected (0.00 sec)假如mysql主机read_only为0，他们会被分配到组1
假如mysql主机read_only为1，他们会被分配到组2
配置载入到运行环境
LOAD MYSQL SERVERS TO RUNTIME检测read_only值的日志记录在表 mysql_server_read_only_log
Admin> SELECT * FROM monitor.mysql_server_read_only_log ORDER BY time_start_us DESC LIMIT 3;
+-----------+-------+------------------+--------------+-----------+-------+
| hostname | port | time_start_us | success_time | read_only | error |
+-----------+-------+------------------+--------------+-----------+-------+
| 10.10.0.1 | 3306 | 1456969634783579 | 762 | 0 | NULL |
| 10.10.0.2 | 3306 | 1456969634783579 | 378 | 1 | NULL |
| 10.10.0.3 | 3306 | 1456969634783579 | 317 | 1 | NULL |
+-----------+-------+------------------+--------------+-----------+-------+
3 rows in set (0.01 sec)看主机组分配结果，根据read_only值分配到了不同的hostgroup_id(此值的定义查看表mysql_replication_hostgroups)
Admin> SELECT * FROM mysql_servers;
+--------------+-----------+------+--------+--------+-------------+-----------------+---------------------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag |
+--------------+-----------+------+--------+--------+-------------+-----------------+---------------------+
| 1 | 10.10.0.1 | 3306 | ONLINE | 1 | 0 | 1000 | 0 |
| 2 | 10.10.0.2 | 3306 | ONLINE | 1 | 0 | 1000 | 0 |
| 2 | 10.10.0.3 | 3306 | ONLINE | 1 | 0 | 1000 | 0 |
+--------------+-----------+------+--------+--------+-------------+-----------------+---------------------+
3 rows in set (0.00 sec)最后一步，保存配置到磁盘
Admin> SAVE MYSQL SERVERS TO DISK;
Query OK, 0 rows affected (0.01 sec)
Admin> SAVE MYSQL VARIABLES TO DISK;
Query OK, 54 rows affected (0.00 sec)-
用户配置
服务器集群配置完成后，我们来看看用户配置：
新增一个用户到表mysql_users：
Admin> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('root','',1);
Query OK, 1 row affected (0.00 sec)
Admin> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('stnduser','stnduser',1);
Query OK, 1 row affected (0.00 sec)
Admin> SELECT * FROM mysql_users;
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| root | | 1 | 0 | 1 | NULL | 0 | 0 | 0 | 1 | 1 | 10000 |
| stnduser | stnduser | 1 | 0 | 1 | NULL | 0 | 0 | 0 | 1 | 1 | 10000 |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
2 rows in set (0.00 sec)default_hostgroup为该用户默认分组，指定用户可以链接哪台mysql服务器，可以配置其他路由规则来改变。
ProxySQL Admin> LOAD MYSQL USERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
ProxySQL Admin> SAVE MYSQL USERS TO DISK;
Query OK, 0 rows affected (0.01 sec)
mysql -u stnduser -p stnduser -h 10.10.0.1 -P6033 -e"SELECT @@port"
这条语句测试是否正常链接到目标服务器
-
功能测试
sysbench --report-interval=5 --num-threads=4 --num-requests=0 --max-time=20 --test=tests/db/oltp.lua --mysql-user='stnduser' --mysql-password='stnduser' --oltp-table-size=10000 --mysql-host=127.0.0.1 --mysql-port=6033 run
[ output omitted ]
sysbench --num-threads=4 --max-requests=0 --max-time=20 --test=tests/db/oltp.lua --mysql-user='stnduser' --mysql-password='stnduser' --oltp-table-size=10000 --mysql-host=10.10.0.1 --mysql-port=6033 --db-ps-mode=disable run
[ output omitted ]-
统计数据，查询规则
ProxySQL收集统计每个服务和表相关运行和工作负载的信息：
ProxySQL Admin> SHOW TABLES FROM stats;
+--------------------------------+
| tables |
+--------------------------------+
| stats_mysql_query_rules |
| stats_mysql_commands_counters |
| stats_mysql_processlist |
| stats_mysql_connection_pool |
| stats_mysql_query_digest |
| stats_mysql_query_digest_reset |
| stats_mysql_global |
+--------------------------------+
7 rows in set (0.00 sec)stats_mysql_connection_pool表记录mysql后端链接和整体流量，正常状态是ONLINE，当移除或者停机时，状态变为OFFLINE_HARD
ProxySQL Admin> SELECT * FROM stats.stats_mysql_connection_pool;
+-----------+-----------+----------+--------------+----------+----------+--------+---------+---------+-----------------+-----------------+
| hostgroup | srv_host | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | Queries | Bytes_data_sent | Bytes_data_recv |
+-----------+-----------+----------+--------------+----------+----------+--------+---------+---------+-----------------+-----------------+
| 1 | 10.10.0.1 | 3306 | ONLINE | 0 | 4 | 5 | 0 | 144982 | 7865186 | 278734683 |
| 2 | 10.10.0.1 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 2 | 10.10.0.2 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 2 | 10.10.0.3 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
+-----------+-----------+----------+--------------+----------+----------+--------+---------+---------+-----------------+-----------------+
4 rows in set (0.00 sec)stats_mysql_commands_counters表记录每种命令的执行信息和执行时间区间分部
ProxySQL Admin> 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 | 1921940 | 7249 | 4214 | 2106 | 570 | 340 | 14 | 5 | 0 | 0 | 0 | 0 | 0 | 0 |
| COMMIT | 5986400 | 7249 | 119 | 3301 | 1912 | 1864 | 44 | 8 | 1 | 0 | 0 | 0 | 0 | 0 |
| DELETE | 2428829 | 7249 | 325 | 5856 | 585 | 475 | 5 | 3 | 0 | 0 | 0 | 0 | 0 | 0 |
| INSERT | 2260129 | 7249 | 356 | 5948 | 529 | 408 | 6 | 2 | 0 | 0 | 0 | 0 | 0 | 0 |
| SELECT | 40461204 | 101490 | 12667 | 69530 | 11919 | 6943 | 268 | 149 | 13 | 1 | 0 | 0 | 0 | 0 |
| UPDATE | 6635032 | 14498 | 333 | 11149 | 1597 | 1361 | 42 | 16 | 0 | 0 | 0 | 0 | 0 | 0 |
+---------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+-----------+-----------+--------+--------+---------+----------+
6 rows in set (0.00 sec)stats_mysql_query_digest表记录 每条语句的详细执行信息，包括次数，总时间，最短最长时间
ProxySQL Admin> SELECT * FROM stats_mysql_query_digest ORDER BY sum_time DESC;
+-----------+--------------------+----------+--------------------+----------------------------------------------------------------------+------------+------------+------------+----------+----------+----------+
| hostgroup | schemaname | username | digest | digest_text | count_star | first_seen | last_seen | sum_time | min_time | max_time |
+-----------+--------------------+----------+--------------------+----------------------------------------------------------------------+------------+------------+------------+----------+----------+----------+
| 1 | sbtest | stnduser | 0x13781C1DBF001A0C | SELECT c FROM sbtest1 WHERE id=? | 72490 | 1456971810 | 1456971830 | 17732590 | 23 | 58935 |
| 1 | sbtest | stnduser | 0x704822A0F7D3CD60 | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c | 7249 | 1456971810 | 1456971830 | 9629225 | 20 | 121604 |
| 1 | sbtest | stnduser | 0xADF3DDF2877EEAAF | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c | 7249 | 1456971810 | 1456971830 | 6650716 | 26 | 76159 |
| 1 | sbtest | stnduser | 0x5DBEB0DD695FBF25 | COMMIT | 7249 | 1456971810 | 1456971830 | 5986400 | 64 | 59229 |
| 1 | sbtest | stnduser | 0xCCB481C7C198E52B | UPDATE sbtest1 SET k=k+? WHERE id=? | 7249 | 1456971810 | 1456971830 | 3948930 | 44 | 47860 |
| 1 | sbtest | stnduser | 0x7DD56217AF7A5197 | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? | 7249 | 1456971810 | 1456971830 | 3235986 | 22 | 24624 |
| 1 | sbtest | stnduser | 0xE75DB8313E268CF3 | SELECT SUM(K) FROM sbtest1 WHERE id BETWEEN ? AND ?+? | 7249 | 1456971810 | 1456971830 | 3211197 | 51 | 29569 |
| 1 | sbtest | stnduser | 0x5A23CA36FB239BC9 | UPDATE sbtest1 SET c=? WHERE id=? | 7249 | 1456971810 | 1456971830 | 2686102 | 23 | 27779 |
| 1 | sbtest | stnduser | 0x55319B9EE365BEB5 | DELETE FROM sbtest1 WHERE id=? | 7249 | 1456971810 | 1456971830 | 2428829 | 29 | 11676 |
| 1 | sbtest | stnduser | 0x10634DACE52A0A02 | INSERT INTO sbtest1 (id, k, c, pad) VALUES (?, ?, ?, ?) | 7249 | 1456971810 | 1456971830 | 2260129 | 61 | 13711 |
| 1 | sbtest | stnduser | 0x4760CBDEFAD1519E | BEGIN | 7249 | 1456971810 | 1456971830 | 1921940 | 30 | 39871 |
| 1 | information_schema | stnduser | 0x9DD5A40E1C46AE52 | SELECT ? | 1 | 1456970758 | 1456970758 | 1217 | 1217 | 1217 |
| 1 | information_schema | stnduser | 0xA90D80E5831B091B | SELECT @@port | 1 | 1456970769 | 1456970769 | 273 | 273 | 273 |
| 1 | information_schema | stnduser | 0x52A2BA0B226CD90D | select @@version_comment limit ? | 2 | 1456970758 | 1456970769 | 0 | 0 | 0 |
+-----------+--------------------+----------+--------------------+----------------------------------------------------------------------+------------+------------+------------+----------+----------+----------+
14 rows in set (0.00 sec)下面具体配置查询分流规则
-
mysql查询规则
通过mysql_query_rules表来配置
我们来把上面前2条语句配置到hostgroup为2的主机查询：
ProxySQL Admin> INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (10,1,'stnduser','^SELECT c FROM sbtest1 WHERE id=?$',2,1);
Query OK, 1 row affected (0.00 sec)
ProxySQL Admin> INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (20,1,'stnduser','DISTINCT c FROM sbtest1',2,1);
Query OK, 1 row affected (0.00 sec)查询规则按照rule_id的顺序进行处理
仅处理 active=1 的规则
第一个规则示例使用脱字号 (^) 和美元 ($) ：这些是特殊的正则表达式字符，标记模式的开头和结尾，即在本例中 match_digestormatch_pattern 应完全匹配查询
示例中的第二条规则不使用插入符号或美元：匹配可以位于查询中的任何位置
问号被转义，因为它在正则表达式中具有特殊含义
apply=1 表示如果当前规则匹配，则不应评估进一步的规则
可以通过查询 stats_mysql_query_digest_reset 来检索之前的负载配置，并截断stats_mysql_query_digest 表的内容，建议在激活查询规则之前这样做，以便轻松查看更改。
ProxySQL Admin> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)再次查询stats_mysql_query_digest表，可以看到之前的语句已经关联到hostgroup为2的mysql主机
ProxySQL Admin> 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 | 14520738 | 50041 | SELECT c FROM sbtest1 WHERE id=? |
| 2 | 3203582 | 5001 | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c |
| 1 | 3142041 | 5001 | COMMIT |
| 1 | 2270931 | 5001 | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c |
| 1 | 2021320 | 5003 | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? |
| 1 | 1768748 | 5001 | UPDATE sbtest1 SET k=k+? WHERE id=? |
| 1 | 1697175 | 5003 | SELECT SUM(K) FROM sbtest1 WHERE id BETWEEN ? AND ?+? |
| 1 | 1346791 | 5001 | UPDATE sbtest1 SET c=? WHERE id=? |
| 1 | 1263259 | 5001 | DELETE FROM sbtest1 WHERE id=? |
| 1 | 1191760 | 5001 | INSERT INTO sbtest1 (id, k, c, pad) VALUES (?, ?, ?, ?) |
| 1 | 875343 | 5005 | BEGIN |
+----+----------+------------+----------------------------------------------------------------------+
11 rows in set (0.00 sec)聚合查询结果：
ProxySQL Admin> 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 | 21523008 | 59256 |
| 2 | 23915965 | 72424 |
+----+---------------+-----------------+
2 rows in set (0.00 sec)-
查询缓存
这是个比较常用的功能，默认是关闭的，需要通过mysql_query_rules表的cache_ttl字段打开
ProxySQL Admin> UPDATE mysql_query_rules set cache_ttl=5000 WHERE active=1 AND destination_hostgroup=2;
Query OK, 2 rows affected (0.00 sec)
ProxySQL Admin> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
ProxySQL Admin> SELECT 1 FROM stats_mysql_query_digest_reset LIMIT 1; -- we reset the counters
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)stats_mysql_query_digest表显示hostgroup的值为-1的语句是走了缓存
ProxySQL Admin> 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 | 7457441 | 5963 | COMMIT |
| 1 | 6767681 | 5963 | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c |
| 2 | 4891464 | 8369 | SELECT c FROM sbtest1 WHERE id=? |
| 1 | 4573513 | 5963 | UPDATE sbtest1 SET k=k+? WHERE id=? |
| 1 | 4531319 | 5963 | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? |
| 1 | 3993283 | 5963 | SELECT SUM(K) FROM sbtest1 WHERE id BETWEEN ? AND ?+? |
| 1 | 3482242 | 5963 | UPDATE sbtest1 SET c=? WHERE id=? |
| 1 | 3209088 | 5963 | DELETE FROM sbtest1 WHERE id=? |
| 1 | 2959358 | 5963 | INSERT INTO sbtest1 (id, k, c, pad) VALUES (?, ?, ?, ?) |
| 1 | 2415318 | 5963 | BEGIN |
| 2 | 2266662 | 1881 | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c |
| -1 | 0 | 4082 | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c |
| -1 | 0 | 51261 | SELECT c FROM sbtest1 WHERE id=? |
+----+----------+------------+----------------------------------------------------------------------+
13 rows in set (0.00 sec)你可以将MySQL服务器添加到ProxySQL的后端服务器池：
bash
mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='ProxySQLAdmin> ' INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (0, '127.0.0.1', 3306);
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;在这个例子中，我们将一个运行在本地的MySQL服务器添加到了ProxySQL的后端服务器池中。
-
查询重写
为了匹配查询文本，ProxySQL 提供了 2 种机制：
match_digest：正则匹配摘要（e.g. `SELECT c FROM sbtest1 WHERE id=?` as represented in
stats_mysql_query_digest.query_digest）
match_pattern：正则匹配查询文本（e.g. `SELECT c FROM sbtest1 WHERE id=2`）
官方推荐第一种方式，效率更高，写入匹配文本配置如下：
ProxySQL Admin> INSERT INTO mysql_query_rules (rule_id,active,username,match_pattern,replace_pattern,apply) VALUES (30,1,'stnduser','DISTINCT(.*)ORDER BY c','DISTINCT1',1);
Query OK, 1 row affected (0.00 sec)
ProxySQL Admin> 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 | 5000 | 1 |
| 20 | DISTINCT c FROM sbtest1 | NULL | NULL | 5000 | 1 |
| 30 | NULL | DISTINCT(.*)ORDER BY c | DISTINCT1 | NULL | 1 |
+---------+-------------------------------------+------------------------+-----------------+-----------+-------+
3 rows in set (0.00 sec)
Admin> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)执行结果如下：
Admin> 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 |
+-------+---------+-------------------------------------+------------------------+-----------------+-----------+-------+
| 48560 | 10 | ^SELECT c FROM sbtest1 WHERE id=? | NULL | NULL | 5000 | 1 |
| 4856 | 20 | DISTINCT c FROM sbtest1 | NULL | NULL | 5000 | 0 |
| 4856 | 30 | NULL | DISTINCT(.*)ORDER BY c | DISTINCT1 | NULL | 1 |
+-------+---------+-------------------------------------+------------------------+-----------------+-----------+-------+
3 rows in set (0.01 sec)
3976
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
