List of users accessing database

I have a MySQL database accessed by a group of my teammat开发者_JAVA百科es. Is there any command to get the log information of the users who are currently accessing or who have already accessed and logged out?

Run the following from a mysql tool to view all currently running processes (including sleeping connections):

SHOW PROCESSLIST

Or, you can query the information_schema table to get the same:

select * from information_schema.processlist

To see a history of whom all has logged in, you could configure the general query log to go to a table, by adding the following startup parameter to your mysqld startup "--log-output=TABLE --general-log", then you can query this information out of the general_log table in the mysql schema. Following is the query you could use:

select * from mysql.general_log where command_type = 'Connect';

A word of warning though, this table could get huge. You'll want to periodically clean it out.

The users currently maintaining a connection to the database can be had by looking at SHOW PROCESSLIST or INFORMATION_SCHEMA.PROCESSLIST.

A historical record of that data is not to be had. Using the general query log for that as suggested elsewhere is not a good idea, as it does not scale at all: The general query log records every single statement your server sees and writing it adds considerably to the contention on LOCK_log and to disk I/O. If your general query log is a CSV table, it cannot be queried efficiently, and if it is a MyISAM table, it will essentially serialize all queries (even read queries!) in your database.

That is, because every query will need to be logged, even read queries. For that, a write to the general query log is necessary. For that, a table lock on the MyISAM log table is requested. This is very extremely slow and not adviseable at all even on low load servers.

Other formats for the general query log are not supported.

There is a set of variables which can define actions on server start, slave connect and user connect.

root@localhost [kris]> show global variables like 'init%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| init_connect  |       |
| init_file     |       |
| init_slave    |       |
+---------------+-------+
3 rows in set (0.00 sec)

By setting init_connect to an insert statement that logs current user, the current time and the connection id you can generate the log you want in a more scaleable way. Use an InnoDB table with auto_increment id for this.

Please not that init_connect is not being processed for logins of a root user (SUPER_PRIV) for security reasons. These will escape your logging.

In MySQL 5.5 the Audit API was added to the server. What you really want, I believe, is an audit plugin. See http://dev.mysql.com/doc/refman/5.5/en/writing-audit-plugins.html for details.