MySQL – log every connections

connectionloggingMySQL

I'd like to monitor a MySQL 5.0.77 server and log every incoming connection for a day.
I need to know who is connecting to the database.

I tried with the general logging but it's logging way too much and I can't keep it on long enough, log file is growing too fast.

Is there a way to do that ? Thanks

Best Answer

You should use general log, not as a text file, but as a MySQL table.

Why create the general_log as a table?

  • You can run SQL against it.
  • You can rotate out very easily.

How do you configure it?

Starting in MySQL 5.1, the following option was introduced: log-output.

  • Setting log-output to FILE (default) writes log entries to the text file.
  • Setting log-output to TABLE writes log entries to mysql.general_log.
  • Setting log-output to TABLE,FILE writes log entries to mysql.general_log and the text file.

There is one major thing that must be changed.

Let's look at a standard mysql.general_log table:

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)

It's a CSV file ? Yuck !!! Who has time for that? Not to worry, turn it into a MyISAM table

ALTER TABLE mysql.general_log ENGINE = MyISAM; 
ALTER TABLE mysql.general_log ADD INDEX (event_time);

If you already started MySQL, do it like this instead:

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, the general log table 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)

How do you rotate the general log when it's a MyISAM table?

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;

CAVEAT

This feature is available in MySQL 5.1/5,5/5.6. You should upgrade from MySQL 5.0.77 to the latest

EPILOGUE

I could write a lot more here but have discussed this many times in the DBA StackExchange. here are some of my posts on this subject:

Related Topic