Skip to main content

Query Log for MySQL

Create a query workload from a MySQL query log, either a slow query log or a general query log.

How to get queries logged​

There are several global parameters to enable query logging in MySQL database.

Slow query log​

The slow query log consists of SQL statements that take more than long_query_time seconds to execute and require at least min_examined_row_limit rows to be examined.

  • Enable

    set global log_output='file'
    set global slow_query_log='on';
    set global slow_query_log_file = '/tmp/localhost-slow.log';
    set global long_query_time = 5;

  • Check

     show variables like '%slow%';

  • Log queries not using indexes

    By default, queries that do not use indexes for lookups are not logged. This behavior can be changed using log_queries_not_using_indexes.

    set global log_queries_not_using_indexes=on;

General query log​

The general query log is a general record of what mysqld is doing. The server writes information to this log when clients connect or disconnect, and it logs each SQL statement received from clients.

  • Enable 
    set global general_log_file='/tmp/general_log';
    set global general_log=on;
  • Verify 
    show global variables like '%general%';
    +------------------+------------------+
    | Variable_name | Value |
    +------------------+------------------+
    | general_log   | ON |
    | general_log_file | /tmp/general_log |
    +------------------+------------------+

References​

https://dev.mysql.com/doc/refman/8.0/en/slow-query-log.html
https://dev.mysql.com/doc/refman/8.0/en/query-log.html