Keywords: MySQL connection error | bind-address configuration | network listening
Abstract: This article provides a comprehensive analysis of MySQL connection error 111, examining the impact of bind-address configuration on network connectivity. Through practical case studies, it demonstrates the differences between localhost and IP address connections, offering complete configuration modification and verification procedures. Combining network programming principles, the article explains MySQL server's listening mechanism to help readers fundamentally understand and resolve remote connection issues.
Problem Phenomenon Analysis
During MySQL server deployment, users frequently encounter a typical issue: successful database connections using localhost or 127.0.0.1, but error 111 when using the server's actual IP address (such as 192.168.1.100). This phenomenon not only affects local connections but also prevents other machines from accessing the database remotely.
From a technical perspective, this difference reveals limitations in the MySQL server's network listening configuration. When using localhost or 127.0.0.1, connections occur through the loopback interface, a special network interface designed for inter-process communication within the same machine. When using actual IP addresses, connections must pass through the server's physical network interfaces, involving more complex network stack processing.
Root Cause Investigation
The core cause of error 111 lies in the MySQL server's bind-address configuration. In default installations or certain security configurations, MySQL may be set to listen only on the loopback interface. This means the server will only accept connection requests from 127.0.0.1 or localhost, while rejecting all connection attempts from other IP addresses.
From a network programming perspective, when MySQL server starts, it binds to specific network interfaces and ports. When bind-address is set to 127.0.0.1, the server creates listening sockets only on the loopback interface. Any connection requests sent to other interfaces (such as the Ethernet interface 192.168.1.100) are directly rejected by the operating system because there are no corresponding listening services on those interfaces.
Configuration Modification Steps
To resolve this issue, modification of MySQL's configuration file is required. In most Linux systems, the main configuration file is located at /etc/mysql/my.cnf or /etc/my.cnf. Users need to locate the [mysqld] section and check for the presence of the bind-address configuration item.
If lines similar to bind-address = 127.0.0.1 are found, this restricts MySQL to listen only to local connections. The solution is to comment out this line (by adding a # symbol at the beginning), or modify it to bind-address = 0.0.0.0, which indicates listening on all available network interfaces.
After modifying the configuration file, the MySQL service must be restarted for the changes to take effect. On systemd-based systems, use the command: sudo systemctl restart mysql. On traditional init systems, use: sudo service mysql restart.
Verification and Testing
After configuration modifications, comprehensive connection testing is necessary to verify the solution's effectiveness. First, test locally on the server: mysql -uusername -ppassword -h192.168.1.100. If the connection succeeds, it indicates that local IP connections have been restored.
Next, perform remote connection testing from other machines on the network. Ensure firewall rules allow communication on port 3306 and that network routing is normal. If problems persist, it may be necessary to check iptables rules or cloud provider security group settings.
Security Considerations
While setting bind-address to 0.0.0.0 resolves connection issues, it introduces security risks because MySQL will listen on all network interfaces. In production environments, additional security measures are recommended:
Use firewalls to restrict access source IPs, allowing only specific client IPs to connect to the MySQL port. Configure MySQL user permissions to ensure only authorized users can connect remotely. Consider using SSH tunnels or VPNs to encrypt database communications.
Deep Technical Principles
From an operating system perspective, network services bind to specific IP addresses and ports through socket APIs. When bind-address is set to 127.0.0.1, MySQL specifies the loopback address when calling the bind() system call, and the operating system kernel only passes connection requests destined for that address to the MySQL process.
During TCP connection establishment, the client sends a SYN packet to the target IP and port. If no service is listening on that IP address, the operating system directly replies with an RST packet, which is the underlying cause of error 111. By modifying bind-address, MySQL creates listening sockets on all interfaces, enabling it to accept connection requests from any IP address.