In this post, I provide different parameters to enable logging for General and Long running queries in MySQL.
Using this approach, you do not require to restart your MySQL Server. You can easily configure this log parameter and you can see the output in Log table.
As a Database Administrator, it is our responsibility to log all different kinds of queries because the log is our primary requirement for any kind of troubleshooting.
First check, current status of the different Log Parameters:
SHOW VARIABLES LIKE '%log%';
Using above statement, you can check the current value of log parameter.
Now enable logging for general query and save the output into “mysql.general_log” table:
SET GLOBAL log_output = 'TABLE';
SET GLOBAL general_log = 'ON';
SELECT *FROM mysql.general_log;
Now enable logging for long running queries:
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time= 1;
SET GLOBAL log_queries_not_using_indexes= ON;
Sometimes, it is required to execute the FLUSH command of log to get immediate updates into the Log Table.
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.