springboot 使用SSH 通过A服务器跳板机连接B服务器Mysql(安全策略)

Love The Way You Lie 2023-06-25 03:13 7阅读 0赞

# 1.A与B配置SSH免密连接 #

A服务器 192.168.1.141

B服务器 192.168.1.143

### 1) 生成SSH密钥 ###

ssh-keygen -t rsa

直接回车到结束

回到 ~ 目录下

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDQ2MTI4MQ_size_16_color_FFFFFF_t_70][]

发现 .ssh 文件夹

![2019080113385536.png][]

生成秘钥成功

### 2)发送公钥建立连接 ###

ssh-copy-id 192.168.1.143

试登录一下B服务器(第一次连接需要密码)

ssh 192.168.1.143

如果出现  The authenticity of host 'XXX (XXX.XXX.X.XXX)' can't be established  的警告

执行命令(在本机执行即可):

ssh -o StrictHostKeyChecking=no  192.168.1.143

# 2.在A为B的Mysql创建SSH连接通道 #

在A服务器的3307端口 绑定B服务器的3306端口

ssh -fN -L3307:192.168.1.143:3306 -p22 root@192.168.1.143

ssh -fN -L(要绑定到的本地端口):(服务器B的Host):(服务器B上要访问的端口号) -p(服务器B的SSH端口，默认为22) (服务器B的用户):(服务器B的Host)

# 3.使用Navicat测试连接 #

使用A服务器的SSH连接通道

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDQ2MTI4MQ_size_16_color_FFFFFF_t_70 1][]

连接3307端口  A服务器通道会自动转发到B服务器的3306端口

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDQ2MTI4MQ_size_16_color_FFFFFF_t_70 2][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDQ2MTI4MQ_size_16_color_FFFFFF_t_70 3][]

# 4.使用 springboot 远程连接 B服务器的Mysql (通过A服务器) #

### 1)首先创建 test库 user表 并 插入一条user数据 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDQ2MTI4MQ_size_16_color_FFFFFF_t_70 4][]

### 2)创建springboot应用 ###

项目结构

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDQ2MTI4MQ_size_16_color_FFFFFF_t_70 5][]

工程文件 pom.xml

<modelVersion>4.0.0</modelVersion>
        <parent>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-parent</artifactId>
            <version>2.2.2.RELEASE</version>
            <relativePath/> 
        </parent>
        <groupId>com.lionli</groupId>
        <artifactId>ssh-demo</artifactId>
        <version>0.0.1-SNAPSHOT</version>
        <name>sshDemo</name>
        <description>使用SSH通道通过A服务器连接B服务器Mysql</description>
    
        <properties>
            <java.version>1.8</java.version>
        </properties>
    
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
            </dependency>
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <scope>runtime</scope>
            </dependency>
            <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
                <optional>true</optional>
            </dependency>
    
            
            <dependency>
                <groupId>com.baomidou</groupId>
                <artifactId>mybatis-plus-boot-starter</artifactId>
                <version>3.2.0</version>
            </dependency>
            
            <dependency>
                <groupId>com.jcraft</groupId>
                <artifactId>jsch</artifactId>
                <version>0.1.55</version>
            </dependency>
    
        </dependencies>
    
        <build>
            <plugins>
                <plugin>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-maven-plugin</artifactId>
                </plugin>
            </plugins>
        </build>

配置文件 application.yml (项目数据库查询使用 mybatis-plus 这里不多解释 感兴趣的可以自行百度)

spring:
      datasource:
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
        username: root
        password: root
        
    # SSH连接配置
    sshconfig:
      # 本地通信端口
      local-port: 3306
      # 远程连接端口
      remote-port: 3307
      ssh:
        # SSH用户名
        user: root
        # SSH密码
        password: root
        # SSH通信端口
        remote-port: 22
        # 远程A服务器跳板机IP
        remote-server: 192.168.1.141
      mysql:
        # Mysql服务
        remote-server: localhost
    
    # MyBatis配置
    mybatis-plus:
      mapper-locations: classpath*:mapper/**/*Mapper.xml
      #实体扫描，多个package用逗号或者分号分隔
      typeAliasesPackage: com.lionli
      configuration:
        map-underscore-to-camel-case: true
        cache-enabled: true
      global-config:
        #刷新mapper 调试神器
        refresh: true
        db-config:
          #主键类型  0:"数据库ID自增", 1:"用户输入ID",2:"全局唯一ID (数字类型唯一ID)", 3:"全局唯一ID UUID";
          id-type: auto
          #字段策略 0:"忽略判断",1:"非 NULL 判断"),2:"非空判断"
          field-strategy: not_empty
          #驼峰下划线转换
          db-column-underline: true
          #数据库大写下划线转换
          #capital-mode: true
          #序列接口实现类配置
          #key-generator: com.baomidou.springboot.xxx
          #逻辑删除配置
          logic-delete-value: 1
          logic-not-delete-value: 0
          #数据库类型
          db-type: mysql
        #自定义SQL注入器
        #sql-injector: com.baomidou.mybatisplus.mapper.LogicSqlInjector
        #自定义填充策略接口实现
        #meta-object-handler: com.baomidou.springboot.xxx

项目启动类  SshDemoApplication.java

@SpringBootApplication
    public class SshDemoApplication {
    
        public static void main(String[] args) {
            SpringApplication.run(SshDemoApplication.class, args);
        }
    
    }

