如何通过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 密码、端口

如何通过SSH从C#连接到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;
        }
    }