Connecting Apps to MySQL Instances

This topic demonstrates how you, as an app developer, connect your app to a VMware SQL with MySQL for Kubernetes instance. The instructions show how to create a MySQL database and privileged user and then deploy the Bitnami WordPress Stack Helm chart configured to use your database and user.

Prerequisites

Before connecting an app to a MySQL instance, you must have:

The Kubernetes Command Line Interface (kubectl) installed. For more information, see the Kubernetes documentation.
A MySQL instance running in the same Kubernetes cluster as the app. The MySQL instance can be in a different namespace from the app. For information about creating an instance, see Creating and Deleting MySQL Instances.

Note: To avoid Kubernetes permissions issues, VMware SQL with MySQL for Kubernetes recommends that you grant developers admin access to their target namespace.

Create a Database and Privileged MySQL User for the App

Log in to the MySQL server as the root user, following the instructions in Get Root Access to the MySQL Server.

For example, to connect to a single-node instance named "mysql-sample":
```
kubectl exec -it mysql-sample-0 -c mysql -- bash
mysql@mysql-sample-0:/$
```

Create any MySQL constructs needed by your app, and create a user with privileges to access those constructs as required by your app. NOTE: Older MySQL client libraries (such as those base on 5.6) need an older password encryption method. We show both type of CREATE USER commands below.

For example, the following commands create a bitnami_wordpress database and bn_wordpress user for the Bitnami WordPress Stack deployment:

mysql -p$(cat $MYSQL_ROOT_PASSWORD_FILE) -u root
mysql> CREATE DATABASE bitnami_wordpress;
Query OK, 1 row affected (0.20 sec)

mysql> CREATE USER 'bn_wordpress'@'%' IDENTIFIED BY 'hunter2';
Query OK, 0 rows affected (0.08 sec)
--- OR, for broader compatibility with older client libraries ---
mysql> CREATE USER 'bn_wordpress'@'%' IDENTIFIED WITH mysql_native_password BY 'hunter2';
Query OK, 0 rows affected (0.08 sec)

mysql> GRANT ALL PRIVILEGES ON bitnami_wordpress.* TO 'bn_wordpress'@'%';
Query OK, 0 rows affected (0.03 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.10 sec)

Configure Your App with MySQL User and Connectivity Information

Application configuration will be specific to the app being deployed. This section shows how to deploy the Helm chart for the Bitnami WordPress Stack using TLS certificates generated by cert-manager, the user and database information created in the previous section, and the cluster-internal domain name for the MySQL service.

This section assumes that the MySQL instance is named mysql-sample and is deployed to the default Kubernetes namespace.

Install cert-manager if it is not already present in the cluster, following the details in Prerequisites for Installing via the Tanzu Network Registry.

Generate a certificate for MySQL to present to WordPress. The certificate must meet the WordPress authentication requirements.

WordPress authenticates signed certificates within the cluster. The following command generates two Issuers: the first Issuer, selfsigned-issuer, generates a certificate, ca-certificate, for the second Issuer, tls-issuer. That second Issuer generates the tls-certificate that MySQL will present to WordPress.

cat << EOF | kubectl apply -f -
---
apiVersion: cert-manager.io/v1
kind: Issuer
metadata:
  name: selfsigned-issuer
spec:
  selfSigned: {}
---
apiVersion: cert-manager.io/v1
kind: Certificate
metadata:
  name: ca-certificate
spec:
  isCA: true
  issuerRef:
    name: selfsigned-issuer
  secretName: ca-secret
  commonName: ca-cert
---
apiVersion: cert-manager.io/v1
kind: Issuer
metadata:
  name: tls-issuer
spec:
  ca:
    secretName: ca-secret
---
apiVersion: cert-manager.io/v1
kind: Certificate
metadata:
  name: tls-certificate
spec:
  isCA: false
  dnsNames:
    - mysql-sample.default # See note after the code excerpt
  secretName: mysql-tls-secret
  issuerRef:
    name: tls-issuer
EOF

Note: If you are deploying WordPress in the same Kubernetes cluster as the MySQL instance, specify INSTANCE-NAME.INSTANCE-NAMESPACE for spec.dnsNames above.

Edit the YAML file for the MySQL instance so that the instance presents the tls-certificate when receiving a client connection request. Then apply the updated YAML file.

In the MySQL instance YAML file, add the spec property spec.tls.secret.name. If you are creating a new MySQL instance, you can edit the original deployment YAML file and then apply that file to create the instance. If you are updating an existing MySQL instance, you can use the kubectl edit command to edit the YAML configuration in place. For example:

kubectl edit mysql.with.sql.tanzu.vmware.com/mysql-sample

spec:
  ...
  tls:
    secret:
      name: mysql-tls-secret
  ...

For more information about TLS configuration, see Configuring TLS for MySQL Instances.

You can verify that the deployment was successful by inspecting the startup output:

kubectl logs pod/mysql-sample-0 -c mysql
Initialization complete, now exiting!
2021-04-06T02:07:02.667789Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.22-13) starting as process 1
2021-04-06T02:07:02.675491Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-04-06T02:07:02.907693Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2021-04-06T02:07:03.055031Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2021-04-06T02:07:03.157475Z 0 [Warning] [MY-010068] [Server] CA certificate /etc/mysql/certs/ca.pem is self signed.
2021-04-06T02:07:03.161660Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.

Deploy the Bitnami WordPress Stack Helm chart, specifying the information for the constructs created in the preceding steps:

Database username bn_wordpress
Database user password hunter2
Database name bitnami_wordpress
Secret mysql-tls-secret (containing the MySQL signed certificate)

The following commands mount a volume, which contains the CA secret, onto the Bitnami container, which reads the mount point into the WORDPRESS_DATABASE_SSL_CA_FILE environment variable:

helm repo add  bitnami https://charts.bitnami.com/bitnami
"bitnami" has been added to your repositories

cat << EOF | helm install wp bitnami/wordpress -f -
mariadb:
  enabled: false

externalDatabase:
  host: mysql-sample.default.svc.cluster.local
  user: bn_wordpress
  password: hunter2
  database: bitnami_wordpress

extraEnvVars:
- name: "WORDPRESS_DATABASE_SSL_CA_FILE"
  value: "/etc/mysql/tls/ca.crt"

extraVolumes:
- name: mysql-ca
  secret:
    secretName: "mysql-tls-secret"
    items:
    - key: ca.crt
      path: ca.crt

extraVolumeMounts:
  - name: mysql-ca
    mountPath: /etc/mysql/tls/
EOF


NAME: wp
LAST DEPLOYED: Tue Dec 8 14:32:08 2020
NAMESPACE: default
STATUS: deployed
REVISION: 1
TEST SUITE: None
NOTES:
** Please be patient while the chart is being deployed **

After successful installation, Helm will display WordPress instructions for connecting as a user or admin to the running WordPress server. You can also retrieve the WordPress user password by running the following command:

kubectl get secret wp-wordpress -o go-template='{{index .data "wordpress-password" | base64decode}}'