问题描述
我已经创建了一个从机器 A 到机器 B 的转发器(通过 SSH).机器 B 有一个 MySQL 实例正在运行，其 root 帐户使用 unix_socket 身份验证:
I have created a forwarder (via SSH) from machine A to machine B. Machine B has a MySQL instance running, with a root account using unix_socket authentication:
+----------+-----------+-------------+
| User | Host | plugin |
+----------+-----------+-------------+
| root | localhost | unix_socket |
+----------+-----------+-------------+
无论我使用什么密码(空白、Linux 根密码等)，我都无法从机器 A 以 root 身份登录:
I am unable to log in as root from machine A, regardless of the password I use (blank, the Linux root password, etc...):
machineA:~$ mysql -h 127.0.0.1 -P 1111 -u root -p
Enter password:
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
machineA:~$ mysql -h 127.0.0.1 -P 1111 -u root
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
如果我尝试使用不是 root 帐户的帐户从机器 B 登录 mysql，也会发生同样的情况.
The same happens if I try to log in into mysql from machine B with an account that is not the root account.
如何登录 MySQL?
How can I log in into MySQL?
推荐答案
要连接到远程 MySQL 服务器，无论是直接连接还是通过 SSH 隧道，都不能使用 unix_socket 身份验证插件这需要本地访问 DB Server 操作系统上的 unix socket 文件.
To connect to a remote MySQL server, be it directly or through a SSH tunnel, you can't use the unix_socket authentication plugin which requires local access to the unix socket file on the DB Server operating system.
unix_socket 插件是使用一种特殊类型的文件(unix socket)实现的，它是 *nix 系统中进程间通信 (IPC) 的一种形式.它允许您的 mysql CLI 客户端与数据库对话，并且需要本地访问套接字文件(即:/tmp/mysql.sock).当您连接到套接字时，unix_socket 插件(服务器端)将获取连接到套接字的用户的 uid(即:youruser)并自动对您进行身份验证，而无需密码.
The unix_socket plugin is implemented using a special type of file (unix socket) which is a form of inter-process communication (IPC) in *nix systems. It allows your mysql CLI client to talk to the DB and it requires local access to the socket file (i.e: /tmp/mysql.sock). When you connect to the socket, the unix_socket plugin (server-side) will get the uid of the user connected to the socket (i.e: youruser) and will automatically authenticate you without the need of a password.
可能的解决方案:
如果您使用非 root 用户连接到机器 B，则需要创建一个与您的非 root 操作系统用户帐户名同名的数据库用户.
If you connect to Machine B with a non-root user, you need to create a DB user with the same name as your non-root operating system user account name.
GRANT ALL PRIVILEGES ON *.* TO 'youruser'@'localhost' IDENTIFIED VIA unix_socket;
现在您可以使用您的用户帐户和 mysql CLI 客户端连接到 mysql，只需运行:
Now you can connect to mysql using your user account and the mysql CLI client by simply running:
youruser:~$ mysql
如果您想以 root 用户连接到数据库，那么您需要访问 root 帐户或与您的用户关联的 sudo 策略才能运行 mysql 客户端.
If you want to connect to the DB with the root user, then you either need access to the root account or a sudo policy associated to your user to run the mysql client.
youruser:~$ sudo mysql
您显然可以启用对数据库根帐户的常规身份验证网络访问.这可能会导致安全问题，因此最好将其限制为 localhost:
You can obviously enable regular authenticated network access to your DB root account. This can lead to security issues so it's better to limit it to localhost:
GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' IDENTIFIED BY 'xxx' WITH GRANT OPTION;
现在，如果您可以验证您的登录信息，您就可以从任何本地操作系统帐户登录:
Now you can login from any local operating system account if you can authenticate your login:
youruser:~$ mysql -u root -p
这篇关于使用 unix_socket 登录 MySQL 帐户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！