How to enable MySQL's query log (see all SQL queries as they come)

Question

When developing an application that uses MySQL, it can be useful to watch in real time what requests are being made.

How to see all incoming MySQL queries?

Answer 1

As root, edit /etc/mysql/my.cnf and add this paragraph at the bottom:

[mysqld]
general_log=on
general_log_file=/var/log/mysql/query.log

Still as root, run these commands:

service mysql stop
service mysql start

Then observe the log:

tail -f /var/log/mysql/query.log

Please beware that this setting makes MySQL about 15% slower, so remove or comment the paragraph (then restart) when you don't need it anymore.

Answer 2

Although Nicolas Raoul's answer is right on point, note that as of mysql-server-5.7, the /etc/mysql/my.cnf file is constructed from the config files in both /etc/mysql/conf.d and /etc/mysql/mysqld.conf.d.

So for the solution to work, the file: /etc/mysql/mysqld.conf.d/mysqld.cnf has to be modified. So, the lines:

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

Have to be uncommented, the file saved, and the service restarted with sudo service mysql restart.

Otherwise, you might get errors.

Answer 3

Run this command using linux shell script

mysqladmin -uroot -p'mysql_password ' -i 2 processlist

Will generate processlist and automatically refresh on every minutes