Debian 9 Stretch: Access denied for user 'root'@'localhost'

[peter_b]

[PROBLEM]
Installed mysql-server (actually "MariaDB" now in Debian Stretch ) as usual.
I was never asked for a mysql root password...

Seems that the following command must be called:

Code: Select all

$ sudo mysql_secure_installation

Then enter a mariadb root user password, answer (most of) the questions with "Y".

Assuming I could login now, I got:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

[SOLUTION]
For "classic" authentication behavior, you must switch the (new) default 'unix_socket' to 'mysql_native_password'.
See details (and more detailed solution at stackoverflow and step-by-step instructions at Childsplay's blog).

Here's the short version:

Code: Select all

$ sudo mariadb -u root

Then enter the following SQL commands:

Code: Select all

USE mysql;
UPDATE user SET plugin='mysql_native_password' WHERE User='root';
FLUSH PRIVILEGES;
EXIT

Should work as previously expected now, using the password you've entered in "mysql_secure_installation".



Links:

• Getting Debian 9 “Stretch” using a password for the mysql root user
• ERROR 1698 (28000): Access denied for user 'root'@'localhost' (stackoverflow)