Analysis and Resolution of MySQL ERROR 2013 (HY000) in F5 Load Balancing Environments

Problem Background and Error Phenomenon

In MySQL database management practice, ERROR 2013 (HY000) is a common connection error with the complete message "Lost connection to MySQL server at 'reading authorization packet', system error: 0". This error typically occurs during the initial connection establishment phase between client and MySQL server, particularly in complex network architectures.

Technical Principle Deep Analysis

The MySQL connection establishment process involves multiple critical stages: TCP three-way handshake, initial communication packet exchange, and authorization packet processing. When clients receive the "reading authorization packet" error, it indicates connection termination during the authorization phase. According to MySQL official documentation, this situation is often closely related to connection timeout settings.

In F5 load balancing environments, the connection flow becomes more complex:

// Simulating connection flow under F5 proxy
class MySQLConnectionFlow {
    constructor() {
        this.clientSide = new ClientConnection();
        this.proxySide = new F5Proxy();
        this.serverSide = new MySQLServer();
    }
    
    async establishConnection() {
        try {
            // Client to F5 connection establishment
            await this.clientSide.connectToProxy();
            
            // F5 proxy handling welcome packet
            await this.proxySide.sendWelcomePacket();
            
            // Authentication phase - prone to timeout here
            await this.handleAuthentication();
            
            // Load balancing backend server selection
            await this.proxySide.selectBackendPool();
            
        } catch (error) {
            console.error("Connection failed during authorization packet reading", error);
        }
    }
}

Core Solutions

Connection Timeout Optimization Configuration

According to MySQL official recommendations, when encountering "reading authorization packet" errors, the primary solution is to adjust connection timeout parameters. Default connect_timeout values may be insufficient for network latency or load balancing processing times.

# MySQL configuration file my.cnf optimization example
[mysqld]
# Increase connection timeout to 10 seconds or more
connect_timeout = 10
# For high-latency network environments, further increase
# connect_timeout = 30

Dynamic adjustment via SQL command is also possible:

SET GLOBAL connect_timeout = 10;

DNS Resolution Optimization

MySQL may require reverse DNS resolution during authentication, which can become a performance bottleneck in load balancing environments. Enabling skip-name-resolve option can significantly improve connection performance.

# DNS resolution optimization in my.cnf
[mysqld]
skip-name-resolve
# Note: After enabling, authorization must be based on IP addresses rather than hostnames

System-Level Access Control Check

On certain Unix systems, hosts.allow and hosts.deny file configurations need verification:

# /etc/hosts.allow configuration example
mysqld: ALL: allow
# Or more precise IP range control
mysqld: 192.168.1.0/24: allow

F5 Load Balancing Environment Special Considerations

Version Compatibility Verification

According to F5 official documentation, MySQL proxy functionality has specific version requirements. Compatibility between BIG-IP version and MySQL version needs confirmation:

// Version compatibility check logic
function checkVersionCompatibility(bigipVersion, mysqlVersion) {
    const supportedVersions = {
        "BIG-IP 11.1": ["MySQL 5.1"],
        "BIG-IP 12.0": ["MySQL 5.1", "MySQL 5.5"],
        "BIG-IP 13.0": ["MySQL 5.6", "MySQL 5.7"]
    };
    
    return supportedVersions[bigipVersion] && 
           supportedVersions[bigipVersion].includes(mysqlVersion);
}

F5 iRule Configuration Analysis

F5 log analysis reveals the connection flow:

// F5 MySQL proxy connection state machine
class F5MySQLProxy {
    constructor() {
        this.states = {
            CLIENT_ACCEPTED: "Client connection accepted",
            CLIENT_DATA: "Client data processing",
            LB_SELECTED: "Load balancing selection",
            CLIENT_CLOSED: "Client connection closed",
            SERVER_CLOSED: "Server connection closed"
        };
    }
    
    handleConnection(state, data) {
        switch(state) {
            case this.states.CLIENT_ACCEPTED:
                this.sendWelcomePacket();
                break;
            case this.states.CLIENT_DATA:
                if (!this.isAuthenticated()) {
                    throw new Error("Attempting operation before authentication");
                }
                break;
        }
    }
}

Diagnostic and Monitoring Methods

MySQL Status Monitoring

Monitor abnormal connections via SHOW GLOBAL STATUS command:

-- Check abnormal connection count
SHOW GLOBAL STATUS LIKE "Aborted_connections";

-- Monitor connection-related status
SHOW GLOBAL STATUS LIKE "Connections";
SHOW GLOBAL STATUS LIKE "Max_used_connections";

Network Connection Diagnostics

Perform basic network connectivity tests:

# Test network connectivity
ping mysql_server_ip

# Test port accessibility
telnet mysql_server_ip 3306

# Check firewall rules
iptables -L -n | grep 3306

Comprehensive Troubleshooting Process

1. Basic Connectivity Verification: Confirm network layer reachability
2. MySQL Service Status Check: Verify MySQL service normal operation
3. Connection Parameter Optimization: Adjust connect_timeout and skip-name-resolve
4. System Access Control Verification: Check hosts.allow/hosts.deny configuration
5. Load Balancing Configuration Review: Confirm F5 configuration and version compatibility
6. Log Analysis: Systematic review of MySQL error logs and F5 proxy logs

Preventive Best Practices

• Conduct thorough compatibility testing before production deployment
• Establish comprehensive monitoring and alerting mechanisms
• Perform regular connection stress testing
• Maintain updated versions of MySQL and load balancing software
• Develop detailed disaster recovery plans

Through systematic analysis and targeted configuration optimization, MySQL connection issues in F5 load balancing environments can be effectively resolved, ensuring high availability and stability of database services.