Cloud SQL Fail, Kubernetes Engine to the Rescue

MySQL 8 was officially released two years ago. Since then, multiple threads on Google Cloud’s forums asking for when MySQL 8 will be supported out-of-the-box with Cloud SQL have gone unanswered. Google should add support for this version if they want to remain an option for MySQL cloud developers: It’s faster, more secure, and extends features that developers love like NoSQL and JSON. So why doesn’t Google support MySQL with Cloud SQL, their managed SQL service? No idea. It seems like a major oversight to me. Although there may be additional lift on their part it’s the latest version and, unless they intend to support old MySQL version beyond their sunset dates, they’ll eventually have to support 8. If they don’t, Cloud SQL is useless.

Thankfully, we can still deploy MySQL 8 with Kubernetes Engine.  Although not as easy as the one-click install that comes with Cloud SQL (and without many of the additional managed service benefits like auto-scaling, backups, etc), we can still get the latest MySQL version running in short order.

First, from within the Cloud Shell, we need to describe our pod configuration in a pod.yamlfile:

apiVersion: v1
kind: Pod
metadata:
  name: [service name]
  labels:
    name: [service name]
spec:
  containers:
    - image: launcher.gcr.io/google/mysql8
      name: mysql
      env:
        - name: "MYSQL_ONETIME_PASSWORD"
          value: "yes"
        - name: "MYSQL_RANDOM_ROOT_PASSWORD"
          value: "yes"
      volumeMounts:
        - name: data
          mountPath: /var/lib/mysql
          subPath: data
  volumes:
    - name: data
      persistentVolumeClaim:
        claimName: data
---
kind: PersistentVolumeClaim
apiVersion: v1
metadata:
  name: data
  annotations:
    volume.alpha.kubernetes.io/storage-class: default
spec:
  accessModes: [ReadWriteOnce]
  resources:
    requests:
      storage: 5Gi

This does a few things. It defines a pod/service that will use Google’s MySQL 8 image (launcher.gcr.io/google/mysql8). Although you could set the root MySQL user’s password in the .yaml file, it’s not recommended. If you version your .yaml in Github or another repository and it’s accessed by someone outside of your organization, your database will be compromised. It’s much better to have it generate a one-time random password with MYSQL_RANDOM_ROOT_PASSWORD and MYSQL_ONETIME_PASSWORD.

Save your pod.yaml file, and bring your service to life with:

kubectl create -f pod.yaml

This will create a service with your [service name], which you can find if you issue

kubectl get pods --all-namespaces

So we’re done!

Just kidding. We still need to expose our MySQL service so we can actually access it outside of the Kubernetes cluster. The following will open MySQL up on the default 3306 port to TCP traffic.

kubectl expose pod [service name] --name [service name]-3306 \
--type LoadBalancer --port 3306 --protocol TCP

Now, because we took the security precaution of generating a one-time password for MySQL, we need to find that password, login as root, and create a non-root user (again, for security) for further database access. You should always do this after creating a SQL database.

Our one-time password is saved in the logs by Kubernetes when the pod is brought up. We can view the logs with

kubectl logs [service name]

You want to look for “GENERATED ROOT PASSWORD” and copy the value.

Then run the following to access MySQL.

exec -it inkyoobate-learn -- bash

When prompted for the password, paste what you’ve copied. Once in, we’ll want to create a new MySQL user and grant them some permissions. The syntax may be slightly different from previous versions of MySQL, so take note. Also, you might want to be deliberate about the permissions you grant rather than using a wildcard for all permissions:

CREATE USER 'normaluser'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'normaluser'@'%' WITH GRANT OPTION;

Then, we’re done. exit MySQL. Then visit Services & Ingress, which will contain the IP address you’ll need to access MySQL remotely. It should be under endpoint with the name [service name]-3306.

Open a new terminal, and login with mysql:

mysql -u normaluser -p -h [IP address]

Supposing you did everything above, you should be able to login. Done!