This is the explanation for the failure to login without superuser
Excerpt from the file "/etc/mysql/mariadb.conf.d/50-server.cnf" line 115
# * Unix socket authentication plugin is built-in since 10.0.22-6
# Needed so the root database user can authenticate without a password but
# only when running as the unix root user.
# Also available for other users if required.
# See https://mariadb.com/kb/en/unix_socket-authentication-plugin/
Also make sure needed record in table user has empty plugin field (there can be, for example, "unix_socket").
Since version 5.5.7 mysql has various auth plugins support https://dev.mysql.com/doc/refman/5.6/en/authentication-plugins.html
So if you have non-empty plugin field then password would be ignored and there would be warning at mysql error log (for me it's /var/log/mysql/error.log):
[Warning] 'user' entry 'root@localhost' has both a password and an authentication plugin specified. The password will be ignored.
answered Apr 28 '16 at 21:27
For someone else that runs across this, when running mysql server 5.7.15, this actually locks your user out if you do not provide a plugin. Probably what you are looking for is a plugin of mysql_native_password. – Jonathan Cantrell Sep 28 '16 at 17:41