I’m working on a Linux server and need to watch MySQL database queries as they execute in real time. I want to be able to monitor what’s happening when users visit my website or when the application runs different operations.
Basically I’m looking for a way to set up some kind of monitoring tool that will show me all the SQL statements being processed by the database server. This would help me debug performance issues and understand what queries are being generated by my application.
Is there a built-in MySQL feature for this or do I need to use external tools? I’ve heard about query logs but I’m not sure if that’s the right approach for live monitoring. Any suggestions on how to accomplish this would be really helpful.
MySQL slow query log and pt-query-digest from Percona Toolkit have been invaluable for years. While the general log captures everything, the slow query log helps focus on problematic queries. Adjust long_query_time=0 to catch all queries or set it to 2 for identifying only slow ones. The magic of pt-query-digest comes from its ability to analyze logs to reveal query patterns, execution counts, and average response times. I typically run it weekly on production for spotting optimization opportunities. During troubleshooting, I also use Performance Schema queries like SELECT * FROM performance_schema.events_statements_current WHERE SQL_TEXT IS NOT NULL; for real-time insights into currently running queries without the overhead of file logging. Performance Schema is beneficial as it eliminates log file management while providing detailed execution statistics.
You want the general query log. Enable it by setting general_log=1 and general_log_file=/path/to/logfile in your MySQL config, then restart. Once it’s running, use tail -f /path/to/logfile to watch all queries execute in real-time. I’ve used this tons for debugging production issues and it works great. Just heads up - it logs everything including connections and non-query stuff. Log files get huge under heavy load, so make sure you’ve got disk space and rotate them regularly. You can also enable it temporarily without restarting by running SET GLOBAL general_log = 'ON'; directly in MySQL. Super handy when you need to troubleshoot fast without downtime.
hey, just wanted to say you can use mysqladmin processlist to see current queries. or try SHOW PROCESSLIST; in the MySQL console. for constant monitoring, use watch -n 1 'mysqladmin processlist'. it’ll refresh every sec and helpful for tracking down issues!