I am getting “cannot connect to mysql server (10060)” error while trying to access my MySQL database remotely. Please help!

That was a recent support ticket received at our Server Management Services department where we manage our customer servers.

Website owners usually face this error due to firewall restrictions, MySQL service downtime, and more.

Today, we’ll discuss the top 7 reasons for this error and how our Server Administrators fix them.

 

“Cannot connect to MySQL server (10060)” Error 

Customers usually receive this error when they try to access the MySQL server remotely.

“Cannot connect to mysql server (10060)” error means that the connection established failed because the host connected has failed to respond in time.

Let’s now see the main reasons for this error and how our Server Experts fix them.

Before we begin, let’s take a look at the impacts of this error:

• When MySQL cannot be accessed, applications relying on the database will fail to function properly, leading to downtime. This can affect user experience and result in lost revenue for businesses.
• Users and applications may be unable to retrieve or manipulate data, which can hinder business operations, reporting, and analytics.
• Misconfigured MySQL servers that lead to connection issues may expose vulnerabilities, especially if remote access is not properly secured.

An Overview:

• Causes & Fixes
• 1. Service Downtime
• 2. MySQL Configuration Issues
• 3. Firewall Restrictions
• 4. Insufficient User Privileges
• 5. Incorrect Connection String
• 6. MySQL Server Configuration for Networking
• 7. Long-running Transactions or Locks
• Prevention Tips

Causes & Fixes

1. Service Downtime

The MySQL service may not be running due to various issues such as heavy traffic, resource outages, or DDoS attacks.

Fix:

• Check if MySQL is running with this command:

  netstat -plan | grep :3306

  This checks if the MySQL service is listening on port 3306.
• Restart MySQL Service: If the service is not running, restart it using:

  sudo systemctl start mysql # For systemd
sudo service mysql start # For sysvinit/upstart


Additionally, we can examine the MySQL error logs for any messages indicating why the service is down. The log file location can vary based on our configuration.

It is often found in /var/log/mysql/error.log or specified in your MySQL configuration file.

2. MySQL Configuration Issues

By default, MySQL may be configured to listen only for local connections (bind-address 127.0.0.1).

Fix:

1. Open the MySQL configuration file located at /etc/mysql/my.cnf.
2. Then, modify the bind-address line to:

   bind-address = 0.0.0.0

   Alternatively, we can comment out this line to allow connections from any address.
3. After making changes, restart the service:

   sudo systemctl restart mysql

3. Firewall Restrictions

The firewall may block access to MySQL’s default port (3306).

Fix:

• Temporarily disable the firewall to see if it resolves the issue. We can use Telnet to check port:

  telnet xx.xx.xx.xx 3306

  Remember to replace xx.xx.xx.xx with your server’s IP address.
• If connectivity works without the firewall, adjust the rules to allow traffic on port 3306:
  • For UFW (Ubuntu):

    sudo ufw allow 3306
  • For Windows Firewall

    Add an exception for port 3306 through Control Panel settings.

4. Insufficient User Privileges

The MySQL user may lack permissions for remote access.

Fix:

1. Log into MySQL and run:

   
GRANT ALL ON *.* TO 'user'@'host_name' IDENTIFIED BY 'Password';
FLUSH PRIVILEGES;

2. Also, ensure that the remote host or IP is allowed access in the configuration.

5. Incorrect Connection String

Errors in the connection string (e.g., wrong server name, database name, username) can lead to connection failures.

Fix:

Check with the client for the correct connection string details. Furthermore, ensure that all components of the connection string (server name, database name, username, port) are accurate.

6. MySQL Server Configuration for Networking

The skip_networking system variable may be enabled, which prevents MySQL from accepting TCP/IP connections. This setting is often used for security reasons or during specific configurations.

Fix:

1. Open the MySQL configuration file (usually located at /etc/mysql/my.cnf or /etc/my.cnf).
2. Look for a line that says skip-networking and comment it out by adding a # at the beginning of the line, or remove it entirely.

   #skip-networking
3. Then, restart the MySQL service to apply the new settings:

7. Long-running Transactions or Locks

Long-running transactions or locks can stall the MySQL server, causing it to become unresponsive and leading to connection timeouts.

Fix:

1. Log into MySQL and run this command to check for active processes:

   SHOW PROCESSLIST;
2. Look for any transactions that are in a “Locked” state or have been running for an unusually long time.
3. If we identify a problematic process, we can terminate it using:

   KILL process_id;
4. After killing any long-running transactions, monitor the server’s performance to ensure that it is responsive again.

Prevention Tips

• Implement monitoring tools to track MySQL server performance and uptime while setting up alerts for service downtimes or connection errors.
• Ensure the MySQL configuration allows remote connections by setting the bind-address appropriately and regularly reviewing settings like skip_networking.
• Configure firewalls to permit traffic on MySQL’s default port (3306) and regularly test these rules for accessibility.
• Regularly audit user permissions to ensure necessary privileges for remote access and use specific hostnames or IP addresses in user grants for enhanced security.
• Implement connection pooling in applications to efficiently manage database connections and reduce the number of simultaneous connections made to the server.
• Analyze slow queries with tools like EXPLAIN to optimize them and set appropriate timeout values to close idle connections automatically.
• Schedule regular backups of your database to prevent data loss during downtime and keep MySQL updated to benefit from performance improvements and security enhancements.

[Need assistance with a different issue? Our team is available 24/7.]

Conclusion

In short, error “cannot connect to mysql server (10060)” can happen due to firewall restrictions, MySQL configuration issues, MySQL service downtime, and more. Today, we’ve discussed the top 7 reasons for this error and how our Support Engineers fix them.