How to connect to your Acquia Drupal production database from your desktop
https://www.zyxware.com/sites/default/files/styles/user_image/public/default_images/index.png?itok=2YmREnrP
BY Anoop John
6 years ago
Digital-Marketing
0 comments comment

If you are in the habit of running queries directly on your production database to generate custom reports then you will run into the requirement of having to connect to your Drupal database on the Acquia cloud hosting. One of our clients wanted to do this and this is how we got this working for them.

When you host your Drupal site on the Acquia cloud your site resides on a set of different VPSes that runs Varnish, Apache, MySQL and memcache. Additionally you have your staging server, your development server as well as your production servers - all of them being VPSes.

MySQL Workbench is a desktop application that runs on GNU/Linux, Windows and Mac. It is a GUI client that allows you to connect to any MySQL instance in the local network or remotely provided you can access the server through the network. It also allows you to tunnel through SSH and connect to MySQL servers through intermediate servers when the MySQL server is not directly accessible. This is what we will make use in connecting to the Acquia cloud MySQL database since Acquia does not allow direct connections to their MySQL VPS instance from outside of the Acquia cloud.

You should get the relevant credentials for the server and databases from your Acquia control panel

The following are the information you would need

  • Name of the server where your production database is running
  • Name of the server where your production apache server is running
  • You should already have added your SSH keys in Acquia user configuration
  • Name of the database and the database user + password to access the database
  • Username to be used to connect to the web server

You can get these pieces of information from

  • Sites » Cloud » Servers
  • Sites » Cloud » Users & keys
  • Sites » Cloud » Databases » Prod » Connection details

Once you have this ready you can fire up MySQL Workbench and create a new connection.

  • Enter a name for the connection - something like "your site production db"
  • Set Connection Method to "Standard TCP/IP over SSH"
  • Set SSH Host Name to your production web server name. It should be something like web-1234.prod.hosting.acquia.com
  • Set SSH Key file to your SSH private key file
  • Set MySQL hostname to the name of the production database server. Use just the hostname and not the fully qualified domain name. So this should be like fsdb-1235
  • Set MySQL Server port to 3306 (the default)
  • Set Username to your MySQL production database username
  • Set Password to your MySQL production database password
  • Set Default Schema to your production database name

Once you enter these details try testing the connection. If you can't get the connection through you will have to do a bit of troubleshooting from the command line to see where exactly you are not able to get through. Independently you should be able to SSH to your production web server and from there connect to the production database using the mysql command line client. If that piece is not working you will have to re-check the information you have compiled.

If you need help with managing your Drupal site hosted on Acquia let us know and we will be happy to assist you with the same.


RELATED ARTICLE

/themes/custom/zyxpro_light/images/placeholder.png
close

on 03rd December 2013 / by Anoop John
In the last year or so we have been in the process of transitioning from a small business to a me Digital Marketing Content Marketing Lead Generation Leave a reply Your email address will not be published. Required fields are marked * TheAnand (not verified) access_time 10 Jul 2020 - 17:33 Hey, This will be interesting to follow! Looking forward to see your learnings from new media and do let me know if you need any help with optimizing your digital media marketing initiatives. Anoop John access_time 10 Jul 2020 - 17:33 In reply to all the best by TheAnand (not verified) Thanks Anand for the offer to help. Will probably have a lot of questions as I move along. Would definitely need help from people like you to answer these questions. Anoop Add new comment
Leave a reply
Your email address will not be published. Required fields are marked *

Filtered HTML

  • Web page addresses and email addresses turn into links automatically.
  • Allowed HTML tags: <a href hreflang> <em> <strong> <cite> <blockquote cite> <code> <ul type> <ol start type='1 A I'> <li> <dl> <dt> <dd> <h2 id='jump-*'> <h3 id> <h4 id> <h5 id> <h6 id>
  • Lines and paragraphs break automatically.
The content of this field is kept private and will not be shown publicly.
CAPTCHA This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.