How do I reset the MariaDB root password on an Amazon EC2 instance that's running Amazon Linux 2?

Last updated: 2019-09-04

How do I reset the MariaDB root password on an Amazon Elastic Compute Cloud (Amazon EC2) instance that's running Amazon Linux 2? 

Short Description

By default, MariaDB 5.5 on Amazon Linux 2 doesn't have a root password. If you create a root password for MariaDB and then lock yourself out of your database, you must reset the root password.

Note: You won’t be able to query your database while you’re resetting the root password.

Resolution

1.    Verify that you have a recent snapshot of the volume on which the MariaDB data directory resides. You can recreate the volume from this snapshot, if needed.

2.    Stop the MariaDB service:

sudo systemctl stop mariadb

3.    Start MariaDB in safe mode:

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

4.    Log in to the MariaDB monitor:

mysql -u root

5.    Update your 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.    Your new root password takes effect after the grant tables are reloaded. Flush the privileges to reload the grant tables:

FLUSH PRIVILEGES;

7.    Exit the MariaDB monitor:

exit

8.    Stop the MariaDB process:

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

Note: The process might take a few seconds to terminate.

9.    Start the MariaDB service:

sudo systemctl start mariadb

10.    Test your new MariaDB root password:

mysql -u root -p

Did this article help you?

Yes
No

Anything we could improve?

Let us know

Need more help?

Contact AWS Support