When working with remote databases, security is paramount. One of the best ways to securely access a remote MySQL server is by creating an SSH tunnel. Tools like MySQL Workbench and Sequel Pro can connect to your database over this tunnel, ensuring encrypted traffic between your local machine and the server. This article will guide you through setting up an SSH tunnel and configuring both MySQL Workbench and Sequel Pro to use it.

What is an SSH Tunnel?

An SSH tunnel creates a secure connection between your local machine and a remote server by forwarding a local port to a remote service, such as MySQL. By using SSH tunneling, you can connect to a MySQL database server that is only accessible on the remote network without exposing the MySQL port (3306) directly to the internet.

Requirements

Before you start, make sure you have:

• SSH access to the remote server where MySQL is hosted.
• The IP address of the remote server (for this guide, we’ll use xx.xx.xx.xx as the example IP).
• Your MySQL username and password.
• Tools like MySQL Workbench or Sequel Pro installed on your local machine.

Step 1: Creating an SSH Tunnel

To set up an SSH tunnel, you’ll need to use the terminal or an SSH client like PuTTY if you're on Windows. Here's how you can create the tunnel:

For Linux and macOS:

1. Open the terminal.

2. Run the following command:

   ssh -L 3307:localhost:3306 [email protected]

   Explanation:

   • 3307: This is the local port on your machine. You can change this to any available port.
   • localhost: Refers to the MySQL service running on the remote server.
   • 3306: The default MySQL port on the remote server.
   • user: Your SSH username.
   • xx.xx.xx.xx: The remote server's IP address.

   This command will forward port 3307 on your local machine to port 3306 on the remote server through SSH.

For Windows (Using PuTTY):

1. Open PuTTY and enter the remote server’s IP (xx.xx.xx.xx) under the “Host Name (or IP address)” field.
2. On the left side, navigate to Connection -> SSH -> Tunnels.
3. In the “Source port” field, enter 3307 (this is the local port).
4. In the “Destination” field, enter localhost:3306.
5. Click “Add.”
6. Go back to the Session page and click “Open” to start the SSH tunnel.

Step 2: Configuring MySQL Workbench

Once the SSH tunnel is active, you can set up MySQL Workbench to use it:

1. Open MySQL Workbench.
2. Click on the + button next to MySQL Connections to create a new connection.
3. In the Connection Name field, give your connection a name.
4. Set the Hostname to 127.0.0.1 and Port to 3307 (the local port you set up in the SSH tunnel).
5. Under the Username field, enter your MySQL username.
6. Click on the SSH tab, and enable SSH tunneling by checking the box labeled "Use SSH tunnel."
7. Fill out the SSH details:
   • SSH Hostname: xx.xx.xx.xx
   • SSH Username: Your SSH username.
   • SSH Password: You can enter your SSH password here or use an SSH key.
8. Click Test Connection to verify everything is working.
9. Once verified, click OK to save the connection.

Now, you can use MySQL Workbench to manage your remote database securely through the SSH tunnel.

Step 3: Configuring Sequel Pro

To use Sequel Pro over an SSH tunnel, follow these steps:

1. Open Sequel Pro.
2. Choose SSH as the connection method.
3. In the MySQL Host field, enter 127.0.0.1.
4. Set the Port to 3307.
5. Enter your MySQL Username and Password.
6. Fill in the SSH details:
   • SSH Host: xx.xx.xx.xx
   • SSH User: Your SSH username.
   • SSH Password: Your SSH password (if you're using password authentication).
7. Click Connect to start the connection.

Now, you’ll be able to access and manage your remote MySQL database securely through Sequel Pro.

Step 4: Closing the SSH Tunnel

Once you're done, you can close the SSH tunnel by simply exiting the terminal (on Linux/macOS) or closing the PuTTY session (on Windows). This will stop the local port from forwarding traffic to the remote server.

Conclusion

By using an SSH tunnel, you can securely manage a remote MySQL database through tools like MySQL Workbench and Sequel Pro without exposing your MySQL server to the internet. This method encrypts your connection, ensuring that all data between your local machine and the remote MySQL server remains secure. Always remember to close your SSH tunnel when you are finished for maximum security.