使用 Java 通过 SSH 连接到远程 MySQL 数据库

Connect to remote MySQL database through SSH using Java

提问by Alex

How can I connect to remote MySQL database through SSH from java application? Small code example is helpful for me and I'd appreciate this.

如何从 Java 应用程序通过 SSH 连接到远程 MySQL 数据库？小代码示例对我有帮助，我很感激。

采纳答案by Pascal Thivent

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.

我的理解是，您想访问在远程机器上运行的 mysql 服务器并通过 SSH 隧道侦听假设端口 3306。

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 客户端创建从本地机器上的端口 1234 到远程机器上的端口 3306 的隧道，您需要在本地机器上键入以下命令：

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:

要从 Java 执行相同的操作，您可以使用JSch，这是 SSH2 的 Java 实现。从它的网站：

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.

JSch 允许您连接到 sshd 服务器并使用端口转发、X11 转发、文件传输等，您可以将其功能集成到您自己的 Java 程序中。JSch 是在 BSD 风格许可下获得许可的。

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.

举个例子，看看PortForwardingL.java。会话连接后，使用类似jdbc:mysql://localhost:1234/[database]连接 URL 的内容创建到 MySQL 的 JDBC 连接。

回答by Ripon Al Wasim

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();
          }
          }
        }

回答by user4878576

package framework.restapi.utils;

import java.sql.*;

import com.jcraft.jsch.JSch;
import com.jcraft.jsch.Session;
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;

public class SQLConnection {
    private static Connection connection = null;
    private static Session session = null;

    private static void connectToServer(String dataBaseName) throws SQLException {
        connectSSH();
        connectToDataBase(dataBaseName);
    }

