How to Fix MySQL 'Too Many Connections' Error on Linux

#mysql #devops #linux #backend

How to Fix MySQL 'Too Many Connections' Error on Linux

ERROR 1040 (HY000): Too many connections

Your app stops working. New connections get rejected. This is one of the most common MySQL production errors.

Here's exactly what causes it and how to fix it — permanently.

Why It Happens

MySQL has a max_connections limit (default: 151). When every connection slot is full, new connections are refused with error 1040.

This happens when:

Your app opens connections but doesn't close them properly (connection leak)
A traffic spike creates more simultaneous connections than your limit allows
Your app framework creates a new connection per request instead of using a pool

Step 1: Check Current Connection Count

# Connect to MySQL
mysql -u root -p

# Check current connections
SHOW STATUS WHERE Variable_name = 'Threads_connected';
SHOW STATUS WHERE Variable_name = 'Max_used_connections';
SHOW VARIABLES LIKE 'max_connections';

If Threads_connected is close to max_connections, you're at the limit.

-- See what's connected right now
SHOW PROCESSLIST;

-- Kill a specific connection (use the Id from PROCESSLIST)
KILL CONNECTION 1234;

Step 2: Increase max_connections (Temporary Fix)

-- Increase without restarting MySQL
SET GLOBAL max_connections = 300;

-- Verify
SHOW VARIABLES LIKE 'max_connections';

To make it permanent, edit /etc/mysql/mysql.conf.d/mysqld.cnf:

[mysqld]
max_connections = 300

sudo systemctl restart mysql

Step 3: Fix the Real Problem — Connection Pooling

Increasing max_connections is a bandaid. The real fix is connection pooling.

Node.js (mysql2):

// Bad: creating a new connection per request
const connection = mysql.createConnection({...});

// Good: use a pool
const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  database: 'mydb',
  waitForConnections: true,
  connectionLimit: 10,     // Max simultaneous connections
  queueLimit: 0
});

// Use promise wrapper
const promisePool = pool.promise();

Python (SQLAlchemy):

from sqlalchemy import create_engine

engine = create_engine(
    'mysql+pymysql://user:pass@localhost/db',
    pool_size=10,
    max_overflow=20,
    pool_pre_ping=True,  # Detect stale connections
    pool_recycle=3600,   # Recycle connections after 1 hour
)

Step 4: Find and Fix Connection Leaks

-- Watch connections in real time (run every 5 seconds)
SHOW STATUS WHERE Variable_name IN ('Threads_connected','Threads_running');

-- Find long-running queries
SELECT * FROM information_schema.processlist
WHERE command != 'Sleep' AND time > 30
ORDER BY time DESC;

Common leak patterns:

// Leak: connection opened, never released
async function getData() {
  const conn = await pool.getConnection();
  const [rows] = await conn.query('SELECT * FROM users');
  return rows;
  // conn.release() never called!
}

// Fixed:
async function getData() {
  const conn = await pool.getConnection();
  try {
    const [rows] = await conn.query('SELECT * FROM users');
    return rows;
  } finally {
    conn.release(); // Always released
  }
}

Step 5: Monitor Connections Proactively

# Add to cron — alert when connections > 80% of max
cat << 'EOF' >> /usr/local/bin/mysql-check.sh
#!/bin/bash
MAX=$(mysql -u root -p"$MYSQL_ROOT_PASSWORD" -sN -e "SHOW VARIABLES LIKE 'max_connections'" | awk '{print $2}')
CURRENT=$(mysql -u root -p"$MYSQL_ROOT_PASSWORD" -sN -e "SHOW STATUS LIKE 'Threads_connected'" | awk '{print $2}')
PERCENT=$((CURRENT * 100 / MAX))
if [ $PERCENT -gt 80 ]; then
  echo "MySQL connections at ${PERCENT}% (${CURRENT}/${MAX})" | mail -s "MySQL Connection Alert" you@email.com
fi
EOF
chmod +x /usr/local/bin/mysql-check.sh