Spring Boot SSH Mysql?

Question

I can successfully connect to my openshift mysql through workbench, how do I do the same through my spring boot application?

in my application.properties:

# Connection url for the database
spring.datasource.url = jdbc:mysql://<SSH_username>:<SSH_password>@<mysql_hostname>:<mysql_port>/<mysql_schema>

# Username and password
spring.datasource.username = <mysql_username>
spring.datasource.password = <mysql_password>

where do I supply my private key?

Answer 1

In order to get access from your local app to your Mysql server via SSH (such as using MySql-Openshift), the "only" extra thing you would need to do, is establish a previous SSH connection, before your DataSource object tries to get a connection.

As usual, and luckily, there are several ways to do that, but I would try to explain the simplest one that has worked for me.

1) Add Jcraft Library to your classpath (Used to work with SSH connections)

If you use Maven add to your pom.xml these elements:

   <dependency>
        <groupId>com.jcraft</groupId>
        <artifactId>jsch</artifactId>
        <version>0.1.53</version>
    </dependency>

or just download it from http://www.jcraft.com/jsch/

2) Create a class to connect to your SSH server (Here, we use the library imported in the previous step)

For example:

import com.jcraft.jsch.JSch;
import com.jcraft.jsch.Session;

public class SSHConnection {

private final static String S_PATH_FILE_PRIVATE_KEY = "C:\\Users\\Val\\.ssh\\privatekeyputy.ppk"; \\windows absolut path of our ssh private key locally saved
private final static String S_PATH_FILE_KNOWN_HOSTS = "C:\\Users\\Val\\.ssh\\known_hosts";
private final static String S_PASS_PHRASE = "mypassphrase";
private final static int LOCAl_PORT = 3307; 
private final static int REMOTE_PORT = 3306; 
private final static int SSH_REMOTE_PORT = 22; 
private final static String SSH_USER = "87a34c7f89f5cf407100093c";
private final static String SSH_REMOTE_SERVER = "myapp-mydomain.rhcloud.com";
private final static String MYSQL_REMOTE_SERVER = "127.6.159.102";

private Session sesion; //represents each ssh session

public void closeSSH ()
{
    sesion.disconnect();
}

public SSHConnection () throws Throwable
{

    JSch jsch = null;

        jsch = new JSch();
        jsch.setKnownHosts(S_PATH_FILE_KNOWN_HOSTS);
        jsch.addIdentity(S_PATH_FILE_PRIVATE_KEY, S_PASS_PHRASE.getBytes());

        sesion = jsch.getSession(SSH_USER, SSH_REMOTE_SERVER, SSH_REMOTE_PORT);
        sesion.connect(); //ssh connection established!

        //by security policy, you must connect through a fowarded port          
        sesion.setPortForwardingL(LOCAl_PORT, MYSQL_REMOTE_SERVER, REMOTE_PORT); 

}
}

This class keeps all relevant info for establishing an SSH connection. Notice that we only have defined two methods: the constructor, for instantiate the ssh connection, represented by the only non-static field of the class, and other, to get disconnected/close the ssh connection.

3) Define a listener that implements ServletContextListener interface (holding an object of the class defined in step 2)

Notice that we try to create a ssh connection when our app starts, closing it when our app dies.

@WebListener
public class MyContextListener implements ServletContextListener {

private SSHConnection conexionssh;


public MyContextListener() 
{
    super();
}

/**
 * @see ServletContextListener#contextInitialized(ServletContextEvent)
 */
public void contextInitialized(ServletContextEvent arg0) 
{
    System.out.println("Context initialized ... !");
    try 
        {
            conexionssh = new SSHConnection();
        } 
    catch (Throwable e) 
        {
            e.printStackTrace(); // error connecting SSH server
        }
}

/**
 * @see ServletContextListener#contextDestroyed(ServletContextEvent)
 */
public void contextDestroyed(ServletContextEvent arg0) 
{
    System.out.println("Context destroyed ... !");
    conexionssh.closeSSH(); // disconnect
}

4) Using Spring, set the DataSource object in your dispatcher-servlet.xml as usual, but pointing to your fowarded port

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="com.mysql.jdbc.Driver" />
    <property name="url" value="jdbc:mysql://localhost:3307/yourSchema" />
    <property name="username" value="admin4ajCbcWM" />
    <property name="password" value="dxvfwEfbyaPL-z" />
</bean>

And that's all. Another possibilty could be creating your own DataSource, extending the one provided by Spring, and adding it the ssh functionality. Maybe the solution detailed here, is better isolated.

Answer 2

In openshift you connect to the MySQL Database directly.

Openshift provides you a set of environment variables. See: https://developers.openshift.com/en/managing-environment-variables.html#database-variables

You need to use those to connect

inside your application.properties you can use environment variables like this:

spring.xxx=${OPENSHIFT_MYSQL_DB_HOST:localhost}

Where OPENSHIFT_MYSQL_DB_HOST is the environment variable name and localhost the default value.