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

Question

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

Answer 1

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

要使用命令行 ssh 客户端创建从本地计算机上的端口 1234 到远程计算机上的端口 3306 的隧道，您可以从本地计算机键入以下命令：

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

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

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

例如，请查看 PortForwardingL.java。连接会话后，使用 jdbc:mysql://localhost:1234/[database] 作为连接 URL 创建到 MySQL 的 JDBC 连接。

Answer 2

我的详细代码如下：

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

Answer 3

虽然现有答案是正确的，但它们掩盖了其他代码膨胀中的重要代码。

这是通过 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 automatically chosen local free 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);

---

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

Answer 4

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

Answer 5

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

Answer 6

首先感谢你的作品非常棒！

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

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

如果我能让该类可与 try-with-resource 一起使用并自行关闭，那就太好了。会调查这一点。因为我不想错过关闭它的机会。

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

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

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