Connecting to Remote MySQL Database Through SSH Using Java

1. Overview

Secure Shell (SSH) allows us to securely access and manage remote systems, including executing commands, transferring files, and tunneling services.

We can establish a connection to a remote MySQL database through an SSH session. Several SSH clients exist in Java, and one of the most common is Java Secure Channel (JSch).

In this tutorial, we’ll explore how to connect to a MySQL database running on localhost of a remote server through an SSH session.

2. Understanding SSH Port Forwarding

Port forwarding allows the transfer of data between a client system and a remote server by directing traffic from a local port to a port on the remote server over an SSH connection.

This is especially useful when firewalls or other restrictions block direct connection to a remote server’s IP and port.

In our case, the MySQL server is running on localhost of the remote machine, typically using port 3306. While it’s technically possible to connect directly to the remote server’s IP and MySQL port, this is often restricted for security purposes. Instead, we can use local port forwarding over SSH to establish a secure connection to the database.

In local port forwarding, we allocate an available port on our local machine and bind it with the port of the MySQL server running remotely to allow data communication between our program and the remote server.

3. Maven Dependencies

To begin, let’s add the JSch and MySQL connector dependencies to the pom.xml:

<dependency>
    <groupId>com.github.mwiede</groupId>
    <artifactId>jsch</artifactId>
    <version>0.2.20</version>
</dependency>

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>9.0.0</version>
</dependency>

The JSch dependency provides classes like Session, which are essential for establishing an SSH connection to a remote server. Also, the MySQL connector allows us to establish a connection to a running MySQL server.

4. Connection Details

Furthermore, let’s define the connection details to the remote server:

private static final String HOST = "HOST";
private static final String USER = "USERNAME";
private static final String PRIVATE_KEY = "PATH_TO_PRIVATEKEY";
private static final int PORT = 22;

In the code above, we define the necessary credentials to create an SSH session. Next, let’s define the connection details to the remote database:

private static final String DATABASE_HOST = "localhost";
private static final int DATABASE_PORT = 3306;
private static final String DATABASE_USERNAME = "DATABASE_USERNAME";
private static final String DATABASE_PASSWORD = "DATABASE_PASSWORD";

The MySQL database is running on a remote machine’s localhost, using port 3306. We define the database username and password to authenticate the connection.

5. Creating SSH Session

After defining our connection details, let’s create a JSch instance to bootstrap a connection to the remote server:

JSch jsch = new JSch();
jsch.addIdentity(PRIVATE_KEY);

Here, we use a private key to authenticate our identity. However, we can also use password-based authentication.

Next, let’s create a new SSH session:

Session session = jsch.getSession(USER, HOST, PORT);
session.setConfig("StrictHostKeyChecking", "no");
session.connect();

In the code above, we create a new Session with our connection details and disable key host checking for simplicity. Disabling host key checking is convenient for testing but should be avoided in production for security purposes.

Finally, we invoke the connect() method on the Session object to open a new SSH session.

6. Connecting to MySQL Through Port Forwarding

Next, let’s use SSH port forwarding to tunnel the MySQL port:

int port = session.setPortForwardingL(0, DATABASE_HOST, DATABASE_PORT);

In the code above, we invoke the setPortForwardingL() method on the Session object to set up a local port forwarding. By passing 0 as the local port, the program dynamically chooses an available local port for forwarding traffic to the remote MySQL server’s port 3306.

Port forwarding (tunneling) allows traffic sent to a local port to be forwarded through the SSH connection to the MySQL server on the remote machine.

Moreover, let’s use the forward port to connect to the MySQL server:

String databaseUrl = "jdbc:mysql://" + DATABASE_HOST + ":" + port + "/baeldung";
Connection connection = DriverManager.getConnection(databaseUrl, DATABASE_USERNAME, DATABASE_PASSWORD);

In the code above, we establish a connection to the database using the JDBC Connection class. In our database URL, we use the forwarded local port instead of the remote MySQL server’s default port (3306).

Furthermore, let’s verify that the connection exists:

assertNotNull(connection);

In the code above, we assert that the connection to the database is not null.

7. Simple Queries

Additionally, let’s perform some database operations on the established connection. First, let’s create a table:

String createTableSQL = "CREATE TABLE test_table (id INT, data VARCHAR(255))";
try (Statement statement = connection.createStatement()) {
    statement.execute(createTableSQL);
}

Here, we create a test_table in the baeldung database. Next, let’s insert a single record into the created table:

String insertDataSQL = "INSERT INTO test_table (id, data) VALUES (1, 'test data')";
try (Statement statement = connection.createStatement()) {
    statement.execute(insertDataSQL);
}

Finally, let’s assert that the created table is present in the database:

try (Statement statement = connection.createStatement()) {
    ResultSet resultSet = statement.executeQuery("SHOW TABLES LIKE 'test_table'");
    return resultSet.next();
} 

In the code above, we verify that the created table exists in the database.

8. Closing Connection

Essentially, we need to close the SSH session and database connection after the operation:

session.disconnect();
connection.close();

In the code above we invoke the disconnect() and close() methods on the Session and Connection objects respectively to free up resources. It also prevents potential memory leaks.

9. Conclusion

In this article, we learned how to connect to a remote database via an SSH session. Additionally, we learned local port forwarding and applied it to connect to a remote MySQL database through an established SSH session.