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 email@example.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.