Home / server / Questions / 288015
In Process
Bastien974
Asked: 2011-07-08 10:36:41 +0800 CST

MySQL - log every connections

  • 772

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

connection mysql logging

3 Answers

  1. 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:

    • 5

  2. 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.

    • 0

  3. I needed to do the same thing. In MySQL 5.6 and MySQL 5.7, there is a way to capture the information needed and store it in a table. I'm not sure if this is possible with MySQL 5.0 or 5.5. Here is what I did:

    1. Created a database to contain the tables needed.
    2. Created the tables needed
    3. Created a procedure to capture the information and insert into the tables
    4. Modified the MySQL configuration file so all connections execute the stored procedure
    5. Gave all users execute privilege on the stored procedure.

    Note: This will not capture information if a user connects with super or root like privileges.

    In the mysqld section of the MySQL configuration file, I added: # *** Parameter below logs user connections

    init_connect='CALL connectionlogging.usp_InsertUserConnection'

    Information I need to capture is userid, host the connection request is coming from, the database the user is connecting to, information about the connection, and a timestamp.

    I use this information to determine who is using an encrypted connection, what database(s) they are connecting to, and when the user connects.

    • 0