MySQL

Page last updated:

About the MySQL Service

The MySQL Service is built on top of Percona XtraDB Cluster. Following a quote from Percona Frequently Asked Questions and Percona XtraDB Cluster to summarize the technical product:

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.

Overview Dedicated Cluster and multiple Databases/Schema

Overview percona

The new MySQL service requires two steps to provision a bindable database on a dedicated cluster:

  1. First provision the cluster based on different plans.
  2. Second provision n databases/schemas on top of the cluster and use it for binding.

Create a Database Cluster and a Database on Top of it

To create a dedicated cluster, the following command is used: cf create-service mysql small my-mysql-cluster. 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.

Binding operation will result in the following error:

$ cf bs myapp my-mysql-cluster
Binding service my-mysql-cluster to app myapp 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 my-mysql-cluster --guid

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

cf create-service mysql-database default my-mysql-db -c '{ "parent_reference": "<MYSQLCLUSTER_GUID>" }'

You can create databases in any ORG and SPACE.

Bind the database my-mysql-db to your application with cf bind-service myapp my-mysql-db. If this is the first time you bind to a database on a specific cluster in a space, you need to restart your app, as a new Security Group is being added to the space to allow access to the cluster. Security Groups only take effect after an app restart.

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.

Sample Application for default plan

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:

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.

Migrate your service instances

See Migrate from MariaDBEnt to MySQL how to migrate your service instance to this service.

Backup

If you want to create backups, and also be able to restore them afterwards, there is an app available that allows for automatic scheduled backups: backman - the appcloud backup manager.

Any MySQL service instance bound to this app will be automatically backed up, can be downloaded and also restored on-demand.

Please make sure you create a backup, either by manually using mysqldump yourself or with backman, before you do major changes to your database.

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.

View the source for this page in GitHub