-
Notifications
You must be signed in to change notification settings - Fork 16
/
mariadb.html.md.erb
143 lines (93 loc) · 9.25 KB
/
mariadb.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
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
---
title: MariaDB Enterprise
owner: Services
---
## <a id='integrating-your-service'></a> Integrating the Service With Your App
After the [creation](../devguide/services/managing-services.html#create) of the service and the [binding](../devguide/services/application-binding.html#bind) of the service to the application, the environment variable [VCAP_SERVICES](../devguide/deploy-apps/environment-variable.html#VCAP-SERVICES) is created. Information about the credentials are stored in this variable as shown here:
```txt
"mariadbent": [
{
"credentials": {
"host": "galera-service.service.consul",
"hostname": "galera-service.service.consul",
"port": 3306,
"name": "CF_9B81CA4D_E5BE_427E_991D_99FA13ABAE21",
"database": "CF_9B81CA4D_E5BE_427E_991D_99FA13ABAE21",
"username": "9raLhQ5kFQaBLSYj",
"password": "LlsrR1V9hs0xXOAu",
"database_uri": "mysql://9raLhQ5kFQaBLSYj:[email protected]:3306/CF_9B81CA4D_E5BE_427E_991D_99FA13ABAE21?reconnect=true",
"uri": "mysql://9raLhQ5kFQaBLSYj:[email protected]:3306/CF_9B81CA4D_E5BE_427E_991D_99FA13ABAE21?reconnect=true",
"jdbcUrl": "jdbc:mysql://galera-service.service.consul:3306/CF_9B81CA4D_E5BE_427E_991D_99FA13ABAE21?user=9raLhQ5kFQaBLSYj&password=LlsrR1V9hs0xXOAu"
},
"syslog_drain_url": null,
"volume_mounts": [],
"label": "mariadbent",
"provider": null,
"plan": "usage",
"name": "my-db",
"tags": []
}
]
```
<p class="note">
Please be aware that the number of concurrent connections (max_connections) to MariaDB is limited. You can manage this by using connection pooling.
</p>
## <a id='administrating'></a> Administrating Your MariaDB Instances
To connect to a running MariaDB instance with your local development tools, you can use the [`cf ssh`](../devguide/deploy-apps/ssh-services.html) feature of the cf CLI.
You can deploy [PivotalMySQLWeb](https://github.com/pivotal-cf/PivotalMySQLWeb) for MariaDB management on the Swisscom Application Cloud. It's not the most feature rich GUI client for MariaDB, but a nice cloud native app with a simple deployment process.
You can use the [cf-mysql-plugin](https://github.com/andreasf/cf-mysql-plugin) for the [Cloud Foundry CLI](https://github.com/cloudfoundry/cli). This plugin installs the `mysql`and `mysqldump` clients and integrates them into the Cloud Foundry ecosystem.
## <a id='usecases'></a> Use Cases
A typical use case for MariaDB is a Cloud Foundry hosted web app which needs a relational data store.
Our setup is not suitable for expensive analytics or "Big Data" processing.
## <a id='sample-application'></a> Sample Application
Cloud Foundry: [Spring Music Example](https://github.com/cloudfoundry-samples/spring-music)
## <a id='limitations'></a> Known Limitations
Our HA MariaDB offering is based on Galera, which brings some [known limitations (from mariadb.com)](https://mariadb.com/kb/en/mariadb/mariadb-galera-cluster-known-limitations/) with it. Please read this article carefully.
A summary of Galera limitations is on [Tips on Converting to Galera](https://mariadb.com/kb/en/library/tips-on-converting-to-galera/) (blockquote modified for our offering):
> Schema Design:
> - InnoDB storage engine only, every table must have a Primary Key. The legacy storage engine MyISAM is disabled with `enforce_storage_engine=InnoDB` in our offering.
> - Please use indexes according to your query pattern.
> Application Development:
> - Check for errors, even after `COMMIT`.
> - Moderate sized transactions.
> - 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:
> - `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.
> - `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 because of *Row Based Replication*. Tables without primary key may cause a downtime of the Galera Cluster (depending on the number of records).
Please contact Swisscom Application Cloud support when you need to issue **long running DDL changes** (for example `ALTER TABLE` with thousends of records). Long running DDL causes an outage for all customers on the cluster.
Our Galera aaS is a **shared cluster**. Each customer has their own database. Put simply, the service broker executes the following statements:
```sql
CREATE DATABSE CF_DATABASE_NAME
GRANT ALL ON `CF_DATABASE_NAME`.* TO 'USER'@'SUBNET'
REVOKE LOCK TABLES ON `CF_DATABASE_NAME`.* FROM 'USER'@'SUBNET'
```
## <a id='migrations'></a> Minimize Downtime for Schema Migrations
Lots of customers complained that when doing an `ALTER TABLE` statement in Galera, the whole cluster is read-locked for the duration of the statement. On big tables (millions of records) this can result in many hours of "outage". Here are some tips to minimize that behavior.
- Add `ALGORITHM=INPLACE, LOCK=NONE` for Online DDL Operations in your `ALTER TABLE` statements. For example, to add a new index, write `ALTER TABLE test ADD INDEX idx_test (test), ALGORITHM=INPLACE, LOCK=NONE`. Be warned Online DDL is not always online and may incur locks.
- `ALGORITHM=INPLACE, LOCK=NONE` has some limitations and not supported operations. If you it doesn't work for your case, use [pt-online-schema-change](https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html). The tool `pt-online-schema-change` alters a table’s structure without blocking reads or writes. Please do not use this tool before reading its linked documentation and checking your [service backups](../devguide-sc/services/backups.html) carefully. In our current offering you don't have the necessary permissions for `pt-online-schema-change`. Please ask our support to `GRANT PROCESS, REPLICATION SLAVE ON *.* TO` for your `serice-key`. We can't grant that on shared cluster, it means you must have your own dedicated Galera/MariaDB cluster.
- Don't use `TRUNCATE` with huge tables. Please `DROP` the table and recreate.
## <a id='framework'></a> Database Frameworks Integration tips
- Liquibase is not fully Galera compatible. See bug report [primary key on DATABASECHANGELOG, PK_DATABASECHANGELOG, is no longer created](https://liquibase.jira.com/plugins/servlet/mobile#issue/CORE-1909) for solutions.
## <a id='definer'></a> Definer and bind-service (Change Credentials)
With `cf bind-service` the service broker (Cloud Foundry component) creates new credentials for security reasons (`cf env APP_NAME` to view them). This will break existing routines, triggers, views and events because they are still using the old user.
Error code from MariaDB backend:
```txt
Error: 1449 SQLSTATE: HY000 (ER_NO_SUCH_USER)
Message: The user specified as a definer ('%s'@'%s') does not exist
```
See the MariaDB blog post [Changing the DEFINER of MySQL stored routines etc.](https://mariadb.com/resources/blog/changing-definer-mysql-stored-routines-etc) for ideas on how to fix this.
## <a id='migration'></a> Migrate your service instance
See [Migrate from legacy MariaDB to MariaDB Ent](./migrate-mariadb-to-mariadbent.html) how to migrate your service instance to a new database cluster.
## <a id='backup'></a> Backup
Within your MariaDB service instance, you can start a backup and also restore it afterwards.
For more information about this feature, check the [service backup guide](../devguide-sc/services/backups.html).
If you need more fine-grained control over your backups there is also an app available that allows for automatic scheduled backups: [backman - the appcloud backup manager](https://github.com/swisscom/backman).
Any MariaDB service instance bound to this app will be automatically backed up, can be downloaded and also restored on-demand.
If you accidentally `DROP` a table or other error happens, we can not restore your table unfortunately. Please make sure you create a [service backup](../devguide-sc/services/backups.html) in the Portal, via the [CLI with the Swisscom Plugin](https://github.com/swisscom/appcloud-cf-cli-plugin) or with [backman](https://github.com/swisscom/backman) before you do changes.
## <a id='more'></a> More info from upstream open source project
Our MariaDB Enterprise offering is based on the the open source [Cloud Foundry MySQL Service](https://github.com/cloudfoundry/cf-mysql-release).
## <a id='more'></a> Compatibility to MySQL
Please be aware that MariaDB and MySQL are not fully compatible and have a few different features for developers (and administrators).
We deploy MariaDB 10.1.x series.
See [MariaDB versus MySQL - Compatibility](https://mariadb.com/kb/en/library/server-client-software/mariadb-releases/compatibility-differences/mariadb-vs-mysql-compatibility/) and [MariaDB versus MySQL - Features](https://mariadb.com/kb/en/library/server-client-software/mariadb-releases/compatibility-differences/mariadb-vs-mysql-features/) for furhter infos.