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, UNIQUE INDEX idx_connect_time_user_host (connect_time, user_host));
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).
5 comments:
Nice trick!
MySQL plays badly here with the lack of the counter command "close-connect" or any other disconnect notification.
Very useful. Only you mixed up the column name for "user_host" - in the init command it is "user". Also, on production systems, be *very* sure all users have the INSERT privilege. Executing each result line of this query can do that in a batch:
SELECT CONCAT('GRANT INSERT ON TABLE `admin`.`connections` TO \'', user,'\'@\'', host, '\';') FROM mysql.user .
The unique key on connect_time/user_host is a bad idea, creates errors when connecting multiple times in the same second.
A nice approach is to have not one row per log in but one row per user, and a hit counter:
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`)
)
The new init_command:
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();"
Looks pretty good. Just remember that users with SUPER privileges don't execute init-connect, so some users will be missing.
With 5.5's new pluggable authentication, logging the connections may be easier.
To clarify - the init-connect string is not run for people with the SUPER privilege - we discussed this in last week's Ear Candy segment of the OurSQL podcast (http://technocation.org/content/oursql-episode-28:-conferential-integrity).
"Global" privileges != SUPER privileges. Global privileges are when you say GRANT....ON *.* and has nothing to do with the SUPER privileges -- other than the fact that the SUPER privilege is one of the many privileges that can be assigned globally.
So it does not log 100% of logins, but the general log does. The general log also logs login *attempts* even if they are not successful (say, the password is wrong or a user tries to login to a database they do not have permissions to) so there are definitely cases in which the general log is the way to go.
Also, turning the general log on and off in MySQL 5.1 is trivial. There are hacks to turn it on and off in MySQL 5.0 and earlier without restarting MySQL. And it doesn't add as much overhead as you might think; I've turned the general log on during peak times to debug issue and have not seen any noticeable decrease in speed/performance.
I would love to see the difference in performance with the general log on vs. with this method in place. I'd also want to make sure the admin.connections table is MyISAM and the server is set to concurrent_insert=2.
i would love to see mysql adding such feature
Post a Comment