I am trying to write a simple program to connect to a MySQL remote server that can only be connected to via SSH.
It reports the SSH connects and that the port forwards but then states that it cannot connect to any of the specified hosts?
Have I configured this wrong?
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);
}
}
}
}
