Detailed explanation of MySQL 8.0 password expiration policy

[root@VM-0-14-centos ~]# /usr/local/mysql-8.0.19-el7-x86_64/bin/mysql -uyeyz -pyeyz -h127.0.0.1 -P4306 -e "select 1"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---+
| 1 |
+---+
| 1 |
+---+

mysql> alter user yeyz@'127.0.0.1' password expire;
Query OK, 0 rows affected (0.01 sec)

[root@VM-0-14-centos ~]# /usr/local/mysql-8.0.19-el7-x86_64/bin/mysql -uyeyz -pyeyz -h127.0.0.1 -P4306 -e "select 1"
mysql: [Warning] Using a password on the command line interface can be insecure.
Please use --connect-expired-password option or invoke mysql in interactive mode.

-- Prompt us to connect through the --connect-expire-password command, let's add it and see [root@VM-0-14-centos ~]# /usr/local/mysql-8.0.19-el7-x86_64/bin/mysql -uyeyz -pyeyz -h127.0.0.1 -P4306 --connect-expired-password -e "select 1" 
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1820 (HY000) at line 1: You must reset your password using ALTER USER statement before executing this statement.

-- Here we are prompted to execute the alter user syntax to change the password first, and then use the password.

-- Set the password to never expiremysql> create user yeyz1@'127.0.0.1' identified with 'mysql_native_password' by 'yeyz1' password expire never;
Query OK, 0 rows affected (0.01 sec)

-- Set the password expiration period to the specified number of daysmysql> create user yeyz2@'127.0.0.1' identified with 'mysql_native_password' by 'yeyz2' password expire interval 90 day; 
Query OK, 0 rows affected (0.01 sec)

mysql> create user yeyz3@'127.0.0.1' identified with 'mysql_native_password' by 'yeyz3' password expire default;
Query OK, 0 rows affected (0.01 sec)

mysql> show variables like '%lifetime%';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| default_password_lifetime | 0 |
+---------------------------+-------+
1 row in set (0.00 sec)

mysql> alter user yeyz@'127.0.0.1' identified with 'mysql_native_password' by 'yeyz';
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> show variables like '%password_history%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| password_history | 0 |
+------------------+-------+
1 row in set (0.00 sec)

mysql> set global password_history=2;
Query OK, 0 rows affected (0.00 sec)

-- First modification, successfulmysql> alter user yeyz@'127.0.0.1' identified with 'mysql_native_password' by 'yeyz';
Query OK, 0 rows affected (0.01 sec)

-- Second modification, error reportedmysql> alter user yeyz@'127.0.0.1' identified with 'mysql_native_password' by 'yeyz';
ERROR 3638 (HY000): Cannot use these credentials for '[email protected]' because they contradict the password history policy
mysql>

-- Set the password to be reused every 5 timesmysql> create user yeyz3@'127.0.0.1' identified with 'mysql_native_password' by 'yeyz3' password history 5;
Query OK, 0 rows affected (0.01 sec)

-- Set the password to be reused every 5 daysmysql> create user yeyz4@'127.0.0.1' identified with 'mysql_native_password' by 'yeyz4' password reuse interval 5 day; 
Query OK, 0 rows affected (0.01 sec)

-- Set the password to be reused every 5 days or every 5 times, whichever is more stringent.mysql> create user yeyz5@'127.0.0.1' identified with 'mysql_native_password' by 'yeyz5' password reuse interval 5 day password history 5;
Query OK, 0 rows affected (0.01 sec)

-- Use the default global password reuse policy, that is, the password history parameter and the password reuse interval parameter mysql> create user yeyz6@'127.0.0.1' identified with 'mysql_native_password' by 'yeyz6' password reuse interval default password history default; 
Query OK, 0 rows affected (0.01 sec)