How to use 'mysql -uroot -pXXXX' on Debain 9.8? 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?
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.