MySql 错误处理

Centos7 出现 Unable to connect to any of the specified MySQL hosts，而window不会

MySql.Data.MySqlClient.MySqlException (0x80004005): Unable to connect to any of the specified MySQL hosts.
---> MySql.Data.MySqlClient.MySqlException (0x80004005): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

解决：连接字符串添加 SslMode=None;

Retrieval of the RSA public key is not enabled for insecure connections

 System.Exception: Retrieval of the RSA public key is not enabled for insecure connections.
 at MySql.Data.MySqlClient.NativeDriver.Open() at MySql.Data.MySqlClient.Driver.Open()
 at MySql.Data.MySqlClient.Driver.Create(MySqlConnectionStringBuilder settings) 
 at MySql.Data.MySqlClient.MySqlPool.CreateNewPooledConnection() 
 at MySql.Data.MySqlClient.MySqlPool.GetPooledConnection() 
 at MySql.Data.MySqlClient.MySqlPool.TryToGetDriver() 
 at MySql.Data.MySqlClient.MySqlPool.GetConnection() 
 at MySql.Data.MySqlClient.MySqlConnection.Open() 

如果用户使用了 sha256_password 认证，密码在传输过程中必须使用 TLS 协议保护，但是如果 RSA 公钥不可用，可以使用服务器提供的公钥；
可以在连接中通过 ServerRSAPublicKeyFile 指定服务器的 RSA 公钥，或者AllowPublicKeyRetrieval=True参数以允许客户端从服务器获取公钥；
但是需要注意的是 AllowPublicKeyRetrieval=True可能会导致恶意的代理通过中间人攻击(MITM)获取到明文密码，所以默认是关闭的，必须显式开启。
所以可以用mysql_native_password,不要用sha256_password方式，就不会有问题了。在链接的后面添加参数allowPublicKeyRetrieval=true即可。

Problem
When connecting to MySQL Server from a C# program, you may receive one of the following errors:

    MySqlException (0x80004005): Retrieval of the RSA public key is not enabled for insecure connections. (Connector/NET)
    Authentication method ‘caching_sha2_password’ failed. Either use a secure connection, specify the server’s RSA public key with ServerRSAPublicKeyFile, or set   AllowPublicKeyRetrieval=True. (MySqlConnector)
    Authentication method ‘sha256_password’ failed. Either use a secure connection, specify the server’s RSA public key with ServerRSAPublicKeyFile, or set     AllowPublicKeyRetrieval=True. (MySqlConnector)

Fix

    Use one of the following fixes. (Note: if using MySql.Data (Connector/NET), uninstall it first then install MySqlConnector.)
    (Preferred) Use a secure connection by adding ;SslMode=Required to the connection string.
    Specify the (local) path that contains a copy of the server’s public key by adding ;ServerRSAPublicKeyFile=path/to/file.pem to the connection string. To retrieve the   server’s public key, connect securely to the server and execute the following query, saving the results in a file: SHOW STATUS LIKE 'Caching_sha2_password_rsa_public_key';
    (Not recommended) Automatically retrieve the server’s public key by adding ;AllowPublicKeyRetrieval=true to the connection string; this is potentially insecure.

Background

    MySQL Server 5.7 added the sha256_password authentication plugin. MySQL Server 8.0 adds the caching_sha2_password authentication plugin and makes it the default. These     plugins use RSA public key encryption to protect the user’s password in transit.

    As the MySQL Server Team writes:

    Distributing keys securely can be an operational headache. MySQL Server will supply its own RSA public key upon request from the client, so that the key doesn’t have to    be explicitly distributed and configured for each client. But this introduces another security concern – a proxy in the middle may substitute an RSA public key for which  it has the private key, decrypt and harvest the plain-text password, then re-encrypting the password with the actual server RSA public key for the connection attempt to     continue. For this reason, it’s strongly recommended that clients define a local RSA public key to use instead of request the server RSA key during the handshake.

    By default, client libraries will not send the password unless a secure connection (using TLS or RSA public key encryption) can be established. To avoid a MITM attack,     the RSA public key will not be sent in plain text. For Connector/NET, you can use TLS (SslMode=Required) to protect the RSA public key. With MySqlConnector, you also have  the option of specifying the server’s public key directly, using the ServerRSAPublicKeyFile option, or allowing a potentially-insecure connection by using   AllowPublicKeyRetrieval=true.

Further Reading
Protecting MySQL Passwords With the sha256_password Plugin