Connect through ssh tunnel to mysql

[Anonymous]

I am trying to connect to a remote server using ssh tunnel connection where I have to use port forwarding from a local server to the remote server. I then need to be able to connect to a mysql database on the remote server.
Any ideas how I would set this up in Talend?
Any help would be appreciated.

[Anonymous]

hi
Never use "localhost" for hostname (but 127.0.0.1) if ssh host and mysql server on the same machine
https://community.talend.com/t5/Design-and-Development/mysql-and-SSH-port-tunneling-forwarding/td-p/...
Hope it helps
regards
laurent

[Anonymous]

I don't use localhost; I use the IP address.
What I am trying to do is set up a talend job that does port forwarding to a remote server where the mysql database resides. I have attached screen shots from a putty connections which works fine. I need to replicate this somehow in talend.

[Anonymous]

Well, I can't seem to upload the images into this post...anyone know how this is done?

[jens_fricke]

Hello,
I faced the same problem and have found the following solution.
I use TOS 5.1 on Windows 7. For the SSH tunnel to my MySQL server I use plink.exe. You can download this from the PuTTY Dowload Page
1.) I used tSystem component to start the ssh tunnel and set up a port forwarding from local port 13306 to the remote MySQL server port 3306. The command line looks like

C:\plink.exe user@remote_host_or_ip -pw password -ssh -N -C -L 13306:127.0.0.1:3306

But there is one problem if you use this directly within tSystem component: plink establishes the tunnel but does not return and therefore the talend job does not continue. Add "cmd /c" in front does not help. So we need a little helper script, which will starting plink.exe in the background and then returns so the talend job can continue. I used VBScript for that.

 set fs=createobject("wscript.shell")
fs.run "C:\plink.exe user@remote_host_or_ip -pw password -ssh -N -C -L 13306:127.0.0.1:3306", 0

This script is saved as ssh_remote_mysql.vbs
On the tSystem component I used as command

"cscript C:\\ssh_remote_mysql.vbs"

2.) Next I added a tSleep component to my job which gives plink some time (i.e. 3s) to establish the SSH tunnel. Otherwise the first tMysqlInput component runs into an error because the tSystems component starts the VBScript and continues instantly without waiting until the SSH tunnel is established.
3.) Now I'm able to run all my MySQL subjobs. On the MySQL connection settings host must set to 127.0.0.1 and port to 13306
4.) After all MySQL jobs done I added a 2nd tSystem component to delete the SSH tunnel. Therefore I simply kill plink.exe by using the follwing command

"cmd /c taskkill /F /IM plink.exe"

So now every time I run the talend job the SSH tunnel will be established at the beginning and terminated at the end.
I posted this because I did not found a solution somewhere else which is running this way. Hope this helps.

[phil974]

Hi,
Easier ideas , solutions ??

[Anonymous]

Hi,
The solution offered by  jens.fricke  is the best that I could find on the Internet.
I did integrate a slighty different version without using a script file. As  jens.fricke, I put a tSystem component at the top of the job design, unlinked to any other components, with the option "Use Array Command" activated and these commands : 
"powershell"
"/c"
"plink.exe -ssh <machine-ip>:<ssh-port> -l root -pw <password> -L 3306:127.0.0.1:3306 -N"

Go to the Job tab, then open Extra tab and check the option "Multi thread execution".  This will enable the alone tSystem to run as well as the main flow of the job.
I add a tSleep component (with the duration of your choice, the time that your setup needs to connect to the remote server SSH) that I connect to the first component I need to run, in my case a tMysqlInput.
Finally, I add a tSystem that I connect to the last component, like  jens.fricke (thank you for your solution), with the command  "cmd /c taskkill /F /IM plink.exe". That way, the process plink.exe is shutdown at the end of the job.

The solution is not perfect. I would prefer an option in the tSSH component. 
I have to run the command  "plink.exe -ssh <machine-ip>:<ssh-port> -l root -pw <password> -L 3306:127.0.0.1:3306 -N" manually every time I need to use Talend functionnalities such as "Guess Schema" on the tMysqlInput component.
Hope it helps.

[Anonymous]

hi,
old post but nevermind.
a more Talend way will be to forward port & use setting network connection in preferences

regards
laurent

[_AnonymousUser]

