如何通过SSH从C#连接到mysql
本文关键字:连接 mysql 何通过 SSH | 更新日期: 2023-09-27 18:35:52
如何通过 C# 连接到 mysql 数据库,
这是我现在的连接字符串:
connectionString="server=localhost;port=3306;user id=root;Password=*****;database=Data" providerName="MySql.Data.MySqlClient"
如何将SSH字符串以这种形式放置,因为它需要像这样:
SSH 主机名、SSH 用户名、SSH 密码、Mysql 主机名、Mysql用户名、Mysql 密码、端口
我不认为MySql和MySqlClient支持这样的事情。 连接字符串专门用于数据库。 您将需要一个 SSH 客户端首先连接到 SSH 服务器,然后找到一种通过该隧道路由 Sql 连接的方法。
http://www.howtogeek.com/howto/ubuntu/access-your-mysql-server-remotely-over-ssh/
我不认为有一个Microsoft的.Net库来处理SSH连接,但Code Plex上有一个开源项目可能会有所帮助。
http://sshnet.codeplex.com/
// using Renci.sshNet
PasswordConnectionInfo connectionInfo = new PasswordConnectionInfo(hostAdres, hostNaam, wachtwoord);
connectionInfo.Timeout = TimeSpan.FromSeconds(30);
var 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();
var connection = new MySqlConnection("server = " + "127.0.0.1" + "; Database = database; password = PWD; UID = yourname; Port = 22");
connection.Open();
我尝试了前面的所有步骤,但没有奏效,对我有用的方法如下:
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);
}
代码对我有效,使用 sshkeyfile (*.pem),C# Mysql -> Amazon Web Services 中的 Aurora:
class Program
{
static string SshHostName = "***";
static string SshUserName = "***";
static string SshKeyFile = @"C:'Work'pems'***.pem";
static string Server = "***.eu-west-1.rds.amazonaws.com";
static uint Port = 3306;
static string UserID = "***";
static string Password = "***";
static string DataBase = "***";
static void Main(string[] args)
{
ConnectionInfo cnnInfo;
using (var stream = new FileStream(SshKeyFile, FileMode.Open, FileAccess.Read))
{
var file = new PrivateKeyFile(stream);
var authMethod = new PrivateKeyAuthenticationMethod(SshUserName, file);
cnnInfo = new ConnectionInfo(SshHostName, 22, SshUserName, authMethod);
}
using (var client = new SshClient(cnnInfo))
{
client.Connect();
if (client.IsConnected)
{
var forwardedPort = new ForwardedPortLocal("127.0.0.1", Server, Port);
client.AddForwardedPort(forwardedPort);
forwardedPort.Start();
string connStr = $"Server = {forwardedPort.BoundHost};Port = {forwardedPort.BoundPort};Database = {DataBase};Uid = {UserID};Pwd = {Password};";
using (MySqlConnection cnn = new MySqlConnection(connStr))
{
cnn.Open();
MySqlCommand cmd = new MySqlCommand("SELECT * FROM PostalCodes LIMIT 25;", cnn);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
Console.WriteLine($"{reader.GetString(1)}, {reader.GetString(2)}, {reader.GetString(3)}");
Console.WriteLine("Ok");
cnn.Close();
}
client.Disconnect();
}
}
}
}
不能在连接字符串中指定 SSH 代理或 SSH 凭据,必须先建立 SSH 连接,然后使用标准连接字符串,就像问题中的内容一样。
要通过 C# 建立 SSH 连接,您可以使用像 sharpSsh 这样的库。
这是
最终的代码:)为我工作。
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();
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;
}
}