Spring Boot SSH连接数据库

需求:本地(Win10)测试连接服务器失败(报错信息如下),对比本地navicat后发下使用的是SSH连接。因此,java也需要使用SSH方式连接。
注意:由于本地3306端口被Navicat占用,所以程序中随意换了一个端口。只要保证数据库配置中的IP和端口与程序中设置的IP和端口一直就行。
在这里插入图片描述
在这里插入图片描述

错误信息:

2021-09-28 18:46:08.436 main [] DEBUG com.zaxxer.hikari.pool.PoolBase.newConnection:368 - HikariPool-1 - Failed to create/setup connection: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
2021-09-28 18:46:08.448 main [] DEBUG com.zaxxer.hikari.pool.HikariPool.createPoolEntry:499 - HikariPool-1 - Cannot acquire connection from data sourcecom.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
  1. 添加依赖
    <!--mysql-ssh-->
    <dependency>
      <groupId>com.jcraft</groupId>
      <artifactId>jsch</artifactId>
      <version>0.1.55</version>
    </dependency>
  1. 创建连接
import com.jcraft.jsch.JSch;
import com.jcraft.jsch.Session;

import java.util.Properties;

/**
 * @description: mysql-ssh连接
 * https://blog.csdn.net/weixin_45248492/article/details/118406072
 * https://blog.csdn.net/weixin_40461281/article/details/103695882
 * @date 2021/9/22 14:48
 */
public class SSHConnection {

	/**
	 * 需要时打开
	 */
//	private final static String S_PATH_FILE_PRIVATE_KEY = "/Users/xxx/.ssh/id_rsa";
	/**
	 * 需要时打开
	 */
//	private final static String S_PATH_FILE_KNOWN_HOSTS = "/Users/xxx/.ssh/id_rsa/.ssh/known_hosts";

	/**
	 * linux服务器登录名
	 */
	private final static String SSH_USER = "root";
	/**
	 * linux登陆密码
	 */
	private final static String SSH_PASSWORD = "123456";
	/**
	 *linux服务器公网IP
	 */
	private final static String SSH_REMOTE_SERVER = "192.168.1.216";
	/**
	 *跳板机ssh开放的接口,ssh通道端口   默认端口 22
	 */
	private final static int SSH_REMOTE_PORT = 22;
	/**
	 *服务器上数据库端口号
	 */
	private final static int REMOTE_PORT = 3306;
	/**
	 *这个是本地的端口(即配置文件中数据源的PORT),选取一个没有占用的port即可
	 */
	private final static int LOCAL_PORT = 3307;
	/**
	 *要访问的mysql所在的ip (即配置文件中数据源的HOST, 本地(Windows)测试只能使用localhost)
	 */
	private final static String MYSQL_REMOTE_SERVER = "localhost";

	private Session session = null;
	/**
	 *    建立SSH连接
	 */
	public SSHConnection() throws Throwable{
		JSch jsch = new JSch();
		// 需要时开启
		// jsch.setKnownHosts(S_PATH_FILE_KNOWN_HOSTS);
		//jsch.addIdentity(S_PATH_FILE_PRIVATE_KEY);
		session = jsch.getSession(SSH_USER, SSH_REMOTE_SERVER, SSH_REMOTE_PORT);
		session.setPassword(SSH_PASSWORD);
		Properties config = new Properties();
		config.put("StrictHostKeyChecking", "no");
		session.setConfig(config);
		session.connect();
		session.setPortForwardingL(LOCAL_PORT, MYSQL_REMOTE_SERVER, REMOTE_PORT);
	}
	/**
	 *    断开SSH连接
	 */
	public void closeSSH (){
		this.session.disconnect();
	}
}
  1. 添加监听器
    并非所有情况都是用SSH连接的方式,所以要按照条件(@Profile(value = “dev”)和@Conditional({SSHConnectionCondition.class}))注册监听器
import com.healsci.common.condition.SSHConnectionCondition;
import com.healsci.common.toolkit.SSHConnection;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.context.annotation.Conditional;
import org.springframework.context.annotation.Profile;
import org.springframework.stereotype.Component;

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

/**
 * @description: mysql-ssh listener
 * @date 2021/9/22 14:56
 */
@Profile(value = "dev")
@Conditional({SSHConnectionCondition.class})
@WebListener
@Component
public class SSHConnectionListener implements ServletContextListener {

	private static final Logger logger = LoggerFactory.getLogger(SSHConnectionListener.class);

	private SSHConnection sshConnection;

	public SSHConnectionListener() {
		super();
	}

	@Override
	public void contextInitialized(ServletContextEvent arg0) {
		// 建立连接
		try {
			sshConnection = new SSHConnection();
			logger.info("SSHConnectionListener initialized ... !");
		} catch (Throwable e) {
			logger.error("SSHConnectionListener create connection failed ... !");
			e.printStackTrace();
		}
	}

	@Override
	public void contextDestroyed(ServletContextEvent arg0) {
		// 断开连接
		try {
			// disconnect
			sshConnection.closeSSH();
			logger.info("SSHConnectionListener destroyed ... !");
		} catch (Exception e) {
			e.printStackTrace();
			logger.error("SSHConnectionListener disconnect failed ... !");
		}
	}
}
  1. @Conditional的判断逻辑
import org.apache.commons.lang3.StringUtils;
import org.springframework.context.annotation.Condition;
import org.springframework.context.annotation.ConditionContext;
import org.springframework.core.env.Environment;
import org.springframework.core.type.AnnotatedTypeMetadata;

/**
 * @description:  目前仅测试环境216服务器上的mysql用到SSH连接
 * @date 2021/9/22 15:39
 */
public class SSHConnectionCondition implements Condition {

	private static final String SSH_HOST_KEY = "datasource.staging.jdbc-url";
	private static final String SSH_HOST_VALUE = "localhost";
	@Override
	public boolean matches(ConditionContext context, AnnotatedTypeMetadata metadata) {
		Environment environment = context.getEnvironment();
		String property = environment.getProperty(SSH_HOST_KEY);
		if (StringUtils.isNotBlank(property) && property.contains(SSH_HOST_VALUE)){
			return true;
		}
		return false;
	}
}
  1. 数据库配置
    按照代码中SSH映射关系配置数据库访问的IP和端口,到时会转发给目标服务器
datasource.staging.driver-class-name=com.mysql.cj.jdbc.Driver
datasource.staging.jdbc-url=jdbc:mysql://localhost:3307/${DB_NAME_STAGING:staging}?useUnicode=true&characterEncoding=utf-8&useSSL=false&allowMultiQueries=true&serverTimezone=GMT%2B8
datasource.staging.username=admin
datasource.staging.password=123456
  • 2
    点赞
  • 5
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值