How to Log User Connections in MySQL

There is nothing that MySQL 5.1 explicitly performs to log user connections, but there is a combination of MySQL commands that can log user connections without having to turn on the general query log. I repeat: you do not have to turn the general query log on to make this work!

Would you like to find out what that is?

With the help of Baron Schwartz's post on http://www.xaprb.com/blog/2006/07/23/how-to-track-what-owns-a-mysql-connection/ I was able to find out as well. However, I wanted to simplify things a bit, because all I need is to track MySQL connections within MySQL for now. I'll probably increase the scope in the future, but for now, let's keep it simple.

Ingredients

init-connect

NOW()

CURRENT_USER()

CONNECTION_ID()

See it coming together?

Alrighty, here's the recipe...

Assumptions: The name of the database we will use to store this information will be called admin.

1. Create the table to store the connection information in. You don't have to include the unique key if you don't want to.

CREATE TABLE admin.connections (id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, connect_time DATETIME NOT NULL, user_host VARCHAR(50) NOT NULL, connection_id INT UNSIGNED NOT NULL);

2. Set the init-connect variable. This is a string to be executed for each client that connects. Details here.

SET GLOBAL init_connect = "INSERT INTO admin.connections (connect_time, user, connection_id) VALUES (NOW(), CURRENT_USER(), CONNECTION_ID());";

3. Check permissions of all of your users to ensure that they can insert a record into the admin.connections table.

4. Login as a user without global privileges and a row should be inserted in the admin.connections table. Note that the init-connect system variable does not work on users with global privileges. For better or for worse, all of us DBAs know what that means.

5. Watch your connection table grow and flourish. You've just started a new pet (project).