mysql - 检索连接状态

Question

有没有办法在另一个连接上调用 status 命令。类似于:

status 231073

其中数字是来自 SHOW PROCESSLIST 的 ID。我对连接/客户端字符集特别感兴趣。

编辑:我所说的“状态”是指您从 MySQL shell 调用的命令(不是“显示状态”)

Answer 1

您可以使用以下方法来捕获连接信息，例如在应用程序启动时或随时捕获。例如，max_allowed_pa​​cket 可能在客户端运行生命周期期间发生变化。因此，简而言之，将其作为应用启动的一部分。

然后您就收集了有关客户端连接的信息，例如字符集设置。

架构:

DROP TABLE IF EXISTS connectionSnapshot;
CREATE TABLE connectionSnapshot
(   -- based off of (more or less) mysql 5.6 show create table information_schema.PROCESSLIST;
    -- 3 columns at least
    -- TODO: compare to same structure on mysql 5.7
    id INT AUTO_INCREMENT PRIMARY KEY,
    connId BIGINT UNSIGNED NOT NULL, 
    user VARCHAR(16) NOT NULL,
    host VARCHAR(64) NOT NULL,
    login VARCHAR(100) NOT NULL,
    -- the followed just dreamt up:
    character_set_client VARCHAR(100) NOT NULL,
    character_set_connection VARCHAR(100) NOT NULL,
    character_set_results VARCHAR(100) NOT NULL,
    collation_connection VARCHAR(100) NOT NULL,
    lc_time_names VARCHAR(100) NOT NULL,
    max_allowed_packet BIGINT NOT NULL,
    time_zone VARCHAR(100) NOT NULL,
    theWhen DATETIME NOT NULL
);

存储过程:

DROP PROCEDURE IF EXISTS uspConnectionSnapshotMe;
DELIMITER $$
CREATE PROCEDURE uspConnectionSnapshotMe()
BEGIN
    DECLARE lconnId INT;
    DECLARE luser VARCHAR(16);
    DECLARE lhost VARCHAR(64);
    SELECT connection_id() INTO lconnID;
    SELECT USER,HOST into luser,lhost FROM information_schema.PROCESSLIST WHERE ID=lconnId;

    INSERT connectionSnapshot (connId,user,host,login,character_set_client,character_set_connection,character_set_results,
    collation_connection,lc_time_names,max_allowed_packet,time_zone,theWhen) VALUES 
    (lconnId,luser,lhost,current_user(),@@session.character_set_client,@@session.character_set_connection,
    @@session.character_set_results,@@session.collation_connection,
    @@session.lc_time_names,@@session.max_allowed_packet,@@session.time_zone,now());
END$$
DELIMITER ;
-- ****************************************************************************************

测试:

call uspConnectionSnapshotMe();

结果:

select * from connectionSnapshot;
+----+--------+------+-----------------+----------------+----------------------+--------------------------+-----------------------+----------------------+---------------+--------------------+-----------+---------------------+
| id | connId | user | host            | login          | character_set_client | character_set_connection | character_set_results | collation_connection | lc_time_names | max_allowed_packet | time_zone | theWhen             |
+----+--------+------+-----------------+----------------+----------------------+--------------------------+-----------------------+----------------------+---------------+--------------------+-----------+---------------------+
|  1 |   3825 | root | localhost:4660  | root@localhost | utf8                 | utf8                     | utf8                  | utf8_general_ci      | en_US         |            4194304 | SYSTEM    | 2016-09-08 02:40:18 |
|  2 |  37007 | root | localhost:52071 | root@localhost | utf8                 | utf8                     | cp850                 | utf8_general_ci      | en_US         |            4194304 | SYSTEM    | 2016-09-08 02:44:17 |
+----+--------+------+-----------------+----------------+----------------------+--------------------------+-----------------------+----------------------+---------------+--------------------+-----------+---------------------+