linux - How to parse MySQL slow query log for last 24 hours of entries -

linux - How to parse MySQL slow query log for last 24 hours of entries -

i'd process mysql slow query log , retrieve lastly 24 hours of entries, log rotation isn't alternative @ present.

below illustration log entry

# query_time: 0.000431 lock_time: 0.000124 rows_sent: 8 rows_examined: 25 set timestamp=1415792064; select `username`, `password`, `date_created` joomla_users order `kid` desc; # user@host: joomla[joomla] @ [192.168.168.100]

i utilize awk or grep or similar solution executed via command line or part of script identify entries placed in mysql slow query log file within lastly 24 hours comparing "set timestamp" line each entry , place said entries separate log file.

i recommend using percona toolkit's query digest tool sort of analysis. it's straightforward install , you'd want analysis of slow queries (analyze them based on query footprints, not individual arguments in queries, exclude types of queries based on regex expressions, etc)

take percona toolkit (http://www.percona.com/doc/percona-toolkit/2.1/pt-query-digest.html). if decide utilize it, next code should easy adapt

pt-query-digest --since '2014-11-01' --filter '$event->{arg} =~ m/^select/i' /var/log/mysql/mysql-slow-query.log > /tmp/pt_slow_nov

what that's saying analyze queries 2014-11-01 onward, @ select queries, read slow query log /var/log/mysql/slow-query.log (obviously you'll need reference file location) , output analysis /tmp/pt_slow_nov. if you're familiar grep , awk, should pretty straightforward parameterize --since portion of query , stick in cron.

mysql linux awk sed grep