Remote access to dev cloud database

Remote access to dev cloud database

8 posts / 0 new
Last post
dswier's picture
dswier
Points: 0
Remote access to dev cloud database

I'm wondering if there is any way to make changes to the database on our dev cloud server remotely. We currently use drush sql-sync to upload our database every hour, and that works fine. However, I now have a node that needs to be updated on the dev cloud server more frequently and would like a way to just send that change to the dev cloud database. I tried adding the db connection information for the dev environment as a db_url in settings.php, and then switching to it with db_set_active() and running the update query. This does not appear to work. Am I doing something wrong or is it just not possible to make changes to the dev cloud database from my local environment?

Status: 
Unresolved
dwhite5's picture
dwhite
Points: 0

We would like to access the dev database remotely as well. Help much appreciated!

steve.cronen-townsend@acquia.com's picture
steve.cronen-townsend
Points: 49

Yes, you can change your databases on Acquia Cloud via a local client. You have full access to your databases using the Connections Details on your Databases page. With a couple tips from my experience setting this up with a handful of different clients on different operating systems, you will be all set.

Some examples of clients that people use are

The most important point is that access to your Acquia server must use an SSH tunnel. All the clients listed support that, but I have seen really lightweight database clients that do not. And your client to connect to Acquia servers must handle this protocol. Make sure you configure the connection to your Acquia server that way.

The other common mistake is using the Connection Details table itself. The thing that gets people is that the Database column does not give the name of the database as far as MySQL is concerned -- you need to use the entry in the MySQL Name column.

Knowing that this is possible and those couple tips ought to help a lot. It is one of those things where it doesn't work at all until lots of settings are exactly right. So knowing that it is possible is the most important thing.

And you will love the results. It is like having a slick version of PHPMyAdmin (a web application) on your local machine with a native user-interface.

Regards,

Steve Cronen-Townsend | Acquia Client Advisory Team. Acquia Cloud documentation.

 

dwhite5's picture
dwhite
Points: 0

Thanks Steve. This is indeed very helpful. However, we would like to configure local drupal instances (running on the developers machine) to use the dev database. Currently all developers are working on a local database and are manually migrating changes to the dev database. This is very time consuming and failure prone. Any tips on getting a remote drupal install to connect to the dev database?

Barry Jaspan's picture
barry.jaspan
Points: 56

The best/only way to get a live connection to a database on Dev Cloud is via an SSH tunnel. Each of your developers can set one up so that, e.g. localhost:33066 becomes their "Dev Cloud database server" that they put into settings.php. If the SSH tunnel is open, their local Drupal site will work. If not, it will fail with a db connection error, and then they know they need to run ssh to open the tunnel.

Opening the tunnel is easy, something like:

ssh site@srv-NNNN.devcloud.hosting.acquia.com -L 33066:localhost:3306

Now connections to 33066 on the machine running ssh will be forwarded to "localhost" on srv-NNNN, which of course is srv-NNNN itself, port 3306, which is mysqld.

Status Change: 
Resolved
steve_4's picture
steve
Points: 0

This is great, although I can't seem to get it to work, I'm still getting an Access denied when I use these values in my settings.php. So for my database array, for example, I'm using the following:

$databases = array (
204   'default' =>
205   array (
206     'default' =>
207     array (
208       'database' => 'remote_db',
209       'username' => 'remote_usr',
210       'password' => 'remote_pass',
211       'host' => 'localhost',
212       'port' => '33066',
213       'driver' => 'mysql',
214       'prefix' => '',
215     ),
216   ),
217 );

I am using the credentials from Cloud -> Databases on the Acquia Network. I set-up the tunnel with openssh exactly as you described. Can you suggest where my error is?

contact38's picture
www.ofsaa.on.ca
Points: 0

We got this working by setting the host to 127.0.0.1. Thanks Barry!

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