C#SSH隧道到MySQL服务器 [英] C# SSH tunnel to MySQL server
本文介绍了C#SSH隧道到MySQL服务器的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在尝试编写一个简单的程序以连接到只能通过SSH连接的MySQL远程服务器.
I am trying to write a simple program to connect to a MySQL remote server that can only be connected to via SSH.
它报告SSH连接，并且该端口转发了，但随后指出它无法连接到任何指定的主机?
It reports the SSH connects and that the port forwards but then states that it cannot connect to any of the specified hosts?
我配置错了吗?
下面是控制台输出和代码:
Below is the console output and the code:
using System;
using System.Collections.Generic;
using System.Text;
using System.Diagnostics;
using System.IO;
using System.Data;
using System.Web;
using System.Windows.Forms;
//Add MySql Library
using MySql.Data.Entity;
using MySql.Data.MySqlClient;
using MySql.Data.Types;
// SSH
using Renci.SshNet;
using Renci.SshNet.Common;
namespace MySQL_Console
{
class MainClass
{
public static void Main (string[] args)
{
PasswordConnectionInfo connectionInfo = new PasswordConnectionInfo ("mytestdb.co.uk", "root", "password123");
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(Convert.ToUInt32(4479), "localhost", Convert.ToUInt32(3306));
client.AddForwardedPort(portFwld);
portFwld.Start();
if (portFwld.IsStarted)
{
Console.WriteLine ("Port forwarded: {0}", portFwld.ToString());
}
else
{
Console.WriteLine ("Port forwarding has failed.");
}
}
catch (SshException e)
{
Console.WriteLine ("SSH client connection error: {0}", e.Message);
}
catch (System.Net.Sockets.SocketException e)
{
Console.WriteLine ("Socket connection error: {0}", e.Message);
}
}
Console.WriteLine ("\r\nTrying database connection...");
DBConnect dbConnect = new DBConnect ("localhost", "test_database", "root", "passwrod123", "4479");
var ct = dbConnect.Count ("packages");
Console.WriteLine (ct.ToString());
}
}
// MySQL DB class
class DBConnect
{
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;
}
}
//Insert statement
public void Insert()
{
string query = "INSERT INTO tableinfo (name, age) VALUES('John Smith', '33')";
//open connection
if (this.OpenConnection() == true)
{
//create command and assign the query and connection from the constructor
MySqlCommand cmd = new MySqlCommand(query, connection);
//Execute command
cmd.ExecuteNonQuery();
//close connection
this.CloseConnection();
}
}
//Update statement
public void Update(string tableName, List<KeyValuePair<string, string>> setArgs, List<KeyValuePair<string, string>> whereArgs)
{
string query = "UPDATE tableinfo SET name='Joe', age='22' WHERE name='John Smith'";
//Open connection
if (this.OpenConnection() == true)
{
//create mysql command
MySqlCommand cmd = new MySqlCommand();
//Assign the query using CommandText
cmd.CommandText = query;
//Assign the connection using Connection
cmd.Connection = connection;
//Execute query
cmd.ExecuteNonQuery();
//close connection
this.CloseConnection();
}
}
//Delete statement
public void Delete(string tableName, List<KeyValuePair<string, string>> whereArgs)
{
string query = "DELETE FROM tableinfo WHERE name='John Smith'";
if (this.OpenConnection() == true)
{
MySqlCommand cmd = new MySqlCommand(query, connection);
cmd.ExecuteNonQuery();
this.CloseConnection();
}
}
//Select statement
public List<string> Select(string queryString)
{
string query = queryString;
//Create a list to store the result
List<string> list = new List<string>();
//Open connection
if (this.OpenConnection() == true)
{
//Create Command
MySqlCommand cmd = new MySqlCommand(query, connection);
//Create a data reader and Execute the command
MySqlDataReader dataReader = cmd.ExecuteReader();
//Read the data and store them in the list
int fieldCOunt = dataReader.FieldCount;
while (dataReader.Read())
{
for (int i = 0; i < fieldCOunt; i++) {
list.Add(dataReader.GetValue(i).ToString());
}
}
//close Data Reader
dataReader.Close();
//close Connection
this.CloseConnection();
//return list to be displayed
return list;
}
return list;
}
//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;
}
//Backup
public void Backup()
{
try
{
DateTime Time = DateTime.Now;
int year = Time.Year;
int month = Time.Month;
int day = Time.Day;
int hour = Time.Hour;
int minute = Time.Minute;
int second = Time.Second;
int millisecond = Time.Millisecond;
//Save file to C:\ with the current date as a filename
string path;
path = "C:\\" + year + "-" + month + "-" + day + "-" + hour + "-" + minute + "-" + second + "-" + millisecond + ".sql";
StreamWriter file = new StreamWriter(path);
ProcessStartInfo psi = new ProcessStartInfo();
psi.FileName = "mysqldump";
psi.RedirectStandardInput = false;
psi.RedirectStandardOutput = true;
psi.Arguments = string.Format(@"-u{0} -p{1} -h{2} {3}", uid, password, server, database);
psi.UseShellExecute = false;
Process process = Process.Start(psi);
string output;
output = process.StandardOutput.ReadToEnd();
file.WriteLine(output);
process.WaitForExit();
file.Close();
process.Close();
}
catch (IOException e)
{
Console.WriteLine("Error {0}, unable to backup!", e.Message);
}
}
//Restore
public void Restore()
{
try
{
//Read file from C:\
string path;
path = "C:\\MySqlBackup.sql";
StreamReader file = new StreamReader(path);
string input = file.ReadToEnd();
file.Close();
ProcessStartInfo psi = new ProcessStartInfo();
psi.FileName = "mysql";
psi.RedirectStandardInput = true;
psi.RedirectStandardOutput = false;
psi.Arguments = string.Format(@"-u{0} -p{1} -h{2} {3}", uid, password, server, database);
psi.UseShellExecute = false;
Process process = Process.Start(psi);
process.StandardInput.WriteLine(input);
process.StandardInput.Close();
process.WaitForExit();
process.Close();
}
catch (IOException e)
{
Console.WriteLine("Error {0}, unable to Restore!", e.Message);
}
}
}
}
推荐答案
尝试指定127.0.0.1而不是localhost，或更常见的是指定IPAddress.Loopback.ToString().另外，您不需要显式转换为uint32.因此，您应该尝试以下操作:
Try specifying 127.0.0.1 instead of localhost, or more generally IPAddress.Loopback.ToString(). Also, you don't need to explicitly cast to uint32. So you should try something like:
var portFwld = new ForwardedPortLocal(4479, IPAddress.Loopback.ToString(), 3306);
如果这不起作用，请尝试同时指定服务器名称，例如:
If that doesn't work, try also specifying the server name, something like:
var portFwld = new ForwardedPortLocal(IPAddress.Loopback.ToString(), 4479, "servername_goes_here", 3306);
这篇关于C#SSH隧道到MySQL服务器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
