Connect to remote MySQL database through SSH using Java
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;
import com.jcraft.jsch.JSch;
import com.jcraft.jsch.JSchException;
import com.jcraft.jsch.Session;
public class CTestDriver {
private static void doSshTunnel(String strSshUser, String strSshPassword, String strSshHost, int nSshPort,
String strRemoteHost, int nLocalPort, int nRemotePort) throws JSchException {
final JSch jsch = new JSch();
Session session = jsch.getSession(strSshUser, strSshHost, 22);
session.setPassword(strSshPassword);
final Properties config = new Properties();
config.put("StrictHostKeyChecking", "no");
session.setConfig(config);
session.connect();
session.setPortForwardingL(nLocalPort, strRemoteHost, nRemotePort);
}
public static void main(String[] args) {
try {
String strSshUser = "ssh_user_name"; // SSH loging username
String strSshPassword = "abcd1234"; // SSH login password
String strSshHost = "your.ssh.hostname.com"; // hostname or ip or
// SSH server
int nSshPort = 22; // remote SSH host port number
String strRemoteHost = "your.database.hostname.com"; // hostname or
// ip of
// your
// database
// server
int nLocalPort = 3366; // local port number use to bind SSH tunnel
int nRemotePort = 3306; // remote port number of your database
String strDbUser = "db_user_name"; // database loging username
String strDbPassword = "4321dcba"; // database login password
CTestDriver.doSshTunnel(strSshUser, strSshPassword, strSshHost, nSshPort, strRemoteHost, nLocalPort,
nRemotePort);
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:" + nLocalPort, strDbUser,
strDbPassword);
con.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
System.exit(0);
}
}
}
While the existing answers are correct, they obscure the significant code in bloats of other code.
This is the basic code you need to tunnel your JDBC (or any other) database connection through an SSH channel:
String jumpserverHost = "ssh.example.com";
String jumpserverUsername = "sshuser";
// The hostname/IP address and port, you would use on the SSH server
// to connect to the database.
// If the database runs on the same machine as the SSH server, use "localhost".
String databaseHost = "database.example.com";
int databasePort = 3306;
String databaseUsername = "dbuser";
String databasePassword = "dbpass";
JSch jsch = new JSch();
// Public key authentication example
// (but you can use password authentication, if appropriate).
jsch.addIdentity("~/.ssh/id_rsa");
// Connect to SSH jump server (this does not show an authentication code)
Session session = jsch.getSession(jumpserverUsername, jumpserverHost);
session.connect();
// Forward randomly chosen local port through the SSH channel to database host/port
int forwardedPort = session.setPortForwardingL(0, databaseHost, databasePort);
// Connect to the forwarded port (the local end of the SSH tunnel)
// If you don't use JDBC, but another database client,
// just connect it to the localhost:forwardedPort
String url = "jdbc:mysql://localhost:" + forwardedPort;
Connection con =
DriverManager.getConnection(url, databaseUsername, databasePassword);
You will also have to deal with host key verification. For that see:
How to resolve Java UnknownHostKey, while using JSch SFTP library?
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.
My detail code is below:
package mypackage;
import java.sql.*;
import com.jcraft.jsch.JSch;
import com.jcraft.jsch.Session;
public class UpdateMySqlDatabase {
static int lport;
static String rhost;
static int rport;
public static void go(){
String user = "ripon";
String password = "wasim";
String host = "myhost.ripon.wasim";
int port=22;
try
{
JSch jsch = new JSch();
Session session = jsch.getSession(user, host, port);
lport = 4321;
rhost = "localhost";
rport = 3306;
session.setPassword(password);
session.setConfig("StrictHostKeyChecking", "no");
System.out.println("Establishing Connection...");
session.connect();
int assinged_port=session.setPortForwardingL(lport, rhost, rport);
System.out.println("localhost:"+assinged_port+" -> "+rhost+":"+rport);
}
catch(Exception e){System.err.print(e);}
}
public static void main(String[] args) {
try{
go();
} catch(Exception ex){
ex.printStackTrace();
}
System.out.println("An example for updating a Row from Mysql Database!");
Connection con = null;
String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://" + rhost +":" + lport + "/";
String db = "testDB";
String dbUser = "wasim";
String dbPasswd = "riponalwasim123";
try{
Class.forName(driver);
con = DriverManager.getConnection(url+db, dbUser, dbPasswd);
try{
Statement st = con.createStatement();
String sql = "UPDATE MyTableName " +
"SET email = '[email protected]' WHERE email='[email protected]'";
int update = st.executeUpdate(sql);
if(update >= 1){
System.out.println("Row is updated.");
}
else{
System.out.println("Row is not updated.");
}
}
catch (SQLException s){
System.out.println("SQL statement is not executed!");
}
}
catch (Exception e){
e.printStackTrace();
}
}
}