java使用ssh通道连接mysql

白玉神驹 / 2024-03-08 / 原文

添加依赖

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

添加配置

import com.jcraft.jsch.JSch;
import com.jcraft.jsch.JSchException;
import com.jcraft.jsch.Session;
import org.springframework.stereotype.Component;

import java.util.Properties;

/**
 *  db1连接配置 ssh
 */
@Component
public class Ds1SSHConnectionConfig {
    //本地的ssh中的knownhost文件路径
    private String SSH_PATH_FILE_KNOWN_HOSTS = "C:\\Users\\HUAWEI\\.ssh\\known_hosts";
    private String SSH_PATH_FILE_PRIVATE_KEY = "C:\\Users\\HUAWEI\\.ssh\\id_rsa";
    private String SSH_USER = "user";
    //ssh远程连接的ip地址
    private String SSH_REMOTE_SERVER = "1.15.xx.xxx";
    //ssh连接的端口号，一般默认为22
    private Integer SSH_REMOTE_PORT = 22;
    //ssh通行短语 如果不为空需要替换为【设置私钥】第一个构造方法
    private String SSH_PASS_PHRASE = "";
    //本地mysql发起连接的IP地址
    private String MYSQL_REMOTE_SERVER = "192.168.0.2";
    //本地数据库连接时用的端口号(不能填3306)
    private Integer LOCAl_PORT = 3307;
    //远程数据库端口用的端口号
    private Integer REMOTE_PORT = 3306;

    //com.jcraft.jsch.Session;
    private Session session;

    //关闭ssh连接
    public void closeSSH() {
        session.disconnect();
    }

    //创建ssh连接
    public void createSSH() throws JSchException {
        JSch jSch = new JSch();

        //下面这两个设置是在公钥模式需要设置的，非公钥模式不需要进行设置
        //设置known_hosts文件路径，如：~/.ssh/known_hosts（known_hosts中存储是已认证的远程主机host key）
        jSch.setKnownHosts(SSH_PATH_FILE_KNOWN_HOSTS);
        //设置私钥
//        jSch.addIdentity(SSH_PATH_FILE_PRIVATE_KEY, SSH_PASS_PHRASE);
        jSch.addIdentity(SSH_PATH_FILE_PRIVATE_KEY);

        session = jSch.getSession(SSH_USER, SSH_REMOTE_SERVER, SSH_REMOTE_PORT);

        //如果是密码模式需要设置密码
        //session.setPassword(sshPassword);

        //设置连接过程不校验known_hosts文件中的信息
        Properties config = new Properties();
        config.put("StrictHostKeyChecking", "no");
        session.setConfig(config);

        //ssh 建立连接！
        session.connect();

        //根据安全策略，您必须通过转发端口进行连接
        session.setPortForwardingL(LOCAl_PORT, MYSQL_REMOTE_SERVER, REMOTE_PORT);
    }
}

添加监听器

import org.springframework.stereotype.Component;

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

/**
 * 监听器
 */
@Component//尽量加上这个
@WebListener//声明为监听器
public class Ds1ContextListener implements ServletContextListener {
    private Ds1SSHConnectionConfig sshConnectionConfig;

    public Ds1ContextListener() {
        super();
    }

    @Override
    public void contextInitialized(ServletContextEvent sce) {
        System.out.println("DS:1 context initialized ... !");
        try {
            sshConnectionConfig = new Ds1SSHConnectionConfig();
            sshConnectionConfig.createSSH();
        } catch (Throwable e) {
            e.printStackTrace(); // 连接失败
        }

    }

    @Override
    public void contextDestroyed(ServletContextEvent sce) {
        System.out.println("DS:1 context destroyed ... !");
        sshConnectionConfig.closeSSH();//断开ssh连接
    }
}

修改数据库连接地址

spring:
  datasource:
    ds1:
      type: com.alibaba.druid.pool.DruidDataSource
      driver-class-name: com.mysql.cj.jdbc.Driver
      jdbc-url: jdbc:mysql://127.0.0.1:3307/test_db?characterEncoding=utf-8&autoReconnect=true&failOverReadOnly=false&useSSL=false&serverTimezone=GMT%2B8&allowMultiQueries=true