无法通过SSH连接Strato托管MySQL服务器：工具及Java连接求助

Hey, let's break down your issues step by step—since you can connect via terminal but not GUI tools like Navicat/MySQL Workbench, the problem almost always boils down to SSH tunnel configuration (Strato locks down their MySQL servers to only allow local access from their SSH environment, so direct remote connections over the internet won't work). Let's start with Navicat first, since that's your priority:

1. Fixing Navicat Connection to Strato MySQL #

Strato requires you to route your MySQL traffic through an SSH tunnel. Your terminal connection works because you're either already logged into the SSH server and running mysql locally there, or you used port forwarding via PuTTY/ssh -L in the background. Here's how to mirror that setup in Navicat:

Step 1: Configure the SSH Tunnel #

Open Navicat and create a new MySQL connection, then switch to the SSH tab:

• Check the box for Use SSH Tunnel
• Host: Enter your Strato SSH server address (usually ssh.strato.de, or check your Strato account for your dedicated SSH host)
• Port: Leave as 22 (default SSH port, unless Strato told you otherwise)
• Username: Your Strato SSH login username (this is NOT your MySQL username)
• Authentication: Choose Password and enter your SSH login password; if you use SSH keys, select Key File and import your PuTTY .ppk private key

Step 2: Configure the MySQL Connection #

Switch back to the General tab:

• Hostname/IP Address: Enter 127.0.0.1 (this points to your local machine, since the SSH tunnel forwards remote MySQL traffic to your local port)
• Port: Enter the local port you used in PuTTY for forwarding (most people use 3306, but if you changed it, match that number)
• Username: Your Strato MySQL database username
• Password: Your MySQL database password
• Database: Leave blank to browse all databases after connecting, or enter the specific database name you want to access

Common Mistakes to Check #

• Did you mix up SSH and MySQL credentials? It's easy to enter your SSH password in the MySQL field (and vice versa)
• Is your PuTTY port forwarding conflicting with Navicat? If you're running PuTTY in the background, make sure the local port in Navicat matches the one you set in PuTTY (e.g., if PuTTY forwards local 3307 to remote 3306, Navicat's port must be 3307)
• Is your local firewall blocking Navicat from accessing the local port? Temporarily disable it to test, or add an exception for Navicat

2. Java Connection to Strato MySQL #

Again, you'll need to use an SSH tunnel. You have two options: either set up the tunnel via PuTTY/command line first, or build the tunnel directly into your Java code.

Option 1: Pre-Existing SSH Tunnel (Simpler) #

First, make sure your PuTTY/ssh -L tunnel is active (same setup as your terminal connection). Then use this standard JDBC configuration:

• Driver Class: For MySQL 8.0+, use com.mysql.cj.jdbc.Driver; for older versions, use com.mysql.jdbc.Driver
• JDBC URL: jdbc:mysql://127.0.0.1:3306/your_database_name?useSSL=false&serverTimezone=UTC
  • useSSL=false: Strato's MySQL often doesn't require SSL (adjust to true if you get SSL-related errors)
  • serverTimezone=UTC: Fixes timezone mismatch errors (Strato's servers typically use UTC)
• Username: Your MySQL database username
• Password: Your MySQL database password

Sample code:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class StratoMySQLConnector {
    public static void main(String[] args) {
        String jdbcUrl = "jdbc:mysql://127.0.0.1:3306/your_db_name?useSSL=false&serverTimezone=UTC";
        String dbUser = "your_mysql_username";
        String dbPassword = "your_mysql_password";

        try (Connection conn = DriverManager.getConnection(jdbcUrl, dbUser, dbPassword)) {
            System.out.println("Successfully connected to Strato MySQL!");
        } catch (SQLException e) {
            System.err.println("Connection failed: " + e.getMessage());
        }
    }
}

Option 2: Build SSH Tunnel in Java (No External Tools) #

If you don't want to manually open PuTTY every time, use the JSch library to create the tunnel directly in your code:

1. Add the JSch dependency (for Maven, add this to pom.xml):

<dependency>
    <groupId>com.jcraft</groupId>
    <artifactId>jsch</artifactId>
    <version>0.1.55</version>
</dependency>

2. Sample code with embedded SSH tunnel:

import com.jcraft.jsch.JSch;
import com.jcraft.jsch.Session;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class StratoSSHMySQLConnector {
    public static void main(String[] args) {
        // SSH configuration
        String sshHost = "ssh.strato.de";
        int sshPort = 22;
        String sshUser = "your_ssh_username";
        String sshPassword = "your_ssh_password";

        // MySQL configuration
        String mysqlRemoteHost = "localhost"; // Remote MySQL runs on Strato's SSH server localhost
        int mysqlRemotePort = 3306;
        String localForwardPort = "3306";
        String dbName = "your_db_name";
        String dbUser = "your_mysql_username";
        String dbPassword = "your_mysql_password";

        Session sshSession = null;
        Connection dbConn = null;

        try {
            // Establish SSH tunnel
            JSch jsch = new JSch();
            sshSession = jsch.getSession(sshUser, sshHost, sshPort);
            sshSession.setPassword(sshPassword);
            
            // Skip host key check (for testing; set to "yes" in production for security)
            Properties sshConfig = new Properties();
            sshConfig.put("StrictHostKeyChecking", "no");
            sshSession.setConfig(sshConfig);
            
            sshSession.connect();
            System.out.println("SSH tunnel connected successfully");

            // Forward local port to remote MySQL port
            sshSession.setPortForwardingL(Integer.parseInt(localForwardPort), mysqlRemoteHost, mysqlRemotePort);

            // Connect to MySQL via tunnel
            String jdbcUrl = String.format("jdbc:mysql://127.0.0.1:%s/%s?useSSL=false&serverTimezone=UTC",
                    localForwardPort, dbName);
            dbConn = DriverManager.getConnection(jdbcUrl, dbUser, dbPassword);
            System.out.println("MySQL database connected successfully");

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // Clean up resources
            try {
                if (dbConn != null) dbConn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            if (sshSession != null) sshSession.disconnect();
        }
    }
}

Quick Troubleshooting Tip #

Since your terminal works, run echo $SSH_CLIENT in the terminal where you connected to MySQL—if it shows your local IP, you're using port forwarding. If not, you're logged into the SSH server directly, which means you need to set up port forwarding (either via PuTTY or Navicat's built-in SSH tunnel) to connect from GUI tools/Java.

内容的提问来源于stack exchange，提问作者lmiv