-
Notifications
You must be signed in to change notification settings - Fork 16
/
Copy pathmysql.html.md.erb
128 lines (82 loc) · 7.7 KB
/
mysql.html.md.erb
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
---
title: MySQL
owner: Services
---
## <a id='about'></a> About the MySQL Service
The MySQL Service is built on top of Percona XtraDB Cluster. Following a quote from Percona [Frequently Asked Questions] (https://www.percona.com/software/mysql-database/percona-server/faq) and [Percona XtraDB Cluster](https://www.percona.com/software/mysql-database/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.
## <a id='overview'></a> Overview Dedicated Cluster and multiple Databases/Schema
<%= image_tag("./images/overview_percona.png") %>
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.
1. Second provision _n_ databases/schemas on top of the cluster and use it for binding.
## <a id='create'></a> 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](https://docs.developer.swisscom.com/concepts/asg.html) is being added to the space to allow access to the cluster. Security Groups only take effect after an app restart.
<p class="note">
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.
</p>
#### <a id='sample-application-default'></a> Sample Application for default plan
Cloud Foundry: [Spring Music Example](https://github.com/cloudfoundry-samples/spring-music)
## <a id='limitations'></a> 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](https://www.percona.com/doc/percona-xtradb-cluster/LATEST/limitation.html). 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:
- `LOAD DATA INFILE` is disabled. See [Security Issues with LOAD DATA LOCAL](https://dev.mysql.com/doc/refman/8.0/en/load-data-local.html) for more details.
- `ALTER`s are handled differently. See [SCHEMA UPGRADES](http://galeracluster.com/documentation-webpages/schemaupgrades.html) 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](https://www.percona.com/blog/2018/01/10/why-avoid-create-table-as-select-statement/).
- `TRIGGER`s and `EVENT`s 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](https://www.percona.com/doc/percona-xtradb-cluster/LATEST/features/pxc-strict-mode.html) add-on docs.
**Long running DDL changes** (for example `ALTER TABLE` with thousends of records) may cause an outage for your cluster.
### <a id='warnings'></a> 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.
## <a id='migration'></a> Migrate your service instances
See [Migrate from MariaDBEnt to MySQL](./migrate-mariadbent-to-mysql.html) how to migrate your service instance to this service.
## <a id='backup'></a> 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](https://github.com/swisscom/backman).
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](https://github.com/swisscom/backman), before you do major changes to your database.
## <a id='support'></a> Developer Support
If you need support, please ask a question on [Stack Overflow #swisscomdev] (https://docs.developer.swisscom.com/devguide-sc/support.html). 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](https://developer.swisscom.com/support).