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丟棄二級密碼（如果存在）。該帳戶僅保留其主密碼，客戶端可以使用

該帳戶僅使用主密碼連接到服務器。