How to fix too many connections MySQL: Security Hardening Guide

Understanding the Threat Model

Connection exhaustion can result from legitimate load spikes, application bugs, or deliberate attacks. An attacker who can establish multiple connections without authentication delays or rate limits can starve legitimate users of database access. Even authenticated users with weak credentials may run automated queries that hold connections open indefinitely.

Three primary threat vectors drive connection exhaustion:

• Connection leak attacks where malicious scripts open connections without closing them, gradually consuming all available slots
• Slowloris-style attacks that open connections and send queries slowly to keep connections alive while blocking new ones
• Compromised application credentials used to spawn excessive parallel connections from distributed sources

Audit Checklist: Diagnosing Connection Exhaustion

Before adjusting limits, audit current connection usage to identify whether the issue stems from legitimate load, configuration problems, or security threats. Run these checks while the error is occurring or immediately after to capture accurate state.

• Check current connection count: Run SHOW STATUS LIKE 'Threads_connected'; to see active connections and compare against SHOW VARIABLES LIKE 'max_connections'; to determine headroom
• List all active connections: Execute SHOW PROCESSLIST; to view user, host, database, command, time, and state for each connection. Look for idle connections with high time values or repeated connection attempts from single IPs
• Identify connection sources: Run SELECT user, host, COUNT(*) as connection_count FROM information_schema.processlist GROUP BY user, host ORDER BY connection_count DESC; to find which users and hosts consume the most connections
• Review error log: Check MySQL error log (typically /var/log/mysql/error.log) for 'Too many connections' entries and timestamps correlating with traffic spikes or deployment events
• Examine connection history: Run SHOW STATUS LIKE 'Max_used_connections'; to see the peak connection count since server start. If this approaches max_connections regularly, the limit is insufficient for normal load
• Verify application connection handling: Review application logs for database connection errors, failed reconnection attempts, or missing connection.close() calls in error handling paths

Tuning max_connections Safely

Increasing max_connections provides more connection slots but consumes additional RAM. Each connection requires memory for buffers, caches, and thread overhead. Setting the limit too high can trigger out-of-memory conditions that crash MySQL or the entire server.

Calculate safe max_connections using this formula: available_ram_for_mysql / per_connection_memory. Typical per-connection memory ranges from 200KB to 400KB depending on buffer sizes configured in my.cnf. For a server with 4GB RAM allocated to MySQL and 300KB per connection, the safe maximum is approximately 13,000 connections. In practice, most workloads require 100-500 connections.

To increase max_connections, edit the MySQL configuration file (usually /etc/mysql/my.cnf or /etc/my.cnf) and add or update this line under the [mysqld] section:

• max_connections = 250

Implementing Connection Pooling and Application-Level Controls

Connection pooling reuses existing connections instead of opening new ones for each query. This reduces overhead, prevents connection leaks, and limits the total connections your application holds open. Most application frameworks and database libraries support pooling.

For PHP applications, use persistent connections with mysqli or PDO by passing the MYSQL_ATTR_PERSISTENT flag. For Python, libraries like SQLAlchemy and psycopg2 include built-in pooling. Node.js applications should use connection pool libraries like mysql2/promise with pool configuration:

Set pool size limits to prevent a single application instance from exhausting connections. A typical web application requires 5-20 connections per instance depending on concurrency. Configure pool maximum size to leave headroom for other applications and administrative connections.

Enforce connection timeouts at the application level to release connections held by slow queries or network delays. Most database libraries support query timeout and connection lifetime settings. Set query timeouts to 30-60 seconds for web applications to prevent long-running queries from blocking connections.

Review application error handling to ensure connections are closed in finally blocks or using context managers. A common leak pattern occurs when an exception interrupts execution before connection.close() runs. Use try-finally or with statements to guarantee cleanup.

MySQL Security Hardening for Connection Management

Implement per-user connection limits to prevent a single compromised account from exhausting the connection pool. MySQL supports MAX_USER_CONNECTIONS as a user-level resource limit. To set a limit of 20 connections for a specific user, run:

• CREATE USER 'appuser'@'192.168.1.10' IDENTIFIED BY 'secure_password';
• GRANT SELECT, INSERT, UPDATE, DELETE ON app_database.* TO 'appuser'@'192.168.1.10';

Verifying Your Hardened Configuration

After implementing hardening measures, verify the configuration protects against connection exhaustion while supporting legitimate load. Run these validation tests:

Test connection limit enforcement by attempting to open more connections than max_connections allows from a test client. You should receive 'Too many connections' error (MySQL error 1040) when the limit is reached. Verify existing connections continue functioning.

Verify per-user limits by connecting as a restricted user and opening connections until MAX_USER_CONNECTIONS is reached. Confirm other users can still connect while the limited user is blocked.

Test timeout behavior by opening a connection, running a query, then leaving the connection idle. After wait_timeout seconds, the next query attempt should fail with 'MySQL server has gone away' error, confirming the connection was closed.

Validate host restrictions by attempting connections from unauthorized IPs or hostnames. These should fail with 'Host is not allowed to connect' error (MySQL error 1130). Test from allowed hosts to confirm legitimate access works.

Monitor connection metrics under simulated load using a load testing tool to generate concurrent requests. Track Threads_connected, Aborted_connects, and Connection_errors_max_connections in SHOW GLOBAL STATUS. If aborted connections or max_connections errors increase significantly, adjust limits or investigate application connection handling.