Python 无法通过 socket '/tmp/mysql.sock 连接到本地 MySQL 服务器

Can't connect to local MySQL server through socket '/tmp/mysql.sock

提问by Alex Gaynor

When I attempted to connect to a local MySQL server during my test suite, it fails with the error:

当我在测试套件期间尝试连接到本地 MySQL 服务器时，它失败并显示错误：

OperationalError: (2002, "Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)")

However, I'm able to at all times, connect to MySQL by running the command line mysqlprogram. A ps aux | grep mysqlshows the server is running, and stat /tmp/mysql.sockconfirm that the socket exists. Further, if I open a debugger in exceptclause of that exception, I'm able to reliably connect with the exact same parameters.

但是，我始终可以通过运行命令行mysql程序连接到 MySQL 。Aps aux | grep mysql显示服务器正在运行，并 stat /tmp/mysql.sock确认套接字存在。此外，如果我在except该异常的子句中打开调试器，我能够可靠地连接完全相同的参数。

This issue reproduces fairly reliably, however it doesn't appear to be 100%, because every once in a blue moon, my test suite does in fact run without hitting this error. When I attempted to run with sudo dtrussit did not reproduce.

这个问题相当可靠地重现，但它似乎不是 100%，因为每一次在蓝月亮中，我的测试套件实际上运行时不会遇到此错误。当我尝试使用sudo dtruss它运行时没有重现。

All the client code is in Python, though I can't figure how that'd be relevant.

所有客户端代码都在 Python 中，但我不知道这有什么关系。

Switching to use host 127.0.0.1produces the error:

切换到使用主机127.0.0.1会产生错误：

DatabaseError: Can't connect to MySQL server on '127.0.0.1' (61)

回答by Martin

I think i saw this same behavior some time ago, but can't remember the details.
In our case, the problem was the moment the testrunner initialises database connections relative to first database interaction required, for instance, by import of a module in settings.py or some __init__.py. I'll try to digg up some more info, but this might already ring a bell for your case.

我想我前段时间看到过同样的行为，但不记得细节了。
在我们的例子中，问题是 testrunner 初始化数据库连接的那一刻，相对于所需的第一次数据库交互，例如，通过在 settings.py 或一些 __init__.py 中导入模块。我会尝试挖掘更多信息，但这可能已经为您的案例敲响了警钟。

回答by hd1

Make sure your /etc/hosts has 127.0.0.1 localhostin it and it should work fine

确保你的 /etc/hosts 已经127.0.0.1 localhost在里面，它应该可以正常工作

回答by hsen

Check number of open files for the mysql process using lsof command.

使用 lsof 命令检查 mysql 进程的打开文件数。

Increase the open files limit and run again.

增加打开文件限制并再次运行。

回答by jtoberon

The relevant section of the MySQL manual is here. I'd start by going through the debugging steps listed there.

MySQL 手册的相关部分在这里。我首先要完成那里列出的调试步骤。

Also, remember that localhost and 127.0.0.1 are not the same thing in this context:

另外，请记住 localhost 和 127.0.0.1 在这种情况下不是一回事：

• If host is set to localhost, then a socket or pipe is used.
• If host is set to 127.0.0.1, then the client is forced to use TCP/IP.

• 如果 host 设置为localhost，则使用套接字或管道。
• 如果主机设置为127.0.0.1，则客户端将被强制使用 TCP/IP。

So, for example, you can check if your database is listening for TCP connections vi netstat -nlp. It seems likely that it IS listening for TCP connections because you say that mysql -h 127.0.0.1works just fine. To check if you can connect to your database via sockets, use mysql -h localhost.

因此，例如，您可以检查您的数据库是否正在侦听 TCP 连接 vi netstat -nlp。它似乎很可能正在侦听 TCP 连接，因为您说它mysql -h 127.0.0.1工作得很好。要检查是否可以通过套接字连接到数据库，请使用mysql -h localhost.

