Connecting To Remote MySQL From Hibernate Using Tunnelling Over SSH

  • Posted on: 12 December 2014
  • By: Zhijun Chen

For certain reasons sometimes you may want to connect to remote MySQL server over SSH tunnel when you maintain web applications, e.g. debug a production issue which is hard to replicate locally. Most IDEs like Eclipse offer the debug tools which you can put break points and step through. You can find out the problem once connecting to remote database and debug with caution using local code copy.

The following steps illustrate how to achieve this for Hibernate based web applications (Presume you have the user set up for SSH and database connection).

  • Open MySQL tunnel over SSH

 You will only need the following command to open a tunnel:

ssh -L 3333:db.remotehost.com:3306 username@remotehost.com

This will open port 3333 on local machine to connect to MySQL port 3306 on remote server, enter your password on remote host once prompted.

  • Update Hibernate configuration file

Once we've got the tunnel, the next step would be updating Hibernate configuration file hibernate.cfg.xml to connect to the port 3333 as follows:

<hibernate-configuration>
  <session-factory>
    ...
    <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
    <property name="hibernate.connection.url">jdbc:mysql://localhost:3333/remotedb</property>
    <property name="hibernate.connection.username">remote_user</property>
    <property name="hibernate.connection.password">remote_password</property>
    ...
  </session-factory>
</hibernate-configuration>

The remotedb is the database to connect on remote server, remote_user/remote_password is the username and password for that database on remote MySQL database.

Now you can open your web application and it should use the remote database you specified.

Add new comment

Plain text

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.