How to enable MySQL Query Log?

MySQLMySQLi Database

To enable query log, use the command SET global. You cannot use set general_log in MySQL version 8.0.12 version. If you are using version 8.0.12, then you will get the below given error. Here, we are trying to set general_log to see what error will come −

mysql> SET general_log = 1;

The following is the output −

ERROR 1229 (HY000): Variable 'general_log' is a GLOBAL variable and should be set with SET GLOBAL

Now, we will use the global command with general_log. The query is as follows −

mysql> SET global general_log = 1;
Query OK, 0 rows affected (0.10 sec)

To check it is enabled or not, use the SHOW command −

mysql> SHOW variables like '%general_log%';

The following is the output −

+------------------+---------------------+
| Variable_name    | Value               |
+------------------+---------------------+
| general_log      | ON                  |
| general_log_file | DESKTOP-QN2RB3H.log |
+------------------+---------------------+
2 rows in set (0.03 sec)

From the above sample output, you can see that it got enabled by giving value 1. We can disable it by giving the value 0. The following is the query to disable −

mysql> SET global general_log = 0;
Query OK, 0 rows affected (0.06 sec)

Now let us check the same using the following query −

mysql> SHOW variables like '%general_log%';

The following is the output −

+------------------+---------------------+
| Variable_name    | Value               |
+------------------+---------------------+
| general_log      | OFF                 |
| general_log_file | DESKTOP-QN2RB3H.log |
+------------------+---------------------+
2 rows in set (0.00 sec)
raja
Published on 18-Oct-2018 12:47:39
Advertisements