Skip to main content
Version: 31.0.0

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.

Create a Service Instance

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

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.

Using SSL in Your App

For a9s MySQL 10.4, we've added support for SSL Plans. To ensure that the connection between your app and the database is encrypted, you need to configure the MySQL client in your app to verify the CA certificate. In the Ruby Gem, it would be configured like this:

client = Mysql2::Client.new(
'host': service_key[:host],
'port': service_key[:port],
# ...
'ssl_mode': :verify_ca
)

The content of the CA certificate – when using an SSL plan – is available in the service binding via the key cacrt.

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 password for the backups using the service instance dashboard. 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 -md md5 -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 here 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

Add a Graphite Endpoint

Important: Streaming of logs and metrics might not be availabale for your plan! If unsure, please check your plan description.

If you want to monitor your service with Graphite, you can set the custom parameter graphite. It expects the host and port where the Graphite metrics should be sent to.

For example, in order to send Graphite metrics to an endpoint yourspace.your-graphite-endpoint.com:12345, you can use the following command:

cf update-service my-instance -c '{ "graphite": "yourspace.your-graphite-endpoint.com:12345" }'

The endpoint would then receive metrics in the format:

<service_guid>.<service_type>.<host>.<metric> <metric value> <metric timestamp>

Metrics Frequency

By default, metrics will be emitted every 10 seconds. You can change the interval via the custom parameter metrics_frequency.

For example, in order to send Graphite metrics to an endpoint every minute, you would set the custom parameter metrics_frequency to 60 using the following command:

cf update-service my-instance -c '{  "metrics_frequency": 60 }'

Metrics Prefix

Depending on your graphite provider, you might need to prefix the metrics with a certain value, like an API key for example. In this case you can leverage the custom parameter metrics_prefix.

cf update-service my-instance -c '{  "metrics_prefix": "my-api-key-for-a-certain-provider" }'

The resulting metric path would have the format:

<metrics_prefix>.<service_guid>.<service_type>.<host>.<metric>

Metric Groups

Note: Some metrics may change between the MySQL versions, therefore they might differ or not be available based on the MySQL version that is currently running on the service instance. In order to be sure, consult the documentation link provided in the metric item description and confirm the information based on both the MySQL metric and version.

General Status

General MySQL status.

