MySQL General Log – How to Enable and Use

connectionslinuxlogMySQL

Is there away to have MySQL dump its log to a remote MySQL server?

I would like to audit MySQL connections but I don't want the server to store all the information local on its own hardware.

Best Answer

I have good news and bad news on this one.

GOOD NEWS

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

Step 01) Add this to /etc/my.cnf

[mysqld] 
log
log-output=TABLE

Step 02) 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 a the general log as a CSV table

Step 03) 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.

Related Question