ProxySQL Query Logging

Query Logging

ProxySQL能够记录完整的查询日志，是通过查询规则来配置，粒度可大可小。

Setup

首先，全局启用logging

SET mysql-eventslog_filename='queries.log';

变量需要load到runtime，最后save到磁盘

LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

接下来，创建查询路由规则。如果你需要记录所有的查询，简单配置一下即可：

1 INSERT INTO mysql_query_rules (rule_id, active, match_digest, log,apply) VALUES (1,1,'.',1,0);

View Code

如果你想记录某个人的所有查询记录，如‘Bob’：

1 INSERT INTO mysql_query_rules (rule_id, active, username, match_pattern, log,apply) VALUES (1, 1, 'Bob', '.', 1, 0);

View Code

现在，加载到runtime并持久化到磁盘即可：

1 LOAD MYSQL QUERY RULES TO RUNTIME;
2 SAVE MYSQL QUERY RULES TO DISK;

View Code

Reading Logged Queries

查询日志以二进制的形式记录，源码中有一个app能将二进制转换成普通平面文件

1 $ ./tools/eventslog_reader_sample /var/lib/proxysql/file1.log.00001258
2 ProxySQL LOG QUERY: thread_id="2" username="root" schemaname=information_schema" client="127.0.0.1:58307" HID=0 server="127.0.0.1:3306" starttime="2016-10-23 12:34:37.132509" endtime="2016-10-23 12:34:38.347527" duration=1215018us digest="0xC5C3C490CA0825C1"
3 select sleep(1)
4 ProxySQL LOG QUERY: thread_id="2" username="root" schemaname=information_schema" client="127.0.0.1:58307" HID=0 server="127.0.0.1:3306" starttime="2016-10-23 12:41:38.604244" endtime="2016-10-23 12:41:38.813587" duration=209343us digest="0xE9D6D71A620B328F"
5 SELECT DATABASE()
6 ProxySQL LOG QUERY: thread_id="2" username="root" schemaname=test" client="127.0.0.1:58307" HID=0 server="127.0.0.1:3306" starttime="2016-10-23 12:42:38.511849" endtime="2016-10-23 12:42:38.712609" duration=200760us digest="0x524DB8D7A9B4C132"
7 select aaaaaaa