my MySQL default configuration

my.cnf file. Try and use this before you start MySQL Server for the first time!

[mysqld]
log-output=TABLE
expire_logs_days=1
general_log=ON
general_log_file=/var/db/mysql/mysqld-queries.log
slow_query_log=ON
slow_query_log_file=/var/db/mysql/mysqld-slow-queries.log
log_queries_not_using_indexes=ON
log-error=/var/db/mysql/mysqld-error.log
default-storage-engine=innodb
innodb_file_per_table

Run the following SQL script to update the logs to use MyISAM (instead of CSV!).

SET @old_log_state = @@global.general_log;
SET GLOBAL general_log = 'OFF';
TRUNCATE TABLE general_log;
ALTER TABLE mysql.general_log ENGINE = MyISAM;
ALTER TABLE mysql.general_log ADD COLUMN `id` INT(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT;
SET GLOBAL general_log = @old_log_state;
SET GLOBAL general_log = 'ON';
 
SET @old_log_state = @@global.slow_query_log;
SET GLOBAL slow_query_log = 'OFF';
TRUNCATE TABLE slow_log; 
ALTER TABLE mysql.slow_log ENGINE = MyISAM;
ALTER TABLE mysql.slow_log ADD COLUMN `id` INT(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT;
SET GLOBAL slow_query_log = @old_log_state;
SET GLOBAL slow_query_log = 'ON';

The above didn’t work, it would result in no logs being inserted in to the table. Will take a 2nd look at this when I have more time.

SET @old_log_state = @@global.slow_query_log;
SET GLOBAL slow_query_log = 'OFF';
ALTER TABLE mysql.slow_log ENGINE = MyISAM;
ALTER TABLE mysql.slow_log ADD INDEX (start_time);
SET GLOBAL slow_query_log = @old_log_state;

Cron the following command on a hourly / daily basis

/usr/local/bin/mysql -e "FLUSH LOGS; USE mysql; TRUNCATE TABLE slow_log; TRUNCATE TABLE general_log;"