MySQL 8 on Google Cloud with Kubernetes
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.yaml
file:
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!