Resolving MySQL 5.7 Login Issue Caused by the auth_socket Plugin and Exploring Its Use Cases

Phenomenon

Engineers reported that after installing MySQL 5.7.31, any password allowed login and password changes had no effect, even after restarting the server.

Analysis

The problem was suspected to be related to the --skip-grant-tables option, but mysqld --print-defaults showed no such flag. The logged‑in user was root@localhost , indicating no proxy user involvement.

Database queries revealed normal user entries:

mysql> select user(),current_user();
+----------------+----------------+
| user()         | current_user() |
+----------------+----------------+
| root@localhost | root@localhost |
+----------------+----------------+
1 row in set (0.01 sec)

Further inspection of the mysql.user table showed password hashes were present:

mysql> select user,host,authentication_string from mysql.user;
+------------------+-----------+------------------------------------------------------------------------+
| user             | host      | authentication_string                                                  |
+------------------+-----------+------------------------------------------------------------------------+
| lisi             | %         | *52BCD17AD903BEC378139B11966C9B91AC4DED7C                               |
| mysql.session    | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE                               |
| mysql.sys        | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE                               |
| root             | localhost | *1840214DE27E4E262F5D981E13317691BA886B76                               |
+------------------+-----------+------------------------------------------------------------------------+
5 rows in set (0.01 sec)

The plugin column showed that only the root user used the auth_socket plugin, while others used mysql_native_password :

mysql> select user,host,plugin from mysql.user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| lisi             | %         | mysql_native_password |
| mysql.session    | localhost | mysql_native_password |
| mysql.sys        | localhost | mysql_native_password |
| root             | localhost | auth_socket           |
+------------------+-----------+-----------------------+
5 rows in set (0.02 sec)

Problem Resolution

Changing the root user's authentication plugin to mysql_native_password fixed the login issue:

update user set plugin="mysql_native_password" where user='root';

After the fix, normal password authentication works as expected.

auth_socket Plugin Usage Scenarios

The auth_socket plugin authenticates users via the operating system's Unix socket, requiring no password. It is considered secure because:

Login is only possible through the local Unix socket, ensuring local access.

The OS user must match the MySQL user, leveraging the OS's own authentication mechanisms.

This makes the plugin suitable for pre‑production environments where both the OS and MySQL root accounts are used frequently. After production, it is advisable to switch to a password‑based method, for example:

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

Keywords: #password security# #auth_socket plugin#