restarted rds but still cannot login using root to MariaDB


When I tried to login to a MariaDB instance using root: mysql -u root -p -h dbinstancename I get an error:

ERROR 1045 (28000): Access denied for user 'root'@'172.xx.xx.xx' (using password: YES)

When I login with a user login, I notice the host for the user is not 172.xx.xx.xx but %. user->root, host->% I restarted the database instance but invain. I normally access the database instance from a EC2 as the DB instance is private. Both the EC2 and DB Instance are in the same VPC. I was able to login to the DB instance until I was trying to grant privileges to user:

MariaDB [(none)]> grant file on *.* to ben@172.xx.xx.xx;
ERROR 1045 (28000): Access denied for user 'root'@'%' (using password: YES)

And keep getting Access denied for user 'root'@'%' (using password: YES) for other grant related commands. And finally I failed to login with root with similar error. The normal user login has limited privileges. Hope someone could help to get me login again using root.

profile picture
asked 4 months ago100 views
2 Answers

I just deleted the database instance and restore a new DB instance from snapshot. Fortunately, it is a development database.

profile picture
answered 4 months ago

Those two things may happen at the moment:

  1. Change in Host for Root User: When granting privileges, the host for the root user may have been changed from '%' (allowing connections from any host) to a specific IP/host. This change restricts login access from other locations.

  2. Database Instance Restart: Restarting the database instance won't resolve this issue because the root user configuration is stored within the data itself.

To address this, follow these steps:

  • Stop the MariaDB service:

    sudo systemctl stop mariadb
  • Take a snapshot for backup:

    sudo tar cvf backup.tar /var/lib/mysql
  • Modify the mysql.user table in the data files to reset the password and host for the root user.

  • Start the MariaDB service:

    sudo systemctl start mariadb
  • Check the mysql.user table after restarting to ensure the root user host is set back to '%' and try logging in again with the new password.

These steps should help you regain access to your MariaDB instance using the root user.

profile picture
answered 12 days ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions