If you leave slow query logging running you are eating up even more
resources.
One must at least define the log files in my.cnf
general_log_file=/var/log/mysql/mysql-general.log
slow_query_log_file=/var/log/mysql/mysql-slow.log
Just don’t turn on slow logging in my.cnf. Can turn it on and off with two scripts … one to turn on slow logging = slowlogon
and one to turn it off … slowlogoff. Make them executable by root user only. Both in /usr/local/bin/ means you can launch them from any location on server. Replace root and pass below with your superuser credentials.
slowlogon
# to turn on:
mysql -e ‘set global log = 1′ -u root -p’pass’
mysql -e ‘set global log_slow_queries = 1′ -u root -p’pass’
mysql -e ‘set global slow_query_log = 1′ -u root -p’pass’
multitail -i /var/log/mysql/mysql-general.log -i /var/log/mysql/mysql-slow.log;
The multitail line will show in realtime.
slowlogoff
# to turn off
set global slow_query_log = 0
set global log_slow_queries = 0
set global log = 0;
Command to look at the slow log by itself:
mysqldumpslow mysql-slow.log
For a quickie, one could:
fgrep ‘quiz’ /var/log/mysql/mysql-slow.log
or for assignments
fgrep ‘assign’ /var/log/mysql/mysql-slow.log
https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html