Logging User Connections in MySQL

closeThis post was published 5 years 6 months 27 days ago, which is pretty much forever ago in internet time. As such, some of what's discussed here may no longer work as described.
TL;DR: FYI, YMMV

At the company where I work, my predecessor made a number of questionable choices. One of those was giving far too many privileges to users in MySQL. He created many users specifically to connect to a single database, but gave those users root-level access to the system. Yikes! Fixing this was easy enough, but identifying which databases users actually needed access to was sometimes a challenge.

One of the users was called “Remote,” and for the life of me I couldn’t figure out what databases that user actually needed to access—if any. I had strong suspicions that it wasn’t being used at all, but since “Remote” had no databases assigned to it, I couldn’t be sure that it wasn’t being used by one of our websites. What I really needed was a way to determine whether or not the user was active.

That led me to this post. A good start, but it was a comment to that post that contained the solution I ended up implementing, which I have outlined below.

Logging MySQL User Connections

Per the original post, I created a new database called admin. I then ran the following query to create my “connections” table:

CREATE TABLE `connections` (
	`user_host` VARCHAR(50) NOT NULL,
	`connections` INT(10) UNSIGNED NOT NULL DEFAULT '0',
	`lastconnect` DATETIME NOT NULL,
	PRIMARY KEY (`user_host`)
)

Note that I set user_host to be the primary key. That will be important later on. Once I set up the table, I ensured that every user had SELECT, INSERT, and UPDATE permissions for the admin database. Then I ran this query:

SET GLOBAL init_connect = "
	INSERT INTO admin.connections
		(`user_host`, `connections`, `lastconnect`)
	VALUES (CURRENT_USER(), 1, NOW())
	ON DUPLICATE KEY UPDATE
		`connections`=`connections`+1, lastconnect=NOW();"

This code uses a SET comment, which affects the operation of the server. In this case, I’m setting a global variable called init_connect, which is simply a string that gets executed by the server for each client that connects. In this case, the string is a SQL statement that grabs a few pieces of information, and then inserts that information into the database.

I didn’t want every connection to appear as its own entry, I just wanted to see the total number of connections and date of last connection for each user. This is why I set user_host as the primary key. In the query, I included the ON DUPLICATE KEY command to run an UPDATE instead of an INSERT (which is why it’s important for all users to have UPDATE as well as INSERT permissions).

I loaded up our corporate website to see if it worked. Sure enough, it did. Now I just need to let some time pass so I can see if the “Remote” user ever gets used. If it does, I’ll know I need to set up specific permissions for it. If it doesn’t, that sucker’s getting deleted.

Six Months Later

The result of logging user connections in MySQL for 6 months.

The result of logging user connections in MySQL for 6 months.

After waiting (roughly) six months, I checked up on the logging table to see how things were going. Most of the users had connected many times, but one was conspicuously missing. As you can see, “Remote” had never connected. Not even once.

With that information, I was confident that deleting the user was a safe action to take. It has been a couple of months since I did, and no one has complained yet.

I want to point out that at the time of this writing, we’re running MySQL 5.5.15. As always, if you’re running a different version, your mileage may vary.

Leave a reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>