Connecting Apps to MySQL Instances

Page last updated:

This topic demonstrates how you, as an app developer, connect your app to a VMware Tanzu™ 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 Tanzu™ 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 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:/$
    
  2. Create any MySQL constructs needed by your app, and create a user with privileges to access those constructs as required by your app.

    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)
    
    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. For more information, see the cert-manager documentation.

  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
    
    $ helm install wp bitnami/wordpress -f - <<EOF
    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}}'