MySQL 8 Dual Password: Smooth Password Rotation Without Downtime

The Nightmare of “Password Rotation” on Production

If you’ve ever had shaky hands while typing an ALTER USER command on a DB cluster handling over 5,000 concurrent connections, you’re not alone. Periodic Password Rotation is mandatory for security, but it always carries the risk of service disruption.

The familiar scenario is: You change the password on the MySQL Server, then scramble to update the configuration for dozens of microservices. Even a few seconds of being out of sync, and Access denied errors will flood the system logs. As a result, user requests fail, and the boss starts breathing down your neck asking why the system is down.

I once managed a Fintech system with more than 20 services running on Kubernetes. Despite using Rolling Updates, updating ConfigMaps still had a certain latency. That 5-10 second gap was enough to cause hundreds of payment transactions to fail. That’s when I realized the traditional way was no longer suitable for systems requiring 99.99% availability.

Why Does the Old Password Change Method Always Fail?

The problem lies in atomicity. In MySQL versions prior to 8.0.14, a User was only allowed to have a single password at any given time. When you execute the change command, the old password is overwritten immediately.

The issue occurs due to synchronization latency:

• Server-side: Has already switched to the new password.
• Client-side (App): Is still using the old password in the Connection Pool or configuration cache.

To solve this thoroughly, we need a mechanism that allows a User to use both the old and new passwords simultaneously during the transition phase.

Old Trick: Creating Secondary Users and Their Drawbacks

In the past, DBAs often created a temporary User (like app_user_v2) with equivalent permissions. Only after the application had successfully switched to the new User would the old User be deleted.

While this method avoids downtime, it creates “clutter” in the management system. Manually copying dozens of Privileges from the old User to the new User is prone to errors, creating security vulnerabilities that are hard to control.

MySQL 8 Dual Password: A Game-Changing Solution

Starting from version 8.0.14, MySQL introduced the Dual Password feature. This is a real lifesaver for DevOps engineers. A MySQL account can now hold two passwords at the same time: a Primary password and a Secondary password.

The mechanism is very clever: MySQL will check the login credentials against both passwords. As long as one of them matches, the connection is accepted immediately.

A Safe 3-Step Deployment Process

Suppose the user webapp_user is using the password OldPass123 and you need to change it to NewPass456.

Step 1: Set a Secondary Password with RETAIN CURRENT PASSWORD

Instead of overwriting, we will push the current password to the “backup seat.”

-- Set the new password as primary, keep the old one as secondary
ALTER USER 'webapp_user'@'%' IDENTIFIED BY 'NewPass456' RETAIN CURRENT PASSWORD;

At this point, both passwords are valid. Old instances still run normally with OldPass123, and not a single request is interrupted.

Step 2: Update Your Applications

Now you can leisurely update the new password in Vault, ConfigMap, or environment variables. Then, proceed to deploy/restart services according to your roadmap.

During the rolling update process, the system will have pods using the old pass and pods using the new pass existing side-by-side. Both will connect successfully to the Database.

Step 3: Cleanup with DISCARD OLD PASSWORD

Once you are 100% sure all services have switched to the new password (verify via logs or monitoring), remove the old password for security.

-- Completely remove the secondary password
ALTER USER 'webapp_user'@'%' DISCARD OLD PASSWORD;

After this command, OldPass123 is officially deactivated.

Pro-tips to Avoid Headaches

While this feature is powerful, you should still keep a few important points in mind:

• Check the version: This feature is only available from MySQL 8.0.14 and above. Don’t try it on MySQL 5.7 or you’ll get a syntax error.
• Permission management: You need the APPLICATION_PASSWORD_ADMIN privilege if you want a user to be able to change their own secondary password.
• Never forget the DISCARD step: Letting two passwords coexist for too long is a serious security risk. Treat Dual Password as a temporary state during deployment.
• Monitor logs: Enable the General Log or use the Performance Schema to check if any connections are still using the old password before executing the DISCARD command.

The Dual Password technique makes operational workflows much more professional. It eliminates the timing dependency between DBA and Dev teams. If your system requires high stability, apply this technique in your next password rotation.