Percona MySQL: Collecting All Queries with the Slow Query Log

If you want to collect all queries running on your database for a period of time, for analysis or replay, the simplest way to do this is using the Slow Query Log.

To start collecting:

set global slow_query_log_file='/var/log/mysql/slow.log';
SET GLOBAL slow_query_log=1;
SET GLOBAL long_query_time=0;
SET GLOBAL log_slow_admin_statements=1;
SET GLOBAL log_queries_not_using_indexes=1;
SET GLOBAL slow_query_log_always_write_time=0;
set global slow_query_log_use_global_control="all";

To stop collecting and revert setting changes:

SET GLOBAL slow_query_log=0;
SET GLOBAL long_query_time=10;
SET GLOBAL log_slow_admin_statements=0;
SET GLOBAL log_queries_not_using_indexes=0;
SET GLOBAL slow_query_log_always_write_time=10;

The statements will be logged to:

/var/log/mysql/slow.log

The performance degradation from logging all queries was not significant in my case, with my site under medium-high load.

You may have to vary the slow_query_log_file location in the first line based on your configuration of secure_log_path.