Connect to Remote MySQL Database Through Ssh Using Java

Connect to remote MySQL database through SSH using Java

My understanding is that you want to access a mysql server running on a remote machine and listening on let's say port 3306 through a SSH tunnel.

To create such a tunnel from port 1234 on your local machine to port 3306 on a remote machine using the command line ssh client, you would type the following command from your local machine:

ssh -L 1234:localhost:3306 mysql.server.remote

To do the same thing from Java, you could use JSch, a Java implementation of SSH2. From its website:

JSch allows you to connect to an sshd server and use port forwarding, X11 forwarding, file transfer, etc., and you can integrate its functionality into your own Java programs. JSch is licensed under BSD style license.

For an example, have a look at PortForwardingL.java. Once the session connected, create your JDBC connection to MySQL using something like jdbc:mysql://localhost:1234/[database] as connection URL.

Connecting to MySql database with SSH tunneling on remote host with specific mysql host

Well it was wrong way of port forwarding that was the root cause of the problem:

instead of:

int assinged_port = session.setPortForwardingL(localPort, remoteHost, remotePort);

it should have been

int assinged_port = session.setPortForwardingL(localPort, localSSHUrl, remotePort);

The connection runs fine now.

Unable to connect to MySQL Database with Java through SSH

Solution by OP.

The reason SSH wasn't working was because I needed to use a local open port, connect to SQL through that, then forward that to the remote port 3306. Also, I needed to port forward through localhost, not the server address.
(That is, change the session.setPortForwardingL(3306, ServerIP, 3306); line to session.setPortForwardingL(some-open-port, "localhost", 3306); and "jdbc:mysql://%s:3306/db_name" to "jdbc:mysql://%s:same-open-port/db_name".

Connecting to MySQL DB using SSH not successful through code but successful through MySQL Workbench

OK, I have figured out what was my problem and now I solved it and it is working as expected:

First I have not used the forwarded port which I have created when establishing ssh tunneling connection:

public class SQL {
    private final String MYSQL_HOSTNAME_CONNECTION = EnvConf.getProperty("mysql.hostname");
    private static final String sshUser = EnvConf.getProperty("ssh.username");
    private static final String sshHost = EnvConf.getProperty("ssh.hostname");
    private static int forwardedPort;

    public static void main(String[] args) throws JSchException {
        establishSSHConnection();
        getJDBCRemoteConnection();
    }

    private static void establishSSHConnection() {
        JSch jsch = new JSch();
        Session session = null;
        try {
            jsch.addIdentity(PATH_TO_PEM_FILE);
            session = jsch.getSession(sshUser, sshHost);
            Properties config = new Properties();
            config.put("StrictHostKeyChecking", "no");
            session.setConfig(config);
            session.connect();
            forwardedPort = session.setPortForwardingL( 0, MYSQL_HOSTNAME_CONNECTION , 3306);
            Channel channel = session.openChannel("shell");
            channel.connect();
            System.out.println("Connected!!!");
        } catch (Exception e) {
            e.printStackTrace(System.out);
        }

    }

    private static Connection getJDBCRemoteConnection() {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String dbName = "dbName"
            String userName = "root";
            String password = "password";
            
            String port = "3306";
            String jdbcUrl = "jdbc:mysql://localhost:" + forwardedPort + "/" + dbName + "?user=" + userName + "&password=" + password;

            Connection con = DriverManager.getConnection(jdbcUrl);
            return con;
        } catch (ClassNotFoundException | SQLException e) {
            System.out.println(e.toString());
        }
        return null;
    }
}

---

Related Topics