MySQL login issues with root - Auth Socket vs Native Password

Many of us might have come across this issues of not being able to login as the MySQL 'root' user either from the command line or via the phpMyAdmin interface.

But if you try to login in  either as the Linux 'root' user or use sudo, you will login in immediately without the need of the MySQL 'root' users password.

The reason to here is that the authentication for the MySQL 'root' user is set to  "auth_socket" which basically means that MySQL will let the user login via the same (local)host it the user is already authenticated or logged in as the Linux/Unix user.

The official definition says: 

The server-side auth_socket authentication plugin authenticates clients that connect from the local host through the Unix socket file. 

To check the same login as 'root' via the auth_socket system, either change to Linux 'root' user and connect to MySQL or user 'sudo'

sudo mysql -u root 

Once logged in check for current plugin status via the following query, you will see the active plugin.

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%socket%';
+-------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+-------------+---------------+
| auth_socket | ACTIVE        |
+-------------+---------------+
1 row in set (0.00 sec)

Now we need to check if this is active with the  'MySQL' root user.

mysql> SELECT user, plugin FROM mysql.user WHERE user IN ('root');
+------+-------------+
| user | plugin      |
+------+-------------+
| root | auth_socket |
+------+-------------+
1 row in set (0.00 sec)
 

This confirms that the MySQl 'root' user indeed is using the 'auth_socket'.  To use the normal password based authentication we will need to change the auth plugin to "mysql_native_password".   Run the following query to set it to "mysql_native_password" as well as set the MySQL 'root' user password.

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password_here';
Query OK, 0 rows affected (0.05 sec)

Just to verify the same run the plugin check query once again.

mysql> SELECT user, plugin FROM mysql.user WHERE user IN ('root');
+------+-----------------------+
| user | plugin                |
+------+-----------------------+
| root | mysql_native_password |
+------+-----------------------+
1 row in set (0.00 sec)

Now we are all set.

You can logout and login normally with the new password even on phpMyAdmin.