Wednesday, July 31, 2013

MySQL : Monitor live MySQL queries

You can run the mysql command "show processlist" to see what queries are being processed at any given time, but that probably won't achieve what you're hoping for as this won’t show the whole SQL text.
 
Here are the steps to dump sql into a file for analysis.
 
mysql> SHOW VARIABLES LIKE "general_log%";

+------------------+----------------------------+
| Variable_name    | Value                      |
+------------------+----------------------------+
| general_log      | OFF                        |
| general_log_file | /var/run/mysqld/mysqld.log |
+------------------+----------------------------+
 
Turn on the SQL Logging and go to the general_log_file location.
 
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 disable the general_log parameter to OFF.

mysql> SET GLOBAL general_log = 'OFF';


or the performance will plummet and your disk will fill!

No comments: