One Star

Connect through ssh tunnel to mysql

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.
14 REPLIES
Seventeen Stars

Re: Connect through ssh tunnel to mysql

hi
Never use "localhost" for hostname (but 127.0.0.1) if ssh host and mysql server on the same machine
http://www.talendforge.org/forum/viewtopic.php?id=2381
Hope it helps
regards
laurent
One Star

Re: Connect through ssh tunnel to mysql

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.
One Star

Re: Connect through ssh tunnel to mysql

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

Re: Connect through ssh tunnel to mysql

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.
One Star

Re: Connect through ssh tunnel to mysql

Hi,
Easier ideas , solutions ??
One Star

Re: Connect through ssh tunnel to mysql

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.
Seventeen Stars

Re: Connect through ssh tunnel to mysql

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

regards
laurent
One Star

Re: Connect through ssh tunnel to mysql

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
One Star

Re: Connect through ssh tunnel to mysql

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
One Star

Re: Connect through ssh tunnel to mysql

Sorry the error is made by me jens.fricke's solution worl fine Smiley HappySmiley HappySmiley Happy
One Star

Re: Connect through ssh tunnel to mysql

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.
One Star

Re: Connect through ssh tunnel to mysql

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.
One Star

Re: Connect through ssh tunnel to mysql

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.
One Star

Re: Connect through ssh tunnel to mysql

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