远程MySQL主机连接失败求助（含代码及报错信息）

Alright, let's work through this problem together. You mentioned your local database copy connects fine, but hitting the XenForo DB on your web host is throwing errors—let's break down what's happening and get it sorted.

First, let's recap the error progression:

• Your initial code threw Connection must be valid and open because you used OpenAsync() without awaiting it, plus static connection objects are prone to state issues.
• Your revised code fixed the connection state problem, but now you're getting Unable to connect to any of the specified MySQL hosts—this means we're past code logic issues and into network/permission/MySQL configuration problems.

Here's how to diagnose and fix each possible cause:

1. Check Remote MySQL User Permissions & IP Whitelisting #

Most web hosts lock down MySQL access to specific IP addresses to prevent unauthorized access. This is the most common cause of this error.

• Whitelist your local public IP: Log into your host's control panel (cPanel, Plesk, etc.), find the MySQL database settings, and add your local machine's public IP to the allowed list. You can find your public IP by searching "what's my IP" in your browser, or running curl ifconfig.me in a terminal.
• Verify user connection permissions: Run this query on the remote MySQL server (via phpMyAdmin or your host's terminal tool):

  SELECT host FROM mysql.user WHERE user = 'ls-v_forum';
  

  If the result is localhost or an IP that doesn't match yours, update the permission:

  GRANT ALL PRIVILEGES ON `ls-v_forum`.* TO 'ls-v_forum'@'your_public_ip' IDENTIFIED BY 'your_password';
  FLUSH PRIVILEGES;
  

  Replace your_public_ip and your_password with your actual details.

2. Test Network Connectivity to the MySQL Port #

MySQL uses port 3306 by default—make sure this port is accessible from your local machine:

• Check local firewall: Ensure Windows Firewall, macOS Firewall, or any third-party antivirus/firewall isn't blocking outgoing connections to port 3306.
• Test with telnet/netcat: Run one of these commands in your terminal to check if you can reach the remote server:

  # Using telnet
  telnet your_remote_server_ip 3306
  # Using netcat (if installed)
  nc -zv your_remote_server_ip 3306
  

  If this fails:
  • Confirm with your host if they use a non-default MySQL port (some hosts use 3307 or another number). If so, add Port=xxxx; to your connection string.
  • Ask your host if their firewall is blocking incoming connections to port 3306 from your IP.

3. Validate Your Connection String #

Typos and incorrect settings are easy to miss:

• Double-check the server value: Some hosts use a dedicated database hostname (like db.yourdomain.com) instead of the main web server IP—confirm this with your host.
• Add SSL if required: Many modern hosts mandate SSL for MySQL connections. Update your connection string to include SslMode=Required;:

  "server=your_remote_host; Port=3306; database=ls-v_forum; UID=ls-v_forum; password=your_pass; SslMode=Required;"
  

• Ensure the password is exactly correct—copy-paste it to avoid typos, especially if it has special characters.

4. Fix Critical SQL Injection Vulnerability (Bonus) #

Even once the connection works, your current code has a huge security risk: concatenating user input directly into SQL queries. Switch to parameterized queries immediately—this also avoids syntax errors if input has quotes/special characters:

public int? FetchUserId(string emailOrUser)
{
    using (var connection = new MySqlConnection("your_updated_connection_string"))
    {
        connection.Open();
        
        // First check username
        using (var userCmd = new MySqlCommand("SELECT user_id FROM xf_user WHERE username = @input", connection))
        {
            userCmd.Parameters.AddWithValue("@input", emailOrUser);
            var result = userCmd.ExecuteScalar();
            if (result != null)
            {
                return Convert.ToInt32(result);
            }
        }
        
        // If username not found, check email
        using (var emailCmd = new MySqlCommand("SELECT user_id FROM xf_user WHERE email = @input", connection))
        {
            emailCmd.Parameters.AddWithValue("@input", emailOrUser);
            var result = emailCmd.ExecuteScalar();
            return result != null ? Convert.ToInt32(result) : null;
        }
    }
}

5. Confirm Remote MySQL Server is Running #

If all else fails, check your host's control panel to see if the MySQL service is active. If it's down, reach out to your host's support team for help.

内容的提问来源于stack exchange，提问作者Moretti