Managing the general query log and slow query log

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. The general query log can be very useful when you suspect an error in a client and want to know exactly what the client sent to mysqld:

Specify the file for logging. If you do not specify, it will be created in the data directory with the name hostname.log.

SET @@GLOBAL.general_log_file='/var/log/mysql/general_query_log';

Enable the general query log:

SET GLOBAL general_log = 'ON';

SHOW DATABASES;

You can see that the queries are logged:

sudo cat /var/log/mysql/general_query_log

/usr/sbin/mysqld, Version: 8.0.12 (MySQL Community Server - GPL). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
2018-09-10T05:36:44.665674Z         8 Query     SHOW DATABASES

General query log generates a very big log file. Be very cautious when enabling it on a production server. It drastically affects the server's performance.

Slow query log

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

To log all the queries, you can set the value of long_query_time to 0. The default value of long_query_time is 10 seconds and min_examined_row_limit is 0.

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

To enable slow query log, you can dynamically set slow_query_log=1 and you can set the filename using slow_query_log_file. To specify the log destination, use --log-output:

Verify long_query_time and adjust it as per your requirement:

SELECT @@GLOBAL.LONG_QUERY_TIME;

+--------------------------+
| @@GLOBAL.LONG_QUERY_TIME |
+--------------------------+
|                10.000000 |
+--------------------------+
1 row in set (0.00 sec)

SET @@GLOBAL.LONG_QUERY_TIME=1;

SELECT @@GLOBAL.LONG_QUERY_TIME;

+--------------------------+
| @@GLOBAL.LONG_QUERY_TIME |
+--------------------------+
|                 1.000000 |
+--------------------------+
1 row in set (0.00 sec)

Verify the slow query file. By default, it would be in the data directory with the hostname-slow log:

SELECT @@GLOBAL.slow_query_log_file;

+-------------------------------------------+
| @@GLOBAL.slow_query_log_file              |
+-------------------------------------------+
| /var/lib/mysql/mysq-4gb-2vcpu-00-slow.log |
+-------------------------------------------+
1 row in set (0.00 sec)

SET @@GLOBAL.slow_query_log_file='/var/log/mysql/mysql_slow.log';

SELECT @@GLOBAL.slow_query_log_file;

+-------------------------------+
| @@GLOBAL.slow_query_log_file  |
+-------------------------------+
| /var/log/mysql/mysql_slow.log |
+-------------------------------+
1 row in set (0.00 sec)

FLUSH LOGS;

Enable the slow query log:

SELECT @@GLOBAL.slow_query_log;

+-------------------------+
| @@GLOBAL.slow_query_log |
+-------------------------+
|                       0 |
+-------------------------+
1 row in set (0.00 sec)

SET @@GLOBAL.slow_query_log=1;

SELECT @@GLOBAL.slow_query_log;

+-------------------------+
| @@GLOBAL.slow_query_log |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.00 sec)

Verify that the queries are logged (You have to execute few long running queries to see them in slow query log):

SELECT SLEEP(2);

sudo cat /var/log/mysql/mysql_slow.log

/usr/sbin/mysqld, Version: 8.0.12 (MySQL Community Server - GPL). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument

Selecting query log output destinations

You can log the queries to either FILE or TABLE in MySQL itself by specifying the log_output variable.

SET @@GLOBAL.log_output='TABLE';

SET @@GLOBAL.general_log='ON';

Execute a few queries and then query the mysql.general_log table:

SELECT * FROM mysql.general_log WHERE command_type='Query' \G

*************************** 1. row ***************************
  event_time: 2018-09-10 05:46:11.528098
   user_host: root[root] @ localhost []
   thread_id: 8
   server_id: 1
command_type: Query
    argument: SELECT * FROM mysql.general_log WHERE command_type='Query'
1 row in set (0.01 sec)

You can use the slow_log table in a similar way:

SET @@GLOBAL.slow_query_log=1;

SET @@GLOBAL.long_query_time=1;

SELECT SLEEP(10);

SELECT * FROM mysql.slow_log \G

SELECT * FROM mysql.slow_log \G
*************************** 1. row ***************************
    start_time: 2018-09-10 05:48:56.908864
     user_host: root[root] @ localhost []
    query_time: 00:00:10.000638
     lock_time: 00:00:00.000000
     rows_sent: 1
 rows_examined: 0
            db: employees
last_insert_id: 0
     insert_id: 0
     server_id: 1
      sql_text: SELECT SLEEP(10)
     thread_id: 8
1 row in set (0.00 sec)