The MySQL query log is a log file that contains a record of all queries executed on a MySQL server. This log can be useful for troubleshooting issues with the server and tracking down slow or inefficient queries. In this post, we will explain how to enable and disable the MySQL query log, as well as how to view the contents of the log file.
To enable the MySQL query log, you will need to edit your MySQL configuration file. This file is typically called my.cnf or
First, open the configuration file in a text editor, such as nano or vi. Then, add the following lines to the [mysqld] section of the file:
general_log_file = /var/log/mysql/query.log general_log = 1
These lines will enable the MySQL query log and specify the location of the log file. By default, the log file will be called query.log and will be stored in the /var/log/mysql directory. You can change the location and name of the log file by modifying these settings to suit your needs.
Once you have added these lines to the configuration file, save and close the file. Then, restart the MySQL server to apply the changes. On Linux systems, you can do this by running the following command:
sudo service mysql restart
After the MySQL server has restarted, all queries executed on the server will be logged to the query log file. You can view the contents of the log file using any text editor or by using the tail command to view the most recent entries. For example, to view the last 10 lines of the log file, you can use the following command:
tail -n 10 /var/log/mysql/query.log
Keep in mind that enabling the MySQL query log can have an impact on the performance of your server, as it will require additional resources to write the log entries. Therefore, it is recommended to use the query log only for troubleshooting purposes and to disable it when it is not needed. To disable the query log, simply set the general_log setting to 0 in the MySQL configuration file and restart the server.
In conclusion, to enable the MySQL query log, you will need to edit the MySQL configuration file and add the general_log_file and general_log settings. Then, restart the MySQL server to apply the changes and all queries executed on the server will be logged to the specified log file.
In addition to enabling the MySQL query log in the configuration file, you can also enable it temporarily from within the MySQL shell. This can be useful if you only need to enable the query log for a short period of time, such as for troubleshooting a specific issue.
To enable the query log from within the MySQL shell, connect to the MySQL server using the mysql command and your MySQL username and password. Then, run the following command:
SET GLOBAL general_log = 'ON';
This will enable the query log and all subsequent queries executed on the server will be logged to the default log file, which is /var/log/mysql/query.log. You can also specify a different log file location by using the general_log_file variable, like this:
SET GLOBAL general_log_file = '/path/to/log/file';
Once you have enabled the query log, you can view the contents of the log file using any text editor or by using the tail command, as described in the previous answer.
To disable the query log, simply run the following command from within the MySQL shell:
SET GLOBAL general_log = 'OFF';
This will disable the query log and stop logging queries to the log file. Keep in mind that this will only disable the query log temporarily. If you restart the MySQL server, the query log will be disabled again unless it is enabled in the configuration file.