How can I view live MySQL queries?

barfoon picture barfoon · Feb 20, 2009 · Viewed 570.6k times · Source

How can I trace MySQL queries on my Linux server as they happen?

For example I'd love to set up some sort of listener, then request a web page and view all of the queries the engine executed, or just view all of the queries being run on a production server. How can I do this?

Answer

artfulrobot picture artfulrobot · Sep 19, 2011

You can log every query to a log file really easily:

mysql> SHOW VARIABLES LIKE "general_log%";

+------------------+----------------------------+
| Variable_name    | Value                      |
+------------------+----------------------------+
| general_log      | OFF                        |
| general_log_file | /var/run/mysqld/mysqld.log |
+------------------+----------------------------+

mysql> SET GLOBAL general_log = 'ON';

Do your queries (on any db). Grep or otherwise examine /var/run/mysqld/mysqld.log

Then don't forget to

mysql> SET GLOBAL general_log = 'OFF';

or the performance will plummet and your disk will fill!