SpringBoot通过ssh通道连接mysql数据库

绝地灬酷狼 2022-09-03 10:17 9阅读 0赞

> 1.  navicat可以通过ssh通道连接mysql数据库，那java中如何实现了，springboot又该怎样集成呢？
> 2.  在开发过程中，我们经常会遇到联调、测试、找bug等问题，由于项目在不同的环境，我们找问题也非常不方便，无法通过本地代码debug调试。在公司内部的局域网内，通过浏览器中ModHeader修改请求参数，代理、重定向等方式可以方便不少，但还是无法解决跨网段的问题。

1.添加包

<dependency>
            <groupId>com.jcraft</groupId>
            <artifactId>jsch</artifactId>
            <version>0.1.53</version>
    </dependency>

2.创建ssh连接工具类，连接到ssh server

import com.jcraft.jsch.JSch;
    import com.jcraft.jsch.Session;
    
    import java.util.Properties;
    
    public class SSHConnection {
    
    private final static String S_PATH_FILE_PRIVATE_KEY = "/Users/hdwang/.ssh/id_rsa";
    private final static String S_PATH_FILE_KNOWN_HOSTS = "/Users/hdwang/.ssh/known_hosts";
    private final static String S_PASS_PHRASE = "";
    private final static int LOCAl_PORT = 3307;
    private final static int REMOTE_PORT = 3306; 
    private final static int SSH_REMOTE_PORT = 1022;
    private final static String SSH_USER = "zhangsan";
    private final static String SSH_PASSWORD = "123456";
    private final static String SSH_REMOTE_SERVER = "192.168.0.2";
    
    //通过SSH 隧道连接后 访问数据资源的服务器地址一般是 localhost或者主机内网IP，不用公网IP
    private final static String MYSQL_REMOTE_SERVER = "123.mysql.com"; 
    
    private Session sesion; //represents each ssh session
    
    public void closeSSH ()
    {
        sesion.disconnect();
    }
    
    public SSHConnection () throws Throwable
    {
    
        JSch jsch = null;
    
            jsch = new JSch();
            jsch.setKnownHosts(S_PATH_FILE_KNOWN_HOSTS);
            //jsch.addIdentity(S_PATH_FILE_PRIVATE_KEY);
    
            sesion = jsch.getSession(SSH_USER, SSH_REMOTE_SERVER, SSH_REMOTE_PORT);
    
            sesion.setPassword(SSH_PASSWORD);
    
            Properties config = new Properties();
            config.put("StrictHostKeyChecking", "no");
            sesion.setConfig(config);
    
            sesion.connect(); //ssh connection established!
    
            //by security policy, you must connect through a fowarded port          
            sesion.setPortForwardingL(LOCAl_PORT, MYSQL_REMOTE_SERVER, REMOTE_PORT);
    
    }
    }

**//通过SSH 隧道连接后 访问数据资源的服务器地址一般是 localhost或者主机内网IP，不用公网IP**  
private final static String MYSQL\_REMOTE\_SERVER = "123.mysql.com";

3.管理ssh连接

import javax.servlet.ServletContextEvent;
    import javax.servlet.ServletContextListener;
    import javax.servlet.annotation.WebListener;
    
    @WebListener
    public class MyContextListener implements ServletContextListener {
    
        private SSHConnection conexionssh;
    
    
        public MyContextListener() {
            super();
        }
    
        /**
         * @see ServletContextListener#contextInitialized(ServletContextEvent)
         */
        public void contextInitialized(ServletContextEvent arg0) {
            System.out.println("Context initialized ... !");
            try {
                conexionssh = new SSHConnection();
            } catch (Throwable e) {
                e.printStackTrace(); // error connecting SSH server
            }
        }
    
        /**
         * @see ServletContextListener#contextDestroyed(ServletContextEvent)
         */
        public void contextDestroyed(ServletContextEvent arg0) {
            System.out.println("Context destroyed ... !");
            conexionssh.closeSSH(); // disconnect
        }
    
    }

4.配置数据库，和你之前配置的一样，但是要修改一下连接地址

spring.datasource.url=jdbc:mysql://127.0.0.1:3307/test?characterEncoding=utf8
    spring.datasource.username=root
    spring.datasource.password=123456
    spring.datasource.driver-class-name=com.mysql.jdbc.Driver

说明：

原理是，程序在本机创建ssh连接，连接到ssh server，然后再发送数据库操作指令，指令会被转发到目标数据库服务器上，返回操作结果。

![0360fe10eaf7d02959f521f85af67633.png][]

