Posted inMySQL

Fixing MySQL Connection Errors: From 1045 to ‘Too Many Connections’ – Real-World Stories and Solutions

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

When MySQL Goes on Strike: The Sleepless Nights of a SysAdmin

I still remember an on-call shift at 2 AM. The entire client website suddenly went down with the message: “Error establishing a database connection.” My heart was pounding faster than my fingers on the keyboard. After investigating, I found a Too many connections error caused by a connection leak in the code. Experiences like these taught me one thing: don’t panic — start by checking the right places.

Don’t rush to restart the server right away, as it can easily corrupt data. Instead, stay calm and diagnose through specific symptoms to find the most accurate solution.

Two Approaches When MySQL Has Issues

When facing connection errors, IT professionals typically choose one of two approaches:

  • Approach 1: Firefighting: Restart MySQL, delete lock files, or blindly increase configuration parameters. This brings the system back immediately but doesn’t address the root cause. You may even lose data that was still in the buffer and hadn’t been flushed to disk.
  • Approach 2: Root Cause Analysis: Dig into error logs, check the network, and verify user permissions. This takes a bit more time upfront but keeps the system stable in the long run.

I always prioritize Approach 2. Only when the system is completely down and needs a few minutes to breathe will I use Approach 1 to temporarily keep the service alive.

—

1. Error 1045 (28000): Wrong Credentials or Access Denied

This is the most common error. It occurs when the password is incorrect or the user doesn’t have permission to connect from the current host. Sometimes you enter the correct password but still get blocked because MySQL strictly distinguishes between 'root'@'localhost' and 'root'@'127.0.0.1'.

How to Fix It:

If you’ve forgotten the root password, start MySQL in skip-grant-tables mode to reset it:

# Stop the service
sudo systemctl stop mysql

# Start temporarily without a password
sudo mysqld_safe --skip-grant-tables &

# Log in and set a new password
mysql -u root
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword@2024';

Note: Check the user table in the mysql database. Make sure the host column allows your user to connect from the corresponding IP, rather than being restricted to localhost only.

—

2. Error 2002 (HY000): Socket Error or MySQL Not Running

This error usually occurs when the client can’t find the .sock file. It may be caused by MySQL crashing unexpectedly or a full disk preventing the service from starting.

How to Fix It:

First, check the service status:

sudo systemctl status mysql

If the service is running but the error persists, find the socket file location in your my.cnf configuration:

grep -r "socket" /etc/mysql/

A hard-earned lesson: always run df -h and df -i. Once I spent an entire hour troubleshooting before realizing the server had run out of Inodes, which prevented MySQL from creating a socket file even though disk space was still available.

—

3. Error 1130 (HY000): Remote Connection Blocked

MySQL only listens on localhost by default for security reasons. If you want to connect from tools like DBeaver or Navicat on your local machine, you’ll need to open up this configuration.

Steps to Fix:

  1. Update the configuration: Open mysqld.cnf, find the bind-address line, and change it from 127.0.0.1 to 0.0.0.0.
  2. Grant User Privileges: Run the SQL below to allow the user to connect from an external IP.
-- Allow 'dev_user' to connect from any IP (not recommended for root)
CREATE USER 'dev_user'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON database_name.* TO 'dev_user'@'%';
FLUSH PRIVILEGES;

Security tip: Never use '%' for the root user. If you’re on AWS or Google Cloud, remember to open port 3306 in your Security Group or Firewall settings as well.

—

4. The “Too Many Connections” Nightmare

This is a nightmare when scaling. MySQL only allows 151 concurrent connections by default. Each connection consumes roughly 2MB – 10MB of RAM. If you blindly raise this limit, the server will soon crash from memory exhaustion.

The Proper Fix:

Don’t rush to increase max_connections. First, check what’s hogging the resources:

SHOW PROCESSLIST;

If you see hundreds of connections in “Sleep” state, your application has a Connection Pooling problem. Reduce the wait timeout to let MySQL automatically close stale connections:

-- Configuration in my.cnf
[mysqld]
max_connections = 500
wait_timeout = 60
interactive_timeout = 60

In a project handling 5,000 requests/s, I used ProxySQL as a middleware layer. It excels at connection reuse, reducing MySQL load by up to 40% without any hardware upgrades.

—

Quick Diagnostic Checklist

Whenever the system reports a connection error, I quickly run through these 5 steps:

  1. Network: Can you ping the server? Is port 3306 blocked by the firewall?
  2. Service: Is systemctl status mysql showing green or red?
  3. Logs: Check the last 100 lines of /var/log/mysql/error.log. 90% of the time, the answer is right there.
  4. Resources: Is RAM spilling into swap? Is there enough free disk space?
  5. Privileges: Does the user have permission to connect from that IP?

Understanding error codes lets you resolve issues in 5 minutes instead of spending the whole night guessing. Here’s to smooth database administration — may you never receive an emergency call at midnight!

Share:
Tags: