How to use 'mysql -uroot -pXXXX' on Debain 9.8?

Question

My apologies for this lame question. Debian 9.8 changed some things and mysql is broke but I can't seem to fix it.

I installed MySQL on Debian 9.8, which now installs MariaDB 10.1. After install I ran mysql_secure_installation and set the root password, removed temporary databases and dis-allowed anonymous users. I also added my user to the mysql group, logged off and then logged back in.

The following fails on Debian with MariaDB 10.1, but works on Fedora 29 with MariaDB 10.1:

$ mysql -uroot -pXXXXXXXXXXXXXXXXXXXXXXXX
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
$ sudo mysql -uroot -pXXXXXXXXXXXXXXXXXXXXXXXX
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

I thought the problem may be How to allow user to login to MySQL? so I used sudo su - to elevate, and then logged in to MySQL and executed the GRANT statements for root.

The Stack Overflow fix did not fix the problem. In fact I cannot seem to login from root now. Sigh...

I now think the problem may have something to do with this MariaDB knowledge base, but I don't know how to fix it. Secure passwordless root accounts only on new installs:

Secure passwordless root accounts only on new installs

Unlike the old MySQL packages in Debian, MariaDB 10.0 onwards in Debian uses unix socket authentication on new installs to avoid root password management issues and thus be more secure and easier to use with provision systems of the cloud age.

This only affects new installs. Upgrades from old versions will continue to use whatever authentication and user accounts already existed. This is however good to know, because it can affect upgrades of dependant systems, typically e.g. require users to rewrite their Ansible scripts and similar tasks. The new feature is much easier than the old, so adjusting for it requires little work.

So they document they've done something but they don't document how to fix it. Sigh...

How do I run mysql from the command line with the root user and password? What needs to be fixed on Debian 9?

Answer 1

If you do sudo su - to root, are you able to log in without a password? If not, you can always log in by skipping the grant tables. See e.g. step 2 and 3 in this tutorial.

Once inside, you need to change the authentication plugin back to 'mysql_native_password' for the root@localhost user:

ALTER USER root@localhost IDENTIFIED VIA mysql_native_password;
SET PASSWORD = PASSWORD('foo');

See documentation on this here.