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

1. Log in to the MySQL server as the admin user, following the instructions in Get Admin Access to the MySQL Server.

2. 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> 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.

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

2. 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.

3. 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.
   

4. 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_ENABLE_DATABASE_SSL"
     value: "yes"
   - 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}}'