多进程NoSQL转MySQL迁移脚本遇pymysql连接报错(2003/Errno99)求助

Hey there, let’s dig into this frustrating connection error you’re hitting—this isn’t a MySQL ban, it’s a local TCP port exhaustion issue that’s super common with multi-process data migration scripts. Let’s break down why it’s happening and how to fix it:

Why You’re Seeing This Error #

The [Errno 99] Cannot assign requested address message means your local machine has run out of available temporary TCP ports to use for new connections to MySQL. Here’s the breakdown:

• Every time a process creates a new MySQL connection, it uses a unique local port.
• When you close the connection, that port doesn’t get released immediately—it enters a TIME_WAIT state (usually 60 seconds by default) to ensure any leftover TCP packets are handled.
• With multiple processes spawning and closing connections rapidly, you’re flooding the system with TIME_WAIT ports, and eventually, there are none left to assign for new connections.

Fixes to Try (Ordered by Best Practice) #

1. Use a Connection Pool Instead of Per-Task Connections #

Stop creating and destroying connections for every single operation—this is the root of the port exhaustion. Instead, use a connection pool to reuse connections across your processes. For pymysql, you can use libraries like DBUtils or SQLAlchemy to manage pooling:

• Example with DBUtils PooledDB (per-process connection pooling):

  from dbutils.pooled_db import PooledDB
  import pymysql
  
  # Initialize pool once (outside your process worker function)
  pool = PooledDB(
      creator=pymysql,
      maxconnections=5,  # Max connections per process
      host='foo.com',
      user='your_user',
      password='your_pass',
      database='your_db'
  )
  
  # In your process worker:
  def process_data(data):
      conn = pool.connection()
      try:
          with conn.cursor() as cur:
              cur.execute("INSERT INTO ...", data)
              conn.commit()
      finally:
          conn.close()  # Returns connection to pool, doesn't actually close it
  

  This way, each process reuses a small set of persistent connections instead of spawning new ones every time.

2. Tune TCP Kernel Parameters (Temporary Fix) #

If you need a quick workaround while setting up a connection pool, adjust your system’s TCP settings to reduce TIME_WAIT port buildup:

• On Linux, run these commands as root (they’ll take effect immediately, but reset on reboot):

  # Allow reusing TIME_WAIT ports for new connections (safe for internal services)
  sysctl -w net.ipv4.tcp_tw_reuse=1
  # Reduce TIME_WAIT timeout from 60s to 30s
  sysctl -w net.ipv4.tcp_fin_timeout=30
  # Optional: Faster recycling of TIME_WAIT ports (avoid on NAT networks)
  sysctl -w net.ipv4.tcp_tw_recycle=1
  

  To make these permanent, add them to /etc/sysctl.conf and run sysctl -p.

3. Limit Concurrent Process Count #

If you’re running too many processes at once, even a connection pool might not help. Reduce the number of worker processes to match your system’s available temporary ports (Linux defaults to ~28k ports in the 32768-60999 range, but subtract ports used by other services). Start with 30-50 processes and adjust based on performance.

4. Double-Check Connection Cleanup #

Even if you think you’re closing connections, make sure there’s no leakage from unhandled exceptions. Use try/finally blocks or context managers to guarantee connections are closed:

# Context manager approach (automatically closes connections)
with pymysql.connect(host='foo.com', ...) as conn:
    with conn.cursor() as cur:
        cur.execute("INSERT INTO ...")
        conn.commit()

This ensures connections get closed even if an error occurs mid-operation.

5. Use Unix Sockets (If MySQL Is Local) #

If your MySQL server is running on the same machine as the script, switch to using a Unix socket instead of TCP. This bypasses TCP ports entirely, eliminating the exhaustion issue. Update your connection string to use the socket path (usually /var/run/mysqld/mysqld.sock):

conn = pymysql.connect(
    unix_socket='/var/run/mysqld/mysqld.sock',
    user='your_user',
    password='your_pass',
    database='your_db'
)

Final Notes #

Start with implementing a connection pool—it’s the most sustainable fix that aligns with best practices for database interactions. The other fixes are either temporary workarounds or optimizations for specific environments.

内容的提问来源于stack exchange，提问作者criogenist