Skip to main content

Create MariaDB Resources (RC)

This is an example of how to create a MariaDBInstance, MariaDBDatabase and MariaDBUser.

1. Create Kubernetes manifests and deploy them to the cluster​

Create manifests for the MariaDBInstance, MariaDBDatabase and MariaDBUser and deploy them to the cluster.

It is a good practice to include them in the application's Helm chart.

The resources have a dependency order. A MariaDBUser references a database through spec.databaseRef, so create the resources in this order:

  1. MariaDBInstance
  2. MariaDBDatabase
  3. MariaDBUser

1.1 Create a MariaDBInstance manifest​

Security group and security group rules to access the MariaDBInstance from pods are created automatically.

Connection credentials for the master user dbadmin are stored in a Kubernetes secret <.metadata.name>-dbadmin and AWS Secrets Manager secret.

# Example MariaDBInstance
apiVersion: database.entigo.com/v1alpha1
kind: MariaDBInstance
metadata:
  name: example-mariadb
spec:
  allocatedStorage: 20
  engineVersion: '11.4.10'
  instanceType: 'db.t3.micro'

---
# Example MariaDBInstance with custom settings and parameter group
apiVersion: database.entigo.com/v1alpha1
kind: MariaDBInstance
metadata:
  name: example-mariadb
spec:
  allocatedStorage: 20
  engineVersion: '11.4.10'
  instanceType: 'db.t3.micro'
  iops: 3000
  multiAZ: true
  parameterGroupName: 'default.mariadb11.4'
  deletionProtection: true
  autoMinorVersionUpgrade: true
  allowMajorVersionUpgrade: false
  backupRetentionPeriod: 7
  backupWindow: '03:00-05:00'
  maintenanceWindow: 'Mon:00:00-Mon:03:00'
---
# Example MariaDBInstance with custom parameters
apiVersion: database.entigo.com/v1alpha1
kind: MariaDBInstance
metadata:
  name: example-mariadb
spec:
  allocatedStorage: 20
  instanceType: 'db.t3.micro'
  deletionProtection: false
  parameterGroupParameters:
    max_connections: '200'
    applyMethod: pending-reboot

Note: Creation of the MariaDBInstance can take more than 5 minutes.

Use kubectl watch to verify it is ready.

kubectl get mariadbinstances.database.entigo.com example-mariadb -w

For all the options see https://docs.entigo.com/api/MariaDBInstance

1.2 Create a MariaDBDatabase manifest​

Creating a MariaDBDatabase is required if creating a MariaDBUser, because the user is granted privileges on a specific database.

# Example MariaDBDatabase
apiVersion: database.entigo.com/v1alpha1
kind: MariaDBDatabase
metadata:
  name: example-database
spec:
  instanceRef:
    name: example-mariadb

---
# Example MariaDBDatabase with a custom character set and collation
apiVersion: database.entigo.com/v1alpha1
kind: MariaDBDatabase
metadata:
  name: example-database
spec:
  instanceRef:
    name: example-mariadb
  defaultCharacterSet: utf8mb4
  defaultCollation: utf8mb4_unicode_ci

For all the options see https://docs.entigo.com/api/MariaDBDatabase

Use kubectl watch to verify it is ready.

kubectl get mariadbdatabases.database.entigo.com example-database -w

1.3 Create a MariaDBUser manifest​

A MariaDBUser requires spec.instanceRef, spec.databaseRef and spec.privileges.

Connection credentials for users created with a MariaDBUser manifest are stored in a Kubernetes secret <.spec.instanceRef.name>-<.metadata.name>.

# Example MariaDBUser
apiVersion: database.entigo.com/v1alpha1
kind: MariaDBUser
metadata:
  name: example-user
spec:
  instanceRef:
    name: example-mariadb
  databaseRef:
    name: example-database
  privileges:
    - SELECT
    - INSERT
    - UPDATE
    - DELETE

---
# Example MariaDBUser with connection limits and a grant to another user
apiVersion: database.entigo.com/v1alpha1
kind: MariaDBUser
metadata:
  name: example-owner
spec:
  instanceRef:
    name: example-mariadb
  databaseRef:
    name: example-database
  table: '*'
  privileges:
    - ALL PRIVILEGES
  maxConnectionsPerHour: 100
  maxQueriesPerHour: 1000
  maxUpdatesPerHour: 500
  maxUserConnections: 10
  grant:
    users:
      - example-user

For all the options see https://docs.entigo.com/api/MariaDBUser

Use kubectl watch to verify they are ready.

kubectl get mariadbusers.database.entigo.com example-user  -w
kubectl get mariadbusers.database.entigo.com example-owner -w

2. Mount connection credentials to a container​

Connection credentials for the master user dbadmin are stored in a Kubernetes secret <.metadata.name>-dbadmin and AWS Secrets Manager secret.

Connection credentials for users created with a MariaDBUser manifest are stored in a Kubernetes secret <.spec.instanceRef.name>-<.metadata.name>.

For more information about Secrets in Kubernetes, see Kubernetes documentation.

# Example
apiVersion: v1
kind: Pod
metadata:
  name: mariadb
