mysql - Retrieving connection status -

is there way status command called on connection. like:

status 231073 

where number id show processlist. in particular i'm interested in connection/client character sets.

edit: status mean command call mysql shell (not show status)

you use following capture info of connections, say, @ start of app or @ anytime. instance max_allowed_packet change during client run lifecycle. so, in short, have in part of app startup.

then have collected info on client connections, such character set settings.

schema:

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 @ least     -- todo: compare 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,     -- followed 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 ); 

stored proc:

drop procedure if exists uspconnectionsnapshotme; delimiter $$ create procedure uspconnectionsnapshotme() begin     declare lconnid int;     declare luser varchar(16);     declare lhost varchar(64);     select connection_id() lconnid;     select user,host luser,lhost information_schema.processlist 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 ; -- **************************************************************************************** 

test:

call uspconnectionsnapshotme(); 

results:

select * 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 | +----+--------+------+-----------------+----------------+----------------------+--------------------------+-----------------------+----------------------+---------------+--------------------+-----------+---------------------+