Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question
    
See this related bug report bugs.mysql.com/bug.php?id=28377 –  Michael Mior Jul 7 '11 at 19:03
    
you need to know only from where or from where and on what database ? –  golja Mar 21 '13 at 12:44

2 Answers 2

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:

share|improve this answer

Here's an idea, but it might not work.
Create a FIFO pipe in /var/log/mysql/named_pipe In one end, get MySQL to use it as the general log file filename.

On the other end, set up something like

grep "login" < /var/log/mysql/named_pipe > /var/log/mysql/logins

You'll need to grep for whatever line it is exactly that you're after out of the log.

It might work.. it might not.

share|improve this answer
    
Even if this does work, there is a performance hit for all this logging. –  Michael Mior Jul 7 '11 at 19:02
    
you want the data or not? You're gonna have to log it at some level. Other options might include mysql proxy, but you've still gotta write it to disk at some point –  Tom O'Connor Jul 7 '11 at 19:04
    
mysql refuses to start when the log file is a FIFO pipe. I don't want to data, only the "Connect" information. I'm not worried about the performance hit if it's only for a day. –  Bastien974 Jul 7 '11 at 19:10
1  
tcpdump or ngrep.. then scrape the information out of the packets. MySQL sucks. Especially when it's picky about it's log files. –  Tom O'Connor Jul 7 '11 at 19:20
    
Well sure, you need to log it, but using the general log is overkill. –  Michael Mior Jul 7 '11 at 20:34

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.