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

Short description

By default, MariaDB Server 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, then you must reset the root password.

Note: When you're resetting your rot password, you can't query your database.

Resolution

Complete the following steps:

1. Create a snapshot of the volume where the MariaDB data directory resides. You can recreate the volume from this snapshot, if needed.

2. Run the following command to stop MariaDB Server:

   sudo systemctl stop mariadb

3. Run the following command to start MariaDB Server in safe mode:

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

4. Run the following command to determine the MariaDB Server version:

   mariadb —versionmariadb Ver 15.1 Distrib 10.5.23-MariaDB, for Linux (x86_64) using EditLine wrapper

5. Run the following command to set the MariaDB command history to /dev/null and log in to the MariaDB monitor:

   export MYSQL_HISTFILE=/dev/nullmysql -u root mysql

   Important: When you set the command history to /dev/null, your password information doesn't appear in plaintext in the history file. You can reset this value after you complete the procedure.

6. Run the following command to update your root password on MariaDB Server versions that are earlier than 10.4:

   UPDATE user SET Password = PASSWORD('new_password_here') WHERE User = 'root';

   Run the following command to update your MariaDB root password for MariaDB Server versions 10.4 and later:

   FLUSH PRIVILEGES;ALTER USER root@localhost IDENTIFIED VIA mysql_native_password USING PASSWORD('new_password_here');

   Example output:

   Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0

7. To reload the grant tables, run the following command to flush the privileges:

   FLUSH PRIVILEGES;

   Note: Your new password takes effect after the reload.

8. Run the following command to exit the MariaDB monitor:

   exit

9. Run the following command to stop the MariaDB process:

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

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

10. Run the following command to start MariaDB Server:

    sudo systemctl start mariadb

11. Run the following command to test your new MariaDB root password:

    mysql -u root -p