If none of this helps, then you probably need to post more details about your MySQL config, exactly how you're instantiating the connection, etc.

如果这些都没有帮助，那么您可能需要发布有关 MySQL 配置的更多详细信息，具体是如何实例化连接等。

回答by beiller

Check that your mysql has not reached maximum connections, or is not in some sort of booting loop as happens quite often if the settings are incorrect in my.cnf.

检查您的 mysql 是否未达到最大连接数，或者是否处于某种启动循环中，如果 my.cnf 中的设置不正确，则经常发生这种情况。

Use ps aux | grep mysql to check if the PID is changing.

使用 ps 辅助 | grep mysql 检查 PID 是否正在更改。

回答by Chamith Malinda

This may be one of following problems.

这可能是以下问题之一。

1. Incorrect mysql lock. solution: You have to find out the correct mysql socket by,

1. 错误的 mysql 锁。解决方案：您必须通过以下方式找出正确的mysql套接字，

mysqladmin -p variables | grep socket

mysqladmin -p 变量 | grep 套接字

and then put it in your db connection code:

然后把它放在你的数据库连接代码中：

pymysql.connect(db='db', user='user', passwd='pwd', unix_socket="/tmp/mysql.sock")

/tmp/mysql.sock is the returned from grep

/tmp/mysql.sock 是从 grep 返回的

2.Incorrect mysql port solution: You have to find out the correct mysql port:

2.不正确的mysql端口解决方法：你必须找出正确的mysql端口：

mysqladmin -p variables | grep port

and then in your code:

然后在你的代码中：

pymysql.connect(db='db', user='user', passwd='pwd', host='localhost', port=3306)

3306 is the port returned from the grep

3306是grep返回的端口

I think first option will resolve your problem.

我认为第一个选项将解决您的问题。

回答by Koreth

The socket is located in /tmp. On Unix system, due to modes & ownerships on /tmp, this could cause some problem. But, as long as you tell us that you CAN use your mysql connexion normally, I guess it is not a problem on your system. A primal check should be to relocate mysql.sock in a more neutral directory.

套接字位于 /tmp 中。在 Unix 系统上，由于 /tmp 上的模式和所有权，这可能会导致一些问题。但是，只要你告诉我们你可以正常使用你的mysql连接，我想这不是你系统上的问题。最初的检查应该是将 mysql.sock 重新定位到更中性的目录中。

The fact that the problem occurs "randomly" (or not every time) let me think that it could be a server problem.

问题“随机”发生（或不是每次）这一事实让我认为这可能是服务器问题。

• Is your /tmp located on a standard disk, or on an exotic mount (like in the RAM) ?

• Is your /tmp empty ?

• Does iotopshow you something wrong when you encounter the problem ?

• 您的 /tmp 是位于标准磁盘上，还是位于外来安装（例如在 RAM 中）上？

• 你的 /tmp 是空的吗？

• iotop当您遇到问题时，是否显示出了问题？

回答by Francis Yaconiello

I've seen this happen at my shop when my devs have a stack manager like MAMP installed that comes preconfigured with MySQL installed in a non standard place.

当我的开发人员安装了像 MAMP 这样的堆栈管理器时，我在我的商店中看到了这种情况，该管理器预配置了安装在非标准位置的 MySQL。

at your terminal run

在你的终端运行

mysql_config --socket

that will give you your path to the sock file. take that path and use it in your DATABASES HOST paramater.

这将为您提供 sock 文件的路径。走这条路并在您的 DATABASES HOST 参数中使用它。

What you need to do is point your

你需要做的是指出你的

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'test',
        'USER': 'test',
        'PASSWORD': 'test',
        'HOST': '/Applications/MAMP/tmp/mysql/mysql.sock',
        'PORT': '',
    },
}

NOTE

笔记

also run which mysql_configif you somehow have multiple instances of mysql server installed on the machine you may be connecting to the wrong one.

which mysql_config如果您以某种方式在机器上安装了多个 mysql 服务器实例，也可以运行，您可能连接到错误的一个。