注意：数据库连接地址由原来的123.mysql.com:3306改为127.0.0.1:3307,这样子，ssh连接会为每一个127.0.0.1:3307上的操作转发到123.mysql.com:3306上去，便可以正常操作数据库了。

**ssh连接的创建，可以采用私钥的方式，亦可以采用用户名密码的方式。**

**\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# 文章二 \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#**

通过 SSH 连接受限数据库

*  `pom.xml` 中添加 `JSch` 依赖

<dependency>
        <groupId>com.jcraft</groupId>
        <artifactId>jsch</artifactId>
        <version>${jsch.version}</version>
    </dependency>

*  创建 `SSH` 属性类

import org.springframework.boot.context.properties.ConfigurationProperties;
    
    import lombok.Getter;
    import lombok.Setter;
    import lombok.ToString;
    
    @Getter
    @Setter
    @ToString(exclude="password")
    @ConfigurationProperties(prefix="ssh")
    public class SshProperties {
    
        private String host;
        private Integer port;
        private String username;
        private String password;
        private Forward forward;
    
        @Getter
        @Setter
        @ToString
        public static class Forward {
    
            private String fromHost;
            private Integer fromPort;
            private String toHost;
            private Integer toPort;
    
        }
    
    }

*  创建 `SSH` 配置类

import javax.annotation.PreDestroy;
    
    import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
    import org.springframework.boot.context.properties.EnableConfigurationProperties;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.context.weaving.LoadTimeWeaverAware;
    import org.springframework.instrument.classloading.LoadTimeWeaver;
    
    import com.jcraft.jsch.JSch;
    import com.jcraft.jsch.JSchException;
    import com.jcraft.jsch.Session;
    
    import cn.comein.ssh.config.SshProperties.Forward;
    import lombok.extern.slf4j.Slf4j;
    
    @Configuration
    @EnableConfigurationProperties(SshProperties.class)
    @ConditionalOnProperty(prefix = "ssh", value = "enabled", havingValue = "true", matchIfMissing = false)
    @Slf4j
    // 实现 LoadTimeWeaverAware 接口是因为需要 SSH 正向代理需要在EntityManagerFactory加载前运行
    public class SshConfiguration implements LoadTimeWeaverAware {
    
        private final Session session;
    
        public SshConfiguration(SshProperties sshProperties) {
            Session session = null;
            try {
                // 可以自行为 JSch 添加日志，需要实现 com.jcraft.jsch.Logger 接口
                // JSch.setLogger(new JSchLogger())
                session = new JSch().getSession(sshProperties.getUsername(), sshProperties.getHost(), sshProperties.getPort());
                session.setConfig("StrictHostKeyChecking", "no");
                session.setPassword(sshProperties.getPassword());
                session.connect();
                Forward forward = sshProperties.getForward();
                if (forward != null) {
                    session.setPortForwardingL(forward.getFromHost(), forward.getFromPort(), forward.getToHost(), forward.getToPort());
                    log.info("{}:{} -> {}:{}", forward.getFromHost(), forward.getFromPort(), forward.getToHost(), forward.getToPort());
                }
            } catch (JSchException e) {
                log.error("ssh " + sshProperties.getHost() + " failed.", e);
            }
            this.session = session;
        }
    
        @PreDestroy
        // 配置销毁时，断开 SSH 链接
        public void disconnect() {
            if (session != null) {
                session.disconnect();
            }
        }
    
        @Override
        public void setLoadTimeWeaver(LoadTimeWeaver loadTimeWeaver) {
    
        }
    
    }

*  `application.properties`文件中添加 SSH 相关属性值

ssh.enabled=false # 是否启用 SSH 配置
    ssh.host=127.0.0.1 # SSH 地址
    ssh.port=22 # SSH 端口
    ssh.username= # SSH 用户名
    ssh.password= # SSH 密码
    ssh.forward.from_host= # 绑定的本地地址
    ssh.forward.from_port= # 绑定的本地端口
    ssh.forward.to_host= # 正向代理的远程地址
    ssh.forward.to_port= # 正向代理的远程端口

*  修改数据源为绑定的本地地址与端口

jdbc.url=jdbc:mysql://<from_host>:<from_port>/your_database_name

SSH 连接失败可能的原因

1.  用户名或密码错误
2.  `sshd_config` 中需要配置 `PasswordAuthentication yes`，允许使用密码登陆
3.  使用 `root` 用户登陆，需要配置 `PermitRootLogin yes`，才能登陆

[0360fe10eaf7d02959f521f85af67633.png]: /images/20220829/2069d3a4a4494b9a8011837ed6e3ebaa.png