最後更新: 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