I use the same solution but am having one additional issue - when I go to run the job from the job conductor i am getting the following (where when I run from TalendStudio I get no errors at all - thinking it must have something to do with the way the commandline service is running plink.) Any Ideas?:
2015-11-04 14:17:22|20151104141722_PYf9o|20151104141722_PYf9o|20151104141722_PYf9o|6924|PROD|AVRRegFromFiles|_TxxtAHvdEeWzvPDo1KDdWQ|0.1|Default||begin||
SUCCESS: The process "plink.exe" with PID 6496 has been terminated.
Microsoft (R) Windows Script Host Version 5.8
Copyright (C) Microsoft Corporation. All rights reserved.
2015-11-04 14:17:30|20151104141722_PYf9o|20151104141722_PYf9o|20151104141722_PYf9o|PROD|AVRRegFromFiles|Default|6|Java Exception|tMysqlInput_2|com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:Communications link failure
The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.|1
2015-11-04 14:17:30|20151104141722_PYf9o|20151104141722_PYf9o|20151104141722_PYf9o|6924|PROD|AVRRegFromFiles|_TxxtAHvdEeWzvPDo1KDdWQ|0.1|Default||end|failure|7785
Exception in component tMysqlInput_2
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1127)
    at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:356)
    at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2502)
    at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2539)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2321)
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:832)
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:46)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:417)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:344)
    at java.sql.DriverManager.getConnection(DriverManager.java:571)
    at java.sql.DriverManager.getConnection(DriverManager.java:215)
    at prod.avrregfromfiles_0_1.AVRRegFromFiles.tMysqlInput_2Process(AVRRegFromFiles.java:1676)
    at prod.avrregfromfiles_0_1.AVRRegFromFiles.tSleep_1Process(AVRRegFromFiles.java:1080)
    at prod.avrregfromfiles_0_1.AVRRegFromFiles.tSystem_1Process(AVRRegFromFiles.java:934)
    at prod.avrregfromfiles_0_1.AVRRegFromFiles.tSystem_2Process(AVRRegFromFiles.java:712)
    at prod.avrregfromfiles_0_1.AVRRegFromFiles$10.run(AVRRegFromFiles.java:8981)
Caused by: java.net.ConnectException: Connection refused: connect
    at java.net.DualStackPlainSocketImpl.connect0(Native Method)
    at java.net.DualStackPlainSocketImpl.socketConnect(DualStackPlainSocketImpl.java:79)
    at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:339)
    at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:200)
    at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:182)
    at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:172)
    at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)
    at java.net.Socket.connect(Socket.java:579)
    at java.net.Socket.connect(Socket.java:528)
    at java.net.Socket.<init>(Socket.java:425)
    at java.net.Socket.<init>(Socket.java:241)
    at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:258)
    at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:306)
    ... 19 more

[Anonymous]

Hello,
I have the same issue and used the two solutions without result. Someone to help?
Here my error in talend:
Exception in component tMysqlInput_1
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1127)
    at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:356)
    at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2502)
    at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2539)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2321)
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:832)
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:46)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:417)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:344)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at vente_privee.logistic_task_join_wp_ams_cmd_0_1.logistic_task_join_wp_ams_cmd.tSleep_1Process(logistic_task_join_wp_ams_cmd.java:1089)
    at vente_privee.logistic_task_join_wp_ams_cmd_0_1.logistic_task_join_wp_ams_cmd.runJobInTOS(logistic_task_join_wp_ams_cmd.java:2280)
    at vente_privee.logistic_task_join_wp_ams_cmd_0_1.logistic_task_join_wp_ams_cmd.main(logistic_task_join_wp_ams_cmd.java:1992)
Caused by: java.net.ConnectException: Connection refused: connect
    at java.net.DualStackPlainSocketImpl.connect0(Native Method)
    at java.net.DualStackPlainSocketImpl.socketConnect(Unknown Source)
    at java.net.AbstractPlainSocketImpl.doConnect(Unknown Source)
    at java.net.AbstractPlainSocketImpl.connectToAddress(Unknown Source)
    at java.net.AbstractPlainSocketImpl.connect(Unknown Source)
    at java.net.PlainSocketImpl.connect(Unknown Source)
    at java.net.SocksSocketImpl.connect(Unknown Source)
    at java.net.Socket.connect(Unknown Source)
    at java.net.Socket.connect(Unknown Source)
    at java.net.Socket.<init>(Unknown Source)
    at java.net.Socket.<init>(Unknown Source)
    at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:258)
    at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:306)
    ... 17 more
disconnected