MySQL Workbench and MySQL CLI Client

Table of Contents

• How to Connect Them with a9s MySQL Services
• Prerequisites to Connect with a9s MySQL
• Tunneling with a9s MySQL Service
• Connect with MySQL Workbench
• Connect with MySQL CLI Client
• Conclusion

How to Connect Them with a9s MySQL Services

anynines recently released its a9s MySQL Data Service offering. Most of the developers use MySQL Workbench client for connecting to MySQL server. Now, let us walk down with all the required steps to connect our a9s MySQL service instances with MySQL Workbench and as well as MySQL CLI client in this blog post.

Prerequisites to Connect with a9s MySQL

To connect with a9s MySQL one needs to have the following prerequisites:

• MySQL Workbench installation. Please do follow the instructions at https://dev.mysql.com/doc/workbench/en/wb-installing.html
• MySQL CLI installation. Please do follow the instructions at https://dev.mysql.com/doc/refman/5.7/en/document-store-shell-install.html

Tunneling with a9s MySQL Service

This is our first step to connect with a9s MySQL service. Here, we are creating a tunnel to a9s MySQL Data Service bounded with your_application hosted with a hostname as your_host_name. To obtain the hostname of a data service connected to an application, we need to use CF CLI command as shown and pick the hostname from the key-value pair called “host” as shown below:

1$ cf env your_application
2Getting env variables for app your_application in org your_user_org / space test as your_username...   OK

1{
2  "VCAP_SERVICES": {
3    "a9s-mysql101": [
4      {
5        "binding_name": null,
6        "credentials": {
7          "dns_servers": [
8            "10.0.35.11"
9          ],
10          "host": "your_host_name",
11          "hosts": [
12            "your_one_of_the_hosts"
13          ],
14          "name": "database_name",
15          "password": "mysql_password",
16          "port": 3306,
17          "uri": "mysql://mysql_username:mysql_password@database_name.your_host_name:3306/database_name",
18          "username": "mysql_username"
19        },
20        "instance_name": "your_a9s_mysql_service",
21        "label": "a9s-mysql101",
22        "name": "your_a9s_mysql_service",
23        "plan": "mysql-single-small",
24        "provider": null,
25        "syslog_drain_url": null,
26        "tags": [
27          "sql",
28          "database",
29          "object-relational",
30          "consistent"
31        ],
32        "volume_mounts": []
33      }
34    ]
35  }
36}

Now, let us create a tunnel to our a9s MySQL service by using CF CLI command as shown.

1$ cf ssh your_application -L 3306:your_host_name:3306

Replace your _application and your _host_name from the output obtained above with cf env command.

Connect with MySQL Workbench

After successful tunnel creation, please do test the connection and add it into your workbench with the following screenshots below step by step. This illustration is done using MAC OS.

1. Fill Username section from the above cf env output section as shown below:

3. Now, let us test the connection by filling in the password with mysql_password from the above cf env output section as shown below:

5. After successfully testing the connection, you will be prompted with an output as shown below:

Connect with MySQL CLI Client

To connect with MySQL CLI Client after tunnel creation, just execute the below command as shown below:

1$ mysql -u mysql_username -h 0 -p -D database_name -P 3306

In the above command, substitute mysql_username and database_name from the above cf env output section. Please do make sure that the tunnel is established in your terminal when you are accessing a9s MySQL services using MySQL CLI client.

Conclusion

A simple guide to setup various MysQL clients to our a9s MySQL Data Service. Hope you enjoyed the step by step tutorial.