When debugging MySQL server performance, capturing all queries (not just slow ones) is crucial for comprehensive analysis. While SHOW FULL PROCESSLIST provides a snapshot, we need more robust solutions for continuous monitoring.

The most comprehensive approach is enabling MySQL's general query log:

SET GLOBAL general_log = 'ON';
SET GLOBAL log_output = 'FILE';
SET GLOBAL general_log_file = '/var/log/mysql/mysql-query.log';

Remember to disable it after your debugging session:

SET GLOBAL general_log = 'OFF';

For a lightweight alternative, create a shell script to periodically capture processlist:

#!/bin/bash
END_TIME=$((SECONDS+120))  # Run for 2 minutes
while [ $SECONDS -lt $END_TIME ]; do
    mysql -u root -p -e "SHOW FULL PROCESSLIST\\G" >> query_capture.log
    sleep 1
done

For MySQL 5.6+ with performance_schema enabled:

SELECT * FROM performance_schema.events_statements_current;
SELECT * FROM performance_schema.events_statements_history;
SELECT * FROM performance_schema.events_statements_history_long;

Use tools like pt-query-digest from Percona Toolkit to analyze the logs:

pt-query-digest /var/log/mysql/mysql-query.log > query_analysis.txt

• General query log creates significant I/O overhead - use temporarily
• Rotate logs frequently when monitoring for extended periods
• Consider using TCPdump for network-level capture in distributed systems
• For cloud MySQL instances, check provider-specific monitoring tools

When debugging MySQL performance issues, having a complete query log is invaluable. While many developers are familiar with the slow query log, capturing all queries provides more comprehensive insights. Here are the most effective approaches:

The most straightforward way to log all queries is by enabling the general query log. This can be done temporarily without restarting the server:

SET GLOBAL general_log = 'ON';
SET GLOBAL log_output = 'FILE';
SET GLOBAL general_log_file = '/var/log/mysql/mysql-query.log';

To revert after capturing:

SET GLOBAL general_log = 'OFF';

For those who prefer the SHOW FULL PROCESSLIST approach, here's a bash script to capture it periodically:

#!/bin/bash
LOG_FILE="/tmp/mysql_queries_$(date +%Y%m%d_%H%M%S).log"
INTERVAL=1 # seconds

while true; do
    echo "==== $(date) ====" >> $LOG_FILE
    mysql -u [username] -p[password] -e "SHOW FULL PROCESSLIST" >> $LOG_FILE
    sleep $INTERVAL
done

For MySQL 5.6+ servers, the Performance Schema offers detailed query monitoring:

-- Enable events_statements_history
UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME = 'events_statements_history';

-- Query captured statements
SELECT * FROM performance_schema.events_statements_history
WHERE SQL_TEXT IS NOT NULL;

For more advanced analysis, the Percona Toolkit provides excellent tools:

# Capture TCP traffic and decode MySQL queries
tcpdump -i any -s 65535 -x -nn -q -tttt port 3306 -c 1000 > mysql.tcp
pt-query-digest --type tcpdump mysql.tcp

• General query log can impact performance on busy servers
• Log file rotation is recommended for long-term logging
• Consider filtering specific databases/users if needed
• Always secure log files containing sensitive query data