Connect MySQL Workbench to an Acquia Cloud database

Issue

How do I use MySQL Workbench to manage a MySQL database on Acquia Cloud?

Resolution

It's possible to manage a MySQL database on Acquia Cloud using MySQL Workbench. To do this, you'll need to enable SSH access using a password-protected key. Please be aware that older versions of MySQL Workbench have a bug where password protected private key files fail to authenticate. If you are not using the current version of MySQL Workbench the following steps will fail. If you're unable to successfully sign in to your server using an SSH key, you won't be able to establish the SSH tunnel through which you'll be connecting to the MySQL database. Ensure that this works properly before proceeding.

Notes

Some users have had difficulty connecting to their database using MySQL Workbench. If you have issues, consider using Acquia Cloud IDE to download your site, make changes locally, and then export it back to Acquia Cloud.

You can not SSH directly into database servers (fsdb and fsdbmesh machines), but you can SSH into web servers.

Note

Some users have had difficulty configuring MySQL workbench. You may find the Database Administration module useful in these cases.

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. For more information on PuTTYgen and keys, see WinSCP's documentation on keys.

On Linux, use the id_dsa or id_rsa file in your .ssh directory.

You can use Drush to retrieve your MySQL connection details. You will need to successfully SSH to a web server first, which ensures that you have working SSH keys. Here's an example of a Drush command that you would use to get connection information:

$ drush @prod sql-connect
mysql --database=yourdatabase --host=yourhost --port=3306 --user=yoursshusername --password=yoursshpassword
$

Alternately, you can find instructions for gathering your credentials in the following field descriptions:

In MySQL Workbench, open Database > Manage Connections, and then click New. The important settings here are:

• Connection Name - Enter a connection name of your choice.
• Connection Method - Click Standard TCP/IP over SSH.
• SSH Hostname - This is the full hostname of the web server, as displayed on your Acquia Cloud servers page; (do not include the username@ at the start). It should look something like server.environment.hosting.acquia.com. The server and environment will depend on your account.
• SSH Username - Enter the username that you use to connect using SSH (the part to the left of the @ from the login displayed on your Acquia Cloud Users and keys page).
• SSH Password - You can ignore this because you will be using an SSH key. (You'll be prompted for the passphrase for your SSH key later.)
• SSH Key File - Browse your way to the SSH key file you created or converted to the OpenSSH format.
• MySQL Hostname - Use the MySQL Hostserver on your Acquia Cloud Databases; click Cloud > Databases, choose a database, and then click Database credentials on the menu. This can be the same server or a different dedicated server from which you tunnel over SSH. You can use localhost instead of 127.0.0.1.
• MySQL Server Port - On most systems this will be 3306. Check with your MySQL administrator if you are unsure.
• Username - Enter the database username as displayed in the table on your Acquia Cloud Databases page.
• Password - You can Store in Vault your database password (as displayed in the table on your Acquia Cloud Databases page), or you can leave this blank to be prompted when you connect.
• Default Schema - You can leave this blank.

After you enter the preceding options, click Test Connection, and then you will be prompted for passwords. The first will be the passphrase for your SSH key. If you haven't already stored it, you may also be prompted for the database user's password.

If all goes well, the system should inform you that you're connected and that the connection parameters are correct. You can then close the Manage DB Connections dialog box and use the connection.

Connection issues

Some users may still experience connection issues, with errors like:

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

Stack overflow has some useful suggestions on further troubleshooting.