When running long or complex queries in MySQL, you may encounter the dreaded error:
Error 2013: Lost connection to MySQL server during query
Code language: JavaScript (javascript)This problem is especially common when using mysqldump on large databases. Fortunately, the fix is often a matter of adjusting MySQL’s default timeout and connection settings. Let’s go step by step.
Understanding the Problem
“MySQL lost connection” errors happen when the client fails to maintain a stable link with the server. The root causes can vary:
- Network interruptions
- Server overload or insufficient resources
- Restrictive configuration limits
- Inefficient or long-running queries
The key is to identify whether the issue comes from configuration, resource exhaustion, or query design.
Increase wait_timeout and interactive_timeout
MySQL automatically closes idle connections after a set period. If your application keeps connections open longer than this limit, you’ll likely hit the error.
Check the current values:
SHOW SESSION VARIABLES LIKE 'wait_timeout';
SHOW SESSION VARIABLES LIKE 'interactive_timeout';
Code language: JavaScript (javascript)By default, both are set to 28800 seconds (8 hours).
If your queries need more time, you can extend them:
SET @@GLOBAL.wait_timeout = 57600;
SET @@GLOBAL.interactive_timeout = 57600;
Code language: CSS (css)In this example, the timeout is doubled to 16 hours.
You can also make this change permanent by editing your my.cnf configuration:
[mysqld]
wait_timeout = 57600
interactive_timeout = 57600
Increase max_allowed_packet
Another common cause is packets that exceed MySQL’s size limit. If you see “MySQL server has gone away” in your logs, try increasing the packet size:
[mysqld]
max_allowed_packet = 64M
This ensures larger queries or blob data transfers don’t exceed MySQL’s default limits.
Optimize Queries and Tables
Sometimes the problem isn’t configuration but performance. Poorly written queries or fragmented tables can cause timeouts.
- Use
EXPLAINto review query plans and ensure indexes are being used. - Run:
OPTIMIZE TABLE table_name;
to defragment tables and improve performance.
Monitor Server Resources
If your server is hitting CPU, RAM, or I/O bottlenecks, connections may drop unexpectedly. Regular monitoring of server resources (via htop, mysqladmin, or dedicated monitoring tools) is essential. Scaling up server resources may be required.
Check max_connections
If too many clients are connecting simultaneously, MySQL may start refusing or dropping connections. Increase the limit cautiously:
[mysqld]
max_connections = 500
Be mindful that higher values demand more system memory.
Review Server Logs
Sometimes the error is caused by MySQL itself closing connections. Reviewing the error logs will give you direct insights:
tail -f /var/log/mysql/error.log
Code language: JavaScript (javascript)Look for memory issues, aborted connections, or misconfigured parameters.
Conclusion
“MySQL lost connection” errors are common but usually fixable through configuration tuning and proactive server management. By adjusting timeouts, packet size, and connection limits—while ensuring your queries and hardware are optimized—you can significantly reduce the likelihood of connection drops.
Best practices:
- Always back up your database before changing configs.
- Test adjustments in staging before applying them in production.
- Keep monitoring your MySQL instance for early signs of instability.
A healthy, well-tuned MySQL server will maintain stable connections and deliver the reliability your applications depend on.