Metric IdTypeDescription
aborted_connectsIntegerNumber of aborted client connections. This can be due to the client not calling mysql_close() before exiting, the client sleeping without issuing a request to the server for more seconds than specified by wait_timeout or interactive_timeout, or by the client program ending in the midst of transferring data. documentation
com_deleteIntegerNumber of DELETE commands executed. Differs from Handler_delete, which counts the number of times rows have been deleted from tables. documentation
com_insertIntegerNumber of INSERT commands executed. documentation
com_selectIntegerNumber of SELECT commands executed. Also includes queries that make use of the query cache. documentation
com_updateIntegerNumber of UPDATE commands executed. documentation
connection_errors_internalIntegerNumber of refused connections due to internal server errors, for example out of memory errors, or failed thread starts. The global value can be flushed by FLUSH STATUS. documentation
connection_errors_max_connectionsIntegerNumber of refused connections due to the max_connections limit being reached. The global value can be flushed by FLUSH STATUS. documentation
innodb_buffer_pool_pages_totalIntegerTotal number of InnoDB buffer pool pages. documentation
innodb_buffer_pool_read_requestsIntegerNumber of requests to read from the InnoDB buffer pool. documentation
innodb_buffer_pool_readsIntegerNumber of reads that could not be satisfied by the InnoDB buffer pool and had to be read from disk. documentation
innodb_data_fsyncsIntegerNumber of InnoDB fsync (sync-to-disk) calls. fsync call frequency can be influenced by the innodb_flush_method configuration option.documentation
innodb_page_sizeIntegerPage size used by InnoDB. Defaults to 16KB, can be compiled with a different value. documentation
innodb_row_lock_waitsIntegerThe number of times operations on InnoDB tables had to wait for a row lock. documentation
max_used_connectionsIntegerMax number of connections ever open at the same time. The global value can be flushed by FLUSH STATUS. documentation
qcache_hitsIntegerNumber of requests served by the query cache. The global value can be flushed by FLUSH STATUS. documentation
questionsIntegerNumber of statements executed by the server, excluding COM_PING, COM_STATISTICS, COM_STMT_PREPARE, COM_STMT_CLOSE, and COM_STMT_RESET statements. Differs from Queries in that it doesn't count statements executed within stored programs. documentation
slow_queriesIntegerNumber of queries which took longer than long_query_time to run. The slow query log does not need to be active for this to be recorded. documentation
threads_connectedIntegerNumber of clients connected to the server. See Handling Too Many Connections. The Threads_connected name is inaccurate when the thread pool is in use, since each client connection does not correspond to a dedicated thread in that case. documentation
threads_runningIntegerNumber of client connections that are actively running a command, and not just sleeping while waiting to receive the next command to execute. Some internal system threads also count towards this status variable if they would show up in the output of the SHOW PROCESSLIST statement.documentation
wsrep_cluster_sizeIntegerNumber of nodes currently in the cluster. documentation
wsrep_cluster_statusIntegerCluster component status. Possible values are PRIMARY (primary group configuration, quorum present), NON_PRIMARY (non-primary group configuration, quorum lost) or DISCONNECTED (not connected to group, retrying). documentation
readsIntegerSum total of com_select and qcache_hits.
writesIntegerSum total of com_insert, com_update, and com_delete.
innodb_buffer_pool_pages_utilizationIt is the calculation by the formula (innodb_buffer_pool_pages_total - innodb_buffer_pool_pages_free) / innodb_buffer_pool_pages_total.
*.mysql.*.*.*.*.status.aborted_connects
*.mysql.*.*.*.*.status.com_delete
*.mysql.*.*.*.*.status.com_insert
*.mysql.*.*.*.*.status.com_select
*.mysql.*.*.*.*.status.com_update
*.mysql.*.*.*.*.status.connection_errors_internal
*.mysql.*.*.*.*.status.connection_errors_max_connections
*.mysql.*.*.*.*.status.innodb_buffer_pool_pages_total
*.mysql.*.*.*.*.status.innodb_buffer_pool_read_requests
*.mysql.*.*.*.*.status.innodb_buffer_pool_reads
*.mysql.*.*.*.*.status.innodb_data_fsyncs
*.mysql.*.*.*.*.status.innodb_page_size
*.mysql.*.*.*.*.status.innodb_row_lock_waits
*.mysql.*.*.*.*.status.max_used_connections
*.mysql.*.*.*.*.status.qcache_hits
*.mysql.*.*.*.*.status.questions
*.mysql.*.*.*.*.status.slow_queries
*.mysql.*.*.*.*.status.threads_connected
*.mysql.*.*.*.*.status.threads_running
*.mysql.*.*.*.*.status.wsrep_cluster_size
*.mysql.*.*.*.*.status.wsrep_cluster_status
*.mysql.*.*.*.*.status.reads
*.mysql.*.*.*.*.status.writes
*.mysql.*.*.*.*.status.innodb_buffer_pool_pages_utilization

Sys Schema

The sys schema objects can be used for typical tuning and diagnosis use cases.

