Query Logging

ProxySQL is able to log queries that pass through.
Before version 2.0.6 , logging is configured with Query Rules using mysql_query_rules.log: this allows very broad or granular logging.
From version 2.0.6 , a new global variable was added: mysql-eventslog_default_log .
If no matching rule specifies a value mysql_query_rules.log , mysql-eventslog_default_log applies.
The default value for mysql-eventslog_default_log is 0, and the possible values are 0 and 1 .

Setup

First, enable logging globally

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

Next, create query rules to match your logging desires. If you need to log ALL queries processed by the query processor, a simple rule will work:

Note that not all queries are processed by the query processor. Some special queries like commit, rollback and set autocommit are handled before the query processor. If you want to log also such queries it is required to enable logging globally.

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

If you want to log all INSERT statements against table tableX:

Now, make the rules active and persistent:

Query Logging Format before 2.0.6

Before version 2.0.6 , 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. The sample app is not included in the binary distribution.

https://github.com/sysown/proxysql/tree/v2.0.5/tools

To build the sample app:

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

Query Logging Format from 2.0.6

In version 2.0.6 a new variable controls the query logging format: mysql-eventslog_format.
Possible values:

  • 1 : this is the default: queries are logged in binary format (like before 2.0.6)
    Note that in version 2.0.6 better support was introduced for prepared statements and the logging of rows_affected and rows_sent. For this reason make sure to use an updated eventslog_reader_sample to read these files.
  • 2 : the queries are logged in JSON format.

JSON format logging

To enable logging in JSON format it is required to set mysql-eventslog_format=2.

Example of JSON logging:

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.