MySQL login issues with root - Auth Socket vs Native Password


DB no entry


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.

access denied to root user

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.

mysql login as root via sudo

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.


  

Main category

Add new comment

Filtered HTML

  • Web page addresses and email addresses turn into links automatically.
  • Allowed HTML tags: <a href hreflang> <em> <strong> <cite> <blockquote cite> <code> <ul type> <ol start type> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.
CAPTCHA
8 + 9 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.
Address

OpenSourceCook.in
"Natraj"  Bungalow,
Colony No.7,  Sr.No. 38.
(Lane Behind Sai Baba Mandir)
Kale Borate Nagar, Hadapsar,
Pune - 411028.
Get Directions