logo标签列表

通过SSH隧道远程访问MySQL数据库

javamysqlremote-accessssh-tunnelblackboard
3
我将尝试编写Java程序，通过ssh隧道访问远程MySQL数据库。
以下是我的代码：
int lport = 5656;

int rport = 3306;

String rhost = "111.222.333.444";

String host = "111.222.333.444";

String user = "username";

String password = "password1234";

String dbUser = "mySQLuser";

String dbPass = "mySQLpassword1234";

String schema = "test_db";





Connection conn = null;


try {


Properties config = new Properties();

config.put("StrictHostKeyChecking", "no");


JSch jsch = new JSch();

jschSession = jsch.getSession(user, host, 22);

jschSession.setPassword(password);

jschSession.setConfig(config);

jschSession.connect();

logger.info("Connected");


int assigned_port = jschSession.setPortForwardingL(lport, rhost, rport);

logger.info("localhost:" + assigned_port + " -> " + rhost + ":" + rport);

logger.info("Port Forwarded");




Class.forName("com.mysql.jdbc.Driver").newInstance();

String url = "jdbc:mysql://localhost:" + rport + "/" + schema;

conn = DriverManager.getConnection(url, dbUser, dbPass);

logger.info("Database connection established");


Statement stmt = conn.createStatement();


String sql = "SELECT * FROM TEST_TABLE";

ResultSet rs = stmt.executeQuery(sql);


while(rs.next()) {

logger.info(rs.getInt(1) + " " + rs.getString(2));

}


rs.close();

stmt.close();


logger.info("DONE");


} catch (Exception e) {

e.printStackTrace();

logger.error(e.getMessage());

if (jschSession != null && jschSession.isConnected()) {

System.out.println("Closing SSH Connection during error");

logger.error("Closing SSH Connection during error");

jschSession.disconnect();

}


} finally {

if (conn != null && !conn.isClosed()) {

System.out.println("Closing Database Connection");

logger.info("Closing Database Connection");

conn.close();

}


if (jschSession != null && jschSession.isConnected()) {

System.out.println("Closing SSH Connection");

logger.info("Closing SSH Connection");

//jschSession.disconnect();

}


}

我相信所有的用户名和密码都是正确的。

然而，当我尝试运行上述代码时，它总是在以下行失败：

conn = DriverManager.getConnection(url, dbUser, dbPass);

除以下情况外：

INFO   | jvm 1    | 2013/02/20 17:25:24 | java.sql.SQLException: Access denied for user 'mySQLuser'@'localhost' (using password: YES)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:885)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:3421)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1247)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at com.mysql.jdbc.Connection.createNewIO(Connection.java:2775)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at com.mysql.jdbc.Connection.<init>(Connection.java:1555)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:285)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at java.sql.DriverManager.getConnection(DriverManager.java:582)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at java.sql.DriverManager.getConnection(DriverManager.java:185)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at com.prject.testing.Test.<init>(Test.java:97)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.beans.BeanUtils.instantiateClass(BeanUtils.java:147)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:76)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory$4.run(AbstractAutowireCapableBeanFactory.java:997)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at java.security.AccessController.doPrivileged(Native Method)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.instantiateBean(AbstractAutowireCapableBeanFactory.java:995)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:955)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:487)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:458)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:294)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:225)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:291)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:193)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.beans.factory.support.DefaultListableBeanFactory.findAutowireCandidates(DefaultListableBeanFactory.java:874)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:816)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:731)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:485)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.beans.factory.annotation.InjectionMetadata.inject(InjectionMetadata.java:92)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor.postProcessPropertyValues(AutowiredAnnotationBeanPostProcessor.java:284)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1118)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:519)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:458)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:294)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:225)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:291)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:193)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:605)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:926)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:477)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.web.servlet.FrameworkServlet.configureAndRefreshWebApplicationContext(FrameworkServlet.java:638)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.web.servlet.FrameworkServlet.createWebApplicationContext(FrameworkServlet.java:595)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.web.servlet.FrameworkServlet.createWebApplicationContext(FrameworkServlet.java:652)

