Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1168
  • Last Modified:

Connect Apache Ant remotely to MySQL over SSH tunneling

A hosting provider I'm working with has port 3306 blocked for public access on their firewall, they recommend connecting to MySQL over ssh tunneling. This works fine for MySQL Workbench as it provides such functionality, but I'm trying to get a similar thing going with Apache Ant so I can automate restoring databases. I'm trying to figure out if I'm doing this correctly, from what I've read <sshsession> in Apache Ant 1.8.x provides tunneling. But I'm not sure whether to use localtunnel or remote tunnel and I'm not sure if I'll be able to even do what I want over a tunnel. Any suggestions?

<sshsession host="example.com" username="user" keyfile="${sshKeyLocation}" passphrase="password">
    <remotetunnel rport="3306" lhost="example.com" lport="3306"/>
    <sequential>
        <sql driver="com.mysql.jdbc.Driver" url="jdbc:mysql://example.com:3306/${databaseName}" userid="dbuser" password="dbpass">
            <transaction src="temp.sql"/>
        </sql>
    </sequential>
</sshsession>
0
level9wizard
Asked:
level9wizard
1 Solution
 
johanntagleCommented:
Maybe you can try it in two steps:

1.  Set up tunnel:

ssh -f example.com -L 3305:127.0.0.1:3306 -N

2.  Use 127.0.0.1:3305 in your connection string.

(Replace 3305 with whatever you want as the local port).

0
 
level9wizardAuthor Commented:
Thanks, this lead me in the right direction. The actual code used with Ant (in case anyone else stumbles on this Q/A):

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<project name="Release Builder" default="release" basedir=".">
    <description><![CDATA[Release Builder]]></description>
   
    <!-- REQUIREMENTS: MySQL JDBC Driver, DatabaseNameParser, DomainFileName, jsch -->
   
   
    <!-- Configuration -->
    <property name="mysqlUser" value="root"/>
    <property name="mysqlPassword" value="xxxxxxx"/>
    <property name="sshKeyLocation" value="private.ossh"/>
   
    <!-- End Configuration -->
    <basename property="basedir.name" file="${basedir}"/>
   
    <target name="release" description="Release to the live server">
        <!-- Create the time stamp -->
        <tstamp/>
       
        <sshsession host="example.com" username="root" keyfile="${sshKeyLocation}" passphrase="xxxxxxxxxxxx" trust="true">
            <localtunnel rport="3306" rhost="example.com" lport="3305"/>
            <sequential>
                <sql driver="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3305/project_name" userid="zzzzzzz" password="xxxxxxx">
                    SELECT * FROM SiteTree LIMIT 1
                </sql>
            </sequential>
        </sshsession>
    </target>
</project>
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now