In this article, the process of establishing a Java SSH remote MySQL DB connection is explored. This approach enables Java applications to securely connect to a remote MySQL database by tunneling through an SSH server. The article demonstrates how to set up the SSH connection using the JSch library, facilitating secure communication between the application and the remote MySQL database.
1. What is SSH Tunneling?
SSH tunneling establishes a secure connection to a remote server by creating an encrypted channel through which data is transmitted. It enables the forwarding of local traffic securely to a remote database server.
SSH tunneling is necessary because it provides a secure, encrypted channel between the client and the remote server, ensuring that sensitive data (such as login credentials or database queries) is not exposed over insecure networks. SSH tunneling can securely connect to a database or service without exposing it to the public internet, reducing the risk of attacks such as man-in-the-middle (MITM) attacks, eavesdropping, and unauthorized access.
2. Dependencies
Include the necessary libraries required for SSH tunneling and MySQL database connectivity in Java. The jsch library is used to establish the SSH connection and manage port forwarding, while the mysql-connector-java library enables Java applications to connect to a MySQL database.
<dependencies>
<dependency>
<groupId>com.github.mwiede</groupId>
<artifactId>jsch</artifactId>
<version>0.2.0</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.32</version>
</dependency>
</dependencies>
3. Code Implementation
public class SshMysqlConnection {
private final static Logger LOGGER = Logger.getLogger(SshMysqlConnection.class.getName());
private static final String SSH_HOST = "ssh.example.com";
private static final int SSH_PORT = 22;
private static final String SSH_USER = "ssh_user";
private static final String SSH_PRIVATE_KEY = "/path/to/private/key"; // Or use a password
private static final String REMOTE_MYSQL_HOST = "127.0.0.1";
private static final int REMOTE_MYSQL_PORT = 3306;
private static final int LOCAL_PORT = 3307; // Local forwarding port
private static final String MYSQL_USER = "db_user";
private static final String MYSQL_PASSWORD = "db_password";
private static final String DATABASE_NAME = "testdb";
private Session session;
public void connectAndQueryDatabase() {
try {
// Establish the SSH Tunnel
JSch jsch = new JSch();
jsch.addIdentity(SSH_PRIVATE_KEY);
session = jsch.getSession(SSH_USER, SSH_HOST, SSH_PORT);
session.setConfig("StrictHostKeyChecking", "no");
session.connect();
System.out.println("SSH connection established.");
// Set up port forwarding
int assignedPort = session.setPortForwardingL(LOCAL_PORT, REMOTE_MYSQL_HOST, REMOTE_MYSQL_PORT);
System.out.println("Port forwarding established: localhost:" + assignedPort + " -> " + REMOTE_MYSQL_HOST + ":" + REMOTE_MYSQL_PORT);
// Connect to MySQL database
String jdbcUrl = "jdbc:mysql://localhost:" + assignedPort + "/" + DATABASE_NAME;
try (Connection connection = DriverManager.getConnection(jdbcUrl, MYSQL_USER, MYSQL_PASSWORD)) {
System.out.println("Connected to the MySQL database.");
queryDatabase(connection);
}
} catch (JSchException | SQLException e) {
LOGGER.log(Level.SEVERE, e.getMessage());
} finally {
if (session != null && session.isConnected()) {
session.disconnect();
System.out.println("SSH connection closed.");
}
}
}
private void queryDatabase(Connection connection) {
String query = "SELECT * FROM users";
try (PreparedStatement statement = connection.prepareStatement(query); ResultSet resultSet = statement.executeQuery()) {
while (resultSet.next()) {
System.out.println("User ID: " + resultSet.getInt("id"));
System.out.println("Username: " + resultSet.getString("username"));
}
} catch (Exception e) {
LOGGER.log(Level.SEVERE, e.getMessage());
}
}
public static void main(String[] args) {
SshMysqlConnection app = new SshMysqlConnection();
app.connectAndQueryDatabase();
}
}
These class-level configurations include SSH details (SSH_HOST, SSH_PORT, SSH_USER, and SSH_PRIVATE_KEY) for server authentication, and MySQL settings (REMOTE_MYSQL_HOST, REMOTE_MYSQL_PORT, LOCAL_PORT, MYSQL_USER, MYSQL_PASSWORD, DATABASE_NAME) for database connectivity. The Session object handles the SSH connection lifecycle and enables port forwarding.
Establishing SSH Tunnel
- A
JSchinstance is created to handle SSH operations. TheaddIdentity()method provides the private key for authentication. session = jsch.getSession(SSH_USER, SSH_HOST, SSH_PORT)creates an SSH session using the username, host, and port.session.setConfig("StrictHostKeyChecking", "no")disables host key checking, meaning the SSH client won’t verify the server’s identity before connecting (this is helpful for avoiding manual approval of unknown hosts).session.connect()establishes the SSH connection.
Port Forwarding
- Port forwarding is established using
session.setPortForwardingL, mapping the local port (LOCAL_PORT) to the remote MySQL database host and port. - This creates a secure pathway for database traffic from the application to the remote server.
Connecting to MySQL
- The database connection is established using the
DriverManagerwith a JDBC URL that points to the locally forwarded port. - If the connection is successful, the
queryDatabasemethod is called to execute an SQL query.
4. Conclusion
In this article, we explored how to securely connect to a remote MySQL database through an SSH tunnel using Java. We demonstrated the process of establishing an SSH connection with the JSch library, forwarding traffic securely to the MySQL server, and then connecting to the MySQL database using JDBC. By setting up proper configurations for both SSH and MySQL, we ensured a secure and efficient communication channel between the Java application and the remote database.
5. Download the Source Code
This article covered the process of establishing a Java SSH connection to a remote MySQL DB.
You can download the full source code of this example here: Java SSH remote MySQL db connection
