Fix mysql ERROR 1698 (28000): Access denied for user 'root'@'localhost'

After a fresh install of MariaDB on a Ubuntu 16.04 machine, I got this error when trying to login to mysql:

$ mysql -u root -p

ERROR 1698 (28000): Access denied for user 'root'@'localhost'

Then I realized that I can login without password if I use sudo (of-course I have to enter the sudo password):

$ sudo mysql -u -p

So I's looking around on the Internet and figure out the problem. In Ubuntu 16.04, mysql is using by default the UNIX auth_socket plugin which means that db users will be authenticated by the system user credentials.

$ sudo mysql -u root

mysql> USE mysql;
mysql> SELECT User, Host, plugin FROM mysql.user;

+--------------+-------------------+
| User            | plugin                |
+--------------+-------------------+
| root             | auth_socket       |


So to login to mysql without sudo and use the password, I have to set the root (or any user) user to use the mysql_native_password plugin:

$ sudo mysql -u root

mysql> USE mysql;
mysql> UPDATE user SET plugin='mysql_native_password' WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> exit;

$ sudo systemctl restart mysql



References: https://stackoverflow.com/questions/39281594/error-1698-28000-access-denied-for-user-rootlocalhost