Mysql – how to enable query log

It may be useful to enable mysql query log on development of even production server for a short duration for debugging or code/flow analysis purpose. Here are the steps for enabling mysql query log on Ubuntu Linux. The similar instructions can be used on other Linux flavours also.

First locate [mysql] section inside /etc/mysql/my.cnf. You will have to use sudo to edit the config file. Add the following line:

[mysqld]
...
...
...
## Log all queries.
log=/var/log/mysql/query.log

Note that this can fill up disk space fast. So avoid doing it permanently in production.

Now restart mysql, visit some page on site which will cause some mysql queries and check the log file as shown below:

$ sudo service mysql restart
$ sudo chmod a+r /var/log/mysql/query.log
$ tail -f /var/log/mysql/query.log

For a wordpress site, you should see something like this in query.log when you visit home page:

151115 18:11:55	    9 Connect	SOMEUSER@localhost on 
		    9 Query	SET NAMES utf8mb4
		    9 Query	SELECT @@SESSION.sql_mode
		    9 Init DB	DBNAME
		    9 Query	SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes'
		    9 Query	SELECT option_value FROM wp_options WHERE option_name = '_transient_timeout_facebook_locale' LIMIT 1
...
...
...