MySQL

Page last updated:

Create a Database Cluster and a Database on Top of it

To create a dedicated cluster, the following command is used: cf create-service small mysql. Please choose a plan according to your needs. This may take up to 20 minutes, because dedicated VMs are being deployed in the background. After successfully completing the command, a dedicated cluster is created. However, no database (CREATE DATABASE) and no credentials (CREATE USER / GRANT) have been created, yet.

The binding operation will result in the following error:

$ cf bs cf-env test-mysql-cluster
Binding service test-mysql-cluster to app cf-env in org test / space test as test...
The service instance doesn't support binding.
FAILED

Therefore, we need to create databases CREATE DATABASE via the service broker. First, get the GUID of your service and keep it secret. The GUID is used to provision separate MySQL databases in this specific cluster.

cf service test --guid

Then, create as many databases as your plan allows (where parent_reference is the GUID of your service instance):

cf cs mysql-database default test-mysql-cluster-db -c '{ "parent_reference": "'780cc39c-5742-465c-a3c0-2513be03b128'" }'

You can create databases in any ORG and SPACE.

Bind the database mysql-database to your application with cf bind-service myapp test-mysql-cluster-db.

Overview percona

Integrating the Service With Your App

After the creation of the service and the binding of the service to the application, the environment variable VCAP_SERVICES is created. Information about the credentials is stored in this variable as shown here:

  "mysql-database": [
   {
    "binding_name": null,
    "credentials": {
     "database": "CF_BBFC9089_5F02_48DC_8902_F92DDE608C43",
     "database_uri": "mysql://8GwLLTRZjeBhOykz:zbRMjItyictbCtuH@780cc39c-5742-465c-a3c0-2513be03b128.service.consul:3306/CF_BBFC9089_5F02_48DC_8902_F92DDE608C43?reconnect=true",
     "host": "780cc39c-5742-465c-a3c0-2513be03b128.service.consul",
     "hostname": "780cc39c-5742-465c-a3c0-2513be03b128.service.consul",
     "jdbcUrl": "jdbc:mysql://780cc39c-5742-465c-a3c0-2513be03b128.service.consul:3306/CF_BBFC9089_5F02_48DC_8902_F92DDE608C43?user=8GwLLTRZjeBhOykz\u0026password=zbRMjItyictbCtuH",
     "name": "CF_BBFC9089_5F02_48DC_8902_F92DDE608C43",
     "password": "zbRMjItyictbCtuH",
     "port": 3306,
     "uri": "mysql://8GwLLTRZjeBhOykz:zbRMjItyictbCtuH@780cc39c-5742-465c-a3c0-2513be03b128.service.consul:3306/CF_BBFC9089_5F02_48DC_8902_F92DDE608C43?reconnect=true",
     "username": "8GwLLTRZjeBhOykz"
    },
    "instance_name": "test-mysql-cluster-db",
    "label": "mysql-database",
    "name": "test-mysql-cluster-db",
    "plan": "default",
    "provider": null,
    "syslog_drain_url": null,
    "tags": [
     "mysql"
    ],
    "volume_mounts": []
   }

Please be aware that the number of concurrent connections (max_connections) to MySQL is limited and depends on your plan. You must manage this by using connection pooling.

What is Percona XtraDB Cluster?

Let us quote Frequently Asked Questions and Percona XtraDB Cluster from Percona:

About

Percona XtraDB Cluster is a high availability and high scalability open source solution for MySQL clustering. It integrates Percona Server – an enhanced drop-in replacement for MySQL – and Percona XtraBackup with the Codership Galera library of MySQL high availability solutions.

How is it different from standard MySQL?

Percona’s releases are based upon recent versions of the MySQL server source code. It is fully compatible with standard MySQL.

How is it related to other MySQL forks?

Percona Server with XtraDB is the closest to the official MySQL Enterprise releases from Oracle. It is more conservative than MariaDB, which includes extra storage engines and more far-reaching code changes.

At the moment we deploy MySQL 5.7.x. As soon as MySQL 8.0 is available we will inform you via our newsletter.

Administrating Your MySQL Instances

To connect to a running Percona XtraDB Cluster instance with your local development tools, you can use the cf ssh feature of the cf CLI.

You can deploy PivotalMySQLWeb for database management on the Swisscom Application Cloud. It’s not the most feature rich GUI client for MySQL, but a nice cloud native app with a simple deployment process.

Use Cases

A typical use case for Percona XtraDB Cluster is a Cloud Foundry hosted web app which needs a relational data store.

Our setup is not suitable for expensive analytics (DWH) or “Big Data” processing.

Sample Application

Cloud Foundry: Spring Music Example

Known Limitations

Our MySQL offering is based on Percona XtraDB Cluster with Galera Replication, which brings some limitations with it. Vendor summary Percona XtraDB Cluster Limitations. Please read through these points carefully.

Schema Design:

  • InnoDB storage engine only. All other storage engines are disabled. ERROR 1105 (HY000): Percona-XtraDB-Cluster prohibits use of $Y command on a table (CF_BBFC9089_5F02_48DC_8902_F92DDE608C43.bugtable) that resides in non-transactional storage engine with pxc_strict_mode = ENFORCING or MASTER
  • Please use indexes according to your query pattern.
  • Every table must have a Primary Key!

Application Development:

  • Check for errors, even after COMMIT.
  • Moderately sized transactions.
  • Avoid the use of explicitly locking LOCK TABLES as it is local and replication transaction can’t forcefully abort it.
  • Don’t make assumptions about AUTO_INCREMENT values other than uniqueness. Not monotonicity, not gapless. (This applies for non-Galera, but probably happens more often here.)

Administration and Schema Migrations:

  • ALTERs are handled differently. See SCHEMA UPGRADES for more details. Please be careful and test schema migrations.
  • CTAS CREATE TABLE AS SELECT is not allowed. See Why You Should Avoid Using “CREATE TABLE AS SELECT” Statement.
  • TRIGGERs and EVENTs may need checking. By default, with row-based replication, triggers run on the master, and the effects of their executions are replicated to the slaves.

Please add a primary key to every table you create. This is needed for Row Based Replication. Tables without a primary key cause an error during DML operations:

ERROR 1105 (HY000): Percona-XtraDB-Cluster prohibits use of DML command on a table (CF_BBFC9089_5F02_48DC_8902_F92DDE608C43.bugtable) without an explicit primary key with pxc_strict_mode = ENFORCING or MASTER

We deploy your cluster with pxc_strict_mode=MASTER, so please make sure you read the guide about PXC Strict Mode add-on docs.

Long running DDL changes (for example ALTER TABLE with thousends of records) may cause an outage for your cluster.

Warnings

In accordance with the service plan you choose from the marketplace, resource limitations are enforced. Please note that exhausting those limitations comes with certain risks, and MySQL itself has very limited means of preventing overload / overuse.

Storage

If a cluster member of your MySQL service instance runs out of disk space, its mysqld process will crash and most likely not be able to recover automatically. Additionally, there’s a high risk of data corruption if this happens.

Disk Quota

TODO -> Sync with @nbari

Security Configuration

  • We don’t allow LOAD DATA INFILE. See Security Issues with LOAD DATA LOCAL for more details.
  • MySQL users are forced to use mutual SSL (password only login doesn’t work). We create a user like this CREATE USER 'test'@'test' IDENTIFIED BY "test" require subject '/CN=test..' AND issuer '/CN=test..';. The certificate issuer and the subject are different for every cluster provisioned.

Please, follow the instructions of how to use SSL enabled connection for your local development tools. First create a service key.

Execute brew install jq on macOS (for other systems, please refer to your package manager). Then get the required certificates using the following shell commands:

DATABASE_NAME="CF_584BDA1C_C647_4F04_9761_A98B1D82D999"
KEY_NAME="testservicekey"
LOCAL_PATH="/Users/$USER/Desktop"

echo -e "$(cf service-key "$DATABASE_NAME" "$KEY_NAME" | grep -v 'service instance' | jq '.ca')" |  sed -e 's/^"//' -e 's/"$//' > $LOCAL_PATH/ca.pem
echo -e "$(cf service-key "$DATABASE_NAME" "$KEY_NAME" | grep -v 'service instance' | jq '.private_key')" |  sed -e 's/^"//' -e 's/"$//' > $LOCAL_PATH/client-key.pem
echo -e "$(cf service-key "$DATABASE_NAME" "$KEY_NAME" | grep -v 'service instance' | jq '.certificate')" |  sed -e 's/^"//' -e 's/"$//'  > $LOCAL_PATH/client-cert.pem

Next create a SSH tunnel with cf ssh <your-app> -L 3306:<db-host>.service.consul:3306. The <db-host> is a UUID (for example 9bd40015-6daa-4fe9-a38e-575f8ad17bb9) URI for your deployment.

Create a .my.cnf in your home directory.

[client]
user = <user>
host = localhost
password = <password>
ssl-ca = /Users/<local-user>/Desktop/ca.pem
ssl-cert = /Users/<local-user>/Desktop/client-cert.pem
ssl-key = /Users/<local-user>/Desktop/client-key.pem
protocol = tcp

If you work with graphical MySQL Workbench click on “MysQL Connections” -> SSL -> Use SSL: Require and Verify CA and then fill in the paths to your certs. Do not use the SSL Wizard.

Connect to database workbench

More Info from Upstream Open Source Project

Our Percona Xtradb Cluster Enterprise offering is based on the the open source Percona Xtradb Cluster from Pivotal.

Developer Support

If you need support, please ask a question on Stack Overflow #swisscomdev. With the joint support of the community, our experts and MySQL users, we are able to provide you with faster and better answers and cover a broader range of expertise.

If you get the impression that your database server might be down, please contact us.

Techsupport

Thanks @RaphComic and @prolificpencomics for the artwork.

View the source for this page in GitHub