C#通过ssh连接Mysql数据库

 

开始

最近在做一个项目需要用到ssh去连接mysql数据库，踩了不少坑，今日发博记录下。

正文

直接上代码。

 //ssh配置
            var sshHost = "sshhost";
            var sshUser = "root";
            var sshPwd = "sshPassword";
            var sshPort = 22;

            //数据库连接
            var server = "数据库server";
            var database = "数据库名称";
            var user = "数据库用户名";
            uint dbPort = 33306;
            var dbPwd = "密码";

            //mysqlbuilder方式便于后续参数变更
            MySqlConnectionStringBuilder connBuilder = new MySqlConnectionStringBuilder();
            connBuilder.AllowBatch = true;
            connBuilder.Server = server;
            connBuilder.Port = 3306;//初始化默认
            connBuilder.UserID = user;
            connBuilder.Password = dbPwd;
            connBuilder.Database = database;

            //ssh连接信息
            var auth =
                new PasswordAuthenticationMethod(sshUser, sshPwd);
            ConnectionInfo conInfo =
                new ConnectionInfo(sshHost, sshPort, sshUser, auth);
            try
            {
                //ssh客户端，用using便于资源释放
                using (SshClient client = new SshClient(conInfo))
                {
                    //获取本机ip环回地址 一般是127.0.0.1
                    var local = IPAddress.Loopback.ToString();
                    //端口转发
                    /*
                     *lcoal 本地绑定ip
                     *dbPort 本地绑定端口
                     *server 数据库server
                     *3306 服务器上数据库的端口可能不是3306
                     *
                     */
                    ForwardedPortLocal port = new ForwardedPortLocal(local, dbPort, server, 3306);
                    client.Connect();
                    if (!client.IsConnected)
                    {
                        Console.WriteLine("ssh连接失败");
                    }
                    //给客户端追加需要转发的端口信息
                    client.AddForwardedPort(port);
                    //启动
                    port.Start();
                    /*
                     * 重点，当我们转发端口之后一定需要将我们转发之后的host和port把MysqlBuilder中的Server和Port替换掉。
                     * 否则会报Unable to connect to any of the specified MySQL hosts.
                     */
                    connBuilder.Port = port.BoundPort;//本地绑定ip
                    connBuilder.Server = port.BoundHost;//本地绑定ip
                    //连接字符串
                    MySqlConnection conn = new MySqlConnection(connBuilder.ConnectionString);
                    conn.Open();
                }
            }
            catch (Exception ex)
            {

                Console.WriteLine(ex.Message);
            }

结语

这个还是需要细心去注意每个参数的赋值，要不然错误很难找出来，特别是转发端口的那段代码真的需要好好研究。

参考连接：

https://www.cnblogs.com/sunyj/p/6070206.html

https://stackoverflow.com/questions/32603982/connection-to-mysql-from-net-using-ssh-net-library#32605335