MySQL8.0.16账户双密码实验一例

从MySQL 8.0.14开始，允许用户帐户拥有双密码，指定为主密码和辅助密码。 双密码功能可以在以下场景中无缝地执行凭证更改： 系统有大量MySQL服务器，可能涉及主从复制 多个应用程序连接到不同的MySQL服务器 必须对应用程序用于连接服务器的帐户进行定期密码更改 实验如下: mysql版本: mysql>select version(); +-----------+ | version() | +-----------+ | 8.0.16    | +-----------+ 1 row in set (0.00 sec) mysql>create user root@'%' identified by '123456'; Query OK, 0 rows affected (0.25 sec) mysql>grant all privileges on *.* to root@'%'; Query OK, 0 rows affected (0.10 sec) mysql>alter user root@'%' identified by 'root' RETAIN CURRENT PASSWORD; Query OK, 0 rows affected (0.28 sec) 开另外一个session: 两个密码都可以登录: # mysql -uroot -p123456 -h 192.168.140.52 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 21 Server version: 8.0.16 MySQL Community Server - GPL Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. 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>\q Bye [root@test2 ~]# mysql -uroot -proot -h 192.168.140.52 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 22 Server version: 8.0.16 MySQL Community Server - GPL Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. 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>\q Bye 丢弃旧密码: mysql>alter user root@'%' DISCARD OLD PASSWORD; Query OK, 0 rows affected (0.12 sec) 开另外一个会话，用旧密码登录报错： # mysql -uroot -p123456 -h 192.168.140.52 mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'root'@'192.168.140.52' (using password: YES) 新密码可以登录: # mysql -uroot -p123456 -h 192.168.140.52 mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'root'@'192.168.140.52' (using password: YES) [root@test2 ~]#  [root@test2 ~]#  [root@test2 ~]# mysql -uroot -proot -h 192.168.140.52 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 27 Server version: 8.0.16 MySQL Community Server - GPL Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. 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>\q Bye 备注:

RETAIN CURRENT PASSWORD保留帐户当前密码作为其辅助密码，替换任何现有的二级密码。新密码将成为主密码，

但客户端可以使用该帐户使用主密码或辅助密码连接到服务器。

对于ALTER USER， DISCARD OLD PASSWORD丢弃二级密码（如果存在）。该帐户仅保留其主密码，客户端可以使用

该帐户仅使用主密码连接到服务器。