背景:测试环境连接生产环境的数据库,无法本地调试
环境:
JDK8
Maven:3.6.3
Springboot:2.1.4
jsch:0.1.55
Jsch百度百科介绍:JSch 是SSH2的一个纯Java实现。它允许你连接到一个sshd 服务器,使用端口转发,X11转发,文件传输等等。
Jsch Maven依赖包:
<!-- https://mvnrepository.com/artifact/com.jcraft/jsch -->
<dependency>
<groupId>com.jcraft</groupId>
<artifactId>jsch</artifactId>
<version>0.1.55</version>
</dependency>
连接配置工具类:
import com.jcraft.jsch.JSch;
import com.jcraft.jsch.Session;
import java.util.Properties;
/**
* @author dcf
* @date 2022-09-23 17:51
**/
public class SshConnectionTool {
//ssh连接的用户名
private final static String SSH_USER = “XXX";
//ssh连接的密码
private final static String SSH_PASSWORD = “****";
//ssh远程连接的ip地址
private final static String SSH_REMOTE_SERVER = “XXX.XXX.XXX.XXX";
//ssh连接的端口号
private final static int SSH_REMOTE_PORT = 22;
//本地mysql发起连接的数据库IP地址
private final static String MYSQL_REMOTE_SERVER = "XXX.XXX.XXX.XXX";
//本地数据库连接时用的端口号
private final static int LOCAl_PORT = 3310;
//远程数据库端口用的端口号
private final static int REMOTE_PORT = 3306;
private Session sesion; //ssh 会话
public void closeSSH ()
{
sesion.disconnect();
}
public SshConnectionTool () throws Throwable
{
JSch jsch = new JSch();
sesion = jsch.getSession(SSH_USER, SSH_REMOTE_SERVER, SSH_REMOTE_PORT);
sesion.setPassword(SSH_PASSWORD);
//设置连接过程不校验known_hosts文件中的信息
Properties config = new Properties();
config.put("StrictHostKeyChecking", "no");
sesion.setConfig(config);
sesion.connect(); //ssh 建立连接!
//根据安全策略,您必须通过转发端口进行连接
sesion.setPortForwardingL(LOCAl_PORT, MYSQL_REMOTE_SERVER, REMOTE_PORT);
}
}
会话监听类:
import org.springframework.stereotype.Component;
import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;
/**
* @author dcf
* @date 2022-09-23 17:55
**/
@Component
public class SshContextListener implements ServletContextListener{
private SshConnectionTool conexionssh;
public SshContextListener() {
super();
}
/**
* @see ServletContextListener#contextInitialized(ServletContextEvent)
*/
@Override
public void contextInitialized(ServletContextEvent arg0) {
System.out.println("Context initialized ... !");
try {
conexionssh = new SshConnectionTool();
} catch (Throwable e) {
e.printStackTrace(); // 连接失败
}
}
/**
* @see ServletContextListener#contextDestroyed(ServletContextEvent)
*/
@Override
public void contextDestroyed(ServletContextEvent arg0) {
System.out.println("Context destroyed ... !");
conexionssh.closeSSH(); // 断开连接
}
}
需要连接的mysql地址配置:
td-sql:
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.cj.jdbc.Driver
jdbcUrl: jdbc:mysql://127.0.0.1:3310/test?allowMultiQueries=true&useSSL=false&serverTimezone=Asia/Shanghai
username: name
password: password
注:mysql地址配置中的127.0.0.1,其实在连接配置工具类会话方法中默认的,当然里面有多个重载方法,根据不同的重载方法进行不同的配置mysql地址和端口号
public int setPortForwardingL(int lport, String host, int rport) throws JSchException {
return this.setPortForwardingL("127.0.0.1", lport, host, rport);
}
public int setPortForwardingL(String bind_address, int lport, String host, int rport) throws JSchException {
return this.setPortForwardingL(bind_address, lport, host, rport, (ServerSocketFactory)null);
}
public int setPortForwardingL(String bind_address, int lport, String host, int rport, ServerSocketFactory ssf) throws JSchException {
return this.setPortForwardingL(bind_address, lport, host, rport, ssf, 0);
}