出于安全考虑,有的时候数据库服务器只能通过SSH访问,比如MySql服务装在了服务器A上,并且A机器只允许B机器才能访问,而部署环境可能在C机器上,这时候就要C服务器通过B服务器连接A服务器,这时候就需要用到SSH方式连接,需要SSH.NET类库:
下载地址:https://github.com/sshnet/SSH.NET
代码如下:
using MySql.Data.MySqlClient; using Renci.SshNet; using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Windows.Forms; namespace SSHMySql { public partial class Form1 : Form { public Form1() { InitializeComponent(); SSHConnectMySql(); } public void SSHConnectMySql() { string SSHHost = "*.*.*.*"; // SSH地址 int SSHPort = 22; // SSH端口 string SSHUser = "user"; // SSH用户名 string SSHPassword = "pwd"; // SSH密码 string sqlIPA = "127.0.0.1";// 映射地址 实际上也可以写其它的 Linux上的MySql的my.cnf bind-address 可以设成0.0.0.0 或者不设置 string sqlHost = "192.168.1.20"; // mysql安装的机器IP 也可以是内网IP 比如:192.168.1.20 uint sqlport = 3306; // 数据库端口及映射端口 string sqlConn = "Database=mysql;Data Source=" + sqlIPA + ";Port=" + sqlport + ";User Id=user;Password=pwd;CharSet=utf8"; string sqlSELECT = "select * from user"; PasswordConnectionInfo connectionInfo = new PasswordConnectionInfo(SSHHost, SSHPort, SSHUser, SSHPassword); connectionInfo.Timeout = TimeSpan.FromSeconds(30); using (var client = new SshClient(connectionInfo)) { try { client.Connect(); if (!client.IsConnected) { MessageBox.Show("SSH connect failed"); } var portFwdL = new ForwardedPortLocal(sqlIPA, sqlport, sqlHost, sqlport); //映射到本地端口 client.AddForwardedPort(portFwdL); portFwdL.Start(); if (!client.IsConnected) { MessageBox.Show("port forwarding failed"); } MySqlConnection conn = new MySqlConnection(sqlConn); MySqlDataAdapter myDataAdapter = new MySqlDataAdapter(); myDataAdapter.SelectCommand = new MySqlCommand(sqlSELECT, conn); try { conn.Open(); DataSet ds = new DataSet(); myDataAdapter.Fill(ds); dataGridView1.DataSource = ds.Tables[0]; } catch (Exception ee) { MessageBox.Show(ee.Message); } finally { conn.Close(); } client.Disconnect(); } catch (Exception ex) { MessageBox.Show(ex.Message); } } } } }
注:如果出现错误可以把本地(开发机器)上的MySql服务停止掉。
需要的dll:SSHDLL.rar