我还检查了mySQL，“mySQLuser”用户的“host”已经在“User”表中设置为“%”。有人能给我一些建议吗？
更新：
采纳@bmorris591的建议，
我已将String url =“jdbc：mysql：// localhost：”+ rport +“/”+ schema;更改为String url =“jdbc：mysql：// localhost：”+ lport +“/”+ schema;
但是，出现了一个新的异常：
java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
    at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1997)
    at com.mysql.jdbc.MysqlIO.readPacket(MysqlIO.java:573)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1044)
    at com.mysql.jdbc.Connection.createNewIO(Connection.java:2775)
    at com.mysql.jdbc.Connection.<init>(Connection.java:1555)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:285)
    at java.sql.DriverManager.getConnection(DriverManager.java:582)
    at java.sql.DriverManager.getConnection(DriverManager.java:185)
    at com.project.testing.Test.<init>(Test.java:98)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
    at org.springframework.beans.BeanUtils.instantiateClass(BeanUtils.java:147)
    at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:76)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory$4.run(AbstractAutowireCapableBeanFactory.java:997)
    at java.security.AccessController.doPrivileged(Native Method)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.instantiateBean(AbstractAutowireCapableBeanFactory.java:995)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:955)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:487)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:458)
    at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:294)
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:225)
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:291)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:193)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.findAutowireCandidates(DefaultListableBeanFactory.java:874)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:816)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:731)
    at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:485)
    at org.springframework.beans.factory.annotation.InjectionMetadata.inject(InjectionMetadata.java:92)
    at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor.postProcessPropertyValues(AutowiredAnnotationBeanPostProcessor.java:284)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1118)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:519)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:458)
    at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:294)
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:225)
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:291)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:193)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:605)
    at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:926)
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:477)
    at org.springframework.web.servlet.FrameworkServlet.configureAndRefreshWebApplicationContext(FrameworkServlet.java:638)
    at org.springframework.web.servlet.FrameworkServlet.createWebApplicationContext(FrameworkServlet.java:595)
    at org.springframework.web.servlet.FrameworkServlet.createWebApplicationContext(FrameworkServlet.java:652)
    at org.springframework.web.servlet.FrameworkServlet.initWebApplicationContext(FrameworkServlet.java:514)
    at org.springframework.web.servlet.FrameworkServlet.initServletBean(FrameworkServlet.java:455)
    at org.springframework.web.servlet.HttpServletBean.init(HttpServletBean.java:138)
    at javax.servlet.GenericServlet.init(GenericServlet.java:212)
    at sun.reflect.GeneratedMethodAccessor120.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.apache.catalina.security.SecurityUtil$1.run(SecurityUtil.java:270)
    at org.apache.catalina.security.SecurityUtil$1.run(SecurityUtil.java:269)
    at java.security.AccessController.doPrivileged(Native Method)
    at javax.security.auth.Subject.doAsPrivileged(Subject.java:517)
    at org.apache.catalina.security.SecurityUtil.execute(SecurityUtil.java:302)
    at org.apache.catalina.security.SecurityUtil.doAsPrivilege(SecurityUtil.java:163)
    at org.apache.catalina.security.SecurityUtil.doAsPrivilege(SecurityUtil.java:117)
    at org.apache.catalina.core.StandardWrapper.loadServlet(StandardWrapper.java:1200)
    at org.apache.catalina.core.StandardWrapper.load(StandardWrapper.java:1026)
    at org.apache.catalina.core.StandardContext.loadOnStartup(StandardContext.java:4421)

错误仍然发生在这行代码中：

conn = DriverManager.getConnection(url, dbUser, dbPass);

错误信息让我很困惑， 有没有人能给我更多建议？
这意味着ssh隧道没有正常工作。在添加数据库连接之前，请尝试添加代码以查看是否有效。 - Stevo
1
略微偏离主题的问题，但是为什么需要一个Java程序来建立SSH隧道呢？直接使用普通的SSH建立隧道不是更简单吗？然后你的Java程序就可以访问本地端口了。 - beny23
嗨，我也遇到了连接Java应用程序与SSH/SSL通过TCP/IP连接的问题。所以，我找到了这个链接[http://blog.sodhanalibrary.com/2015/12/connect-amazon-ec2-instance-using-java.html]，它使用证书PEM - Privacy Enhanced Mail (PEM)。你有关于这个问题的解决方案吗？ - e2a
2个回答
2

看起来错误在这一行：

String url = "jdbc:mysql://localhost:" + rport + "/" + schema;

假设 rport 是 3306，即远程机器上的MySQL端口，您正在连接到该端口。

"jdbc:mysql://localhost:3306/" + schema;

您的本地计算机是哪一台？您需要连接到：

String url = "jdbc:mysql://localhost:" + lport + "/" + schema;

这是本地机器上转发到远程机器的 3306 端口。

谢谢您的建议。我已经改为使用lport，现在出现了一个新的异常： java.io.EOFException：无法从服务器读取响应。预期读取4个字节，在连接意外丢失之前读取了0个字节。 这让我有点困惑...... - Hei
如果您的隧道没有打开，我猜测是这种情况。如果您从Java应用程序执行LocalForward，则应该能够从命令行测试隧道。 - Boris the Spider
那么，您对Java代码进行隧道处理有何建议呢？我最初是按照这个教程操作的，但在我的情况下似乎没有成功。 http://www.journaldev.com/235/java-program-to-connect-to-remote-database-through-ssh-using-port-forwarding - Hei
我会测试隧道 - 例如从代码中删除mySQL相关内容并添加一个无限循环，然后运行代码并尝试使用终端从命令行连接到远程mySQL。这样你就可以调试SSH隧道了。 - Boris the Spider
0

"mySQLuser"@"%" 允许你从任何远程主机连接。你应该使用 "mySQLuser"@"localhost" 来本地登录，这是通过 SSH 隧道时的情况。

默认的 MySQL 安装有一个匿名用户帐户，其主机为 localhost，因此在本地登录时，它被视为更“具体”的用户。也就是说，如果没有 "mySQLuser"@"localhost" 帐户，则被视为匿名用户帐户。

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接