restarted rds but still cannot login using root to MariaDB

Question

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.

Answer

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

Answer

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.

restarted rds but still cannot login using root to MariaDB

관련 콘텐츠