How To Enable Logs in MySQL Server

The log file plays an important role during the troubleshooting of any application. It’s also useful to find about details about of running application. This article you will show you to how to enable different types of logs in MySQL server. Also, you will understand how to change log file locations in MySQL server.

There are mainly 3 types of log files in MySQL as followings:

• Error Log – This contains all the information about errors generated by MySQL server. This helps debugging of any issue occurred with MySQL service or database
• General Log – This contains all the general logs of MySQL activity like query, user connection or disconnect etc. It helps to find whats happening on MySQL server.
• Slow Query Log – This contains “slow” SQL statements, All the queries which is taking more time than expected in result of that application performance goes down

Error Log in MySQL

To Enable MySQL error log or change MySQL error log location. Edit MySQL configuration file and update following setting under [mysqld] as per your requirements. This file contains error generated in MySQL server.

 [mysqld]
 log_error = /var/log/mysql/error.log

General Log in MySQL

To enable general logs in MySQL or to change location of general log files, edit configuration file and make following changes. Uncomment following lines to enable general logs and change log file path to create log on different directory. This file contains all general logs of mysql server for eg: queries, user connect or disconnect etc.

 general_log_file   = /var/log/mysql/mysql.log
 general_log        = 1

Slow Query Log in MySQL

To enable or change file path of MySQL slow query logs. Edit MySQL configuration file and uncomment or add the following line in [mysqld] section. This file contains logs of those queries which is taking more time to complete. Which queries taking time more than defined as long_query_time are logged.

 log_slow_queries       = /var/log/mysql/mysql-slow.log
 long_query_time 	= 2

Restart MySQL Service

After making any of the above changes in MySQL configuration, You must have to restart MySQL services to apply changes. Basically, the service management differs with most of the Linux operating systems. Here is the command used for the popular Linux operating systems to restart MySQL service.
This method requires a server restart.

sudo service mysql restart
sudo systemctl restart mysql.service