I recently helped my buddy move from Centos 7 to Ubuntu 18.04. In the process, we noticed one of his custom websites was a little messed up. It was throwing a lot of errors in regards to mysqli_connect getting a boolean value instead of the query. We found out this was due to a difference between MariaDB (Centos 7) and plain old MySQL (Ubuntu). So we fixed this by removing MySQL and installing MariaDB. However, there was something else wrong. No user could log in to the root account on MySQL using a password, they would get the old access denied for root@localhost error. But the root account worked. We found out this was because MariaDB now uses the unix_socket auth plugin by default. What this means is, that if you are using root on the server, you can access root in MySQL, but no other user can. So when you rerun mysql_secure_installation as root it will look like the password is blank, even though you had set it in the past as if it had never set it. In actuality, it had been set, it’s just not using it.
To fix this, I had to scour the net. Most tutorials gave me a syntax error when using their code. For example:
DO NOT USE THIS
|
1 |
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'test'; |
This will throw an error
|
1 |
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 'test' at line 1 |
The correct way to make sure root uses the password is to use the following:
|
1 |
update mysql.user set plugin = 'mysql_native_password' where User = 'root'; |
No need to change the password you set the first time you used mysql_secure_installation. Like I had said, you DID set a password, but it just was not being used.
Security Implications
This is a bit less secure than using unix_socket, however, my if you want to be able to log in from your own user to root, using a password, you need to use mysql_native_password.