php mysqli::mysqli(): (HY000/2002): 无法通过 socket 'MySQL' 连接到本地 MySQL 服务器 (2)

mysqli::mysqli(): (HY000/2002): Can't connect to local MySQL server through socket 'MySQL' (2)

提问by Dharman

I get this error when I try to connect to the mysql database using php mysqli class. Using following code:

当我尝试使用 php mysqli 类连接到 mysql 数据库时出现此错误。使用以下代码：

$db = new MySQLi("localhost","kamil","*****");
if (mysqli_connect_errno())
{
    echo "An error occured. Please try again later.";
    exit();
}

password is * for security.

密码为 * 以确保安全。

I have created user kamilwith all privileges on external ip address and localhost. When I run: select user,host from mysql.userit properly displays those two users.

我已经创建kamil了对外部 IP 地址和本地主机拥有所有权限的用户。当我运行时：select user,host from mysql.user它正确显示这两个用户。

I did some research and used this benchmark: https://stackoverflow.com/a/2183134/1839439to see what it connects to. As it turns out it is only able to connect to 127.0.0.1and 127.0.0.1:3306which is localhost, however when I supply localhostit throws out this error.

我做了一些研究并使用了这个基准：https: //stackoverflow.com/a/2183134/1839439看看它连接到什么。事实证明它是唯一能够连接到127.0.0.1和127.0.0.1:3306其为localhost，但是当我提供localhost它抛出了这个错误。

My question is why does it only allow me to connect to DB using localhost ip address and not the name or external ip. Do I need a different host if I want to be able to use mysql on website or if I can use 127.0.0.1?

我的问题是为什么它只允许我使用 localhost ip 地址而不是名称或外部 ip 连接到数据库。如果我想在网站上使用 mysql 或者我可以使用，我是否需要不同的主机127.0.0.1？

EDIT:
hostsfile

编辑：
hosts文件

127.0.0.1       localhost
::1             localhost ip6-localhost ip6-loopback
fe00::0         ip6-localnet
ff00::0         ip6-mcastprefix
ff02::1         ip6-allnodes
ff02::2         ip6-allrouters

127.0.1.1       raspberrypi

---

---

该用户的Mysql用户表结果：

| kamil            | 109.255.177.28 |
| kamil            | localhost      |

回答by Joni

When you use just "localhost" the MySQL client library tries to use a Unix domain socket for the connection instead of a TCP/IP connection. The error is telling you that the socket, called MySQL, cannot be used to make the connection, probably because it does not exist (error number 2).

当您只使用“localhost”时，MySQL 客户端库会尝试使用 Unix 域套接字而不是 TCP/IP 连接进行连接。该错误告诉您MySQL无法使用名为 的套接字建立连接，可能是因为它不存在（错误编号 2）。

From the MySQL Documentation:

从MySQL 文档：

On Unix, MySQL programs treat the host name localhost specially, in a way that is likely different from what you expect compared to other network-based programs. For connections to localhost, MySQL programs attempt to connect to the local server by using a Unix socket file. This occurs even if a --port or -P option is given to specify a port number. To ensure that the client makes a TCP/IP connection to the local server, use --host or -h to specify a host name value of 127.0.0.1, or the IP address or name of the local server. You can also specify the connection protocol explicitly, even for localhost, by using the --protocol=TCP option.

在 Unix 上，MySQL 程序特别对待主机名 localhost，与其他基于网络的程序相比，它的方式可能与您期望的不同。对于到 localhost 的连接，MySQL 程序尝试使用 Unix 套接字文件连接到本地服务器。即使提供了 --port 或 -P 选项来指定端口号，也会发生这种情况。要确保客户端与本地服务器建立 TCP/IP 连接，请使用 --host 或 -h 指定主机名值 127.0.0.1，或本地服务器的 IP 地址或名称。您还可以使用 --protocol=TCP 选项明确指定连接协议，即使是本地主机。

There are a few ways to solve this problem.

有几种方法可以解决这个问题。

1. You can just use TCP/IP instead of the Unix socket. You would do this by using 127.0.0.1instead of localhostwhen you connect. The Unix socket might by faster and safer to use, though.
2. You can change the socket in php.ini: open the MySQL configuration file my.cnfto find where MySQL creates the socket, and set PHP's mysqli.default_socketto that path. On my system it's /var/run/mysqld/mysqld.sock.

3. Configure the socket directly in the PHP script when opening the connection. For example:

   $db = new MySQLi('localhost', 'kamil', '***', '', 0, 
                                 '/var/run/mysqld/mysqld.sock')
   

1. 您可以只使用 TCP/IP 而不是 Unix 套接字。您可以通过使用127.0.0.1而不是localhost在连接时执行此操作。不过，Unix 套接字可能会更快更安全地使用。
2. 您可以在以下位置更改套接字php.ini：打开 MySQL 配置文件my.cnf以查找 MySQL 创建套接字的位置，并将 PHP 设置mysqli.default_socket为该路径。在我的系统上是/var/run/mysqld/mysqld.sock.

3. 打开连接时直接在PHP脚本中配置socket。例如：

   $db = new MySQLi('localhost', 'kamil', '***', '', 0, 
                                 '/var/run/mysqld/mysqld.sock')
   

回答by Vijay Kumar Kanta

If it's a PHP issue, you could simply alter the configuration file php.iniwherever it's located and update the settings for PORT/SOCKET-PATH etc to make it connect to the server.

如果是 PHP 问题，您可以简单地更改其所在位置的配置文件php.ini并更新 PORT/SOCKET-PATH 等的设置以使其连接到服务器。

In my case, I opened the file php.ini and did

就我而言，我打开了文件 php.ini 并做了

mysql.default_socket = /var/run/mysqld/mysqld.sock
mysqli.default_socket = /var/run/mysqld/mysqld.sock

And it worked straight away. I have to admit, I took hint from the accepted answer by @Joni

它立即起作用。我不得不承认，我从@Joni 接受的答案中得到了暗示

回答by castis

If 'localhost' doesn't work but 127.0.0.1 does. Make sure your local hosts file points to the correct location. (/etc/hosts for linux/mac, C:\Windows\System32\drivers\etc\hosts for windows).

如果 'localhost' 不起作用但 127.0.0.1 起作用。确保您的本地主机文件指向正确的位置。（Linux/mac 为 /etc/hosts，Windows 为 C:\Windows\System32\drivers\etc\hosts）。

Also, make sure your user is allowed to connect to whatever database you're trying to select.

此外，请确保您的用户可以连接到您尝试选择的任何数据库。

回答by Badal Singh

Please check the following file

请检查以下文件

%SystemRoot%\system32\drivers\etc\host

The line which bind the host name with ip is probably missing a line which bind them togather

将主机名与 ip 绑定的行可能缺少将它们绑定在一起的行

127.0.0.1  localhost

If the given line is missing. Add the line in the file

如果给定的行丢失。在文件中添加一行

---

---

Could you also check your MySQL database's user table and tell us the host column value for the user which you are using. You should have user privilege for both the host "127.0.0.1" and "localhost" and use % as it is a wild char for generic host name.

您还可以检查您的 MySQL 数据库的用户表并告诉我们您正在使用的用户的主机列值。您应该拥有主机“127.0.0.1”和“localhost”的用户权限，并使用 % ，因为它是通用主机名的通配符。