topic Re: Connect through ssh tunnel to mysql in Talend Studio

Connect through ssh tunnel to mysql

Anonymous — Sat, 16 Nov 2024 12:11:43 GMT

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.

Re: Connect through ssh tunnel to mysql

Anonymous — Sun, 16 Sep 2012 08:08:52 GMT

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/71693
Hope it helps
regards
laurent

Re: Connect through ssh tunnel to mysql

Anonymous — Tue, 18 Sep 2012 14:09:05 GMT

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.

Re: Connect through ssh tunnel to mysql

Anonymous — Tue, 18 Sep 2012 14:49:06 GMT

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

Re: Connect through ssh tunnel to mysql

jens_fricke — Tue, 20 Nov 2012 23:41:47 GMT

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.

Re: Connect through ssh tunnel to mysql

phil974 — Sun, 29 Dec 2013 20:13:37 GMT

Hi,
Easier ideas , solutions ??

Re: Connect through ssh tunnel to mysql

Anonymous — Mon, 31 Aug 2015 15:59:18 GMT

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.

Re: Connect through ssh tunnel to mysql

Anonymous — Tue, 01 Sep 2015 11:32:22 GMT

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

regards
laurent

Re: Connect through ssh tunnel to mysql

_AnonymousUser — Thu, 05 Nov 2015 14:21:51 GMT

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

Re: Connect through ssh tunnel to mysql

Anonymous — Thu, 12 May 2016 14:35:36 GMT

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

Re: Connect through ssh tunnel to mysql

Anonymous — Thu, 12 May 2016 14:49:38 GMT

Sorry the error is made by me jens.fricke's solution worl fine

Re: Connect through ssh tunnel to mysql

Anonymous — Wed, 21 Dec 2016 09:41:56 GMT

Hello -
Been trying to do the same, however, we have differences as follows:
1. Using Talend 6.2.1 on Windows 10
2. Linux Server (RHEL 7 MySQL Package) separate from DB server
Goal is to setup DB connection from local PC (using Talend) to MySQL via SSH connection: Local PC -> SSH Connection (e.g. mdmuser/<password>) -> MySQL DB (e.g. talend_mdm_dev/<password>).
It is necessary to connect to Linux server first before accessing DB server.

Re: Connect through ssh tunnel to mysql

cacak — Fri, 07 Apr 2017 03:55:11 GMT

Hello -
Been trying to do the same, however, we have differences as follows:
1. Using Talend 6.2.1 on Windows 10
2. Linux Server (RHEL 7 MySQL Package) separate from DB server
Goal is to setup DB connection from local PC (using Talend) to MySQL via SSH connection: Local PC -> SSH Connection (e.g. mdmuser/<password>) -> MySQL DB (e.g. talend_mdm_dev/<password>).
It is necessary to connect to Linux server first before accessing DB server.

Please describe the components you added in Talend Jobs.
I want to connect via tssh, but i dont know about the command or anything more.

Re: Connect through ssh tunnel to mysql

cacak — Fri, 07 Apr 2017 08:27:17 GMT

hi
Never use "localhost" for hostname (but 127.0.0.1) if ssh host and mysql server on the same machine

Hope it helps
regards
laurent

How is your components structure?
I have a structure : tSSH-----(main)---tFlowToIterate---(Iterate)---tMysqlInput-----(main)----tFileOutputExcel
And the result is nothing, just connected for a long time.
tSSH: Host (My servers IP Public), Port (22), User (myuser), auth method (public key),
Commands : "mysql -u root -p myDBName"
"myrootPass;"
tMysqlInput: Component Setting like localhost conection, with host 127.0.0.1

When i ran only the tSSH component with command "ls;", its running weel.
Hope somebody can help me to fix it.

Re: Connect through ssh tunnel to mysql

cacak — Fri, 07 Apr 2017 08:46:11 GMT

And i can execute those command (mysql -u root ip DBName) and some select query via PuTTY