Wondering how to reset the MariaDB root password on EC2? We can help you.

By default, MariaDB 5.5 on Amazon Linux 2 doesn’t have a root password.

While we create a root password for MariaDB and then lock yourself out of the database, we must reset the root password.

Here, at Bobcares, we assist our customers with several AWS queries as part of our AWS Support Services.

Today, let us see how we can reset the root password.

Reset the MariaDB root password on EC2

We need to make note that, while we reset the root password, we will not be able to query the database.

Moving ahead, let us see how our Support Techs go about the password reset.

1. First and foremost, we need to verify that we have a recent snapshot of the volume on which the MariaDB data directory resides. If necessary, we can recreate the volume from this snapshot.
2. Then we go ahead and stop the MariaDB service:

   sudo systemctl stop mariadb

3. After that, we start MariaDB in safe mode:

   sudo mysqld_safe --skip-grant-tables --skip-networking &

4. We then log in to the MariaDB monitor:

   mysql -u root

5. Here, we update the MariaDB root password:

   UPDATE mysql.user SET Password = PASSWORD('new_password_here') WHERE User = 'root';
   
   The output looks like this:
   Query OK, 1 row affected (0.00 sec)
   Rows matched: 1 Changed: 1 Warnings: 0

6. Then we flush the privileges to reload the grant tables. Once, it reloads, the new password takes effect.

   FLUSH PRIVILEGES;

7. Now we exit the MariaDB monitor:

   exit

8. Eventually, we stop the MariaDB process:

   sudo kill `sudo cat /var/run/mariadb/mariadb.pid`

9. Then we start the MariaDB service:

   sudo systemctl start mariadb

10. Finally, we test the new MariaDB root password:

    mysql -u root -p

[Need help with the reset? We are here for you]

Conclusion

In short, there is a high chance that we may lock ourselves out of the database while we reset the root password. Today, we saw how our Support Techs fix the root password for our customers.