lubuntu - mariadb ignores correct password

Question

This is a problem I got today when upgrading my Lubuntu version to 18.04. After the update, the system deleted my MariaDB packages. When I tried to installing them again the installer said the will move the current installation to another folder (so I'm not worried about data lost yet, but I need a way to import the data after I solve this.).

However, after I installed the new MariaDB package, it didn't prompt me to assign a root password (I assumed it would be the same as the previous installation) however I was wrong.

I searched the web about how to reset the root password, but after starting the service in --skip-grant-tables mode and then assigning a new password to my MariaDB database, I still couldn't log in after a reboot. (I needed to reboot since starting the service manually didn't work).

 ~$ mysql -u root -p
Enter password:
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

I appreciate any help with this issue since it's hindering my work.

Answer 1

Try to run it with sudo and without the password switch, like this

sudo mysql -u root

and enter your user's password normally when sudo asks for authentication.

There is a specific reason for this because mariadb now is configured by default to use "Unix socket" authentication plugin so the "root" user of mariadb uses the same credentials as the OS "root" user.

You can change this behaviour from the user configuration, but beware it can break other processes that rely on this or the installation of new software that uses MariaDB/MySQL.

An alternative can be to setup a new user with the same privileges as root and set it to not use the socket authentication plugin, so you leave the root user untouched and you can login with an user and password.

Hope it helps.

References:

• This Server Fault post
• This SuperUser post