user_login_history 表
user_login_history 表提供了本用户的历史登陆记录信息。该表的数据根据 mysql.login_history 系统表生成。
DESC information_schema.user_login_history;
输出结果如下：
+---------------+---------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------------+------+------+---------+-------+
| TIME | datetime | YES | | NULL | |
| HOST | varchar(64) | YES | | NULL | |
| USER | varchar(32) | YES | | NULL | |
| DB | varchar(64) | YES | | NULL | |
| CONNECTION_ID | bigint(21) | YES | | NULL | |
| RESULT | varchar(16) | YES | | NULL | |
| CLIENT_HOST | varchar(64) | YES | | NULL | |
| DETAIL | varchar(1024) | YES | | NULL | |
+---------------+---------------+------+------+---------+-------+
8 rows in set (0.00 sec)
查看 information_schema.user_login_history 表的信息：
SELECT * FROM information_schema.user_login_history;
输出结果如下：
+---------------------+-------------+------+------+---------------------+---------+-------------+------------------------------------------------------------------------------+
| TIME | HOST | USER | DB | CONNECTION_ID | RESULT | CLIENT_HOST | DETAIL |
+---------------------+-------------+------+------+---------------------+---------+-------------+------------------------------------------------------------------------------+
| 2023-04-11 17:04:56 | 172.16.5.31 | zak | | 3401888976338944407 | success | 172.16.5.33 | |
| 2023-04-11 17:06:15 | 172.16.5.31 | zak | | 3401888976338944409 | success | 172.16.5.33 | |
| 2023-04-11 17:08:25 | 172.16.5.31 | zak | | 3401888976338944413 | success | 172.16.5.33 | |
| 2023-04-11 17:25:45 | 172.16.5.33 | zak | | 6685035094924591511 | fail | 10.2.4.4 | [privilege:1045]Access denied for user 'zak'@'10.2.4.4' (using password: NO) |
| 2023-04-11 17:25:49 | 172.16.5.33 | zak | | 6685035094924591513 | success | 10.2.4.4 | |
| 2023-04-11 17:21:47 | 172.16.5.31 | zak | | 7723222561173012883 | fail | 10.2.4.4 | [privilege:1045]Access denied for user 'zak'@'10.2.4.4' (using password: NO) |
| 2023-04-11 17:22:19 | 172.16.5.31 | zak | | 7723222561173012885 | success | 10.2.4.4 | |
| 2023-04-11 17:58:34 | 172.16.5.33 | zak | | 135534599332692371 | success | 10.2.4.4 | |
+---------------------+-------------+------+------+---------------------+---------+-------------+------------------------------------------------------------------------------+
information_schema.user_login_history 表中列的含义如下：
- TIME：表示用户登陆的时间。
- HOST：表示登陆平凯数据库主机节点。
- USER：表示登陆用户名。
- DB：表示登录时进入的 database。
- CONNECTION_ID：表示登陆数据库是建立的连接 ID。
- RESULT：表示登陆的结果，成功或失败。
- CLIENT_HOST：表示连接数据库的客户端地址。
- DETAIL：其他详细信息，如登陆失败时提示失败原因。