Digital Marketing

How to connect to your Acquia Drupal production database from your desktop

Anoop John | | 3 min read

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 Topics