Log files allow database developers to keep track of performance and errors in their databases. There are three log files in every MySQL database – error log, general query log and slow query log. Error log file tracks all MySQL errors that occur in your database, general query log tracks all queries & connections sent to your database and slow query log tracks all slow running queries that run beyond specific threshold. By default, no log files are enabled in MySQL and all logs are present in /var/log/syslog files. In this article, we will learn how to enable and view MySQL log files.
How to View MySQL Log Files
First of all, open the file /etc/mysql/conf.d/mysqld_safe_syslog.cnf and comment or remove the following lines. This will stop MySQL from logging errors to /var/log/syslog file.
You can also leave them unchanged if you want to continue logging in syslog.
Next, open MySQL configuration file at /etc/mysql/my.cnf.
To enable error log, add the following lines.
[mysqld_safe] log_error=/var/log/mysql/mysql_error.log [mysqld] log_error=/var/log/mysql/mysql_error.log
To enable general query log, add the following lines.
general_log_file = /var/log/mysql/mysql.log general_log = 1
To enable slow query log, add the following lines.
log_slow_queries = /var/log/mysql/mysql-slow.log long_query_time = 2 log-queries-not-using-indexes
Save and close the file. Restart MySQL server to apply changes.
$ service mysql restart
The above changes are permanent and will enable MySQL logging.
You can also enable MySQL log during a session with the following SQL queries. But these last only as long as your session lasts.
SET GLOBAL general_log = 'ON'; SET GLOBAL slow_query_log = 'ON';
In this article, we have learnt how to view MySQL log files. You can use them to easily monitor your database performance and errors.
How to Run MySQLdump without Locking Tables
How to Check if MySQL Database Exists
How to Check if String Starts With Another String in JS
How to Check if Variable is Undefined in JS