Common MySQL Connection Errors and Step‑by‑Step Troubleshooting Guide

1. Phenomena: Typical MySQL Connection Errors

Identify the exact error code returned by the client because each points to a different layer of the stack.

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

ERROR 2003 (HY000): Can't connect to MySQL server on 'x.x.x.x' (111)

or

(110)

ERROR 1045 (28000): Access denied for user 'xxx'@'xxx' (using password: YES)

ERROR 1130 (HY000): Host 'x.x.x.x' is not allowed to connect to this MySQL server

ERROR 1040 (HY000): Too many connections

ERROR 2013 (HY000): Lost connection to MySQL server during query

Application‑level error: java.sql.SQLException: Communications link failure (equivalent to 2003/2013)

These correspond respectively to local socket issues, network reachability, authentication failure, host whitelist restriction, connection‑limit exhaustion, and mid‑query disconnections.

2. Information Collection

Client type (local mysql CLI or remote application)

Connection method (Unix socket vs TCP -h IP)

MySQL version ( SELECT VERSION(); or mysqld --version)

Deployment mode (bare metal, VM, Docker, Kubernetes)

Full error code and message

Whether the environment has never connected successfully or worked before and now fails

These facts decide the starting point of the investigation.

3. Preliminary Judgment: Local vs Network Issue

3.1 Local socket failure (Error 2002)

If the client runs on the same host without -h, it uses the Unix socket. Error 2002 usually means:

MySQL service is not started

Socket file path mismatch (different socket parameter in my.cnf)

Check service status:

systemctl status mysqld   # or systemctl status mysql

If the service is inactive, start it. If it is active but the error persists, verify the socket path:

grep -i "socket" /etc/my.cnf /etc/mysql/mysql.conf.d/mysqld.cnf 2>/dev/null

Confirm the file exists and the MySQL process has permission to create it. If the directory /var/run/mysqld is missing (common on reboot when /var/run is tmpfs), recreate it or add a tmpfiles.d entry:

cat /usr/lib/tmpfiles.d/mysql.conf

3.2 Network unreachable (Error 2003)

When -h IP is specified, the error code includes a number in parentheses: (111) Connection refused – the host is reachable but the port is not listening or is REJECTed by a firewall. (110) Connection timed out – the packet never receives a response; typical of network blockage, DROP firewall rules, or routing problems.

This distinction drives the next steps.

4. Command Checks: Layered Verification

4.1 Verify that port 3306 is listening

ss -ltnp | grep 3306

Typical output when listening on all interfaces:

LISTEN 0 151 0.0.0.0:3306 0.0.0.0:* users:("mysqld",pid=1832,fd=22)

If only 127.0.0.1:3306 appears, MySQL is bound to the loopback address; remote connections will fail (bind‑address issue).

4.2 Test TCP connectivity from the client side

telnet <MySQL_IP> 3306

or, if telnet is unavailable: nc -zv <MySQL_IP> 3306 Successful output confirms the network path; timeout indicates a firewall or routing block. Check host firewall ( firewall-cmd --list-ports or iptables -L -n | grep 3306) and cloud security‑group rules.

4.3 Examine MySQL error logs

tail -100 /var/log/mysqld.log   # CentOS/RHEL
# or
tail -100 /var/log/mysql/error.log   # Debian/Ubuntu

If the log location is unknown, query the configuration:

grep -i "log-error\|log_error" /etc/my.cnf /etc/mysql/mysql.conf.d/mysqld.cnf 2>/dev/null

4.4 Docker / Kubernetes specific checks

Docker: verify container status and port mapping: docker ps --filter "name=mysql" Kubernetes: list pods and services in the target namespace, then check Service port exposure and NetworkPolicy restrictions.

5. Evidence Analysis – Typical Root Causes

5.1 Bind‑address limits remote access

Symptom: local mysql -u root -p works, remote mysql -h <IP> -u root -p returns ERROR 2003 (111).

Evidence: ss -ltnp | grep 3306 # shows 127.0.0.1:3306 Fix: change bind-address in my.cnf to 0.0.0.0 and restart MySQL.

5.2 User whitelist restriction (Error 1130)

Symptom:

ERROR 1130 (HY000): Host '192.168.1.20' is not allowed to connect to this MySQL server

.

Check the mysql.user table:

SELECT user, host FROM mysql.user WHERE user='app_user';

