Mariadb password in Ubuntu 18.04

I setup a password for Mariadb when I ran sudo mysql_secure_installation but when I try to run mariadb with mysql -u root -p I get the following error.

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

I am on Ubuntu 18.04 on AWS's Lightsail VPS. I am using Mariadb version 10.1.44-MariaDB-0ubuntu0.18.04.1.

MariaDB [mysql]> SELECT user,plugin,host FROM mysql.user;
+---------------+-------------+-----------+
| user | plugin | host |
+---------------+-------------+-----------+
| root | unix_socket | localhost |
| wordpressuser | | localhost |
+---------------+-------------+-----------+

Secondly, I am confused about what is the proper way to setup and use Mariadb.

1. Should I setup a password when it asks for me during mysql_secure_installation?
2. Should mysql be accessed using sudo mysql or mysql -u root -p?

I can setup a password as instructed on JournalDev but I was wondering what is the right way to do it.

$ mysql -u root -p
MariaDB [(none)]> use mysql;
MariaDB [mysql]> update user SET PASSWORD=PASSWORD("Passw0rd!") WHERE USER='root';

1 Answer

Have a read here which should answer all your questions:Authentication Plugin - Unix Socket

Basically, by default the root mysql account is secured using the unix_socket plugin so that only the root Ubuntu user can logon to the root database user. So sudo mysql -u root mysql should work but mysql -u root mysql as a non-root user will not.

It's possible to change this behaviour and use password authentication if you really need to (see linked article) but you may be making your database less secure. It really depends on your use-case and needs.