Skip to main content
Version: 51.0.0

Using a9s MariaDB

This topic describes how to use a9s MariaDB.

Use a9s MariaDB With an App

To use a9s MariaDB 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 MariaDB Service

After the service is installed, a9s-mariadb and its service plans appear in your CF 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-mariadb106 mariadb-single-small, mariadb-single-big, mariadb-cluster-small, mariadb-cluster-big This is the a9s MariaDB 10 service.

Create a Service Instance

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

cf create-service a9s-mariadb106 mariadb-single-small my-mariadb-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-mariadb-app my-mariadb-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 MariaDB 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-mariadb-app
Getting env variables for app a9s-mariadb-app in org test / space test as admin...
OK

System-Provided:
{
"VCAP_SERVICES": {
"a9s-mariadb106": [
{
"binding_name": null,
"credentials": {
"host": "d15575b.service.dc1.a9s-mariadb-consul",
"name": "d15575b",
"password": "a9s-password",
"port": 3306,
"uri": "mysql://a9s-brk-usr:a9s-password@d15575b.service.dc1.a9s-mariadb-consul:3306/d15575b",
"username": "a9s-brk-usr"
},
"label": "a9s-mariadb106",
"plan": "mariadb-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 MariaDB client.

Using SSL in Your App

For a9s MariaDB 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 MariaDB 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 MariaDB Service Instance

danger

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-mariadb-service-instance a9s-mariadb106 mariadb-single-small a9s-mariadb-app create succeeded

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

Unbind a Service Instance

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

cf unbind-service a9s-mariadb-app my-mariadb-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-mariadb-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.

Migrate Service Instance Data

See the MariaDB migration page.

Add a Graphite Endpoint

caution

Streaming of logs and metrics might not be available 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

info

Some metrics may change between the MariaDB versions, therefore they might differ or not be available based on the MariaDB 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 MariaDB metric and version.

General Status

General MariaDB 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_pages_freeIntegerNumber of free 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.
*.mariadb.*.*.*.*.status.aborted_connects
*.mariadb.*.*.*.*.status.com_delete
*.mariadb.*.*.*.*.status.com_insert
*.mariadb.*.*.*.*.status.com_select
*.mariadb.*.*.*.*.status.com_update
*.mariadb.*.*.*.*.status.connection_errors_internal
*.mariadb.*.*.*.*.status.connection_errors_max_connections
*.mariadb.*.*.*.*.status.innodb_buffer_pool_pages_total
*.mariadb.*.*.*.*.status.innodb_buffer_pool_read_requests
*.mariadb.*.*.*.*.status.innodb_buffer_pool_reads
*.mariadb.*.*.*.*.status.innodb_data_fsyncs
*.mariadb.*.*.*.*.status.innodb_page_size
*.mariadb.*.*.*.*.status.innodb_row_lock_waits
*.mariadb.*.*.*.*.status.max_used_connections
*.mariadb.*.*.*.*.status.qcache_hits
*.mariadb.*.*.*.*.status.questions
*.mariadb.*.*.*.*.status.slow_queries
*.mariadb.*.*.*.*.status.threads_connected
*.mariadb.*.*.*.*.status.threads_running
*.mariadb.*.*.*.*.status.wsrep_cluster_size
*.mariadb.*.*.*.*.status.wsrep_cluster_status
*.mariadb.*.*.*.*.status.reads
*.mariadb.*.*.*.*.status.writes
*.mariadb.*.*.*.*.status.innodb_buffer_pool_pages_utilization
For a9s MariaDB < 10.6 versions the metric names use `mysql` instead of the `mariadb`.
Example: *.mysql.*.*.*.*.status.reads

Sys Schema

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

Metric IdTypeDescription
sessions.countIntegerAmount of sessions based on the query SELECT COUNT(*) AS sessions FROM x$session
io/file.event_classIntegerThe event class. documentation
io/file.totalIntegerThe total number of occurrences of events in the class. documentation
io/file.total_latencyIntegerThe total wait time of timed occurrences of events in the class. documentation
io/file.min_latencyIntegerThe minimum single wait time of timed occurrences of events in the class. documentation
io/file.avg_latencyIntegerThe average wait time per timed occurrence of events in the class. documentation
io/file.max_latencyIntegerThe maximum single wait time of timed occurrences of events in the class. documentation
io/table.event_classIntegerThe event class. documentation
io/table.totalIntegerThe event class. documentation
io/table.total_latencyIntegerThe total wait time of timed occurrences of events in the class. documentation
io/table.min_latencyIntegerThe minimum single wait time of timed occurrences of events in the class. documentation
io/table.avg_latencyIntegerThe average wait time per timed occurrence of events in the class. documentation
io/table.max_latencyIntegerThe maximum single wait time of timed occurrences of events in the class. documentation
lock/table.event_classIntegerThe event class. documentation
lock/table.totalIntegerThe total number of occurrences of events in the class. documentation
lock/table.total_latencyIntegerThe total wait time of timed occurrences of events in the class. documentation
lock/table.min_latencyIntegerThe minimum single wait time of timed occurrences of events in the class. documentation
lock/table.avg_latencyIntegerThe average wait time per timed occurrence of events in the class. documentation
lock/table.max_latencyIntegerThe maximum single wait time of timed occurrences of events in the class. documentation
*.mariadb.*.*.*.*.sys.sessions.count
*.mariadb.*.*.*.*.sys.wait_classes_global_by_latency.event_class.wait/io/file.event_class
*.mariadb.*.*.*.*.sys.wait_classes_global_by_latency.event_class.wait/io/file.total
*.mariadb.*.*.*.*.sys.wait_classes_global_by_latency.event_class.wait/io/file.total_latency
*.mariadb.*.*.*.*.sys.wait_classes_global_by_latency.event_class.wait/io/file.min_latency
*.mariadb.*.*.*.*.sys.wait_classes_global_by_latency.event_class.wait/io/file.avg_latency
*.mariadb.*.*.*.*.sys.wait_classes_global_by_latency.event_class.wait/io/file.max_latency
*.mariadb.*.*.*.*.sys.wait_classes_global_by_latency.event_class.wait/io/table.event_class
*.mariadb.*.*.*.*.sys.wait_classes_global_by_latency.event_class.wait/io/table.total
*.mariadb.*.*.*.*.sys.wait_classes_global_by_latency.event_class.wait/io/table.total_latency
*.mariadb.*.*.*.*.sys.wait_classes_global_by_latency.event_class.wait/io/table.min_latency
*.mariadb.*.*.*.*.sys.wait_classes_global_by_latency.event_class.wait/io/table.avg_latency
*.mariadb.*.*.*.*.sys.wait_classes_global_by_latency.event_class.wait/io/table.max_latency
*.mariadb.*.*.*.*.sys.wait_classes_global_by_latency.event_class.wait/lock/table.event_class
*.mariadb.*.*.*.*.sys.wait_classes_global_by_latency.event_class.wait/lock/table.total
*.mariadb.*.*.*.*.sys.wait_classes_global_by_latency.event_class.wait/lock/table.total_latency
*.mariadb.*.*.*.*.sys.wait_classes_global_by_latency.event_class.wait/lock/table.min_latency
*.mariadb.*.*.*.*.sys.wait_classes_global_by_latency.event_class.wait/lock/table.avg_latency
*.mariadb.*.*.*.*.sys.wait_classes_global_by_latency.event_class.wait/lock/table.max_latency
For a9s MariaDB < 10.6 versions the metric names use `mysql` instead of the `mariadb`.
Example: *.mysql.*.*.*.*.status.reads

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

info

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 MariaDB 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-mariadb

Service instance: my-mariadb
Service: a9s-mariadb
Bound apps:
Tags:
Plan: mariadb-single-small
Description: This is a service creating and managing dedicated MariaDB service instances and clusters, powered by the anynines Service Framework
Documentation url:
Dashboard: https://a9s-mariadb-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.

info

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.

info

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.

info

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 Credentials for Accessing Your Service Instance you will get the hostname of the service and the user credentials.

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

System-Provided:
{
"VCAP_SERVICES": {
"a9s-mariadb106": [
{
"binding_name": null,
"credentials": {
"host": "d15575b.service.dc1.consul",
"name": "d15575b",
"password": "a9s-password",
"port": 3306,
"uri": "mysql://a9s-brk-usr:a9s-password@d15575b.service.dc1.a9s-mariadb-consul:3306/d15575b",
"username": "a9s-brk-usr"
},
"label": "a9s-mariadb106",
"plan": "mariadb-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 MariaDB Instance.

$ cf ssh a9s-mariadb-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.

info

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 mariadb1 call mykey run:

cf create-service-key mariadb1 mykey

Listing service keys

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

cf service-keys mariadb1

Accessing service keys

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

cf service-key mariadb1 mykey

Deleting service keys

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

cf delete-service-key mariadb1 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.

MariaDB Custom Parameters

As an end user you have the opportunity to customize your service instance by using custom parameters. Custom parameters are passed on to a service instance by using the -c switch of the cf CLI commands cf create-service and cf update-service. For example

cf update-service my-instance -c '{"my_custom_parameter": "value"}'

would set the custom parameter named my_custom_parameter to the value value for the Service Instance my-instance.

You don't have to utilize those settings. There are sane defaults in place that fit your service plan well.

The following tables shows what customers parameters are available.

NameDescriptionTypeDefault
binlog_expire_daysThe number of days that the binary log should be kept on the instance that is running MariaDB before purgingA Positive Integer7
databasesList of the names of the databases that should exist in the instance other than the default database. Read more here.An array of stringsnull
info
  • binlog_expire_days is available starting from a9s MariaDB 10.4
  • databases is available starting from a9s MariaDB 10.6

databases Parameter

This custom parameter allows the Application Developer to create custom databases in their MariaDB instance. However, it's important to mention that there is always a default database that has the same name as the deployment name and it will exist regardless of that parameter value.

For example:

cf create-service a9s-mariadb mariadb-single-small my-mariadb-service

cf update-service my-mariadb-service -c '{"databases" : ["db1", "db2"]}'

In the example above, we first created a mariadb instance, and in the next step we updated the service instance to add two more databases that are called db1 and db2, so the user databases that will exist in the instance at this point in time are [default-database, db1, db2].

The databases parameter is a declarative parameter, this means that every time this parameter is specified, all the databases that the user wants to keep in the instance should be mentioned in the list and not just the new ones, the only exception for this rule is the default database.

For example:

cf update-service my-mariadb-service -c '{"databases" : ["db1", "db2"]}'

after this update is done, the instance will have the following databases default-database, db1, db2

However, if we update the databases parameter again:

cf update-service my-mariadb-service -c '{"databases" : ["db1", "db3"]}'

then, the custom database db2 will be dropped because it's not in the list and db3 will be created.

If you want to delete all the custom databases, simply pass an empty list [] to the databases parameter.

info

All the users that are created either by a service-key or by binding to an app, will have all the necessary privileges on all the user-databases, including the existing users.

Updating the value of databases Parameter

When updating the value of the databases parameter, please make sure to list all the custom databases that you want to keep and not just the ones you want to add, any database that is not specified with the parameter value will be dropped from the instance, the only exception is the default database.

Custom Databases After Restoring a Backup

We consider the backup as the source of truth, that means when restoring a backup, any database that does not exist in the backup file will be dropped.