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=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:
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 your mysql data directory, into a file named after the hostname with -slow.log on the end. For example:
/var/lib/mysql/MyServerName-slow.log
The performance degradation from logging all queries was not significant in my case, with my site under medium-high load.
1 Pingback