How to Enable MySQL Log
- Database
- MySql
If you are a Web Developer, you need the reference to various log files, in order to debug your application or improve its performance. Logs are the best place to start troubleshooting. Concerning the famous MySQL database server (or MariaDB server), you might need to refer to the following log files:
The Error Log: It contains information about errors that occur while the server is running (also while starting and stopping a server)
The General Query Log: This is a general record of what mysqld is doing (Like connect, disconnect, queries ...)
The Slow Query Log: It consists of "slow" SQL statements (as indicated by its name)
To enable the logs in MySQL, we need some configurational changes to the various MySQL files. Taking these into consideration I have added the step-by-step process to configure MySQL.
1. In Terminal, issue the below command.,
nano /etc/mysql/my.cnf
This is the default setup for Logging and Replication (in the Debian server). In other distributions, the structure may be different, but you can always use MySQL server parameters:
# * Logging and Replication #
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file = /var/log/mysql/mysql.log #general_log = 1 #
# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf. #
# Here you can see queries with especially long duration #log_slow_queries = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes #
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change. #server-id = 1 #log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 10 max_binlog_size = 100M #binlog_do_db = include_database_name #binlog_ignore_db = include_database_nameAll log files are NOT enabled by default, Default Debian setup sends an Error log to syslog, the other log files are not enabled.
Error Log : Error Log goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf, which contains the following:
[mysqld_safe]
syslog
This is the recommended method. If, for some reason, you do not want the Error log to go to syslog, comment the above lines in /etc/mysql/conf.d/mysqld_safe_syslog.cnf or completely remove this file. Then, add the following lines to /etc/mysql/my.cnf
[mysqld_safe]
log_error=/var/log/mysql/mysql_error.log
[mysqld]
log_error=/var/log/mysql/mysql_error.logGeneral Query Log : To enable General Query Log, uncomment (or add) the relevant lines
general_log_file = /var/log/mysql/mysql.log
general_log = 1Slow Query Log : To enable Slow Query Log, uncomment (or add) the relevant lines
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 2
log-queries-not-using-indexesOnce done, remember to restart your MySQL server with the below command (Line 1 alone). And use the below commands to view the respective blog.
service mysql restart (or) systemctl restart mysql.service
//Display log results
//Error log
tail -f /var/log/syslog
//General Query log
tail -f /var/log/mysql/mysql.log
//Slow Query log
tail -f /var/log/mysql/mysql-slow.logIn case of any further issues, please do not hesitate to leave your comments. We will get in touch with you at the earliest possible,