Skip to content

Query Logging

Jump to bottom
René Cannaò edited this page Dec 12, 2017 · 6 revisions

Query Logging

proxySQL is able to log queries that pass through. Logging is configured with Query Rules. This allows very broad or granular logging.

Setup

First, enable logging globally

SET mysql-eventslog_filename='queries.log';

The variable needs to be loaded at runtime, and eventually saved to disk:

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

Next, create query rules to match your logging desires. If you need to Log ALL queries, a simple rule will work:

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

If you don't trust Bob, you can log all of Bob's queries:

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

Now, make the rules active and persistent:

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

Reading Logged Queries

The queries are logged in binary format. There is a sample app included in source that can read the binary files and output plain text.

$ ./tools/eventslog_reader_sample /var/lib/proxysql/file1.log.00001258
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"
select sleep(1)
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"
SELECT DATABASE()
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"
select aaaaaaa

https://github.com/sysown/proxysql/tree/v1.4.4/tools

To build the sample app:

  • Clone the repo / Download the source
  • Change to tools directory
  • execute make

Related Issues and Feature Requests

Here's some related discussion on this feature.

Issue #561 -- Logging all queries.

Feature Request #871 -- Logging in JSON format for Splunk/ElasticStack, etc.

Feature Request #1184 -- Logging to Embedded Database.

 

Clone this wiki locally