how to log all queries

[Eric Hernandez]

Group,

How exactly do you log all queries?  I am trying to make a proxy thats main function is to log all queries to disk.

I have tried setting mysql-eventslog_filename in the main and stats database but it doesn't actually write anything to disk. 

It does create the log files and they can be rotated but they are always 0 bytes

-rw-------  1 root  root      0 Sep 13 11:26 proxysql.log.00000001

-rw-------  1 root  root      0 Sep 13 11:17 proxysql.log.00000002

mysql> update global_variables set variable_value  = '/var/log/proxysql.log' where variable_name = 'mysql-eventslog_filename';

Query OK, 1 row affected (0.00 sec)

mysql> select * from runtime_global_variables where variable_name = 'mysql-eventslog_filename';

+--------------------------+-----------------------+

| variable_name            | variable_value        |

+--------------------------+-----------------------+

| mysql-eventslog_filename | /var/log/proxysql.log |

+--------------------------+-----------------------+

1 row in set (0.00 sec)

mysql> LOAD MYSQL VARIABLES TO RUNTIME;

Query OK, 0 rows affected (0.00 sec)

Also how do you convert these binary log files to clear text?

Thanks,

-Eric H

[Raja Kadali]

Hi Eric,

You have to set a mysql query rule to get the logging of the queries that you want.

eg: 

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

LOAD MYSQL QUERY RULES TO RUNTIME;

SAVE MYSQL QUERY RULES TO DISK;

In the given example, the  rule will make sure that all the queries coming from usename 'raja' gets logged into the file that you mentioned as events file in the variables.

If you want longs from all users just ignore adding a username(default is null)

Also, the log files are binary, you cant open them using normal text editors. proxysql give a tool to read these files, it is eventslog_reader_sample you can check this in the github page

Regards,

-Raja.

[kph...@platform9.com]

worked perfectly. thanks.