c# - SSH 使用 C# 建立 MySQL 连接隧道

Question

我正尝试使用 SSH 隧道通过 C# 访问我的 MySQL 数据库，但出现异常

Unable to connect to any of the specified MySQL hosts.

我在这个帮助下得到了这段代码:
C# SSH tunnel to MySQL server

这是我的代码:

PasswordConnectionInfo connectionInfo = new PasswordConnectionInfo("example.com", 2222, "username", "password");
connectionInfo.Timeout = TimeSpan.FromSeconds(30);

using (var client = new SshClient(connectionInfo))
{
    try
    {
        Console.WriteLine("Trying SSH connection...");
        client.Connect();
        if (client.IsConnected)
        {
            Console.WriteLine("SSH connection is active: {0}", client.ConnectionInfo.ToString());
        }
        else
        {
            Console.WriteLine("SSH connection has failed: {0}", client.ConnectionInfo.ToString());
        }

        Console.WriteLine("\r\nTrying port forwarding...");
        var portFwld = new ForwardedPortLocal(IPAddress.Loopback.ToString(),2222, "example.com", 3306); 
        client.AddForwardedPort(portFwld);
        portFwld.Start();
        if (portFwld.IsStarted)
        {
            Console.WriteLine("Port forwarded: {0}", portFwld.ToString());
    Console.WriteLine("\r\nTrying database connection...");

 DBConnect dbConnect = new DBConnect("127.0.0.1", "database", "username", "password", "3306");
    int id =  dbConnect.Count("table");
    MessageBox.Show(id + " count ");
            }
            else
            {
                Console.WriteLine("Port forwarding has failed.");
            }

        }
        catch (SshException ex)
        {
            Console.WriteLine("SSH client connection error: {0}", ex.Message);
        }
        catch (System.Net.Sockets.SocketException ex1)
    {
        Console.WriteLine("Socket connection error: {0}", ex1.Message);
    }

}

private MySqlConnection connection;

private string server;
public string Server
{
    get
    {
        return this.server;
    }
    set
    {
        this.server = value;
    }
}

private string database;
public string Database
{
    get
    {
        return this.database;
    }
    set
    {
        this.database = value;
    }
}

private string uid;
public string Uid
{
    get
    {
        return this.server;
    }
    set
    {
        this.server = value;
    }
}

private string password;
public string Password
{
    get
    {
        return this.password;
    }
    set
    {
        this.password = value;
    }
}

private string port;
public string Port
{
    get
    {
        return this.port;
    }
    set
    {
        this.port = value;
    }
}

//Constructor
public DBConnect(string server, string database, string uid, string password, string port = "3306")
{
    this.server = server;

    this.database = database;
    this.uid = uid;
    this.password = password;
    this.port = port;

    Initialize();
}

//Initialize values
private void Initialize()
{
    string connectionString;
    connectionString = "SERVER=" + server + ";" + "DATABASE=" + database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";";
    connection = new MySqlConnection(connectionString);
}


//open connection to database
private bool OpenConnection()
{
    try
    {
        connection.Open();
        Console.WriteLine("MySQL connected.");
        return true;
    }
    catch (MySqlException ex)
    {
        //When handling errors, you can your application's response based on the error number.
        //The two most common error numbers when connecting are as follows:
        //0: Cannot connect to server.
        //1045: Invalid user name and/or password.
        switch (ex.Number)
        {
            case 0:
                Console.WriteLine("Cannot connect to server.  Contact administrator");
                break;

            case 1045:
                Console.WriteLine("Invalid username/password, please try again");
                break;

            default:
                Console.WriteLine("Unhandled exception: {0}.", ex.Message);
                break;

        }
        return false;
    }
}

//Close connection
private bool CloseConnection()
{
    try
    {
        connection.Close();
        return true;
    }
    catch (MySqlException ex)
    {
        Console.WriteLine(ex.Message);
        return false;
    }
}

//Count statement
public int Count(string tableName)
{
    string query = "SELECT Count(*) FROM " + tableName;
    int Count = -1;

    //Open Connection
    if (this.OpenConnection() == true)
    {
        //Create Mysql Command
        MySqlCommand cmd = new MySqlCommand(query, connection);

        //ExecuteScalar will return one value
        Count = int.Parse(cmd.ExecuteScalar() + "");

        //close Connection
        this.CloseConnection();

        return Count;
    }

    return Count;

}

我在控制台中得到的输出是:

Trying SSH connection...
A first chance exception of type 'System.ObjectDisposedException' occurred in mscorlib.dll
A first chance exception of type 'System.ObjectDisposedException' occurred in System.dll
SSH connection is active: Renci.SshNet.PasswordConnectionInfo

Trying port forwarding...
Port forwarded: Renci.SshNet.ForwardedPortLocal
A first chance exception of type 'Renci.SshNet.Common.SshConnectionException' occurred in Renci.SshNet.dll

Trying database connection...
A first chance exception of type 'System.Net.Sockets.SocketException' occurred in System.dll
A first chance exception of type 'System.Net.Sockets.SocketException' occurred in MySql.Data.dll
A first chance exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll
A first chance exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll
 Error: 0 : Unable to connect to any of the specified MySQL hosts.
A first chance exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll
A first chance exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll
Unhandled exception: Unable to connect to any of the specified MySQL hosts..

更新:

我已将端口转发设置更改为:

var portFwld = new ForwardedPortLocal("127.0.0.1", 1000, "127.0.0.1", 3306);

并且我已将我的 mySQL 字符串更改为:

connectionString = "server=127.0.0.1;port=1000; UID=username; password=password; database=data1; charset=utf8;Allow User Variables=True";

我正在连接到 ssh 并且我的端口被转发，但我仍然无法连接到 MySQL 数据库，我遇到了一个异常:

A first chance exception of type 'System.IO.EndOfStreamException' occurred in MySql.Data.dll
A first chance exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll
A first chance exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll
 Error: 0 : Reading from the stream has failed.

Answer 1

您必须将MySQL连接到转发的绑定(bind)端口。 IE。到 2222。

或者在语义上更正确，使用 portFwld.BoundPort。等效地，使用 portFwld.BoundHost。

DBConnect dbConnect = new DBConnect(portFwld.BoundHost, "database", "username", "password", portFwld.BoundPort);

---

另请注意，将 MySQL 主机称为“localhost”而不是“example.com”更有意义，因为主机名是在服务器端解析的。在服务器端，您通常不会连接到“example.com”，而是连接到“localhost”。

var portFwld = new ForwardedPortLocal(IPAddress.Loopback.ToString(), 2222, "localhost", 3306); 

---

当然，您需要在需要隧道时保持 SSH session 打开。因此，您必须在 using block 中连接到数据库:

using (var client = new SshClient(connectionInfo))
{
    ...
    client.Connect();
    ...
    portFwld.Start();
    ... 
    DBConnect dbConnect = new DBConnect(portFwld.BoundHost, "database", "username", "password", portFwld.BoundPort);
}