Query Log for ProxySQL Official Translation_u 08_ProxySQL Configuration

Query Log for 08_ProxySQL Configuration

Note: Subsequent official updates to github were not written between 201904 and 201905
~
~

1. Query Logging

ProxySQL can record the query statements that pass through it (all statements, collectively referred to here as queries).Logs are configured with query rules, which allow logging to become broader or
Is detailed.

2. Create a log [Setup]

1. First, enable logging globally.

Admin> SELECT * FROM global_variables WHERE variable_name = 'mysql-eventslog_filename';
+--------------------------+----------------+
| variable_name            | variable_value |
+--------------------------+----------------+
| mysql-eventslog_filename |                |
+--------------------------+----------------+
1 row in set (0.00 sec)

Admin> SET mysql-eventslog_filename='queries.log';
Query OK, 1 row affected (0.00 sec)

Admin> SELECT * FROM global_variables WHERE variable_name = 'mysql-eventslog_filename';
+--------------------------+----------------+
| variable_name            | variable_value |
+--------------------------+----------------+
| mysql-eventslog_filename | queries.log    |
+--------------------------+----------------+
1 row in set (0.00 sec)

Note: The change log file queries.log will be generated in the datadir directory.

2. Load configuration into RUNTIME layer and persist to DISK layer

Admin> LOAD MYSQL VARIABLES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

Admin> SAVE MYSQL VARIABLES TO DISK;
Query OK, 110 rows affected (0.00 sec)

3. Next, create query rules on demand to match log records.

If you need to record all queries, you can use a simple rule like this:

Admin> SELECT * FROM mysql_query_rules ;
Empty set (0.00 sec)

Admin> INSERT INTO mysql_query_rules (rule_id, active, match_digest, log,apply) VALUES (1,1,'.',1,0);
Query OK, 1 row affected (0.00 sec)

Admin> SELECT * FROM mysql_query_rules \G;
*************************** 1. row ***************************
              rule_id: 1
               active: 1
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: .
        match_pattern: NULL
 negate_match_pattern: 0
         re_modifiers: CASELESS
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: NULL
            cache_ttl: NULL
   cache_empty_result: NULL
        cache_timeout: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
    next_query_flagIN: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: NULL
            error_msg: NULL
               OK_msg: NULL
          sticky_conn: NULL
            multiplex: NULL
  gtid_from_hostgroup: NULL
                  log: 1
                apply: 0
              comment: NULL
1 row in set (0.00 sec)

4. Keep rules active and persistent

Admin> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

Admin> SAVE MYSQL QUERY RULES TO DISK;
Query OK, 0 rows affected (0.06 sec)

3. Reading Logged Queries

Query information is recorded in binary format.Include an eventslog_reader_sample application in the source installation package that reads binaries and outputs plain text (not in the RPM package).
For example:

$ ./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

Tool program building process example:
1) Download the source package
2) Switch to Tools Directory
3) Execute make

Related Issues and Feature Requests

Below are some related discussions about this feature.

1) Issue #561 -- Record all queries.
https://github.com/sysown/proxysql/issues/561

2) Feature Request #871 -- Recorded in JSON format to support Splunk/ElasticStack.
https://github.com/sysown/proxysql/issues/871

3) Feature Request #1184 -- Recorded in Embedded Database
https://github.com/sysown/proxysql/issues/1184

~
~
Complete!

Tags: MySQL github RPM Database

Posted on Mon, 27 Apr 2020 10:34:31 -0700 by PhilVaz