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).
13 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
I use this method a lot but I prefer segregating the user from the host, using a string function like substring_index(). If you are only interested in monitoring connections rather than the query per se`, this is the way to go.
I like Ansgar's solution, so far. The only correction I would add is that the users also need UPDATE, since that's how the counter gets incremented in the init_connect statement.
I like Ansgar's solution, so far. The only correction I would add is that the users also need UPDATE, since that's how the counter gets incremented in the init_connect statement.
Hi, I tried to use this and my webapp is no more working. I corrected the query (user => user_host). I removed also the unique index and just let the auto increment id to be the index.
The point is that the connexions table is properly filled, but my Java app is sending an error (XXX.ConnectionPool - SQL: getConnection() exception: Communications link failure).
If i drop the GLOBAL init_connect, my web app work again.
I m lost, i ran a lot of google search and i m still stuck here.
Anyone has an idea, i m not an mySql expert far from that, maybe the solution is easy.
Thanks for you help.
Hi, I tried to use this and my webapp is no more working. I corrected the query (user => user_host). I removed also the unique index and just let the auto increment id to be the index.
The point is that the connexions table is properly filled, but my Java app is sending an error (XXX.ConnectionPool - SQL: getConnection() exception: Communications link failure).
If i drop the GLOBAL init_connect, my web app work again.
I m lost, i ran a lot of google search and i m still stuck here.
Anyone has an idea, i m not an mySql expert far from that, maybe the solution is easy.
Thanks for you help.
namleck: The select concat bit at the top generates the text that will set permissions. So I had to dump it out to a file and then mysql -p < perms.sql to import it. Additionally, you will need to "GRANT INSERT,UPDATE,SELECT ON TABLE..."
Like Ansgar said, the unique key on connect_time/user_host is a bad idea, creates errors when connecting multiple times in the same second.
Could you please edit your posts for the people not reading the comments?
Gave me a lot of head aches...
The unique key on connect_time/user has been removed. :)
Post a Comment