| sessions.count | Integer | Amount of sessions based on the query SELECT COUNT(*) AS sessions FROM x$session | | io/file.event_class | Integer | The event class. documentation| | io/file.total | Integer | The total number of occurrences of events in the class. documentation| | io/file.total_latency | Integer | The total wait time of timed occurrences of events in the class. documentation| | io/file.min_latency | Integer | The minimum single wait time of timed occurrences of events in the class. documentation| | io/file.avg_latency | Integer | The average wait time per timed occurrence of events in the class. documentation| | io/file.max_latency | Integer | The maximum single wait time of timed occurrences of events in the class. documentation| | io/table.event_class | Integer | The event class. documentation| | io/table.total | Integer | The event class. documentation| | io/table.total_latency | Integer | The total wait time of timed occurrences of events in the class. documentation| | io/table.min_latency | Integer | The minimum single wait time of timed occurrences of events in the class. documentation| | io/table.avg_latency | Integer | The average wait time per timed occurrence of events in the class. documentation| | io/table.max_latency | Integer | The maximum single wait time of timed occurrences of events in the class. documentation| | lock/table.event_class | Integer | The event class. documentation| | lock/table.total | Integer | The total number of occurrences of events in the class. documentation| | lock/table.total_latency | Integer | The total wait time of timed occurrences of events in the class. documentation| | lock/table.min_latency | Integer | The minimum single wait time of timed occurrences of events in the class. documentation| | lock/table.avg_latency | Integer | The average wait time per timed occurrence of events in the class. documentation| | lock/table.max_latency | Integer | The maximum single wait time of timed occurrences of events in the class. documentation|

*.mysql.*.*.*.*.sys.sessions.count
*.mysql.*.*.*.*.sys.wait_classes_global_by_latency.event_class.wait/io/file.event_class
*.mysql.*.*.*.*.sys.wait_classes_global_by_latency.event_class.wait/io/file.total
*.mysql.*.*.*.*.sys.wait_classes_global_by_latency.event_class.wait/io/file.total_latency
*.mysql.*.*.*.*.sys.wait_classes_global_by_latency.event_class.wait/io/file.min_latency
*.mysql.*.*.*.*.sys.wait_classes_global_by_latency.event_class.wait/io/file.avg_latency
*.mysql.*.*.*.*.sys.wait_classes_global_by_latency.event_class.wait/io/file.max_latency
*.mysql.*.*.*.*.sys.wait_classes_global_by_latency.event_class.wait/io/table.event_class
*.mysql.*.*.*.*.sys.wait_classes_global_by_latency.event_class.wait/io/table.total
*.mysql.*.*.*.*.sys.wait_classes_global_by_latency.event_class.wait/io/table.total_latency
*.mysql.*.*.*.*.sys.wait_classes_global_by_latency.event_class.wait/io/table.min_latency
*.mysql.*.*.*.*.sys.wait_classes_global_by_latency.event_class.wait/io/table.avg_latency
*.mysql.*.*.*.*.sys.wait_classes_global_by_latency.event_class.wait/io/table.max_latency
*.mysql.*.*.*.*.sys.wait_classes_global_by_latency.event_class.wait/lock/table.event_class
*.mysql.*.*.*.*.sys.wait_classes_global_by_latency.event_class.wait/lock/table.total
*.mysql.*.*.*.*.sys.wait_classes_global_by_latency.event_class.wait/lock/table.total_latency
*.mysql.*.*.*.*.sys.wait_classes_global_by_latency.event_class.wait/lock/table.min_latency
*.mysql.*.*.*.*.sys.wait_classes_global_by_latency.event_class.wait/lock/table.avg_latency
*.mysql.*.*.*.*.sys.wait_classes_global_by_latency.event_class.wait/lock/table.max_latency

Cloud Foundry Org and Space Guid

The platform operators can enable support on a global level to prefix the Graphite metrics with the CloudFoundry organization and space. This means that all metrics of all service instances (not only yours!) contain that information.

In this case the Graphite metric paths have the following format:

<organization_guid>.<space_guid>.<service_guid>.<service_type>.<host>.<metric>

When you enable in addition the metrics_prefix for your instance, you will end up with the metric path format:

<metrics_prefix>.<organization_guid>.<space_guid>.<service_guid>.<service_type>.<host>.<metric>

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. Grab 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:

  1. Click Authorize to approve the authorization request:

Perform a Backup

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

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

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.

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

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:3306.

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 mykey 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.