Background
You have some sticky queries but you can’t see what’s happening. On certain website actions pages are just slow and you fully suspect the database queries are broken. Or you want to reverse engineer another piece of software’s database queries.
By default MySQL logging is turned off. I imagine this is for performance reasons, because writing every single query to disk would means a busy, and eventually huge, log file.
MySQL has at least “general” logging that shows everything, and then “slow query” logging. In a troublesome situation both are incredibly valuable.
Solution
How you can turn on MySQL query logging. Go into the MySQL console and do:
SET GLOBAL general_log = 'ON'; SET GLOBAL general_log_file = '/var/lib/mysql/mysql.log';
To see if this worked, do this:
SHOW VARIABLES LIKE '%general_log%';
Keep the console open, because when you’re done you want to set the “ON” above back to “OFF”.
Open another terminal and do:
tail -f /var/lib/mysql/mysql.log
Slow Query Logging
Slow query logging show you inefficient queries. This is useful on large databases or tables where perhaps there is no index, or perhaps an actually problem with the way the query is implemented.
To check if slow query logging is on and where the default log file is located:
mysql> SHOW VARIABLES LIKE '%slow_query%';
To turn it on:
SET GLOBAL slow_query_log = 'ON';
By default it will show anything slower than ten seconds, but you can reduce it to any figure:
SET long_query_time = 1;
or
SET long_query_time = 0.1;
If you only want to see slow queries for a specific database, do this:
tail -f /var/lib/mysql/mysql-slow.log | grep -A 3 your_db_name
Reference
- https://dev.mysql.com/doc/refman/8.0/en/show-variables.html
- https://docs.cpanel.net/knowledge-base/sql/how-to-enable-the-slow-query-log-in-mysql-or-mariadb/
- http://www.andrew-kirkpatrick.com/2015/04/quick-way-to-enable-mysql-general-query-log-without-restarting/
- https://stackoverflow.com/questions/10823991/how-to-log-mysql-slow-queries-for-specific-database