Skip to main content

Enable MySQL General Query Log

Jason Brown
  • Updated

Introduction

This article aims to provide instructions on how to enable the MySQL general log.

You might want to enable the general log if you need to see what is occurs in MySQL or what queries run.

Procedure

Consult with your version of MySQL's reference manual before applying changes. For MySQL 5.7, this is here.

You can confirm the state of the Query Log:

mysql> SHOW GLOBAL VARIABLES LIKE 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log | OFF |
+---------------+-------+
1 row in set (0.00 sec)

You'll need to edit /etc/my.cnf and restart MySQL or MariaDB to ensure the change takes effect.

Open /etc/my.cnf with your favorite text editor. Ensure that there is a line that contains "[mysqld]" and add these two lines somewhere underneath it specifying the "general_log" variable. The general_log_file variable is optional if it is already set in mysql.

# nano /etc/my.cnf
[mysqld]
...
general_log=1
general_log_file=query.log

Save this file (CTRL + o and CTRL + x if using nano), then restart the SQL service.

/usr/local/cpanel/scripts/restartsrv_mysql

To confirm the variable is set in MySQL, run the following as root in SSH which should return "1" for enabled:

mysql -e "SELECT @@GLOBAL.general_log;"

Note: Enabling this option in MySQL will write a large amount of data, so you may experience IO issues or suffer from a large amount of disk space used. You will want to monitor the usage and potentially set up a rotation of the log with a tool such as logrotate.

Related articles