Spring Boot 使用SSH 远程连接 MySql

配置pom文件

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

yml 配置

# 3307 是转发端口，不能冲突
spring:
  datasource: 
    url: jdbc:mysql://127.0.0.1:3307/db

mysql:
  ssh:
    host: ip
    user: user
    password: password
    port: 22
    local-port: 3307
    remote-host: 172.21.31.2
    remote-port: 3306

创建Config文件

import lombok.Data;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.context.annotation.Configuration;

@Configuration
@ConditionalOnProperty(name = "spring.profiles.active", havingValue = "dev")
@Data
public class SSHMySqlConfig {

    @Value("${mysql.ssh.host}")
    private String host;

    @Value("${mysql.ssh.user}")
    private String sshUser;

    @Value("${mysql.ssh.password}")
    private String sshPwd;

    @Value("${mysql.ssh.port}")
    private int sshPort;

    @Value("${mysql.ssh.remote-host}")
    private String remoteHost;

    @Value("${mysql.ssh.local-port}")
    private int localPort;

    @Value("${mysql.ssh.remote-port}")
    private int remotePort;
}

获取Session做转发

import com.jcraft.jsch.JSch;
import com.jcraft.jsch.Session;


public class SSHMySqlConnection {
    Session session = null;
    /**
    *    建立SSH连接
    */
    public SSHMySqlConnection(String sshUser, String sshPwd, int sshPort, String host, int localPort, int remotePort, String remoteHost) {
        try {
            JSch jsch = new JSch();
            session = jsch.getSession(sshUser, host, sshPort);
            session.setPassword(sshPwd);
            session.setConfig("StrictHostKeyChecking", "no");
            session.connect();
            session.setPortForwardingL(localPort, remoteHost, remotePort);
        } catch (Exception e) {
            // do something
        }
    }
    
    /**
    *    断开SSH连接
    */
    public void closeSSH () {
        this.session.disconnect();
    }
}

创建连接

import cn.hutool.core.util.StrUtil;
import cn.hutool.extra.spring.SpringUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Component;

import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;
import javax.servlet.annotation.WebListener;


@WebListener
@Component
@Slf4j
public class SSHMySqlContextListener implements ServletContextListener {
    private SSHMySqlConnection conexionssh;
    public SSHMySqlContextListener() {
        super();
    }
    /**
     * @see ServletContextListener#contextInitialized(ServletContextEvent)
     */
    @Override
    public void contextInitialized(ServletContextEvent arg0) {
        // 建立连接
        if(StrUtil.equals(SpringUtil.getActiveProfile(), "prod")) {
            log.info("prod 不加载 MYSQL SSH 配置");
            return ;
        }
        log.info(SpringUtil.getActiveProfile() + " 开始加载 MYSQL SSH 配置");
        try {
            SSHMySqlConfig mysqlConfig = SpringUtil.getBean(SSHMySqlConfig.class);
            conexionssh = new SSHMySqlConnection(mysqlConfig.getSshUser(), mysqlConfig.getSshPwd(), mysqlConfig.getSshPort(),
                    mysqlConfig.getHost(), mysqlConfig.getLocalPort(), mysqlConfig.getRemotePort(),  mysqlConfig.getRemoteHost());
            log.info("成功建立 MYSQL SSH 连接！");
        } catch (Throwable e) {
            log.info(" MYSQL SSH 连接失败！");
            e.printStackTrace();
        }
    }
    /**
     * @see ServletContextListener#contextDestroyed(ServletContextEvent)
     */
    @Override
    public void contextDestroyed(ServletContextEvent arg0) {
        // 断开连接
        log.info("Context destroyed ... !");
        try {
            conexionssh.closeSSH();
            log.info("成功断开 MYSQL SSH 连接!");
        } catch (Exception e) {
            e.printStackTrace();
            log.info("断开 MYSQL SSH 连接出错！");
        }
    }
}