MySQL 5.7 新增默认账号 mysql.session和m

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