MySQL general log

I have good news and bad news on this one.

GOOD NEWS

You could use the general log as a table you can query.

Steps to take

1. Add this to /etc/my.cnf:

    [mysqld] log log-output=TABLE 

2. service mysql restart

   OK mysqld is not recording every query in the table mysql.general_log. Problem: look at the initial layout of mysql.general_log:

    mysql> show create table mysql.general_log\G *************************** 1. row *************************** Table: general_log Create Table: CREATE TABLE `general_log` ( `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `user_host` mediumtext NOT NULL, `thread_id` int(11) NOT NULL, `server_id` int(10) unsigned NOT NULL, `command_type` varchar(64) NOT NULL, `argument` mediumtext NOT NULL ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log' 1 row in set (0.09 sec) 

   What good is the general log as a CSV table?

3. Make mysql.general_log a MyISAM table and index it:

    SET @old_log_state = @@global.general_log; SET GLOBAL general_log = 'OFF'; ALTER TABLE mysql.general_log ENGINE = MyISAM; ALTER TABLE mysql.general_log ADD INDEX (event_time); SET GLOBAL general_log = @old_log_state; 

   Now it looks like this:

    mysql> show create table general_log\G *************************** 1. row *************************** Table: general_log Create Table: CREATE TABLE `general_log` ( `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `user_host` mediumtext NOT NULL, `thread_id` int(11) NOT NULL, `server_id` int(10) unsigned NOT NULL, `command_type` varchar(64) NOT NULL, `argument` mediumtext NOT NULL, KEY `event_time` (`event_time`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='General log' 1 row in set (0.00 sec) mysql> 

   The user and host values are appended together in the user_host fields.

How do you rotate out the general log?

Here is an example of how to blank out mysql.general_log:

SET @old_log_state = @@global.general_log; SET GLOBAL general_log = 'OFF'; CREATE TABLE mysql.general_log_new LIKE mysql.general_log; DROP TABLE mysql.general_log; ALTER TABLE mysql.general_log_new RENAME mysql.general_log; SET GLOBAL general_log = @old_log_state; 

Here is an example of how to keep the last 3 days of entries:

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; 

BAD NEWS

Anything you have collected in the text file version of the general log will not come for the ride. You can collect new entries going forward.