Had a difficult time authenticating to a newly installed MariaDB 10.1 instance on Debian 9 (Stretch) recently, wasting a couple of hours and some precious late-night sanity, so I wanted to document that here for myself later, and anyone else who might run across this.
As part of a learning exercise I wanted to install a PHP web app on the cloud somewhere, so I chose Google Cloud Platform using their “always free tier”, which includes free, tiny f1-micro instances. Process was pretty easy, and using their LAMP quick start I created a new LAMP Virtual Machine with Debian Stretch. All was pretty easy except for the MySQL installation.
This step in particular was a crap-fest:
sudo mysql_secure_installation
During this step you’re supposed to set the root password for MySQL/MariaDB, but it doesn’t actually work. I spent an hour uninstalling and reinstalling MariaDB, then re-running that step with no luck. A random mailing list from the Debian forums mentioned that “mysql_secure_installation” doesn’t really work anymore. Then, this answer on Ubuntu forums pointed me to this blog post from Percona that explains the problem pretty well.
Basically, by default MySQL 5.7/MariaDB 10.1 don’t allow root login with a password. They don’t even let you set a password. By default, you’re supposed to log in using sudo:
So this works:
sudo mysql -u root
But this doesn’t:
sudo mysql -u root -p my-password
Meanwhile, you don’t know that because when you run the mysql_secure_installation script that every tutorial tells you to run, it asks you to set a root password. But that doesn’t do anything, and doesn’t work, so you’re in this loop of not being able to log in.
Anyway, the solution in the Percona blog (to change this default “no-password login” behavior back to allowing a password login) didn’t work either, until I read one of the comments.
In particular, this didn’t work for me:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'test';
But this did:
UPDATE mysql.user SET authentication_string = PASSWORD('test'), plugin = 'mysql_native_password' WHERE User = 'root' AND Host = 'localhost';
FLUSH PRIVILEGES;
And now you can log in to MySQL or MariaDB as root with a password:
mysql -u root -p
Oh, installing phpMyAdmin from an apt package didn’t work for some reason either, but I ignored that and used the command line.
Anyway, it all sucked for a night, so I hope someone else (me?!) finds my pain useful later.
So I just installed MariaDB 10.1 on Ubuntu 18.04 and I didn’t run into this problem. mysql_secure_installation seemed to work as intended. Maybe it’s a Debian only thing? Or it’s resolved?