C#连接MySQL遇“无法连接到指定主机”错误求助

Let's walk through the most common reasons for this error and how to fix them, using your code as a reference.

1. Network/Host Reachability Issues #

First, rule out basic connectivity problems:

• Test if the server is reachable: Open a command prompt and run ping jelletaal.nl to confirm DNS resolves correctly. If it fails, there's a DNS or server outage issue.
• Check if port 3306 is open: Run telnet jelletaal.nl 3306 (or use a tool like PuTTY if telnet isn't enabled). If this times out, either:
  • Your local firewall is blocking outgoing traffic on port 3306,
  • The server's firewall is rejecting incoming connections to 3306,
  • MySQL isn't configured to listen on the public IP of jelletaal.nl.
• Verify IP whitelisting: Many hosting providers require you to add your client IP to a "remote MySQL" whitelist (e.g., in cPanel). Make sure your current public IP is allowed to connect to the server.

2. Connection String Format Errors #

Looking at your connection string, there's a subtle issue that might be causing problems:

string conn_string = "Server=jelletaal.nl;Port=3306;Database=jelletaa_thepuckcup;Uid=jelletaa_jelle;Pwd = nP_9+hC_!_2E;";

Notice the space after Pwd =? This will cause the password to be interpreted as nP_9+hC_!_2E (with a leading space), which is incorrect. Remove the spaces around the equals sign:

string conn_string = "Server=jelletaal.nl;Port=3306;Database=jelletaa_thepuckcup;Uid=jelletaa_jelle;Pwd=nP_9+hC_!_2E;";

Even better, use MySqlConnectionStringBuilder (which you already declared but didn't use!) to avoid manual formatting mistakes. It handles special characters and syntax automatically:

MySqlConnectionStringBuilder connBuilder = new MySqlConnectionStringBuilder()
{
    Server = "jelletaal.nl",
    Port = 3306,
    Database = "jelletaa_thepuckcup",
    UserID = "jelletaa_jelle",
    Password = "nP_9+hC_!_2E"
};

try {
    using (MySqlConnection conn = new MySqlConnection(connBuilder.ToString()))
    {
        conn.Open();
        MessageBox.Show("Successfully created connection to database");
    }
} catch (MySql.Data.MySqlClient.MySqlException ex) {
    MessageBox.Show(ex.Message);
}

(Pro tip: Use using statements for connections to ensure they're properly disposed of after use.)

3. MySQL Driver Compatibility & SSL Settings #

If your server is running MySQL 8.0+, older versions of the MySql.Data NuGet package may struggle with the default caching_sha2_password authentication method. Try adding these parameters to your connection string:

• SslMode=None: Disable SSL if your server doesn't require it (common in shared hosting).
• AllowPublicKeyRetrieval=true: Allows the driver to retrieve the server's public key for authentication.

Updated connection string example:

string conn_string = "Server=jelletaal.nl;Port=3306;Database=jelletaa_thepuckcup;Uid=jelletaa_jelle;Pwd=nP_9+hC_!_2E;SslMode=None;AllowPublicKeyRetrieval=true;";

Also, make sure you're using the latest version of the MySql.Data package (or switch to the newer MySqlConnector package, which is actively maintained).

4. Server-Side MySQL Configuration #

If you have access to the server (or can ask your host):

• Check the bind-address setting in my.cnf (Linux) or my.ini (Windows). If it's set to 127.0.0.1, MySQL only accepts local connections. Change it to 0.0.0.0 to allow connections from any IP, or your server's public IP.
• Verify the MySQL service is running: Use systemctl status mysql (Linux) or check the Services app (Windows) to confirm it's active.
• Ensure your database user jelletaa_jelle has permissions to connect from your client IP. Run this query on the server:

SELECT User, Host FROM mysql.user WHERE User = 'jelletaa_jelle';

If the Host column is localhost or a specific IP that doesn't match your client, update it with:

GRANT ALL PRIVILEGES ON jelletaa_thepuckcup.* TO 'jelletaa_jelle'@'%' IDENTIFIED BY 'nP_9+hC_!_2E';
FLUSH PRIVILEGES;

(% allows connections from any IP; replace with your specific IP for better security.)

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