Implementing MySQL 8.0 Password History and Reuse Interval Policies

This article continues the discussion of MySQL 8.0's new password validation strategy. It assumes a scenario where an administrator creates two users—one for development and one for operations—and each must meet specific password‑change requirements.

Requirements :

Development users must change passwords regularly and cannot reuse any of the last three passwords.

Operations users must also change passwords regularly but cannot reuse any password changed within the last seven days.

Previously these constraints could only be enforced by maintaining a manual list of historic passwords. MySQL 8.0 now provides built‑in support, allowing the “notebook” approach to be discarded.

1. Set global parameters in the configuration file

The password_history variable defines how many recent passwords are retained, while password_reuse_interval defines the retention period in days.

Implementing the development user requirement (retain last 3 passwords)

Log in as an administrator and set the global parameter:

mysql:(none)> set persist password_history=3;
Query OK, 0 rows affected (0.00 sec)

Reconnect, create the development user ytt_dev and change the password three times:

root@ytt-ubuntu:/home/ytt# mysql -S /opt/mysql/mysqld.sock
... 
mysql:(none)> create user ytt_dev identified by 'root123';
Query OK, 0 rows affected (0.15 sec)

root@ytt-ubuntu:/home/ytt# mysql -uytt_dev -hytt-ubuntu -proot123
... 
mysql:(none)> alter user ytt_dev identified by 'root456';
Query OK, 0 rows affected (0.03 sec)

mysql:(none)> alter user ytt_dev identified by 'root789';
Query OK, 0 rows affected (0.17 sec)

Attempting to reuse an earlier password now fails:

mysql:(none)> alter user ytt_dev identified by 'root123';
ERROR 3638 (HY000): Cannot use these credentials for 'ytt_dev@%' because they contradict the password history policy

Using a new, non‑conflicting password succeeds:

mysql:(none)> alter user ytt_dev identified by 'rootnew';
Query OK, 0 rows affected (0.04 sec)

Implementing the operations user requirement (retain passwords for 7 days)

Set the global reuse interval and reset the history count to its default:

mysql:(none)> set persist password_reuse_interval = 7;
Query OK, 0 rows affected (0.00 sec)

mysql:(none)> set persist password_history=default;
Query OK, 0 rows affected (0.00 sec)

Create the operations user ytt_dba and change the password five times:

mysql:(none)> create user ytt_dba identified by 'root123';
Query OK, 0 rows affected (0.01 sec)

root@ytt-ubuntu:/home/ytt# mysql -uytt_dba -hytt-ubuntu -proot123
... 
mysql:(none)> alter user ytt_dba identified by 'root456';
Query OK, 0 rows affected (0.15 sec)

mysql:(none)> alter user ytt_dba identified by 'root789';
Query OK, 0 rows affected (0.08 sec)

mysql:(none)> alter user ytt_dba identified by 'root000';
Query OK, 0 rows affected (0.02 sec)

mysql:(none)> alter user ytt_dba identified by 'root888';
Query OK, 0 rows affected (0.02 sec)

mysql:(none)> alter user ytt_dba identified by 'root999';
Query OK, 0 rows affected (0.12 sec)

Because the reuse interval is set to 7 days, attempting to revert to any password used within that window is rejected:

mysql:(none)> alter user ytt_dba identified by 'root123';
ERROR 3638 (HY000): Cannot use these credentials for 'ytt_dba@%' because they contradict the password history policy

mysql:(none)> alter user ytt_dba identified by 'root456';
ERROR 3638 (HY000): Cannot use these credentials for 'ytt_dba@%' because they contradict the password history policy

Choosing a password not used in the last seven days succeeds:

mysql:(none)> alter user ytt_dba identified by 'rootnew';
Query OK, 0 rows affected (0.10 sec)

If a single user must satisfy both constraints, both global variables can be set together (history = 3, reuse interval = 7):

mysql:(none)> set persist password_reuse_interval = 7;
Query OK, 0 rows affected (0.00 sec)

mysql:(none)> set persist password_history=3;
Query OK, 0 rows affected (0.00 sec)

2. Define password policies per user

Reset the global parameters to defaults (disabling the policies):

mysql:(none)> set persist password_reuse_interval = default;
Query OK, 0 rows affected (0.00 sec)

mysql:(none)> set persist password_history=default;
Query OK, 0 rows affected (0.00 sec)

Create two new users and assign policies individually:

mysql:(none)> create user ytt_dev1 identified by 'root123';
Query OK, 0 rows affected (0.04 sec)

mysql:(none)> create user ytt_dba1 identified by 'root123';
Query OK, 0 rows affected (0.02 sec)

mysql:(none)> alter user ytt_dev1 password history 3;
Query OK, 0 rows affected (0.01 sec)

mysql:(none)> alter user ytt_dba1 password reuse interval 7 day;
Query OK, 0 rows affected (0.02 sec)

Verify the settings by querying mysql.user :

mysql:(none)> select user,password_reuse_history,password_reuse_time from mysql.user where password_reuse_history is not null or password_reuse_time is not null;
+----------+------------------------+---------------------+
| user     | password_reuse_history | password_reuse_time |
+----------+------------------------+---------------------+
| ytt_dba1 | NULL                   | 7                   |
| ytt_dev1 | 3                      | NULL                |
+----------+------------------------+---------------------+

Conclusion

MySQL 8.0’s password history and reuse‑interval policies provide a built‑in mechanism for enforcing password security requirements, eliminating the need for external scripts or manual tracking of historic passwords.