0

Rolando had posted as an answer in other questions how to enable the general log as a table, I have done this but I would like to schedule cleaning in order to only keep the last 3 days. He posted this, but I'm unsure on how to use it, could I use this as a bash script and add it to cron as a scheduled task?

SET @old_log_state = @@global.general_log; 
SET GLOBAL general_log = 'OFF'; 
CREATE TABLE mysql.general_log_new LIKE mysql.general_log; 
INSERT INTO mysql.general_log_new 
SELECT * FROM mysql.general_log WHERE event_time > NOW() - INTERVAL 3 DAY; 
DROP TABLE mysql.general_log; 
ALTER TABLE mysql.general_log_new RENAME mysql.general_log; 
SET GLOBAL general_log = @old_log_state; 
1

It won't cause problems, but you will lose all the old log entries, so advice above is good.

You can do a backup of logs if you want with:

mysqldump -p --lock_tables=false mysql general_log > genlog.sql

Do you need to have the general log on all the time? I usually only turn it on when I'm troubleshooting performance issues. MySQL logs EVERYTHING there (client connects, disconnects, and EVERY statement). In most systems, the logs get very big very quickly. There is also some performance overhead for this.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.