Connection issues while connecting to a MySQL service instance from an external MySQL management tool

Products

• Services Suite

Issue/Introduction

Symptoms:
When attempting to connect to a Pivotal Web Services (PWS) MySQL 1 or 2 instance via an external MySQL management tool, the following error appears:

Can't connect to MySQL server on 'p-mysql-proxy.run.pivotal.io' (10060)

Environment

Cause

There is no direct access to MySQL service instances from outside of PWS because they are shielded externally by Amazon Web Service (AWS) networking rules. The PWS MySQL DB instances are not externally visible and are only accessible from the apps network.

Resolution

SSH Tunneling and the MySQL CLI

• Configure an SSH tunnel to your service instance using CF SSH. Tailor the example command below with information from your service key or service instance:

$ cf ssh -L 63306:us-cdbr-iron-east-01.p-mysql.net:3306 YOUR-HOST-APP

• Use any available local port for port forwarding. For example, 63306.
• Replace us-cdbr-iron-east-01.p-mysql.net with the address provided under hostname from a service key or service instance.
• Replace 3306 with the port provided by your service key or service instance.
• Replace YOUR-HOST-APP with the name of your host app. It doesn't need to be the app to which your MySQL service is bound. For example, if that app has not staged or started.
• After you enter the command, open another terminal window and perform the steps mentioned below.

To establish the direct command-line access to your service instance, use the relevant command line tool for that service. This example uses the MySQL command line client to access the p-mysql service instance:

$ mysql -u b5136e448be920 -h 0 -p -D ad_b2fca6t49704585d -P 63306

• Replace b5136e448be920 with the username provided under username in your service key or service instance.
• -h 0 instructs mysql to connect to your local machine.
• -p instructs mysql to prompt for a password. When prompted, use the password provided under password in your service key.
• Replace ad_b2fca6t49704585d with the database name provided under name in your service key.
• -P 63306 instructs mysql to connect on port 63306.
• Alternatively, deploying a tool on PWS such as phpMyAdmin is also an option. You can find an example of a CF ready version here.