ssh通道连接mysql数据库

注：办公需要内网开发

      1. navicat可以通过ssh通道连接mysql数据库

      2. 带注释处均为需要修改项

 

1.java中实现及springboot配置如下

1.1 pom.xml依赖

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

1.2 连接ssh类

import com.jcraft.jsch.JSch;
import com.jcraft.jsch.Session;
import java.util.Properties;

public class SSHConnection {
    private final static int LOCAl_PORT = 3307;//本地端口
    private final static int REMOTE_PORT = xxxx;//开发DB端口
    private final static int SSH_REMOTE_PORT = sshport;//ssh端口
    private final static String SSH_USER = "sshuser";//ssh账号
    private final static String SSH_PASSWORD = "sshpassword";//ssh密码
    private final static String SSH_REMOTE_SERVER = "sshurl";//ssh ip
    private final static String MYSQL_REMOTE_SERVER = "DBurl";//数据库ip
    private Session sesion;
    public void closeSSH() {
        sesion.disconnect();
    }
    public SSHConnection() throws Throwable {
        JSch jsch = new JSch();
        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(); 
                 
        sesion.setPortForwardingL(LOCAl_PORT, MYSQL_REMOTE_SERVER, REMOTE_PORT);
    }
}

1.3 管理ssh连接

import org.springframework.context.annotation.Profile;
import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;
import javax.servlet.annotation.WebListener;

@WebListener
@Profile("test")//配置生效application-test.properties文件（本地）
public class MyContextListener implements ServletContextListener {

    private SSHConnection conexionssh;

    public MyContextListener() {
        super();
    }
    
    @Override
    public void contextInitialized(ServletContextEvent arg0) {
        try {
            conexionssh = new SSHConnection();
        } catch (Throwable e) {
            e.printStackTrace(); 
        }
    }
    
    @Override
    public void contextDestroyed(ServletContextEvent arg0) { 
        conexionssh.closeSSH(); 
    }
}

1.4 配置数据库

spring.datasource.url = jdbc:mysql://127.0.0.1:3307/xxx?useUnicode=true&characterEncoding=utf8&useSSL=false
spring.datasource.username = xxx
spring.datasource.password = xxxxxx

1.5 @ServletComponentScan注解配置

服务启动类添加@ServletComponentScan注解，使得MyContextListener类的@WebListener注解自动注册。

@ServletComponentScan(value= "com.xxx.xxx")//指向上面两个类所在包

2.本地navicat可以通过ssh通道连接mysql数据库

参考地址：

https://www.cnblogs.com/hdwang/p/8670594.html