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';
  • Maybe that helps if you can google translate from Russian. – Alexey Burdin Jul 26 at 6:59

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.

| improve this answer | |
  • I glanced through that page but it was hard to understand a lot of terminology at first glance. I was thrown off by the fact the mysql_secure_installation suggests setting a password during the setup whereas Mariadb had unix_socket which disallows login to root from a local client. Shortly after I posted I came across slides which clears the doubt. AKA I should ignore setting password and use sudo to access mysql. – Sudhir Khanger Jul 26 at 10:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.