spec:
  terminationGracePeriodSeconds: 1
  containers:
    - name: mariadb
      image: mariadb:latest
      command: ['sleep', 'infinity']
      env:
        - name: MYSQL_HOST
          valueFrom:
            secretKeyRef:
              name: example-mariadb-example-user
              key: endpoint
        - name: MYSQL_TCP_PORT
          valueFrom:
            secretKeyRef:
              name: example-mariadb-example-user
              key: port
        - name: MYSQL_USER
          valueFrom:
            secretKeyRef:
              name: example-mariadb-example-user
              key: username
        - name: MYSQL_PWD
          valueFrom:
            secretKeyRef:
              name: example-mariadb-example-user
              key: password

3. Result​

3.1 MariaDBInstance, MariaDBDatabase and MariaDBUser​

MariaDBInstance, MariaDBDatabase and MariaDBUser created in Kubernetes

$ kubectl get mariadbinstances.database.entigo.com
NAME              SYNCED   READY   COMPOSITION                            AGE
example-mariadb   True     True    mariadbinstances.database.entigo.com   19m
$ kubectl get mariadbdatabases.database.entigo.com
NAME               SYNCED   READY   COMPOSITION                             AGE
example-database   True     True    mariadbdatabases.database.entigo.com    17m
$ kubectl get mariadbusers.database.entigo.com
NAME            SYNCED   READY   COMPOSITION                        AGE
example-owner   True     True    mariadbusers.database.entigo.com   18m
example-user    True     True    mariadbusers.database.entigo.com   18m

3.2 Secrets with connection credentials in Kubernetes and AWS Secrets Manager​

Kubernetes secrets with connection credentials

$ kubectl get secret
NAME                            TYPE                                DATA   AGE
example-mariadb-dbadmin         Opaque                              4      14m
example-mariadb-example-owner   connection.crossplane.io/v1alpha1   4      14m
example-mariadb-example-user    connection.crossplane.io/v1alpha1   4      14m

$ kubectl get secret example-mariadb-dbadmin -o yaml
apiVersion: v1
kind: Secret
metadata:
  name: example-mariadb-dbadmin
  namespace: <namespace>
type: Opaque
data:
  endpoint: <base64-encoded-endpoint>
  port: <base64-encoded-port>
  username: <base64-encoded-username>
  password: <base64-encoded-password>

$ kubectl get secret example-mariadb-example-user -o yaml
apiVersion: v1
kind: Secret
metadata:
  name: example-mariadb-example-user
  namespace: <namespace>
type: Opaque
data:
  endpoint: <base64-encoded-endpoint>
  port: <base64-encoded-port>
  username: <base64-encoded-username>
  password: <base64-encoded-password>

3.3 Secrets mounted to a container​

Open a terminal in the pod and connect to the database using the mounted credentials.

$ kubectl get pod
NAME      READY   STATUS    RESTARTS   AGE
mariadb   1/1     Running   0          24s

$ kubectl exec -it mariadb -- bash
mariadb:/# env | grep ^MYSQL
MYSQL_TCP_PORT=3306
MYSQL_PWD=...
MYSQL_USER=example-user
MYSQL_HOST=example-mariadb-instance-...rds.amazonaws.com

mariadb:/# mariadb -h "$MYSQL_HOST" -P "$MYSQL_TCP_PORT" -u "$MYSQL_USER" example-database

Verify the configuration is correct.

MariaDB [example-database]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| example-database   |
| information_schema |
+--------------------+

MariaDB [example-database]> SELECT User, Host FROM mysql.user;
+---------------+-----------+
| User          | Host      |
+---------------+-----------+
| dbadmin       | %         |
| example-owner | %         |
| example-user  | %         |
+---------------+-----------+

MariaDB [example-database]> SHOW GRANTS FOR CURRENT_USER();
+--------------------------------------------------------------------------------+
| Grants for example-user@%                                                      |
+--------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `example-user`@`%`                                       |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `example-database`.* TO `example-user` |
+--------------------------------------------------------------------------------+

4. Delete the resources​

The resources depend on each other, so they must be deleted in the reverse order of creation:

  1. MariaDBUser
  2. MariaDBDatabase
  3. MariaDBInstance

A MariaDBUser protects both its MariaDBInstance and its referenced MariaDBDatabase, and a MariaDBDatabase protects its MariaDBInstance. While a dependent resource exists, deletion of the resource it references is blocked and the delete request stays pending until the dependent is removed.

In addition, MariaDBInstance and MariaDBDatabase have deletionProtection enabled by default. A resource with deletionProtection: true cannot be deleted. Set it to false first, then delete.

4.1 Disable deletion protection​

Patch the resources that have deletion protection enabled.

kubectl patch mariadbdatabases.database.entigo.com example-database --type merge -p '{"spec":{"deletionProtection":false}}'
kubectl patch mariadbinstances.database.entigo.com example-mariadb --type merge -p '{"spec":{"deletionProtection":false}}'

4.2 Delete in dependency order​

kubectl delete mariadbusers.database.entigo.com example-user example-owner
kubectl delete mariadbdatabases.database.entigo.com example-database
kubectl delete mariadbinstances.database.entigo.com example-mariadb

Note: Deleting the MariaDBInstance removes the underlying database and all of its data. If MariaDBBackupBeforeDeletion is enabled for the environment, a final snapshot is taken before the instance is removed.