Log incoming MySQL connections to a table

With MySQL 5.1 you can store the general query log in a table. But what if you don't want to log every single statement but only the users logging in and the hosts they're coming from? There exists in fact a very simple solution that even works with MySQL 5.0 and (with some changes) with earlier versions.

The idea is to use the init_connect server variable as a hook to install a logging routine. The init_connect variable holds SQL statements to be executed for each client that connects (except for SUPER users).

We need to create a database and a table first to hold our connection log. I don't really recommend to use the mysql database for this as we don't know what might be added in there by future versions of the server. So let's just add a new database sys.

CREATE DATABASE IF NOT EXISTS sys;

CREATE TABLE sys.connection_log (
    log_id INT UNSIGNED NOT NULL auto_increment,
    login_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    connection_id INT(10) NOT NULL,
    user_id VARCHAR(255) NOT NULL,
    PRIMARY KEY (log_id),
    KEY (user_id)
);

We need an auto_increment column to order the log entries, as the granularity of the TIMESTAMP is not fine enough and the CONNECTION_ID() will be reset by restarts and could possibly even wrap around on a very busy (and long running) server.

The next step is to install a small routine that writes the connection information to the log. The CREATE command should be executed as root or maybe even better as another user with INSERT rights on the sys.connection_log table.

delimiter //

CREATE PROCEDURE sys.init_connect()
       SQL SECURITY DEFINER
       MODIFIES SQL DATA DETERMINISTIC
BEGIN
       INSERT INTO connection_log
              (connection_id, user_id)
       VALUES (CONNECTION_ID(), USER());
END //

delimiter ;

By setting the SQL SECURITY to DEFINER we're able to hide all the internals of the logging system from normal users. We just need to provide global execution rights on the new sys database to any user. Without that everybody would be completely locked out of the database.

REPLACE INTO mysql.db
        (Host, Db, User, Execute_priv)
VALUES  ('%', 'sys', '', 'Y');

FLUSH PRIVILEGES;

To activate the logging we just have to hook our routine into the init_connect variable:

SET GLOBAL init_connect = 'CALL sys.init_connect()';

If you want the connection logging to survive reboots of the server, add the setting to your my.cnf:

[mysqld]
init_connect = 'CALL sys.init_connect()'

That's it. All the connections will now be logged.

Of course you'll notice that nothing prevents a user from calling the routine again after the login. This will add another row to the log table with the same connection id. You can filter those in your analysis. If you really want to catch these situations beforehand you need to do a little bit more work: Just adding a UNIQUE constraint to connection_id in the table is a bad idea in case the server is restarted or the ids wrap around.