Diagnosis and Solutions for socket.error: [Errno 111] Connection refused When Connecting to MySQL with PyMySQL

Problem Background and Error Manifestation

In Python database programming, PyMySQL, as a pure-Python MySQL client library, is widely adopted for its cross-platform compatibility and full support for Python 3. However, developers may encounter specific connection errors when connecting to MySQL databases from local environments. A typical scenario is as follows: developers confirm that the MySQL service is running (connectable via the mysql command-line tool or phpMyAdmin), but using PyMySQL throws the following exceptions:

socket.error: [Errno 111] Connection refused
pymysql.err.OperationalError: (2003, "Can't connect to MySQL server on 'localhost' (111)")

Notably, the same connection parameters succeed when using the MySQLdb library, suggesting that the issue may arise from differences in the underlying connection mechanisms between PyMySQL and MySQLdb.

Root Cause Analysis

Although PyMySQL and MySQLdb offer similar API interfaces, they exhibit key differences in handling local connections. When the host parameter is set to 'localhost', the default behaviors of the two libraries diverge:

• MySQLdb: By default, it prioritizes using Unix domain sockets for local connections, which are generally more efficient than TCP/IP connections.
• PyMySQL: Under certain configurations, it may default to attempting TCP/IP connections, or the Unix socket path it uses may not match the actual path employed by the MySQL server.

This discrepancy can cause PyMySQL to fail to locate the correct communication endpoint even when the MySQL service is operational, triggering the "Connection refused" error. Error code 111 (ECONNREFUSED) explicitly indicates that the connection request was rejected by the target server, often due to incorrect socket paths or port mismatches.

Solution 1: Explicitly Specify the Unix Socket Path

The first solution involves determining the actual Unix socket file path used by the MySQL server and explicitly specifying it in the PyMySQL connection. The specific steps are as follows:

1. Obtain the Current MySQL Socket Path: Execute the system command mysqladmin variables | grep socket to retrieve the MySQL server's configuration information. This command outputs results similar to:

| socket | /tmp/mysql.sock |

Here, /tmp/mysql.sock is the Unix socket file path on which the MySQL server is listening. Different systems or installation methods may result in varying paths, with common alternatives including /var/run/mysqld/mysqld.sock or /var/lib/mysql/mysql.sock.

2. Modify the PyMySQL Connection Code: Add the unix_socket parameter to the connection arguments, pointing to the correct socket file:

import pymysql
conn = pymysql.connect(
    db='base',
    user='root',
    passwd='pwd',
    unix_socket="/tmp/mysql.sock"  # Use the actual path obtained
)

By explicitly specifying the unix_socket parameter, PyMySQL will directly use the Unix domain socket for connection, bypassing the default TCP/IP connection attempt that may cause issues.

Solution 2: Verify and Set the Correct Port Number

If the Unix socket method is not applicable or the developer prefers TCP/IP connections, the second solution is to verify and correctly configure the port number:

1. Confirm the MySQL Listening Port: Execute the command mysqladmin variables | grep port, with example output as follows:

| port | 3306 |

The standard MySQL default port is 3306, but some installations may be configured to use other ports (e.g., 3307, 3308).

2. Specify the Port in the Connection: If the port is not the default 3306, it must be explicitly specified in the PyMySQL connection:

import pymysql
conn = pymysql.connect(
    db='base',
    user='root',
    passwd='pwd',
    host='localhost',
    port=3307  # Use the actual port number obtained
)

Even if the port is the default 3306, explicit specification can prevent issues arising from differences in the library's default behavior.

Best Practices for Connection Parameter Configuration

Based on the above analysis, we summarize the following best practices for PyMySQL connection configuration:

• Understanding Parameter Precedence: When both unix_socket and host parameters are specified, PyMySQL prioritizes Unix socket connections. If only host='localhost' is specified without unix_socket, the library's behavior may vary depending on the version and system configuration.
• Environment-Adaptive Code: In production environments, it is advisable to write more adaptive connection code that can attempt multiple connection methods:

import pymysql
def create_connection():
    # Attempt Unix socket connection
    try:
        return pymysql.connect(
            db='base', user='root', passwd='pwd',
            unix_socket="/tmp/mysql.sock"
        )
    except pymysql.err.OperationalError:
        # Fall back to TCP/IP connection
        try:
            return pymysql.connect(
                db='base', user='root', passwd='pwd',
                host='localhost', port=3306
            )
        except pymysql.err.OperationalError as e:
            # Handle connection failure
            raise

• Externalize Configuration: Store database connection parameters (such as host, port, socket path) in configuration files or environment variables to avoid hardcoding, enhancing code maintainability and cross-environment portability.

In-Depth Technical Details

Understanding the essence of PyMySQL connection errors requires delving into network programming and database client implementation:

• Unix Domain Sockets vs. TCP/IP Sockets: Unix domain sockets (AF_UNIX) are used for inter-process communication on the same host, identified by filesystem paths; TCP/IP sockets (AF_INET) are used for network communication, identified by IP addresses and port numbers. Local MySQL connections typically prioritize Unix domain sockets to avoid network protocol stack overhead.
• PyMySQL Connection Process: When pymysql.connect() is called, the library internally selects the connection method based on parameters: if unix_socket is provided, it creates a Unix domain socket connection; otherwise, it decides on TCP/IP connection based on the host parameter (for 'localhost', it may attempt the loopback address 127.0.0.1).
• Error Handling Mechanism: PyMySQL's OperationalError exception encapsulates underlying socket errors, providing more user-friendly error messages. Error code 2003 corresponds to the MySQL client error "CR_CONN_HOST_ERROR", indicating an error when connecting to the host.

Conclusion and Recommendations

The "Connection refused" error when connecting to MySQL with PyMySQL is typically not due to MySQL service issues but rather mismatches between client configuration and the server's actual setup. Through the methods introduced in this article, developers can:

1. Use the mysqladmin variables command to accurately obtain the MySQL server's socket path and port configuration.
2. Explicitly specify the correct unix_socket or port parameters in PyMySQL connection code.
3. Understand behavioral differences among various MySQL Python client libraries and write more robust connection code.

For persistent connection issues, additional factors such as MySQL server configuration (e.g., bind-address settings), firewall rules, and file permissions (readability of socket files) should be examined. Through systematic troubleshooting, PyMySQL can be ensured to connect reliably to MySQL databases across diverse environments.