如何通过 SSH 从 C# 连接到 mysql
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10806799/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to connect to mysql from C# over SSH
提问by user123_456
How can I connect to a mysql database trough C#,
如何通过 C# 连接到 mysql 数据库，
This is my connection string now:
这是我现在的连接字符串：
connectionString="server=localhost;port=3306;user id=root;Password=*****;database=Data" providerName="MySql.Data.MySqlClient"
How to put SSH string in this form as it needs to be something like:
如何以这种形式放置 SSH 字符串，因为它需要类似于：
SSH Hostname, SSH Username, SSH password, Mysql Hostname, Mysql Username, Mysql Password, Port
SSH 主机名、SSH 用户名、SSH 密码、Mysql 主机名、Mysql 用户名、Mysql 密码、端口
采纳答案by Marnee KG7SIO
I don't think MySql and the MySqlClient support such a thing. The connection string is specifically for the database. You will need an SSH client to connect first to the SSH server and then find a way to route the Sql connection over that tunnel.
我不认为 MySql 和 MySqlClient 支持这样的事情。连接字符串专门用于数据库。您将需要一个 SSH 客户端来首先连接到 SSH 服务器，然后找到通过该隧道路由 Sql 连接的方法。
http://www.howtogeek.com/howto/ubuntu/access-your-mysql-server-remotely-over-ssh/
http://www.howtogeek.com/howto/ubuntu/access-your-mysql-server-remotely-over-ssh/
I don't think there is a Microsoft .Net library for handling SSH connections but there is an open source project on Code Plex that might help.
我认为没有用于处理 SSH 连接的 Microsoft .Net 库，但是 Code Plex 上有一个开源项目可能会有所帮助。
回答by aleroot
You can't specify an SSH proxy or SSH credentials in the connection string, you have to establish the SSH connection first and then use a standard connection string like what you have in your question.
您不能在连接字符串中指定 SSH 代理或 SSH 凭据，您必须先建立 SSH 连接，然后使用标准连接字符串，就像您在问题中所拥有的那样。
To establish an SSH connection through C# you can use a library like sharpSsh.
要通过 C# 建立 SSH 连接，您可以使用像sharpSsh这样的库。
回答by Rien Rinaldini
PasswordConnectionInfo connectionInfo = new PasswordConnectionInfo(hostAdres, hostNaam, wachtwoord);
connectionInfo.Timeout = TimeSpan.FromSeconds(30);
client = new SshClient( connectionInfo);
client.Connect();
ForwardedPortLocal portFwld = new ForwardedPortLocal ("127.0.0.1", Convert.ToUInt32(hostpoort), DataBaseServer, Convert.ToUInt32(remotepoort)); client.AddForwardedPort(portFwld);
portFwld.Start();
// using Renci.sshNet
connection = new MySqlConnection("server = " + "127.0.0.1" + "; Database = database; password = PWD; UID = yourname; Port = 22");
connection.Open();
回答by Seyed Mohammad Hassan Mirdehgh
Here is the final code :) Works for me.
这是最终代码:) 对我有用。
PasswordConnectionInfo connectionInfo = new PasswordConnectionInfo("host", "user", "password");
connectionInfo.Timeout = TimeSpan.FromSeconds(30);
var client = new SshClient(connectionInfo);
client.Connect();
var x = client.IsConnected;
ForwardedPortLocal portFwld = new ForwardedPortLocal("127.0.0.1"/*your computer ip*/, "127.0.0.1" /*server ip*/, 3306 /*server mysql port*/);
client.AddForwardedPort(portFwld);
portFwld.Start();
//// using Renci.sshNet
var connection = new MySqlConnection("server = " + "127.0.0.1" /*you computer ip*/ + "; Database = DataBaseName; UID = ?; PWD =?; Port = " + portFwld.BoundPort /*very important !!*/);
connection.Open();
var k = connection.State;
connection.Clone();
client.Disconnect();
回答by Mala
After so much of research the below code worked for me
Hope it may help you also
public static string GetRDSConnectionString()
{
string Database = "<yourdb>";
string value = "";
string mysqlport = "3306";
uint sqlport = Convert.ToUInt32(mysqlport);
string mysqlhostname = "<aws-hostname.com>";
string ssh_host = "100.1.1.1";
int ssh_port = 22;
string ssh_user = "ubuntu";
var keyFile = new PrivateKeyFile(@"C:\Automation\LCI\harvest-dev-kp.pem");
var keyFiles = new[] { keyFile };
var uname = "ubuntu";
MySqlConnection con = null;
MySqlDataReader reader = null;
var methods = new List<AuthenticationMethod>();
methods.Add(new PasswordAuthenticationMethod(uname, ""));
methods.Add(new PrivateKeyAuthenticationMethod(uname, keyFiles));
ConnectionInfo conInfo = new ConnectionInfo(ssh_host, ssh_port, ssh_user, methods.ToArray());
conInfo.Timeout = TimeSpan.FromSeconds(1000);
using (var client = new SshClient(conInfo))
{
try
{
client.Connect();
if (client.IsConnected)
{
Console.WriteLine("SSH connection is active");
}
else
{
Console.WriteLine("SSH connection is inactive");
}
string Localport = "3306";
string hostport = "3306";
var portFwdL = new ForwardedPortLocal("127.0.0.1", Convert.ToUInt32(hostport), mysqlhostname, Convert.ToUInt32(Localport));
client.AddForwardedPort(portFwdL);
portFwdL.Start();
if (portFwdL.IsStarted)
{
Console.WriteLine("port forwarding is started");
}
else
{
Console.WriteLine("port forwarding failed");
}
string connectionstring = "Data Source=localhost;Initial Catalog=<DBNAME>I;User ID=<USERNAME>;Password=<PASSWORD>;SslMode=none";
con = new MySqlConnection(connectionstring);
MySqlCommand command = con.CreateCommand();
command.CommandText = "<YOUR QUERY>";
try
{
con.Open();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
reader = command.ExecuteReader();
while (reader.Read())
{
value = reader["<db_col_name>"].ToString();
}
client.Disconnect();
}
catch (SocketException ex)
{
Console.WriteLine(ex.Message);
}
finally
{
Console.WriteLine("SSh Disconnect");
}
}
//Console.ReadKey();
return value;
}
}
回答by Juanes30
I tried all the previous steps and it didn't work, the method that worked for me was the following:
我尝试了前面的所有步骤，但没有奏效，对我有用的方法如下：
try
{
using(var client = new SshClient("ssh server id", "sshuser", "sshpassword")) // establishing ssh connection to server where MySql is hosted
{
client.Connect();
if (client.IsConnected)
{
var portForwarded = new ForwardedPortLocal("127.0.0.1", 3306, "127.0.0.1", 3306);
client.AddForwardedPort(portForwarded);
portForwarded.Start();
using (MySqlConnection con = new MySqlConnection("SERVER=127.0.0.1;PORT=3306;UID=someuser;PASSWORD=somepassword;DATABASE=DbName"))
{
using (MySqlCommand com = new MySqlCommand("SELECT * FROM tableName", con))
{
com.CommandType = CommandType.Text;
DataSet ds = new DataSet();
MySqlDataAdapter da = new MySqlDataAdapter(com);
da.Fill(ds);
foreach (DataRow drow in ds.Tables[0].Rows)
{
Console.WriteLine("From MySql: " + drow[1].ToString());
}
}
}
client.Disconnect();
}
else
{
Console.WriteLine("Client cannot be reached...");
}
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
