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';
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.