需求:本地(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.
- 添加依赖
<!--mysql-ssh-->
<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;
/**
* @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();
}
}
- 添加监听器
并非所有情况都是用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 ... !");
}
}
}
- @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;
}
}
- 数据库配置
按照代码中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