Show MySQL Queries

Last Updated on 14/11/2020

This tutorial will guide you on how to show MySQL queries in a log file, in order to make any executed query available for further analysis.

Once you’ve logged into your MySQL instance, run the following command to gather information about the location of your log file:

mysql> SHOW VARIABLES LIKE 'general_log%';
+------------------+-----------------------------+
| Variable_name    | Value                       |
+------------------+-----------------------------+
| general_log      | OFF                         |
| general_log_file | /var/lib/mysql/centos7.log  |
+------------------+-----------------------------+

The above command shows that the query logging is OFF through the general_log variable, also that the location of the log file is /var/lib/mysql/centos7.log.

Run the query below to switch the query logging on:

mysql> SET GLOBAL general_log = 'ON';

Then use your favourite method to view the log file whilst your database processes the MySQL queries:

[root@centos7 ~]# tail -f /var/lib/mysql/centos7.log
	SELECT `user`.* FROM `user` WHERE `user`.`status` = 1 ORDER BY `user`.`id_user` DESC LIMIT 50
	SELECT  `user`.* FROM `user` INNER JOIN `project` ON `user`.`id_user` = `project`.`id_user_owner`
	...

Please note that this approach consumes a huge amount of disk space and reduce the database performance, so it is recommended to turn it off when your analysis is done:

mysql> SET GLOBAL general_log = 'OFF';

Hope it helps.
Cya!