MySQL版本：5.7.23
在闲逛mysql时发现mysql库的user表下有两个账户比较特别：
mysql.session和mysql.sys
mysql> use mysql;
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
31 rows in set (0.00 sec)
查一下user表里面都有哪些账户：
mysql> select user, host from user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
3 rows in set (0.00 sec)
mysql.sys@localhost:
用于 sys schema中对象的定义。使用 mysql.sys 用户可避免DBA重命名或者删除root用户时发生的问题。该用户已被锁定，客户端无法连接。
mysql.session@localhost:
插件内部使用来访问服务器。该用户已被锁定，客户端无法连接。root@localhost:
这个就是root账号啦！其用于管理。该用户拥有所有权限，可执行任何操作。严格来说，这个账号不应该被保留。root是MySQL的特权账号，这个众所周知，也带来安全隐患。建议将root账号禁用或者删除，新建一个特权账号用于管理。
在MySQL 5.6以前，我们通过show processlist\G命令查看系统中正在运行的所有进程：
mysql> show processlist;
+----+------+-----------+-------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+-------+---------+------+----------+------------------+
| 6 | root | localhost | mysql | Query | 0 | starting | show processlist |
+----+------+-----------+-------+---------+------+----------+------------------+
1 row in set (0.00 sec)
从5.7开始，我们又可以通过sys.session表来查看系统正在运行的所有进程，而且该表中的记录相processlist比较完善：
mysql> select * from sys.session\G
*************************** 1. row ***************************
thd_id: 31
conn_id: 6
user: NULL
db: mysql
command: Query
state: Sending data
time: 0
current_statement: select * from sys.session
statement_latency: 1.79 ms
progress: NULL
lock_latency: 0 ps
rows_examined: 0
rows_sent: 0
rows_affected: 0
tmp_tables: 4
tmp_disk_tables: 1
full_scan: YES
last_statement: NULL
last_statement_latency: NULL
current_memory: 0 bytes
last_wait: NULL
last_wait_latency: NULL
source: NULL
trx_latency: NULL
trx_state: NULL
trx_autocommit: NULL
pid: 4612
program_name: mysql
1 row in set (0.06 sec)
很显然，select * from sys.session能得到更多的信息。
我这个是用
root账户在mysql库下执行的命令，当然你也可以去sys库查看;
#表太多了，只写出我们要的表
mysql> use sys;
Database changed
mysql> show tables;
+-----------------------------------------------+
| Tables_in_sys |
+-----------------------------------------------+
|...... |
| processlist |
|...... |
+-----------------------------------------------+
然后查看该表中的信息：
#数据行太多了，囧……
#只显示一行吧
mysql> select * from processlist\G
*************************** 1. row ***************************
thd_id: 1
conn_id: NULL
user: sql/main
db: NULL
command: NULL
state: NULL
time: 3262
current_statement: NULL
statement_latency: NULL
progress: NULL
lock_latency: NULL
rows_examined: NULL
rows_sent: NULL
rows_affected: NULL
tmp_tables: NULL
tmp_disk_tables: NULL
full_scan: NO
last_statement: NULL
last_statement_latency: NULL
current_memory: 0 bytes
last_wait: NULL
last_wait_latency: NULL
source: NULL
trx_latency: NULL
trx_state: NULL
trx_autocommit: NULL
pid: NULL
program_name: NULL
mysql> select * from session\G
*************************** 1. row ***************************
thd_id: 31
conn_id: 6
user: NULL
db: sys
command: Query
state: Sending data
time: 0
current_statement: select * from session
statement_latency: 1.31 ms
progress: NULL
lock_latency: 0 ps
rows_examined: 0
rows_sent: 0
rows_affected: 0
tmp_tables: 4
tmp_disk_tables: 1
full_scan: YES
last_statement: NULL
last_statement_latency: NULL
current_memory: 0 bytes
last_wait: NULL
last_wait_latency: NULL
source: NULL
trx_latency: NULL
trx_state: NULL
trx_autocommit: NULL
pid: 4612
program_name: mysql
1 row in set (0.05 sec)
文章中部分内容引用自
http://www.mamicode.com/info-detail-1301870.html
http://www.dbhelp.net/2017/11/29/mysql-5-7-%E6%96%B0%E5%A2%9E%E9%BB%98%E8%AE%A4%E8%B4%A6%E5%8F%B7-mysql-session%E5%92%8Cmysql-sys.html
网友评论