Linux 访问数据库的用户列表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5575347/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
List of users accessing database
提问by Sayamima
I have a MySQL database accessed by a group of my teammates. Is there any command to get the log information of the users who are currently accessing or who have already accessed and logged out?
我有一个由我的一组队友访问的 MySQL 数据库。有没有什么命令可以获取当前正在访问或者已经访问并退出的用户的日志信息？
回答by squawknull
Run the following from a mysql tool to view all currently running processes (including sleeping connections):
从 mysql 工具运行以下命令以查看所有当前正在运行的进程（包括休眠连接）：
SHOW PROCESSLIST
Or, you can query the information_schema table to get the same:
或者，您可以查询 information_schema 表以获取相同的信息：
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:
要查看所有已登录的历史记录，您可以通过将以下启动参数添加到 mysqld 启动“--log-output=TABLE --general-log”中，将通用查询日志配置为转到一个表，然后您可以从 mysql 模式中的 general_log 表中查询此信息。以下是您可以使用的查询：
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.
警告的话，这张桌子可能会变得很大。您需要定期清理它。
回答by Isotopp
The users currently maintaining a connection to the database can be had by looking at SHOW PROCESSLIST or INFORMATION_SCHEMA.PROCESSLIST.
可以通过查看 SHOW PROCESSLIST 或 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.
不应拥有该数据的历史记录。使用其他地方建议的通用查询日志不是一个好主意，因为它根本无法扩展：通用查询日志记录了您的服务器看到的每一条语句，并写入它大大增加了 LOCK_log 上的争用和磁盘 I/哦。如果你的通用查询日志是一个CSV表，就无法高效查询，如果是MyISAM表，本质上它会序列化你数据库中的所有查询（甚至是读查询！）。
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.
也就是说，因为每个查询都需要记录，甚至读取查询。为此，需要写入一般查询日志。为此，请求对 MyISAM 日志表进行表锁定。这非常慢，即使在低负载服务器上也不建议这样做。
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.
通过将 init_connect 设置为记录当前用户、当前时间和连接 ID 的插入语句，您可以以更可扩展的方式生成所需的日志。为此，请使用带有 auto_increment id 的 InnoDB 表。
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.
请注意，出于安全原因，没有为 root 用户 (SUPER_PRIV) 的登录处理 init_connect。这些将逃避您的日志记录。
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.htmlfor details.
在 MySQL 5.5 中，审计 API 被添加到服务器中。我相信你真正想要的是一个审计插件。有关详细信息，请参阅http://dev.mysql.com/doc/refman/5.5/en/writing-audit-plugins.html。
