Using a9s MySQL

This topic describes how to use a9s MySQL.

Use a9s MySQL for PCF with an App

To use a9s MySQL for PCF with an app, create a service instance and bind the service instance to your app. For more information about managing service instances, see Managing Service Instances with the cf CLI.

View the a9s MySQL for PCF Service

After the tile is installed, you can see the service a9s-mysql101 and its service plans appear in your PCF marketplace. Run cf marketplace to see the service listing:

$ cf marketplace
Getting services from marketplace in org test / space test as admin...
OK
service         plans                                                                           description
a9s-mysql101    mysql-single-small, mysql-single-big, mysql-cluster-small, mysql-cluster-big    This is the a9s MySQL 10 service.
</pre>

### Create a Service Instance

To provision a MySQL database, run `cf create-service`. For example.

```shell
$ cf create-service a9s-mysql101 mysql-single-small my-mysql-service-instance

Depending on your infrastructure and service broker utilization, it might take several minutes to create the service instance.

Run the cf services command to view the creation status. This command displays a list of all your service instances. To view the status of a specific service instance, run cf service NAME-OF-YOUR-SERVICE-INSTANCE.

Bind an App to a Service Instance

After your database is created, run cf bind-service to bind the service to your app:

$ cf bind-service a9s-mysql-app my-mysql-service-instance

Restage or Restart Your App

To enable your app to access the service instance, run cf restage or cf restart to restage or restart your app.

Obtain Credentials for Accessing Your Service Instance

After a service instance is bound to an app, the credentials of your MySQL database are stored in the environment variables of the app. Run cf env APP-NAME to display the environment variables.

You can find the credentials in the VCAP_SERVICES key.

$ cf env a9s-mysql-app
Getting env variables for app a9s-mysql-app in org test / space test as admin...
OK

System-Provided:
{
 "VCAP_SERVICES": {
  "a9s-mysql101": [
   {
    "binding_name": null,
    "credentials": {
     "host": "d15575b.service.dc1.a9s-mysql-consul",
     "name": "d15575b",
     "password": "a9s-password",
     "port": 3306,
     "uri": "mysql://a9s-brk-usr:a9s-password@d15575b.service.dc1.a9s-mysql-consul:3306/d15575b",
     "username": "a9s-brk-usr"
    },
    "label": "a9s-mysql101",
    "plan": "mysql-cluster-small",
    "tags": [
     "sql",
     "database",
     "object-relational",
     "consistent"
    ],
    "volume_mounts": []
   }
  ]
 }
}
...

You can use the host and password values to connect to your database with a MySQL client.

Best Practices

There are some best practices for using service binding information in apps in a separate document.

Timezone configuration

Sometimes the app developer needs to configure a particular timezone to be used by the database server. We currently use the default timezone configured in the BOSH stemcell (UTC by default).

Changing this value affects DATETIME columns and functions such as:

  • NOW()
  • SYSDATE()
  • CURDATE()
  • CURTIME()
  • UNIX_TIMESTAMP()

In cases where a particular timezone is desired we suggest to set this timezone using a session parameter (setting: "time_zone"). Example:

mysql> SET SESSION time_zone = 'Europe/Berlin'

Delete an a9s MySQL Service Instance

WARNING: Before deleting a service instance, you must backup data stored in your database. This operation cannot be undone and all the data is lost when the service instance is deleted.

Before you can delete a service instance, you must unbind it from all apps.

List Available Services

Run cf services to list available services.

$ cf services

Getting services in org test / space test as admin...
OK

name                          service          plan                 bound apps        last operation
my-mysql-service-instance     a9s-mysql101     mysql-single-small   a9s-mysql-app     create succeeded

This example shows that my-mysql-service-instance is bound to the a9s-mysql-app app.

Unbind a Service Instance

Run cf unbind-service to unbind the service from your app:

$ cf unbind-service a9s-mysql-app my-mysql-service-instance

Delete a Service Instance

After unbinding the service, it is no longer bound to an app. Use cf delete-service to delete the service:

$ cf delete-service my-mysql-service-instance

It might take several minutes to delete the service. Deleting a service deprovisions the corresponding infrastructure resources. Run the cf services command to view the deletion status.

Create a fork of a service instance

The procedure of forking a service instance involves creating a backup of a service instance, modifying the backup a bit and restoring it to a different service instance.

Having two service instances is a prerequisite for the process:

$ cf services
Getting services in org my_org / space my_space as user@domain.com...

name     service        plan                 bound apps   last operation       broker
mysql1   a9s-mysql101   mysql-single-small                create succeeded     a9s-mysql
mysql2   a9s-mysql101   mysql-single-small                create succeeded     a9s-mysql

Having a service key for each of the service instances comes in handy later when we need information about the database and credentials to connect to the database. Create one for each service instance using e.g.: cf create-service-key mysql1 mykey.

You can then have a look at the keys contents:

$ cf service-key mysql1 mykey
Getting key mykey for service instance mysql1 as user@domain.com...
{
 "host": "msd551540.service.dc1.a9ssvc",
 "hosts": [
  "msd551540-mysql-0.node.dc1.a9ssvc"
 ],
 "name": "msd551540",
 "password": "a9s14dc031b05a2a2c6568584a6626777d75cd09f50",
 "port": 3306,
 "uri": "mysql://a9sa27fa005143ebbe1c093d81d06b7:a9s14dc031b05a2a2c6568584a6626777d75cd09f50@msd551540.service.dc1.a9ssvc:3306/msd551540",
 "username": "a9sa27fa005143ebbe1c093d81d06b7"
}

$ cf service-key mysql2 mykey
Getting key mykey for service instance mysql2 as user@domain.com...
{
 "host": "msd20f9ad.service.dc1.a9ssvc",
 "hosts": [
  "msd20f9ad-mysql-0.node.dc1.a9ssvc"
 ],
 "name": "msd20f9ad",
 "password": "a9s48440550c2e03145a9818bb7664771b3b1ed3976",
 "port": 3306,
 "uri": "mysql://a9sc5e4dbd17d61595065e00627239d:a9s48440550c2e03145a9818bb7664771b3b1ed3976@msd20f9ad.service.dc1.a9ssvc:3306/msd20f9ad",
 "username": "a9sc5e4dbd17d61595065e00627239d"
}

We need the database names for later use as well as the credentials for mysql2. The database name is the trailing part of the URI. For mysql1 the database name is msd551540 and for mysql2 the database name is msd20f9ad

Additional prerequisites regarding command line tools:

  • BASH (some shell)
  • cat
  • gzip
  • sed
  • openssl
  • mysql (the command line client for MySQL)

Open the service dashboard of the service instance you want to fork. We use mysql1 for this example. You can find the dashboard URL like this:

$ cf service mysql1
Showing info of service mysql1 in org my_org / space my_space as user@domain.com...

name:             mysql1
service:          a9s-mysql101
tags:
plan:             mysql-single-small
description:      This is a service creating and managing dedicated mysql service instances and clusters, powered by the anynines Service Framework
documentation:
dashboard:        https://a9s-mysql-dashboard.de.a9s.eu/service-instances/f5f975d4-ee3c-40ae-83b6-cf076f50e08a
service broker:   a9s-mysql

This service is not currently shared.

Showing status of last operation from service mysql1...

status:    create succeeded
message:
started:   2019-06-12T14:54:51Z
updated:   2019-06-12T15:01:05Z

There are no bound apps for this service.

Make sure you set a encryption key for the backups using the service instance dashboard (use the Edit button next Backup Settings). Create a backup using the dashboard. Download the backup to your local machine. The filename will be something like msd551540-1560352379373.

Decrypt the backup and write its contents to a file:

$ cat msd551540-1560356147379 | openssl enc -aes256 -d -pass 'pass:mytopsecretpassword' | gunzip -c > backup.sql

We have to replace the database name of the original instance in the backup with the database name of the other instance:

$ cat backup.sql | sed -e 's/msd551540/msd20f9ad/g' > backup.fork.sql

Create a tunnel to the admin interface (as explained in a previous chapter) of the MySQL instance that will be the fork of the original instance. I used mysql1 as the original instance and mysql2 as the fork instance:

$ cf ssh someapp -L 127.0.0.1:3306:msd20f9ad.service.dc1.a9ssvc:3306

Finally restore the data in a new shell/terminal:

$ mysql -h 127.0.0.1 -P 3306 -u a9sc5e4dbd17d61595065e00627239d -p msd20f9ad < backup.fork.sql

Don't get confused if you see error messages. The backup contains data that we lack the necessary permissions to restore. In the end our data gets restored and that's our primary goal.

Upgrade the Service Instance to Another Service Plan

Once created, you can upgrade your service instance to another, larger service plan. A larger service plan provides more CPU, RAM and storage. For more information, see Update a Service Instance of the Managing Service Instances with the cf CLI.

$ cf update-service my-mysql-service-instance -p a-bigger-plan

Here are the plans you can upgrade to depending on the one you are currently using:

  • If you are currently using the mysql-single-small plan, you can upgrade to mysql-single-big plan.
  • If you are currently using the mysql-replica-small plan, you can upgrade to mysql-replica-big plan.

Add a Graphite Endpoint

If you want to monitor your service with Graphite, you can set an endpoint to where to information will be sent with the cf update-service command. This command expects th -c flag and a JSON string containing the graphite and metrics_prefix keys.

$ cf update-service my-mysql-service-instance -c '{ "graphite": ["yourspace.your-graphite-endpoint.com:12345"], "metrics_prefix": "your-api-key.my-mysql" }'

Add a Syslog Endpoint

The cf update-service command used with the -c flag can let you stream your syslog to a third-party service. In this case, the command expects a JSON string containing the syslog key.

$ cf update-service my-mysql-service-instance -c '{ "syslog": ["logs4.your-syslog-endpoint.com:33061"] }'

Cloud Foundry Application Security Groups

This topic describes how to check whether a security group was created.

Each a9s Data Service will automatically create and update Cloud Foundry security groups in order to protected service instances to be accessed by applications not running in the same Cloud Foundry applications space.

To get a better understanding about Security Groups you can have a look on the Understanding Application Security Groups topic.

Get Service Instance GUID

Run cf service INSTANCE_NAME --guid to get the guid of the service instance.

$ cf service my-mysql --guid ca16f111-5073-40b7-973a-156c75dd3028

Check available Security Groups

To see all available security groups use cf security-groups.

$ cf security-groups
Getting security groups as demo@anynines.com
OK

     Name                                         Organization     Space
#0   public_networks
#1   dns
#2   tcp_open
#3   guard_432fb752-876d-443b-a311-a075f4df2237   demonstrations   demo
#4   guard_ca16f111-5073-40b7-973a-156c75dd3028   demonstrations   demo

There we can see a security group with the named guard_ca16f111-5073-40b7-973a-156c75dd3028 was successfully created.

NOTE: in some circumstances the connection between the application and the service instance is not possible, in this case check if a security group was created.

Backup and Restore Service Instances

a9s MySQL provides an easy way to create backups and restore if needed. For a more detailed description, please see the a9s Service Dashboard documentation.

Get Dashboard Address, Login and Authorize

  1. Grap the dashboard URL with cf service SERVICE-NAME.
$ cf service my-mysql

Service instance: my-mysql
Service: a9s-mysql
Bound apps:
Tags:
Plan: mysql-single-small
Description: This is a service creating and managing dedicated MySQL service instances and clusters, powered by the anynines Service Framework
Documentation url:
Dashboard: https://a9s-mysql-dashboard.aws.ie.a9s.eu/service-instances/ca16f111-5073-40b7-973a-156c75dd3028

Last Operation
Status: update succeeded
Message:
Started: 2017-10-26T08:28:38Z
Updated: 2017-10-26T08:28:38Z
  1. Browse to the dashboard URL and authenticate on the redirected page with your Cloud Foundry credentials: authentication-page

  2. Click Authorize to approve the authorization request: authorization-page

Perform a Backup

On the dashboard as shown above you can trigger a backup by clicking Trigger backup

service-dashboard

After a short period of time the backup will be queued. The backup process will start soon.

service-dashboard

NOTE: Depending on the size of the data the backup will take some time.

Restore a Backup

Open the dashboard again and select the backup you would like to restore. Click the Restore button of the backup. After a short period of time the restore will be triggered.

service-dashboard

NOTE: Same as the backup, depending on the size of the data, the restore will take some time.

service-dashboard

Make a Service Instance Locally Available

It is possible to access any of the a9s Data Services locally. That means you can connect with a local client to the service for any purpose such as debugging. CF provides a smart way to create SSH forward tunnels via a pushed application. For more information about this feature see the Accessing Apps with SSH section of the CF documentation.

First of all you must have an application bound to the service. How to do this see Bind an Application to a Service Instance.

NOTE: cf ssh support must be enabled in the platform. Ask your administrator if you are not sure.

Get The Service Url and Credentials

When you follow this instructions Obtain Service Instance Access Credentials you will get the hostname of the service and the user credentials.

$ cf env a9s-mysql-app
Getting env variables for app a9s-mysql-app in org test / space test as admin...
OK

System-Provided:
{
 "VCAP_SERVICES": {
  "a9s-mysql101": [
   {
    "binding_name": null,
    "credentials": {
     "host": "d15575b.service.dc1.a9s-mysql-consul",
     "name": "d15575b",
     "password": "a9s-password",
     "port": 3306,
     "uri": "mysql://a9s-brk-usr:a9s-password@d15575b.service.dc1.a9s-mysql-consul:3306/d15575b",
     "username": "a9s-brk-usr"
    },
    "label": "a9s-mysql101",
    "plan": "mysql-cluster-small",
    "tags": [
     "sql",
     "database",
     "object-relational",
     "consistent"
    ],
    "volume_mounts": []
   }
  ]
 }
}
...

Notice the host d67901c.service.dc1.a9svs, the username a9s-brk-usr and the password a9s-password. You will need this in the next step.

Create a Tunnel to The Service

With the cf ssh as mentioned before you can create a ssh forward tunnel to the management dashboard. Use port 27017 to connect to the a9s MySQL Instance.

$ cf ssh a9s-mysql-app -L 3306:d67901c.service.dc1.a9svs:3306
vcap@956aaf4e-6da9-4f69-4b1d-8e631a403312:~$

When the ssh tunnel is open you can access the instance over the address localhost:27017.

NOTE: Don't forget to close the session with exit.

Service keys

To gain access to the service manually rather than binding apps to it you can use service keys.

Creating a service key

To create a key to the service instance mysql1 call nykey run:

cf create-service-key mysql1 mykey

Listing service keys

To list all the keys for the mysql1 service instance run this:

cf service-keys mysql1

Accessing service keys

To obtain the key mykey from the mysql1 service instance run:

cf service-key mysql1 mykey

Deleting service keys

To delete a service key mykey from the service instance mysql1 run:

cf delete-service-key mysql1 mykey

Setup Disk Usage Alerts

Each service comes with the a9s Parachute. This component monitors ephemeral and persistent disk usage. See the a9s Parachute documentation how to configure the component.