    private static void connectSSH() throws SQLException {
        String sshHost = "";
        String sshuser = "";
        String dbuserName = "";
        String dbpassword = "";
        String SshKeyFilepath = "/Users/XXXXXX/.ssh/id_rsa";

        int localPort = 8740; // any free port can be used
        String remoteHost = "127.0.0.1";
        int remotePort = 3306;
        String localSSHUrl = "localhost";
        /***************/
        String driverName = "com.mysql.jdbc.Driver";

        try {
            java.util.Properties config = new java.util.Properties();
            JSch jsch = new JSch();
            session = jsch.getSession(sshuser, sshHost, 22);
            jsch.addIdentity(SshKeyFilepath);
            config.put("StrictHostKeyChecking", "no");
            config.put("ConnectionAttempts", "3");
            session.setConfig(config);
            session.connect();

            System.out.println("SSH Connected");

            Class.forName(driverName).newInstance();

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

            System.out.println("localhost:" + assinged_port + " -> " + remoteHost + ":" + remotePort);
            System.out.println("Port Forwarded");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static void connectToDataBase(String dataBaseName) throws SQLException {
        String dbuserName = "sf2_showpad_biz";
        String dbpassword = "lOAWEnL3K";
        int localPort = 8740; // any free port can be used
        String localSSHUrl = "localhost";
        try {

            //mysql database connectivity
            MysqlDataSource dataSource = new MysqlDataSource();
            dataSource.setServerName(localSSHUrl);
            dataSource.setPortNumber(localPort);
            dataSource.setUser(dbuserName);
            dataSource.setAllowMultiQueries(true);

            dataSource.setPassword(dbpassword);
            dataSource.setDatabaseName(dataBaseName);

            connection = dataSource.getConnection();

            System.out.print("Connection to server successful!:" + connection + "\n\n");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    private static void closeConnections() {
        CloseDataBaseConnection();
        CloseSSHConnection();
    }

    private static void CloseDataBaseConnection() {
        try {
            if (connection != null && !connection.isClosed()) {
                System.out.println("Closing Database Connection");
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    private static void CloseSSHConnection() {
        if (session != null && session.isConnected()) {
            System.out.println("Closing SSH Connection");
            session.disconnect();
        }
    }


    // works ONLY FOR  single query (one SELECT or one DELETE etc)
    private static ResultSet executeMyQuery(String query, String dataBaseName) {
        ResultSet resultSet = null;

        try {
            connectToServer(dataBaseName);
            Statement stmt = connection.createStatement();
            resultSet = stmt.executeQuery(query);
            System.out.println("Database connection success");
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return resultSet;
    }

    public static void DeleteOrganisationReferencesFromDB(String organisationsLike) {
        try {
            connectToServer("ServerName");
            Statement stmt = connection.createStatement();

            ResultSet resultSet = stmt.executeQuery("select * from DB1");

            String organisationsToDelete = "";
            List<String> organisationsIds = new ArrayList<String>();

            // create string with id`s values to delete organisations references
            while (resultSet.next()) {
                String actualValue = resultSet.getString("id");
                organisationsIds.add(actualValue);
            }

            for (int i = 0; i < organisationsIds.size(); i++) {
                organisationsToDelete = " " + organisationsToDelete + organisationsIds.get(i);
                if (i != organisationsIds.size() - 1) {
                    organisationsToDelete = organisationsToDelete + ", ";
                }
            }

            stmt.executeUpdate(" DELETE FROM `DB1`.`table1` WHERE `DB1`.`table1`.`organisation_id` in ( " + organisationsToDelete + " );");


        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnections();
        }
    }

    public static List<String> getOrganisationsDBNamesBySubdomain(String organisationsLike) {
        List<String> organisationDbNames = new ArrayList<String>();
        ResultSet resultSet = executeMyQuery("select `DB`.organisation.dbname from `DB1`.organisation where subdomain like '" + organisationsLike + "%'", "DB1");
        try {
            while (resultSet.next()) {
                String actualValue = resultSet.getString("dbname");
                organisationDbNames.add(actualValue);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnections();
        }
        return organisationDbNames;
    }

     public static List<String> getAllDBNames() {
        // get all live db names incentral DB
        List<String> organisationDbNames = new ArrayList<String>();
        ResultSet resultSet = executeMyQuery("show databases", "DB1");
        try {
            while (resultSet.next()) {
                String actualValue = resultSet.getString("Database");
                organisationDbNames.add(actualValue);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnections();
        }
        return organisationDbNames;
    }

      public static void deleteDataBasesByName(List<String> DataBasesNamesList) {
        try {
            connectSSH();
            int dataBasesAmount = DataBasesNamesList.size();
            for (int i = 0; i < dataBasesAmount; i++) {
                connectToDataBase(DataBasesNamesList.get(i));

                Statement stmt = connection.createStatement();
                stmt.executeUpdate("DROP database `" + DataBasesNamesList.get(i) + "`");

            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            CloseDataBaseConnection();
            closeConnections();
        }
    }
}

回答by Ankur Srivastava

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);
        }
    }
}

回答by Max

First of all, thank you works great!

首先，谢谢你做得很好！

Though, I wonder if I should reuse that Session for every (potentially simultaneous) SQL Connection, or if I should create a new Session every time and only refresh it if for some reason it has expired.

不过，我想知道我是否应该为每个（可能是同时的）SQL 连接重用该会话，或者我是否应该每次都创建一个新会话，并且仅在由于某种原因过期时才刷新它。

Currently, I would every time I make a connection make a new instance of that Controller here and then do the SQL queries with the connection I got from it, then close it manually.

目前，我每次建立连接时都会在此处创建该控制器的新实例，然后使用从它获得的连接执行 SQL 查询，然后手动关闭它。

Would also be nice if I could make the class useable with try-with-resource and it closing itself. Will look into that. Cause I don′t wanna miss closing it.

如果我可以使该类与 try-with-resource 一起使用并关闭它自己，那也会很好。会调查的。因为我不想错过关闭它。

That′s how the thing looks like, I'm getting DB Connections from right now.

这就是事情的样子，我现在正在获取数据库连接。

public class ConnectionManager {

private Connection con = null;
private Session session = null;

public Connection getConnection() {
    Connection con = null;

    var settings = new DbSettingsController();

    boolean useSSH = settings.getSetting(SettingKey.UseSSH).equals("true");
    String sshPort = settings.getSetting(SettingKey.SSHPort);
    String sqlIp = settings.getSetting(SettingKey.MySqlIP);
    String sqlPort = settings.getSetting(SettingKey.MySqlPort);

    if(useSSH) {
        JSch jSch = new JSch();
        try {
            this.session = jSch.getSession(settings.getSetting(SettingKey.SSHUser),
                    settings.getSetting(SettingKey.SSHHost),
                    Integer.valueOf(sshPort));
            this.session.setPassword(settings.getSetting(SettingKey.SSHPassword));
            this.session.setConfig("StrictHostKeyChecking", "no");
            this.session.connect();
            this.session.setPortForwardingL(Integer.parseInt(sshPort), sqlIp, Integer.parseInt(sqlPort));
        } catch (JSchException e) {
            e.printStackTrace();
        }
    }

    var connectionString = String.format("jdbc:mysql://%s:%s/%s?autoReconnect=true&useSSL=false",
            sqlIp, useSSH ? sshPort : sqlPort,
            settings.getSetting(SettingKey.MySqlShema));

    var user = settings.getSetting(SettingKey.MySqlUser);
    var password = settings.getSetting(SettingKey.MySqlPassword);

    try {
        con = DriverManager.getConnection(connectionString, user, password);
    } catch (SQLException e) {
        e.printStackTrace();
    }

    return con;
}

public void close() {
    if(this.con != null) {
        try {
            this.con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    if(this.session != null) {
        this.session.disconnect();
    }
}

If you wonder DbSettingsController I′ve made myself too, just puts settings in a Text column in a local SQLite DB, with a key assigned to it (that enum′s int value). Was just copy paste code I reused from some other project, so it was simple and fast to just do that this way.

如果你想知道我自己也制作了 DbSettingsController，只需将设置放在本地 SQLite 数据库的 Text 列中，并为其分配一个键（该枚举的 int 值）。只是复制粘贴我从其他项目中重用的代码，所以这样做既简单又快速。

回答by Martin Prikryl

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:

这是通过 SSH 通道建立 JDBC（或任何其他）数据库连接所需的基本代码：

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?

您还必须处理主机密钥验证。为此，请参阅：
如何在使用 JSch SFTP 库时解析 Java UnknownHostKey？