回答by RolandoMySQLDBA

I have two sneaky conjectures on this one

我对这个有两个偷偷摸摸的猜想

CONJECTURE #1

猜想#1

Look into the possibility of not being able to access the /tmp/mysql.sockfile. When I setup MySQL databases, I normally let the socket file site in /var/lib/mysql. If you login to mysql as root@localhost, your OS session needs access to the /tmpfolder. Make sure /tmphas the correct access rights in the OS. Also, make sure the sudo user can always read file in /tmp.

查看无法访问该/tmp/mysql.sock文件的可能性。当我设置 MySQL 数据库时，我通常让套接字文件位于/var/lib/mysql. 如果您以 身份登录 mysql root@localhost，您的操作系统会话需要访问该/tmp文件夹。确保/tmp在操作系统中具有正确的访问权限。此外，请确保 sudo 用户始终可以读取/tmp.

CONJECTURE #2

猜想#2

Accessing mysql via 127.0.0.1can cause some confusion if you are not paying attention. How?

127.0.0.1如果您不注意，通过访问 mysql可能会引起一些混乱。如何？

From the command line, if you connect to MySQL with 127.0.0.1, you may need to specify the TCP/IP protocol.

从命令行，如果您使用 连接到 MySQL 127.0.0.1，您可能需要指定 TCP/IP 协议。

mysql -uroot -p -h127.0.0.1 --protocol=tcp

or try the DNS name

或尝试 DNS 名称

mysql -uroot -p -hDNSNAME

This will bypass logging in as root@localhost, but make sure you have root@'127.0.0.1'defined.

这将绕过以身份登录root@localhost，但请确保您已root@'127.0.0.1'定义。

Next time you connect to MySQL, run this:

下次连接到 MySQL 时，运行以下命令：

SELECT USER(),CURRENT_USER();

What does this give you?

这给你什么？

• USER()reports how you attempted to authenticate in MySQL
• CURRENT_USER()reports how you were allowed to authenticate in MySQL

• USER()报告您尝试在 MySQL 中进行身份验证的方式
• CURRENT_USER()报告您如何被允许在 MySQL 中进行身份验证

If these functions return with the same values, then you are connecting and authenticating as expected. If the values are different, you may need to create the corresponding user [email protected].

如果这些函数返回相同的值，那么您正在按预期进行连接和身份验证。如果值不同，您可能需要创建相应的 user [email protected]。

回答by Only You

Configure your DB connection in the 'Manage DB Connections dialog. Select 'Standard (TCP/IP)' as connection method.

在“管理数据库连接”对话框中配置数据库连接。选择“标准（TCP/IP）”作为连接方式。

See this page for more details http://dev.mysql.com/doc/workbench/en/wb-manage-db-connections.html

有关更多详细信息，请参阅此页面 http://dev.mysql.com/doc/workbench/en/wb-manage-db-connections.html

According to this other pagea socket file is used even if you specify localhost.

根据this other page，即使您指定本地主机，也会使用套接字文件。

A Unix socket file is used if you do not specify a host name or if you specify the special host name localhost.

如果未指定主机名或指定特殊主机名 localhost，则使用 Unix 套接字文件。

It also shows how to check on your server by running these commands:

它还显示了如何通过运行以下命令来检查您的服务器：

If a mysqld process is running, you can check it by trying the following commands. The port number or Unix socket file name might be different in your setup. host_ip represents the IP address of the machine where the server is running.

如果 mysqld 进程正在运行，您可以通过尝试以下命令来检查它。您的设置中的端口号或 Unix 套接字文件名可能不同。host_ip 表示运行服务器的机器的 IP 地址。

shell> mysqladmin version 
shell> mysqladmin variables 
shell> mysqladmin -h `hostname` version variables 
shell> mysqladmin -h `hostname` --port=3306 version 
shell> mysqladmin -h host_ip version 
shell> mysqladmin --protocol=SOCKET --socket=/tmp/mysql.sock version