最後更新: 2024-07-16

目錄

• Load Plugin
• Show Plugin
• Plugin: auth_socket
•  

Load Plugin

方法1

/etc/mysql/mysql.conf.d/mysqld.cnf    # U22

[mysqld]
plugin-load-add=auth_socket.so

方法2

mysql -p

INSTALL PLUGIN auth_socket SONAME 'auth_socket.so';

Notes

UNINSTALL PLUGIN auth_socket;

Show Plugin

SHOW PLUGINS \G

*************************** 1. row ***************************
   Name: binlog
 Status: ACTIVE
   Type: STORAGE ENGINE
Library: NULL
License: GPL
*************************** 2. row ***************************
   Name: mysql_native_password
 Status: ACTIVE
   Type: AUTHENTICATION
Library: NULL
License: GPL
...

SELECT PLUGIN_NAME, PLUGIN_STATUS
       FROM INFORMATION_SCHEMA.PLUGINS
       WHERE PLUGIN_NAME LIKE '%socket%';

+-------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+-------------+---------------+
| auth_socket | ACTIVE        |
+-------------+---------------+

Plugin: auth_socket

當使用了 auth_socket, 會遇到 root empty password 的情況

• mysql -h 127.0.0.1    # 正常
• mysql -h localhost     # 沒有問 password !!

使用以下 SQL 也不能設定 PW

ALTER USER 'root'@'%' IDENTIFIED BY 'XXX';

Verify

SELECT User, Host, authentication_string FROM mysql.user;

Troubleshoot 流程

SELECT * FROM mysql.user WHERE user='root' AND host='localhost' \G

Output

plugin: auth_socket

SELECT * FROM mysql.user WHERE user='root' AND host='127.0.0.1' \G

Output

plugin: caching_sha2_password / mysql_native_password

Fix

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

auth_socket login 機制

The plugin checks whether the socket user name(--user=USERNAME)
 matches the MySQL user name specified by the client program

If the names do not match:

The plugin checks whether the socket user name matches the name specified in the authentication_string column of the mysql.user system table row.

Login with different user in auth_socket

e.g.

# 建立 userA, 並設定使用 auth_socket 登入

CREATE USER 'userA'@'localhost' IDENTIFIED WITH auth_socket;

# 設定 "OS User" userB 可以使用 "MySQL User" userA 身份登入 MySQL

ALTER USER 'userA'@'localhost' IDENTIFIED WITH auth_socket AS 'userB';

Notes

1) socket 的 location

show variables like '%socket%';

/var/run/mysqld/mysqlx.sock
/var/run/mysqld/mysqld.sock