How to Configure Dual Passwords in MySQL

This article explains how to configure and use the dual password feature in MySQL to achieve seamless credential rotation without application downtime. You will learn the SQL commands required to introduce a secondary password, transition your applications to the new credentials, and safely deprecate the old password once the migration is complete.

Understanding MySQL Dual Passwords

In high-availability environments, rotating database credentials can cause downtime if the application and database are not updated simultaneously. MySQL solves this issue by supporting dual passwords (a primary and a secondary password) for a single user account.

When dual passwords are enabled, MySQL allows client connections using either password. This enables a three-step rotation workflow: 1. Apply a new password while retaining the current one as a secondary password. 2. Update all application clients to use the new password at their own pace. 3. Discard the old password once all clients have transitioned.

Step-by-Step Credential Rotation

Step 1: Add the New Password

To initiate the rotation, set the new password as the primary password while retaining the existing password as the secondary option. Run the following command:

ALTER USER 'app_user'@'localhost' 
IDENTIFIED BY 'NewSecurePass123' 
RETAIN CURRENT PASSWORD;

At this stage: * NewSecurePass123 becomes the primary password. * The previous password becomes the secondary password. * Applications can connect using either the old password or the new password.

Step 2: Update Client Applications

With both passwords active, update the connection strings in your application deployments, microservices, or configuration managers to use the new password (NewSecurePass123).

Because the old password is still accepted as the secondary credential, this update can be rolled out progressively across your infrastructure with zero connection failures.

Step 3: Discard the Old Password

Once all application instances have been successfully updated and are connecting with the new password, you must remove the secondary password to secure the account.

Execute the following command to discard the old credential:

ALTER USER 'app_user'@'localhost' 
DISCARD OLD PASSWORD;

After running this command, only the primary password (NewSecurePass123) will be accepted. Any connection attempts using the old password will be rejected.

Verifying the Password State

You can verify the status of a user’s passwords by querying the mysql.user system table. This helps confirm whether a secondary password is active before you attempt to discard it.

SELECT user, host, password_expired, password_last_changed 
FROM mysql.user 
WHERE user = 'app_user';