If the client IP is not listed, add the appropriate host pattern or use the % wildcard.

5.3 Password or account lock (Error 1045)

Check the error log for failed attempts and query account status:

SELECT user, host, account_locked, password_expired FROM mysql.user WHERE user='app_user';

If account_locked = 'Y', unlock:

ALTER USER 'app_user'@'10.0.0.%' ACCOUNT UNLOCK;

If the client uses an older driver that cannot handle the default caching_sha2_password plugin, switch to mysql_native_password:

ALTER USER 'app_user'@'10.0.0.%' IDENTIFIED WITH mysql_native_password BY 'original_password';

5.4 Connection‑limit exhaustion (Error 1040)

Show current limits and usage:

SHOW VARIABLES LIKE 'max_connections';
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';

Identify heavy users:

SELECT db, user, host, COUNT(*) AS cnt FROM information_schema.processlist GROUP BY db, user, host ORDER BY cnt DESC LIMIT 10;

If a single application dominates, investigate connection‑pool leaks rather than simply raising max_connections.

5.5 Mid‑query disconnection (Error 2013)

Usually caused by packet size limits or idle timeout.

Check max_allowed_packet when large imports fail: SHOW VARIABLES LIKE 'max_allowed_packet'; Adjust if necessary. For idle‑timeout disconnects, compare client pool idle time with server wait_timeout and interactive_timeout values.

6. Root‑Cause Summary Table

ERROR 2002 + service not running → service failure, check status and logs.

ERROR 2002 + service running → socket path or permission issue.

ERROR 2003 (111) → port not listening or REJECT firewall.

ERROR 2003 (110) → network block / DROP firewall / routing.

ERROR 1130 → host whitelist mismatch.

ERROR 1045 → password error, account lock, or incompatible auth plugin.

ERROR 1040 → connection‑limit exhaustion, investigate connection leaks.

ERROR 2013 → packet size overflow or idle timeout.

7. Fix and Verification Process

After fixing, verify locally via socket to ensure the service itself works.

From the client network, run telnet / nc to confirm port reachability.

Perform a real login with the application account and run a typical query.

For connection‑limit issues, monitor Threads_connected over time.

For account unlock or permission changes, execute the actual business query, not just SELECT 1, to confirm required privileges.

8. High‑Risk Operations Disclaimer

Restarting MySQL

All connections are interrupted; in‑flight transactions roll back.

Before restart, ensure no long‑running DDL ( SHOW PROCESSLIST) and that replication is healthy.

Backup configuration files (e.g., cp my.cnf my.cnf.bak.$(date +%Y%m%d)).

Prefer restarting a replica first, then the primary, or perform the restart during low‑traffic windows.

After restart, check error logs for new messages and verify uptime with SHOW STATUS LIKE 'Uptime';.

Rollback by restoring the original config and restarting again.

Modifying Firewall Rules

Opening port 3306 expands the attack surface; restrict the source IP range.

Validate the rule with both authorized and unauthorized source IPs.

Rollback with

firewall-cmd --zone=public --remove-port=3306/tcp --permanent && firewall-cmd --reload

.

Changing max_connections or wait_timeout

Increasing max_connections raises memory usage; verify available RAM.

Prefer dynamic change first ( SET GLOBAL max_connections = xxx;) and observe stability before persisting to my.cnf.

Rollback with SET GLOBAL max_connections = <original_value>; and revert the config file.

Unlocking Accounts / Switching Authentication Plugin

Check logs for brute‑force patterns before unlocking.

After unlocking, test with the real application workload.

9. Post‑mortem – Reducing Future Recurrences

After any new deployment, run a checklist: local socket test, remote TCP test, and real‑user login.

Document any changes to bind-address, host whitelist, firewall rules, or MySQL parameters.

Configure application connection pools with sensible max connections, idle timeout detection, and enable connection‑validation (e.g., testOnBorrow).

Set up monitoring of mysql_global_status_threads_connected (via mysqld_exporter) and alert on high usage before hitting the limit.

Be aware that account lockout can be abused for denial‑of‑service; combine with network‑level rate limiting.

By following the systematic "phenomenon → information → judgment → command → evidence → root cause → fix → verification → rollback → review" workflow, most MySQL connectivity problems can be diagnosed within minutes, avoiding costly trial‑and‑error cycles.