Tag: slow_log

How to remove or compress huge MySQL general and query log table

If you have enabled MySQL general or slow logging, it can create quite big log, depending upon your MySQL usage/queries.
So we may have to periodically clear them to save space.

Please note that MySQL can save logs to either table or files. This document assumes you are using table as log output.

Files: slow_log.CSV and general_log.CSV (The location and the name of the file can be different)

By default, logging is to CSF file.

MYSQL supports run time clearing of these logs. So no need to restart the MySQL service.
Never delete the CSV file directly. It can crash MySQL.

Slow query log

SET GLOBAL slow_query_log='OFF';
DROP TABLE IF EXISTS slow_log2;
CREATE TABLE slow_log2 LIKE slow_log;
RENAME TABLE slow_log TO slow_log_backup, slow_log2 TO slow_log;
gzip /var/db/mysql/mysql/slow_log_backup.CSV 
DROP TABLE  slow_log_backup;
SET GLOBAL slow_query_log = 'ON';

General log

USE mysql;
SET GLOBAL general_log = 'OFF';
DROP TABLE IF EXISTS general_log2;
CREATE TABLE general_log2 LIKE general_log;
RENAME TABLE general_log TO general_log_backup, general_log2 TO general_log;
gzip /var/db/mysql/mysql/general_log_backup.CSV 
DROP TABLE  general_log_backup;

What we did is create new log table, move current log file to a backup copy and compress the backup and remove it.