How to see log files in MySQL?

Question

Here is a simple way to enable them. In mysql we need to see often 3 logs which are mostly needed during any project development.

• The Error Log. It contains information about errors that occur while the server is running (also server start and stop)

• The General Query Log. This is a general record of what mysqld is doing (connect, disconnect, queries)

• The Slow Query Log. Ιt consists of "slow" SQL statements (as indicated by its name).

By default no log files are enabled in MYSQL. All errors will be shown in the syslog (/var/log/syslog).

To Enable them just follow below steps:

step1: Go to this file (/etc/mysql/conf.d/mysqld_safe_syslog.cnf) and remove or comment those line.

step2: Go to mysql conf file (/etc/mysql/my.cnf) and add following lines

To enable error log add following

[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 following

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

To enable Slow Query Log add following

log_slow_queries       = /var/log/mysql/mysql-slow.log
long_query_time = 2
log-queries-not-using-indexes

step3: save the file and restart mysql using following commands

service mysql restart

To enable logs at runtime, login to mysql client (mysql -u root -p) and give:

SET GLOBAL general_log = 'ON';
SET GLOBAL slow_query_log = 'ON';

Finally one thing I would like to mention here is I read this from a blog. Thanks. It works for me.

Click here to visit the blog

The MySQL logs are determined by the global variables such as:

• log_error for the error message log;
• general_log_file for the general query log file (if enabled by general_log);
• slow_query_log_file for the slow query log file (if enabled by slow_query_log);

To see the settings and their location, run this shell command:

mysql -se "SHOW VARIABLES" | grep -e log_error -e general_log -e slow_query_log

To print the value of error log, run this command in the terminal:

mysql -e "SELECT @@GLOBAL.log_error"

To read content of the error log file in real time, run:

sudo tail -f $(mysql -Nse "SELECT @@GLOBAL.log_error")

^{Note: Hit Control-C when finish}

When general log is enabled, try:

sudo tail -f $(mysql -Nse "SELECT CONCAT(@@datadir, @@general_log_file)")

---

To use mysql with the password access, add -p or -pMYPASS parameter. To to keep it remembered, you can configure it in your ~/.my.cnf, e.g.

[client]
user=root
password=root

So it'll be remembered for the next time.

You have to activate the query logging in mysql.

1. edit /etc/my.cnf

   [mysqld]
   log=/tmp/mysql.log
   

2. restart the computer or the mysqld service

   service mysqld restart
   

3. open phpmyadmin/any application that uses mysql/mysql console and run a query

4. cat /tmp/mysql.log ( you should see the query )

From the MySQL reference manual:

By default, all log files are created in the data directory.

Check /var/lib/mysql folder.

In my (I have LAMP installed) /etc/mysql/my.cnf file I found following, commented lines in [mysqld] section:

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

I had to open this file as superuser, with terminal:

sudo geany /etc/mysql/my.cnf

(I prefer to use Geany instead of gedit or VI, it doesn't matter)

I just uncommented them & save the file then restart MySQL with

sudo service MySQL restart

Run several queries, open the above file (/var/log/mysql/mysql.log) and the log was there :)

In addition to the answers above you can pass in command line parameters to the mysqld process for logging options instead of manually editing your conf file. For example, to enable general logging and specifiy a file:

mysqld --general-log --general-log-file=/var/log/mysql.general.log

Confirming other answers above, mysqld --help --verbose gives you the values from the conf file (so running with command line options general-log is FALSE); whereas mysql -se "SHOW VARIABLES" | grep -e log_error -e general_log gives:

general_log     ON
general_log_file        /var/log/mysql.general.log

Use slightly more compact syntax for the error log:

mysqld --general-log --general-log-file=/var/log/mysql.general.log --log-error=/var/log/mysql.error.log

To complement loyola's answer it is worth mentioning that as of MySQL 5.1 log_slow_queries is deprecated and is replaced with slow-query-log

Using log_slow_queries will cause your service mysql restart or service mysql start to fail