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?
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.
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:
7. Exit the MariaDB monitor:
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