Unable to change root user unix_socket to mysql_native_password

Question

Server version: 10.1.41-MariaDB-0+deb9u1 Debian 9.9

I have installed a LAMP stack on my Debian 9 machine and can not log in with the root user. From my searching, it seems that it's because the install commonly creates a root user without a password, causing it to default to unix_socket, which foils logging in with that user via terminal.

MariaDB [(none)]> SELECT user,authentication_string,plugin,host FROM mysql.user;
+------------+-------------------------------------------+-------------+-----------+ 
| user       | authentication_string                     | plugin      | host      |
+------------+-------------------------------------------+-------------+-----------+ 
| root       | *3624F23A73825C0D79BF3591774631513FE76C24 | unix_socket | localhost | | phpmyadmin |                                           |             | localhost |
+------------+-------------------------------------------+-------------+-----------+ 
2 rows in set (0.00 sec)

I found an article explaining how to resolve this issue by shutting down mysql, starting it with sudo mysqld_safe --skip-grant-tables & so I can log in and then running:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mypass';

then stopping and starting the daemon. My problem is that no matter how I format this line, I get:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'user 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mypass'' at line 1

I've tried tons of different formatting with backtics, case change and other iterations of the command found on the web but everything fails with the same result.

Could someone tell me how I go about changing this successfully?

Answer 1

As the manual says:

The ALTER USER statement was introduced in MariaDB 10.2.0.

Since you're at 10.1, you'll need to reset the password using the old, dangerous method:

update mysql.user 
 set authentication_string=password('mypass'),  
 plugin='mysql_native_password' 
where user='root';