Background

After the virtual machine has been provisioning with LAMP and you check the MariaDB status

systemctl status mariadb

The MariaDB status showed the ERROR 1045 (28000): Access Denied for user ‘root’@’localhost’ (using password: NO).

It happened before BUT no documentation to fix this.

Solution

From the other cloud services that we have, we knew that this is something to do with the ‘UNIX_SOCKET’ authentication.

We also know that cause as this new machine has been rebuilt a few times, changing the root password OR changing the MariaDB root password has got nothing to do with this.

After logging in to MariaDB, we found nothing on this query:

SELECT plugin from mysql.user where User='root';

We tried the following query:

UPDATE mysql.user SET plugin = 'mysql_native_password',  Password = PASSWORD('new_password') WHERE User = 'root';

We found something interesting configuration on the /etc/mysql/debian.cnf . The password was blank on both client and mysql_upgrade. We put the password for testing purposes.

Note: Ensure to double quote within the password. For instance: password = “password”

Restart the MariaDB:

systemctl restart mariadb

Check the MariaDB status:

systemctl status mariadb

The issue has gone.

Note: This may fix the issue, however, we are investigating this approach and we believe it’s not safe to put the password into this file. On the flip side though, the MariaDB root password has to be different anyway from the operating system root.

Change the MariaDB root password:

mysqladmin --user=root --password=currentpassword password "newpassword"

Test the root account.

Open the file – /etc/mysql/debian.cnf

Change the password to a new password.

Note: Ensure to double quote within the password. For instance: password = “password”

Stop MariaDB service:

sudo systemctl stop mariadb

Start the MariaDB service:

sudo systemctl start mariadb

Check the status again:

sudo systemctl status mariadb

Sources

URLs – https://superuser.com/questions/949496/cant-reset-mysql-mariadb-root-password

Access Denied: https://suay.site/?p=606

Credits

Photo by Tima Miroshnichenko: https://www.pexels.com/photo/a-girl-sitting-in-front-of-a-table-between-database-wooden-drawer-6549629/