配置 mybatis-plus

@EnableTransactionManagement(proxyTargetClass = true)
    @Configuration
    @MapperScan("com.lionli.**.mapper*")
    public class MybatisPlusConfig {
    
    	@Bean
    	public OptimisticLockerInterceptor optimisticLockerInterceptor() {
    		return new OptimisticLockerInterceptor();
    	}
    
    }

配置 SSH 工具 SSHConnection.java

@Component
    @Configuration
    public class SSHConnection {
    
        // 自定义的中转接口，需要和数据源接口设置一样
        @Value("${sshconfig.local-port}")
        private Integer localPort;
        // 服务端的数据库端口
        @Value("${sshconfig.remote-port}")
        private Integer remotePort;
        // 服务器端SSH端口 默认是22
        @Value("${sshconfig.ssh.remote-port}")
        private Integer sshRemotePort;
        // SSH用户名
        @Value("${sshconfig.ssh.user}")
        private String sshUser;
        // SSH使用密码
        @Value("${sshconfig.ssh.password}")
        private String sshPassword;
        // 连接到哪个服务端的SSH
        @Value("${sshconfig.ssh.remote-server}")
        private String sshRemoteServer;
        // 服务端的本地mysql服务
        @Value("${sshconfig.mysql.remote-server}")
        private String mysqlRemoteServer;
    
        //represents each ssh session
        private Session sesion;
    
        /**
         * 创建SSH连接
         */
        public void createSSH() throws Throwable {
    
            JSch jsch = new JSch();
            // 需要用到了开启
            sesion = jsch.getSession(sshUser, sshRemoteServer, sshRemotePort);
            sesion.setPassword(sshPassword);
    
            Properties config = new Properties();
            config.put("StrictHostKeyChecking", "no");
            sesion.setConfig(config);
            // 去连接
            sesion.connect(); //ssh connection established!
            //  设置转发
            sesion.setPortForwardingL(localPort, mysqlRemoteServer, remotePort);
        }
    
        /**
         * 关闭SSH连接
         */
        public void closeSSH() {
            sesion.disconnect();
        }
    
    }

监听Servlet事件 MyContextListener.java

@Slf4j
    @WebListener
    @Component
    public class MyContextListener implements ServletContextListener {
    
        @Autowired
        private SSHConnection conexionssh;
    
        /**
         * 监听Servlet初始化事件
         */
        @Override
        public void contextInitialized(ServletContextEvent arg0) {
            // 建立连接
            try {
                conexionssh.createSSH();
                log.info("成功建立SSH连接！");
            } catch (Throwable e) {
                log.info("SSH连接失败！");
                e.printStackTrace(); // error connecting SSH server
            }
        }
        
        /**
         * 监听Servlet终止事件
         */
        @Override
        public void contextDestroyed(ServletContextEvent arg0) {
            // 断开连接
            try {
                conexionssh.closeSSH(); // disconnect
                log.info("成功断开SSH连接!");
            } catch (Exception e) {
                e.printStackTrace();
                log.info("断开SSH连接出错！");
            }
        }
    }

User.java (这里使用lombok)

@Data
    @ToString
    @TableName("user")
    public class User {
        @TableId(value = "id", type = IdType.AUTO)
        private Long id;
        @TableField(value = "user_name")
        private String userName;
        @TableField(value = "password")
        private String password;
    
    }

UserMapper.java (这里为了方便测试直接用Mapper了)

/**
     * 
     * @author Lion Li
     * @date 2019-12-09
     */
    public interface UserMapper extends BaseMapper<User> {
    
    }

UserController.java (这里为了方便测试直接用Mapper了)

@RestController
    public class UserController {
        @Autowired
        private UserMapper userMapper;
    
        /**
         * 获取全部用户列表
         */
        @GetMapping("/getUserList")
        public List<User> test(){
            return userMapper.selectList(new QueryWrapper<>());
        }
    }

### 3)启动测试应用 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDQ2MTI4MQ_size_16_color_FFFFFF_t_70 6][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDQ2MTI4MQ_size_16_color_FFFFFF_t_70 7][]

测试成功

项目已上传到gitee

地址: [springboot-ssh-mysql-demo][]

如果帮到您了,请帮忙点个star

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDQ2MTI4MQ_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20190801133823739.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDQ2MTI4MQ==,size_16,color_FFFFFF,t_70
[2019080113385536.png]: https://img-blog.csdnimg.cn/2019080113385536.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDQ2MTI4MQ_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20191225113534978.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDQ2MTI4MQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDQ2MTI4MQ_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20191225113642801.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDQ2MTI4MQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDQ2MTI4MQ_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20191225113752846.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDQ2MTI4MQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDQ2MTI4MQ_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20191225114059580.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDQ2MTI4MQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDQ2MTI4MQ_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/2019122511494629.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDQ2MTI4MQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDQ2MTI4MQ_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20191225122010326.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDQ2MTI4MQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDQ2MTI4MQ_size_16_color_FFFFFF_t_70 7]: https://img-blog.csdnimg.cn/20191225122044166.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDQ2MTI4MQ==,size_16,color_FFFFFF,t_70
[springboot-ssh-mysql-demo]: https://gitee.com/JavaLionLi/LionLi-Labs/tree/master/springboot-ssh-mysql-demo