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
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
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
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 Id | Type | Description |
|---|---|---|
aborted_connects | Integer | Number 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_delete | Integer | Number of DELETE commands executed. Differs from Handler_delete, which counts the number of times rows have been deleted from tables. documentation |
com_insert | Integer | Number of INSERT commands executed. documentation |
com_select | Integer | Number of SELECT commands executed. Also includes queries that make use of the query cache. documentation |
com_update | Integer | Number of UPDATE commands executed. documentation |
connection_errors_internal | Integer | Number 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_connections | Integer | Number 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_total | Integer | Total number of InnoDB buffer pool pages. documentation |
Innodb_buffer_pool_pages_free | Integer | Number of free InnoDB buffer pool pages. documentation |
innodb_buffer_pool_read_requests | Integer | Number of requests to read from the InnoDB buffer pool. documentation |
innodb_buffer_pool_reads | Integer | Number of reads that could not be satisfied by the InnoDB buffer pool and had to be read from disk. documentation |
innodb_data_fsyncs | Integer | Number of InnoDB fsync (sync-to-disk) calls. fsync call frequency can be influenced by the innodb_flush_method configuration option.documentation |
innodb_page_size | Integer | Page size used by InnoDB. Defaults to 16KB, can be compiled with a different value. documentation |
innodb_row_lock_waits | Integer | The number of times operations on InnoDB tables had to wait for a row lock. documentation |
max_used_connections | Integer | Max number of connections ever open at the same time. The global value can be flushed by FLUSH STATUS. documentation |
qcache_hits | Integer | Number of requests served by the query cache. The global value can be flushed by FLUSH STATUS. documentation |
questions | Integer | Number 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_queries | Integer | Number 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_connected | Integer | Number 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_running | Integer | Number 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_size | Integer | Number of nodes currently in the cluster. documentation |
wsrep_cluster_status | Integer | Cluster 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 |
reads | Integer | Sum total of com_select and qcache_hits. |
writes | Integer | Sum total of com_insert, com_update, and com_delete. |
innodb_buffer_pool_pages_utilization | It 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 Id | Type | Description |
|---|---|---|
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 |
*.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.
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
- 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
- Browse to the dashboard URL and authenticate on the redirected page with your Cloud Foundry credentials:
- 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.
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.
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.
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.
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.
| Name | Description | Type | Default |
|---|---|---|---|
binlog_expire_days | The number of days that the binary log should be kept on the instance that is running MariaDB before purging | A Positive Integer | 7 |
databases | List of the names of the databases that should exist in the instance other than the default database. Read more here. | An array of strings | null |
binlog_expire_daysis available starting from a9s MariaDB 10.4databasesis 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.
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.
databases ParameterWhen 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.
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.