Issue with SSH tunnel into DB - any ideas?

Issue with SSH tunnel into DB - any ideas?

16 posts / 0 new
Last post
contentformula's picture
devtest1
Points: 1
Issue with SSH tunnel into DB - any ideas?

Hiya,

Just getting up and running with the free cloud demo but have hit a roadblock that the documentation and forums haven't helped with.

1) SSH Tunnelling into DB

  • I've set up and configured a key in the web dashboard
  • Using this key, I can SSH onto the server without a hitch via the command line
  • Once on the server via SSH I can then log into mysql correctly as well (mysql -u myDBuser -pmyDBpassword)
  • However, in Navicat, I've used the same SSH key and settings to initiate a tunnel to the server, and it connects to the server fine.
  • Using the DB settings from the web dashboard, I've entered the following as settings to connect to mysql:
    • Hostname / IP address: localhost
    • Port: 3306
    • Username: myDBuser (As stated in connection details popup for the dev DB)
    • Password: myDBpassword (As stated in connection details popup for the dev DB)
  • No matter what I tweak or try the error is: 2013: lost connection to MYSQL server at 'reading initial communication packet', system error:0

Basically - the MYSQL user can't log in. Any ideas what's wrong? I've seen some forum posts using 127.0.0.1 but it makes no difference. This is pretty standard stuff we do day in day out with our other servers, am I missing something specific for the Acquia Cloud?

2) Just a little bugbear really - I've tried adding my standard .bashrc file into my user home directory but can't get even simple colour changes implemented - do I really have to work in black and white the whole time or am I (again) missing something.

Thanks for any help - I'm pretty sure it's just me doing something silly, but on the other side, it might be worth tweaking some of the documentation to link through the resource I'm sure are out there (but are just hidden).

Cheers,
CF

Status: 
Resolved
contentformula's picture
devtest1
Points: 1

Hiya,

Does anyone have any ideas what the issue might be with the SSH tunnel? All help appreciated.

Status Change: 
Unresolved
contentformula's picture
devtest1
Points: 1

OK - so the EXACT same process works when tunneling into a normal paid dev cloud subscription

So I presume that tunneling is DISABLED on the free/demo tier then?

jason.stanbery's picture
Jason Stanbery
Points: 0

hostname isn't "localhost" or "127.0.0.1"

It's the first part of the path to your server's path. So if your server is:
ded-XXXX.prod.hosting.acquia.com

hostname is: ded-XXXX

Barry Jaspan's picture
barry.jaspan
Points: 56

You are correct that we do not currently allow SSH tunneling into Acquia Cloud free servers.

Barry Jaspan's picture
barry.jaspan
Points: 56

Looking into this further, it turns out that we can allow ssh tunneling specifically to mysqld (localhost port 3306). I will look into supporting that.

msypes@viridianhealth.com's picture
msypes
Points: 1

I've been able to access my databases using SSH tunneling with both Navicat and Sequel Pro.

Barry Jaspan's picture
barry.jaspan
Points: 56

Ooops! Yes, some time ago we changed our free tier so that SSH tunnels to port 3306 are allowed. I just forgot to come back to this forum post and update it.

Status Change: 
Resolved
services@tuidev.com's picture
services
Points: 0

Does this still work?

We're having trouble using Sequel Pro on the free tier:

MySQL said: Lost connection to MySQL server at 'reading initial communication packet', system error: 0

dale.smith@tuidev.com's picture
dale.smith
Points: 1

I can SSH into the AC server manually and run mysql just fine (using localhost, 127.0.0.1, and AC site host as my -h flag all work).
However, when using Sequel Pro to connect to mysql via SSH, I get the same error as #1 and #9. Sequel Pro won't let me use localhost as the mysql host, so I'm forced to use either 127.0.01 or my AC site host.

The AC docs say to check a StackOverflow post, of which most replies say to change mysql/firewall settings, and I won't be doing that to AC servers :)

Status Change: 
Unresolved
services@tuidev.com's picture
services
Points: 0

Confirmed this doesn't work on the free tier. Using the same credentials it now works after upgrading to paid.

Barry Jaspan's picture
barry.jaspan
Points: 56

Yes, ssh tunneling to your MySQL server on Acquia Cloud still works fine, including on our free tier. However, you have to do it just right (as you should be required to for such a security-critical function). Here's how:

1. Get your Cloud host name and MySQL username, password, and database name. Note that your database username is *not* your site's name; it is something like "sNNNNNN".

$ drush @mysite.dev  sql-connect
mysql --database=[db_name] --host=[host_name] --user=[user_name] --password=[password] --port=3306

2. Set up an ssh tunnel for a local port to port 3306 on your Cloud server. In this example, I use local port 33066 because I already have a mysqld running on local port 3306. The "-Nf" options to ssh tell it to background itself after creating the tunnel.

$ ssh -Nf -L 33066:localhost:3306 mysite.dev@[host_name].devcloud.hosting.acquia.com

3. Run the mysql client locally, connecting to the local port, providing the correct credentials:

$ mysql --protocol tcp --host localhost --port 33066 -u [user_name] -p[password] [db_name]
... misc text here ...
mysql>
Status Change: 
Resolved
s.fincher@ucas.ac.uk's picture
s.fincher
Points: 0

I use HeidiSQl to connect to both free and paid tiers and have found that using 'localhost' as the hostname works for both, but using '127.0.0.1' for the hostname works for paid, but not for free.

pieter.de.troyer@wunderkraut.com's picture
pieter.de.troyer
Points: 0

how come this is not working for sequal pro? I thought sequel pro just made the tunnel for you. Unfortunately, sequel pro doesn't allow 'localhost' as a mysql hostname when connecting through ssh. so i'm still puzzled. command line works fine.

pdailor@rochesterconsolidated.com's picture
pdailor
Points: 5

https://docs.acquia.com/articles/connect-mysql-workbench-acquia-cloud-da...

This is what finally worked for me:

"Windows users will have probably created an SSH key in PuTTY's format, which by default produces a file with a .ppk extension. MySQL Workbench is not compatible with this key format. Windows users will need to convert it to the OpenSSH format. PuTTYgen has an option for this format. In PuTTYgen, click File > Load private key and choose a key file, and then go to Conversions > Export OpenSSH key. The file extension that you use is not important; as a suggestion, use a .key extension for the file"

yuan.xie's picture
yuan.xie
Points: 30

Documentation is here: https://docs.acquia.com/articles/remote-database-administration-using-th...

Please also see the screenshot for a proper connection setup to Acquia Cloud including free tier.

Make sure your ssh keys are set correctly.

One catch here is that you will need to use "localhost" as the MySql Host. Sequel Pro tries to be smart and make you use 127.0.0.1 instead of "localhost", which then unfortunately will not work with Acquia Cloud. The workaround here is to add a space after "localhost" (again, see screenshot).

Status Change: 
Resolved