1. MySQL8.0双密码特性
自 MySQL 8.0.14 版本起,MySQL 支持为每个用户账户设置两个密码:主密码(新密码)和辅助密码(旧密码)。这种双密码机制能够在一些复杂的系统中,特别是当涉及大量 MySQL 实例、复制、多个应用程序连接以及频繁的密码更新时,保持服务不中断,从而实现更流畅的密码更改流程。
常见使用场景:
- 系统有多个 MySQL 服务器,其中一些可能是主从复制。
- 不同的应用程序连接到不同的 MySQL 服务器。
- 系统需要定期更新连接凭据,且不希望中断现有服务。
如果不使用双密码机制,密码更改可能需要仔细协调更新过程,以避免在某些服务器或应用程序上造成停机或连接中断。而通过双密码机制,可以在不影响现有连接的情况下分阶段完成凭据更新,从而避免停机。
2. 双密码机制的工作流程
(1)为账户添加新密码并保留旧密码
在更改密码时,首先通过 RETAIN CURRENT PASSWORD 子句设置新的主密码,并保留当前密码作为辅助密码。此时,客户端可以继续使用旧密码(辅助密码)连接数据库,同时新密码(主密码)也已经生效,主要语法如下:
ALTER USER 'user'@'host'
IDENTIFIED BY 'new_password'
RETAIN CURRENT PASSWORD;
该命令会将 new_password 设置为主密码,并将旧密码保留为辅助密码。此时,
无论是使用新密码还是旧密码的客户端,都能正常连接到数据库。
案例如下:
# 创建一个用户并设定密码
mysql> create user 'app_user'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.03 sec)
mysql> grant select on *.* to 'app_user'@'localhost';
Query OK, 0 rows affected, 1 warning (0.01 sec)
# 登录测试密码
[root@alidb ~]# /usr/local/mysql8.0/bin/mysql -uapp_user -p'123456' --socket=/data//mysql/mysql3308/tmp/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 24090
Server version: 8.0.39 MySQL Community Server - GPL
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select user();
+--------------------+
| user() |
+--------------------+
| app_user@localhost |
+--------------------+
1 row in set (0.00 sec)
原密码可以正常登录。
再创建新密码进行验证。
#创建新密码
mysql> ALTER USER 'app_user'@'localhost' IDENTIFIED BY 'Test@123456' RETAIN CURRENT PASSWORD;
Query OK, 0 rows affected (0.01 sec)
# 使用新密码登录
[root@alidb ~]# /usr/local/mysql8.0/bin/mysql -uapp_user -p'Test@123456' --socket=/data//mysql/mysql3308/tmp/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 24093
Server version: 8.0.39 MySQL Community Server - GPL
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select user();
+--------------------+
| user() |
+--------------------+
| app_user@localhost |
+--------------------+
1 row in set (0.00 sec)
mysql>
mysql> exit
Bye
# 再次使用原密码登录
[root@alidb ~]# /usr/local/mysql8.0/bin/mysql -uapp_user -p'123456' --socket=/data//mysql/mysql3308/tmp/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 24094
Server version: 8.0.39 MySQL Community Server - GPL
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select user();
+--------------------+
| user() |
+--------------------+
| app_user@localhost |
+--------------------+
1 row in set (0.00 sec)
可见,新密码及原密码均可以登录。
(2)废弃旧密码
当新密码已经在所有服务器上同步,且所有应用程序也更新为使用新密码时,可以使用 DISCARD OLD PASSWORD 子句来丢弃辅助密码(原密码),使得数据库仅接受主密码(新密码)。例如:
ALTER USER 'app_user'@'localhost' DISCARD OLD PASSWORD;
此时,客户端只能使用主密码进行连接,旧密码(辅助密码)将不再有效。
# 新密码登录
root@alidb ~]# /usr/local/mysql8.0/bin/mysql -uapp_user -p'Test@123456' --socket=/data//mysql/mysql3308/tmp/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 24099
Server version: 8.0.39 MySQL Community Server - GPL
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select user();
+--------------------+
| user() |
+--------------------+
| app_user@localhost |
+--------------------+
1 row in set (0.00 sec)
mysql> exit
Bye
#原密码无法登录了
[root@alidb ~]# /usr/local/mysql8.0/bin/mysql -uapp_user -p'123456' --socket=/data//mysql/mysql3308/tmp/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'app_user'@'localhost' (using password: YES)
3.小结
MySQL 8.0 的双密码机制为数据库管理员提供了一个无缝过渡的方式,使得密码更新过程可以分阶段进行,避免了传统方式中可能造成的停机和连接中断问题。通过这种机制,DBA可以在不影响系统可用性的前提下,安全地执行密码更新操作。