Use MySQL Dual Passwords to Change Credentials without Downtime

Updated on February 20, 2021

Introduction

Starting from MySQL 8.0.14, you can have dual passwords for users' accounts. You can use this feature in scenarios where you've multiple applications connected to a database with the same credentials, but you need to change the password. In such a case, if you update the user's password in the MySQL server, end-users consuming the applications will get an access denied error before you finish modifying the database configuration files in each application.

Because this change can take seconds to hours depending on the system design, it can lead to poor user experience, downtime, and revenue loss in mission-critical applications. Periodically changing MySQL credentials is a critical part of your application's security. Using dual passwords is a method to seamlessly change MySQL passwords without locking out users who use the old credentials.

In this guide, you'll create three hypothetical applications and connect them to a single MySQL database server with the same credentials. Next, you'll use the MySQL dual password feature to effect a credential change without any downtime and discard the old password once the new credentials are propagated across the three applications.

Prerequisites

Before you begin, make sure you've got the following:

An Ubuntu 20.04 server.
A sudo user.
A MySQL Database.

This guide requires MySQL 8.0.14 or later.

Create a Test Environment

SSH to your server and log in to the MySQL server.

$ sudo mysql -u root -p

When prompted, enter the root password of your MySQL server and press Enter continue. Then, run the command below to create a test_db database`.

mysql> CREATE DATABASE test_db;

Next, create user test_db_user. For this guide, you'll use PASSWORD_A as the initial password. Later you'll change this user's password to PASSWORD_B and disable the old password.

mysql> CREATE USER 'test_db_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'PASSWORD_A';
mysql> GRANT ALL PRIVILEGES ON test_db.* TO 'test_db_user'@'localhost';
mysql> FLUSH PRIVILEGES;

Exit from the MySQL command-line interface

mysql> QUIT;

Create Three PHP Applications

First, initialize the apps directory.

$ sudo mkdir -p /var/www/html/apps/

Application 1

Create application_1.php file by running the command below.

$ sudo nano /var/www/html/apps/application_1.php

Enter the information below into the file. The script below will try to connect to the MySQL database and if the connection is successful, you will get the message Application 1 has connected to database successfully.. Otherwise, an error will be thrown.

<?php
    try {
        define('DB_NAME', 'test_db');
        define('DB_USER', 'test_db_user');
        define('DB_PASSWORD', 'PASSWORD_A');
        define('DB_HOST', 'localhost');

        $pdo = new PDO("mysql:host=" . DB_HOST . ";dbname=" . DB_NAME, DB_USER , DB_PASSWORD);
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

        echo "Application 1 has connected to database successfully.";

        } catch(PDOException $e) {
            echo $e->getMessage();
        }
?>

Save and close the file.

Application 2

Create application_2.php file. Again, use nano to open the file.

$ sudo nano /var/www/html/apps/application_2.php

Enter the information below into the file. Please note, when this second application connects to the database, you'll get a different message Application 2 has connected to database successfully..

<?php
    try {
        define('DB_NAME', 'test_db');
        define('DB_USER', 'test_db_user');
        define('DB_PASSWORD', 'PASSWORD_A');
        define('DB_HOST', 'localhost');

        $pdo = new PDO("mysql:host=" . DB_HOST . ";dbname=" . DB_NAME, DB_USER , DB_PASSWORD);
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

        echo "Application 2 has connected to database successfully.";

        } catch(PDOException $e) {
            echo $e->getMessage();
        }
?>

Save and close the file.

Application 3

Finally, create the third application. Open a new application_3.php file.

$ sudo nano /var/www/html/apps/application_3.php

Enter the information below into the file. When this application connects to the database, you will get the message "Application 3 has connected to database successfully.". Otherwise, an error will be displayed.

<?php
    try {
        define('DB_NAME', 'test_db');
        define('DB_USER', 'test_db_user');
        define('DB_PASSWORD', 'PASSWORD_A');
        define('DB_HOST', 'localhost');

        $pdo = new PDO("mysql:host=" . DB_HOST . ";dbname=" . DB_NAME, DB_USER , DB_PASSWORD);
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

        echo "Application 3 has connected to database successfully.";

        } catch(PDOException $e) {
            echo $e->getMessage();
        }
?>

Save and close the file when you're through with editing and move on to testing the applications.

Run the Applications

Test the three applications and make sure they are connecting to the database as expected. Remember, these applications are all using PASSWORD_A as the password.

Run application_1.php

  $ curl localhost/apps/application_1.php

Curl response for application_1.php.

  Application 1 has connected to database successfully.

Run application_2.php

  $ curl localhost/apps/application_2.php

Curl response for application_2.php.

  Application 2 has connected to database successfully.

Run application_3.php

  $ curl localhost/apps/application_3.php

Curl response for application_3.php.

  Application 3 has connected to database successfully.

As you can see from the outputs above all the applications are connecting to the database with PASSWORD_A and no issues are encountered.

Use the Dual Password Feature

In this step, you'll use the MySQL dual password feature to change the test_db_user password. Log back to the MySQL command-line interface as root.

$ sudo mysql -u root -p

Enter your root password and hit Enter to proceed. When you get the mysql> prompt, run the command below to change the test_db_user password from PASSWORD_A to PASSWORD_B.

Please note, the statement RETAIN CURRENT PASSWORD instructs the MySQL server to retain the current (old) password until a DISCARD command is issued against the user's account.

Change the password.

mysql> ALTER USER 'test_db_user'@'localhost'
       IDENTIFIED BY 'PASSWORD_B'
       RETAIN CURRENT PASSWORD;

You should get a confirmation message as shown below.

Query OK, 0 rows affected (0.00 sec)

Exit from the MySQL server.

mysql> QUIT;

At this point, if you again try to run the three applications that you created earlier, they should connect fine to the database because you've not discarded the old password. Use the curl command to confirm this.

$ curl localhost/apps/application_1.php
$ curl localhost/apps/application_2.php
$ curl localhost/apps/application_3.php

You should get an output as shown below, where n is the application number.

Application n has connected to database successfully.

Change the Applications Passwords

In this step, you'll open your three applications' configuration files one by one and change the password to PASSWORD_B. Use the commands below.

$ sudo nano /var/www/html/apps/application_1.php
$ sudo nano /var/www/html/apps/application_2.php
$ sudo nano /var/www/html/apps/application_3.php

Then, once you open each file, locate the current password's value (PASSWORD_A).

define('DB_PASSWORD', 'PASSWORD_A');

Change PASSWORD_A to PASSWORD_B.

define('DB_PASSWORD', 'PASSWORD_B');

Save and close each file independently when you are through with editing. Please note, even before you finish updating all the applications' passwords to PASSWORD_B, the old and new passwords (dual passwords) should work just fine.

Although you're using three applications for demonstration purposes in this guide, such a change may be required across hundreds of applications' configuration files in some real-world scenarios. This is where the beauty of MySQL dual passwords comes into action. You don't have to lockout the user before the new password is propagated across all these applications.

Once you've changed the old password in all three applications, it is time to disable the old password.

Get back to the MySQL command-line interface.

$ sudo mysql -u root -p

Enter the root password and press Enter to proceed. Then, issue the command below to disable the old password for the test_db_user.

mysql> ALTER USER 'test_db_user'@'localhost'
       DISCARD OLD PASSWORD;

Ensure you get the response below to confirm the query has been executed.

Query OK, 0 rows affected (0.00 sec)

Exit from the MySQL database.

mysql> QUIT;

Run the applications again. Now, they should connect to the database with PASSWORD_B .

$ curl localhost/apps/application_1.php
$ curl localhost/apps/application_2.php
$ curl localhost/apps/application_3.php

You should get a success message for each application, as shown below.

Application 1 has connected to database successfully.
Application 2 has connected to database successfully.
Application 3 has connected to database successfully.

This confirms that password changed without causing any downtime to your three applications.

Conclusion

In this guide, you've used MySQL dual passwords to effectively change credentials to multiple applications connected to the same database without any downtime. Always use this feature to effect credentials change in all your mission-critical applications.