MySQL服务端设置密码过期后如何无需重连立即拦截客户端命令

• 我在本地MySQL Server Machine（IP：192.168.156.33）上使用root账号登录，为任意主机**%创建新用户test**，执行命令：CREATE USER test IDENTIFIED BY 'password';
• 我在客户端机器上使用test账号登录，执行命令：mysql -u test -h 192.168.156.33 -p
• 我回到服务端执行命令将test账号的密码设为过期：ALTER USER 'test'@'%' PASSWORD EXPIRE;，再执行FLUSH PRIVILEGES;刷新权限，此时客户端的test账号仍处于登录状态
• 客户端的test账号仍可正常执行SHOW DATABASES、USE、SELECT等命令，没有弹出修改密码的提示
• 只有当客户端执行\r命令重连，或执行exit退出后重新登录，再执行命令时才会正常弹出修改密码的提示

我想要实现的效果是：服务端设置账号密码过期后，无需客户端重连就能立即拦截用户的操作，并提示用户修改密码，请问该如何实现？

MySQL的用户权限、密码状态等信息是在客户端连接建立时加载到会话内存中的，默认不会实时同步服务端的权限变更，所以原生机制下已建立的会话不会触发密码过期校验，可通过以下方案实现需求：

方案1：主动终止对应用户的现有会话（最便捷） #

设置密码过期后，直接杀掉该用户的所有已建立会话，用户后续操作时会触发断连，重新登录就会自动弹出密码修改提示，操作步骤如下：

1. 查询该用户的所有会话ID：

SELECT ID FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = 'test';

2. 执行kill命令终止对应会话，将[会话ID]替换为上一步查询到的ID即可：

KILL [会话ID];

如果需要批量处理，可以执行如下语句生成批量kill命令，导出后直接执行即可：

SELECT CONCAT('KILL ', ID, ';') FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = 'test';

方案2：通过init_connect实现操作前校验（无需每次手动杀会话） #

利用MySQL的init_connect全局变量，配置所有普通用户在执行任何操作前先触发密码过期校验，过期则直接抛出错误，配置步骤：

1. 先创建校验存储过程：

DELIMITER //
CREATE PROCEDURE check_password_expired()
BEGIN
  DECLARE is_expired INT DEFAULT 0;
  -- 校验当前用户密码是否过期
  SELECT 1 INTO is_expired FROM mysql.user 
  WHERE user = SUBSTRING_INDEX(USER(), '@', 1) 
  AND host = SUBSTRING_INDEX(USER(), '@', -1) 
  AND password_expired = 'Y';
  IF is_expired = 1 THEN
    -- 抛出自定义错误
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '密码已过期，请修改密码后再执行操作';
  END IF;
END //
DELIMITER ;

2. 配置全局init_connect变量，使其生效：

SET GLOBAL init_connect = 'CALL check_password_expired();';

3. 如需重启数据库后仍生效，需要在配置文件my.cnf（Linux）或my.ini（Windows）的[mysqld]段添加如下配置：

init_connect = 'CALL check_password_expired();'

注意：拥有SUPER权限的用户会跳过init_connect的校验，该逻辑仅对普通用户生效，避免配置错误导致无法登录数据库。

方案3：数据库代理层校验（适用有代理架构的场景） #

如果你的环境部署了ProxySQL、MaxScale等数据库代理中间件，可以在代理层新增请求校验逻辑，每次收到用户的操作请求时，先查询对应用户的密码状态，若已过期则直接返回错误提示，无需修改数据库本身的配置，也能对已建立的会话实时生效。

内容的提问来源于stack exchange，提问作者Somethea Yuon