CODEWITHSUNDEEP
javamysqlspringspring-bootssh
Akshay Lokur
Akshay Lokur

Reputation: 7506

Connect to MySQL through SSH and by using Spring Boot

In my Spring Bootapp WAR, I have an application.properties file wherein I have defined following datasource properties:

spring.datasource.url=jdbc:mysql://localhost/test?autoReconnect=true&useSSL=false&rewriteBatchedStatements=true
spring.datasource.username=used
spring.datasource.password=password
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

with this, I am able to connect to the database.

Now, I want to connect to another database through SSH tunnel. How should I specify spring.datasource.url to get such connection?

Please note I don't want to write any boiler plate Java code to do this SSH tunneling!

P.S. I Googled but could not find satisfactory answer anywhere.

Upvotes: 7

Views: 20055

Answers (4)

Abbas
Abbas

Reputation: 251

I try this dependency and config. Is is worked

<dependency>
    <groupId>com.hierynomus</groupId>
    <artifactId>sshj</artifactId>
    <version>0.30.0</version>
</dependency>

import com.hierynomus.sshj.SSHClient;
import com.hierynomus.sshj.connection.ConnectionException;
import com.hierynomus.sshj.connection.channel.direct.Session;
import com.hierynomus.sshj.transport.TransportException;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import javax.sql.DataSource;
import java.io.IOException;
import java.sql.SQLException;

@Configuration
public class DatabaseConfig {

    @Bean
    public SSHClient sshClient() throws IOException {
        SSHClient sshClient = new SSHClient();
        sshClient.addHostKeyVerifier((hostname, port, key) -> true); // Skip host key verification (for demonstration only)
        sshClient.connect("your-ssh-server-hostname");
        sshClient.authPassword("ssh-username", "ssh-password");
        return sshClient;
    }

    @Bean
    public DataSource dataSource(SSHClient sshClient) throws IOException, TransportException, ConnectionException, SQLException {
        int localPort = 3306; // Local port to tunnel through
        sshClient.setPortForwardingL(localPort, "database-hostname", 3306); // Tunnel MySQL's port 3306
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://localhost:" + localPort + "/your-database-name");
        dataSource.setUsername("database-username");
        dataSource.setPassword("database-password");
        return dataSource;
    }
}

Upvotes: 1

bamossza
bamossza

Reputation: 3926

same problem with me.

my solution resolved,

Step 1: create ec2 instance inside VPC public subnet

Step 2: in the security groups this ec2 instance must be allow port 22 (SSH)

Step 3: RDS MySQL must be allow private-ip for ec2 instance to access

Step 4: In Dev Local ~> setup forward listener tcp_ip open terminal

ssh -f <user>@<ec2-public-dns> -L <localhost_ip>:<localhost_port>:<rds_mysql_host_ip>:<rds_mysql_host_port> -N -i <private_key>.pem

Example:

ssh -f ec2-user@ec2-public-dns -L dev-mysql-c-lib.internal:3306:***.ap-southeast-1.rds.amazonaws.com:3306 -N -i ~/.ssh/ec2-instance.pem

enter image description here

Step 5: in file config application-dev.yml

changed host from public-dns to internal-dns

Example:

host: '***.ap-southeast-1.rds.amazonaws.com'

to

host: 'dev-mysql-c-lib.internal'

spring.datasource.url: jdbc:mysql://dev-mysql-c-lib.internal:3306/mydb

Finally to Running Spring Boot

Lookup MySQL Connected

Work for me. ^____^

Upvotes: 6

papkass
papkass

Reputation: 1289

I don't think spring can create a SSH tunnel for you if that's what you're looking for. If you want to do it in the code, you need a framework like jsch.

The best solution would be to create the SSH tunnel on the server manually or by using autossh (to keep it up). To create a tunnel manually, log on to your app server and run this command in a screen:

ssh -L 3307:localhost:3306 serverIp

3306 is the port mysql is running on. 3307 is the port you are forwarding. With this tunnel you can now connect by using:

spring.datasource.url=jdbc:mysql://localhost:3307/

Upvotes: 14

G&#225;bor Paller
G&#225;bor Paller

Reputation: 185

The same as you used in your question except useSSL=true and possibly verifyServerCertificate=false if your database uses self-signed certificate.

Upvotes: -3

Related Questions