Fix MySQL "Too Many Connections"

Understanding the Error

MySQL has a default limit on concurrent connections (usually 151). When exceeded:

ERROR 1040 (08004): Too many connections

Emergency Fix - Increase Limit

Login as root:

sudo mysql -u root -p

Temporarily increase limit:

SET GLOBAL max_connections = 500;

Make permanent:

sudo nano /etc/mysql/my.cnf

# under [mysqld]
max_connections = 500

Restart MySQL:

sudo systemctl restart mysql

Check Current Connections

mysql -e "SHOW PROCESSLIST"
mysql -e "SHOW STATUS LIKE 'Threads_connected'"
mysql -e "SHOW VARIABLES LIKE 'max_connections'"

Kill Idle Connections

mysql -e "SELECT CONCAT('KILL ',id,';') FROM information_schema.processlist WHERE Command='Sleep' AND Time > 100;" | mysql

Reduce idle timeout:

SET GLOBAL wait_timeout = 300;
SET GLOBAL interactive_timeout = 300;

Find Connection Hogs

mysql -e "SELECT user, COUNT(*) FROM information_schema.processlist GROUP BY user"
mysql -e "SELECT host, COUNT(*) FROM information_schema.processlist GROUP BY host"

Application Fixes

• Use connection pooling
• Close DB connections properly
• Reduce timeouts in app
• Use read replicas

Monitor Usage

watch -n 2 "mysql -e 'SHOW PROCESSLIST' | wc -l"
mysql -e "SHOW STATUS WHERE variable_name IN ('Max_used_connections','Threads_connected')"