This post is part of our ongoing series on running MySQL on Kubernetes. We’ve published a number of articles about running MySQL on Kubernetes for specific platforms and for specific use cases. If you are looking for a specific Kubernetes platform, check out these related articles.
Running HA MySQL on Amazon Elastic Container Service for Kubernetes (EKS)
Running HA MySQL on Google Kubernetes Engine (GKE)
Running HA MySQL on Red Hat OpenShift
How to Backup and Restore MySQL on Red Hat OpenShift
Running HA MySQL on IBM Cloud Kubernetes Service (IKS)
Running HA MySQL on Rancher Kubernetes Engine (RKE)
Running HA MySQL on IBM Cloud Private
And now, onto the post…
We’ve been excited to partner with Microsoft, including enabling innovative customers like Beco to build an IoT cloud on Microsoft Azure. Working with the Azure Kubernetes Service (AKS) team and Brendan Burns, distinguished engineer at Microsoft, we’re excited to showcase how Portworx runs on AKS to provide seamless support for any Kubernetes customer. Brendan and Eric Han, our VP of Product, were both part of the original Kubernetes team at Google and it is exciting to watch Kubernetes mature and extend into the enterprise.
Today’s post will look at how to run a HA MySQL database on Azure Kubernetes Service (AKS), a managed Kubernetes offering from Microsoft, which makes it easy to create, configure, and manage a cluster of virtual machines that are preconfigured to run containerized applications.
Portworx, is a cloud-native storage platform to run persistent workloads deployed on a variety of orchestration engines including Kubernetes. With Portworx, customers can manage the database of their choice on any infrastructure using any container scheduler. It provides a single data management layer for all stateful services, no matter where they run.
In summary, to run HA MySQL on Azure you need to:
- Create an AKS cluster
- Provision storage nodes with Managed Disks in order to allow for compute nodes to scale independently of storage
- Install cloud native storage solution like Portworx as a daemon set on AKS
- Create storage class defining your storage requirements like replication factor, snapshot policy, and performance profile
- Deploy MySQL using Kubernetes
- Test failover by killing or cordoning node in your cluster and confirming that data is still accessible
- Dynamically resize MySQL volume
How to set up an AKS cluster
Portworx is fully supported on Azure Kubernetes Service. Run the following commands to configure a 3 node cluster in Europe West. More on Azure AKS is available here.
$ az group create --name px --location westeurope $ az aks install-cli $ az aks create --resource-group px --name pxdemo --node-count 3 --generate-ssh-keys $ az aks get-credentials --resource-group px --name pxdemo
When the cluster is ready, verify it with the following command:
$ kubectl get nodes NAME STATUS ROLES AGE VERSION aks-nodepool1-28253507-0 Ready agent 1d v1.9.9 aks-nodepool1-28253507-1 Ready agent 1d v1.9.9 aks-nodepool1-28253507-2 Ready agent 1d v1.9.9
Provision Azure Storage Nodes
Running storage nodes separately from compute nodes will allow us to independently scale compute and storage resources. We use Portworx to manage the storage nodes and also access storage from the compute nodes.
Follow these steps to create three storage node VMs. Afterwards, we attach the Managed Disk to the storage nodes in the Azure portal using these instructions. Finally, we ssh into the VM and install Portworx on each of the storage nodes using the commands below.
latest_stable=$(curl -fsSL 'https://install.portworx.com/1.4/?type=dock&stork=false' | awk '/image: / {print $2}')
# Download OCI bits (reminder, you will still need to run `px-runc install ..` after this step)
sudo docker run --entrypoint /runc-entry-point.sh \
--rm -i --privileged=true \
-v /opt/pwx:/opt/pwx -v /etc/pwx:/etc/pwx \
$latest_stable
# Basic installation where
sudo /opt/pwx/bin/px-runc install -c CLUSTER-NAME \
-k etcd://[etcd-service]:2379 \
-a -f
# Reload systemd configurations, enable and start Portworx service
sudo systemctl daemon-reload
sudo systemctl enable portworx
sudo systemctl start portworx
For the CLUSTER-NAME, use the same string in each of your installs for that cluster.
Installing Portworx in AKS
Installing Portworx on Azure Kubernetes Service is not very different from installing it on a Kubernetes cluster setup through Kops. Portworx AKS documentation has the steps involved in running the Portworx cluster in a Kubernetes environment deployed in Azure.
Portworx cluster needs to be up and running on AKS before proceeding to the next step. The kube-system namespace should have the Portoworx pods in running state.
$ kubectl get pods -n=kube-system -l name=portworx NAME READY STATUS RESTARTS AGE portworx-8zb8g 1/1 Running 0 1d portworx-g8tdg 1/1 Running 0 1d portworx-ttv2m 1/1 Running 0 1d
Creating a storage class for MySQL
Once the AKS cluster is up and running, and Portworx is installed and configured, we will deploy a highly available MySQL database.
Through storage class objects, an admin can define different classes of Portworx volumes that are offered in a cluster. These classes will be used during the dynamic provisioning of volumes. The Storage Class defines the replication factor, I/O profile (e.g., for a database or a CMS), and priority (e.g., SSD or HDD). These parameters impact the availability and throughput of workloads and can be specified for each volume. This is important because a production database will have different requirements than a development Jenkins cluster.
In this example, the storage class that we deploy has a replication factor of 3 with I/O profile set to “db,” and priority set to “high.” This means that the storage will be optimized for low latency database workloads like MySQL and automatically placed on the highest performance storage available in the cluster. Notice that we also mention the filesystem, xfs in the storage class.
$ cat > px-mysql-sc.yaml << EOF
kind: StorageClass
apiVersion: storage.k8s.io/v1beta1
metadata:
name: px-ha-sc
provisioner: kubernetes.io/portworx-volume
parameters:
repl: "3"
io_profile: "db_remote"
priority_io: "high"
fs: "xfs"
EOF
$ kubectl get sc NAME PROVISIONER AGE px-ha-sc kubernetes.io/portworx-volume 10s stork-snapshot-sc stork-snapshot 3d
Creating a MySQL PVC on Kubernetes
We can now create a Persistent Volume Claim (PVC) based on the Storage Class. Thanks to dynamic provisioning, the claims will be created without explicitly provisioning Persistent Volume (PV).
$ cat > px-mysql-pvc.yaml << EOF
kind: PersistentVolumeClaim
apiVersion: v1
metadata:
name: px-mysql-pvc
annotations:
volume.beta.kubernetes.io/storage-class: px-ha-sc
spec:
accessModes:
- ReadWriteOnce
resources:
requests:
storage: 1Gi
EOF
$ kubectl create -f px-mysql-pvc.yaml
persistentvolumeclaim "px-mysql-pvc" created
$ kubectl get pvc
NAME STATUS VOLUME CAPACITY ACCESS MODES STORAGECLASS AGE
px-mysql-pvc Bound pvc-d70785ca-9a86-11e8-a409-1a6d80f90d97 1Gi RWO px-ha-sc 8s
Deploying MySQL on AKS
Finally, let’s create a MySQL instance as a Kubernetes deployment object. For simplicity’s sake, we will just be deploying a single MySQL pod. Because Portworx provides synchronous replication for High Availability, a single MySQL instance might be the best deployment option for your MySQL database. Portworx can also provide backing volumes for multi-node MySQL cluster. The choice is yours.
$ cat > px-mysql-app.yaml << EOF
apiVersion: apps/v1
kind: Deployment
metadata:
name: mysql
spec:
strategy:
rollingUpdate:
maxSurge: 1
maxUnavailable: 1
type: RollingUpdate
replicas: 1
selector:
matchLabels:
app: mysql
template:
metadata:
labels:
app: mysql
spec:
schedulerName: stork
containers:
- name: mysql
image: mysql:5.6
imagePullPolicy: "Always"
env:
- name: MYSQL_ROOT_PASSWORD
value: password
ports:
- containerPort: 3306
volumeMounts:
- mountPath: /var/lib/mysql
name: mysql-data
volumes:
- name: mysql-data
persistentVolumeClaim:
claimName: px-mysql-pvc
EOF
$ kubectl create -f px-mysql-app.yaml deployment.extensions "mysql" created
The MySQL deployment defined above is explicitly associated with the PVC, px-mysql-pvc created in the previous step.
This deployment creates a single pod running MySQL backed by Portworx.
$ kubectl get pods NAME READY STATUS RESTARTS AGE mysql-654cc68f68-khnpq 1/1 Running 0 6s
We can inspect the Portworx volume by accessing the pxctl tool running with the MySQL pod.
$ VOL=`kubectl get pvc | grep px-mysql-pvc | awk '{print $3}'`
$ PX_POD=$(kubectl get pods -l name=portworx -n kube-system -o jsonpath='{.items[0].metadata.name}')
$ kubectl exec -it $PX_POD -n kube-system -- /opt/pwx/bin/pxctl volume inspect ${VOL}
Volume : 472432194854453148
Name : pvc-d70785ca-9a86-11e8-a409-1a6d80f90d97
Size : 1.0 GiB
Format : xfs
HA : 3
IO Priority : LOW
Creation time : Aug 7 21:14:20 UTC 2018
Shared : no
Status : up
State : Attached: aks-nodepool1-28253507-2 (10.240.0.6)
Device Path : /dev/pxd/pxd472432194854453148
Labels : namespace=default,pvc=px-mysql-pvc
Reads : 63
Reads MS : 132
Bytes Read : 319488
Writes : 505
Writes MS : 58584
Bytes Written : 166674432
IOs in progress : 0
Bytes used : 121 MiB
Replica sets on nodes:
Set 0
Node : 10.240.0.5 (Pool 0)
Node : 10.240.0.4 (Pool 0)
Node : 10.240.0.6 (Pool 0)
Replication Status : Up
Volume consumers :
- Name : mysql-654cc68f68-khnpq (f65339c6-9a86-11e8-a409-1a6d80f90d97) (Pod)
Namespace : default
Running on : aks-nodepool1-28253507-2
Controlled by : mysql-654cc68f68 (ReplicaSet)
The output from the above command confirms the creation of volumes that are backing MySQL database instance.
Failing over MySQL pod on Kubernetes
Populating sample data
Let’s populate the database with some sample data.
We will first find the pod that’s running MySQL to access the shell.
$ POD=`kubectl get pods -l app=mysql | grep Running | grep 1/1 | awk '{print $1}'`
$ kubectl exec -it $POD -- mysql -uroot -ppassword
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.40 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
Now that we are inside the shell, we can populate create a sample database and table.
mysql> CREATE DATABASE `classicmodels`;
mysql> USE `classicmodels`;
mysql> CREATE TABLE `offices` (
`officeCode` varchar(10) NOT NULL,
`city` varchar(50) NOT NULL,
`phone` varchar(50) NOT NULL,
`addressLine1` varchar(50) NOT NULL,
`addressLine2` varchar(50) DEFAULT NULL,
`state` varchar(50) DEFAULT NULL,
`country` varchar(50) NOT NULL,
`postalCode` varchar(15) NOT NULL,
`territory` varchar(10) NOT NULL,
PRIMARY KEY (`officeCode`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
mysql> insert into `offices`(`officeCode`,`city`,`phone`,`addressLine1`,`addressLine2`,`state`,`country`,`postalCode`,`territory`) values
('1','San Francisco','+1 650 219 4782','100 Market Street','Suite 300','CA','USA','94080','NA'),
('2','Boston','+1 215 837 0825','1550 Court Place','Suite 102','MA','USA','02107','NA'),
('3','NYC','+1 212 555 3000','523 East 53rd Street','apt. 5A','NY','USA','10022','NA'),
('4','Paris','+33 14 723 4404','43 Rue Jouffroy D\'abbans',NULL,NULL,'France','75017','EMEA'),
('5','Tokyo','+81 33 224 5000','4-1 Kioicho',NULL,'Chiyoda-Ku','Japan','102-8578','Japan'),
('6','Sydney','+61 2 9264 2451','5-11 Wentworth Avenue','Floor #2',NULL,'Australia','NSW 2010','APAC'),
('7','London','+44 20 7877 2041','25 Old Broad Street','Level 7',NULL,'UK','EC2N 1HN','EMEA');
Let’s run a few queries on the table.
mysql> select `officeCode`,`city`,`phone`,`addressLine1`,`city` from `offices`; +------------+---------------+------------------+--------------------------+---------------+ | officeCode | city | phone | addressLine1 | city | +------------+---------------+------------------+--------------------------+---------------+ | 1 | San Francisco | +1 650 219 4782 | 100 Market Street | San Francisco | | 2 | Boston | +1 215 837 0825 | 1550 Court Place | Boston | | 3 | NYC | +1 212 555 3000 | 523 East 53rd Street | NYC | | 4 | Paris | +33 14 723 4404 | 43 Rue Jouffroy D'abbans | Paris | | 5 | Tokyo | +81 33 224 5000 | 4-1 Kioicho | Tokyo | | 6 | Sydney | +61 2 9264 2451 | 5-11 Wentworth Avenue | Sydney | | 7 | London | +44 20 7877 2041 | 25 Old Broad Street | London | +------------+---------------+------------------+--------------------------+---------------+ 7 rows in set (0.01 sec)
Find all the offices in USA.
mysql> select `officeCode`, `city`, `phone` from `offices` where `country` = "USA"; +------------+---------------+-----------------+ | officeCode | city | phone | +------------+---------------+-----------------+ | 1 | San Francisco | +1 650 219 4782 | | 2 | Boston | +1 215 837 0825 | | 3 | NYC | +1 212 555 3000 | +------------+---------------+-----------------+ 3 rows in set (0.00 sec)
Exit from the MySQL shell to return to the host.
Simulating node failure
Now, let’s simulate the node failure by cordoning off the node on which MySQL is running.
$ NODE=`kubectl get pods -l app=mysql -o wide | grep -v NAME | awk '{print $7}'`
$ kubectl cordon ${NODE}
node "aks-nodepool1-28253507-2" cordoned
The above command disabled scheduling on one of the nodes.
$ kubectl get nodes NAME STATUS ROLES AGE VERSION aks-nodepool1-28253507-0 Ready agent 1d v1.9.9 aks-nodepool1-28253507-1 Ready agent 1d v1.9.9 aks-nodepool1-28253507-2 Ready,SchedulingDisabled agent 1d v1.9.9 s
Now, let’s go ahead and delete the MySQL pod.
$ POD=`kubectl get pods -l app=mysql -o wide | grep -v NAME | awk '{print $1}'`
$ kubectl delete pod ${POD}
pod "mysql-dff54d66d-m9r6q" deleted
As soon as the pod is deleted, it is relocated to the node with the replicated data. STorage ORchestrator for Kubernetes (STORK), a Portworx-contributed open source storage scheduler, ensures that the pod is co-located on the exact node where the data is stored. It ensures that an appropriate node is selected for scheduling the pod.
Let’s verify this by running the below command. We will notice that a new pod has been created and scheduled in a different node.
$ kubectl get pods -l app=mysql -o wide NAME READY STATUS RESTARTS AGE IP NODE mysql-dff54d66d-tzvjw 1/1 Running 0 15s 192.168.86.169 ip-192-168-95-234.us-west-2.compute.internal
$ kubectl uncordon ${NODE}
node "aks-nodepool1-28253507-2" uncordoned
Finally, let’s verify that the data is still available.
Verifying that the data is intact
Let’s find the pod name and run the ‘exec’ command, and then access the MySQL shell.
$ kubectl exec -it $POD -- mysql -uroot -ppassword Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.40 MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
We will query the database to verify that the data is intact.
mysql> USE `classicmodels`; mysql> select `officeCode`, `city`, `phone` from `offices` where `country` = "USA"; +------------+---------------+-----------------+ | officeCode | city | phone | +------------+---------------+-----------------+ | 1 | San Francisco | +1 650 219 4782 | | 2 | Boston | +1 215 837 0825 | | 3 | NYC | +1 212 555 3000 | +------------+---------------+-----------------+ 3 rows in set (0.00 sec)
Observe that the database table is still there and all the content is intact! Exit from the client shell to return to the host.
Performing Storage Operations on MySQL
After testing end-to-end failover of the database, let’s perform StorageOps on our AKS cluster.
Expanding the Kubernetes Volume with no downtime
Currently the Portworx volume that we created at the beginning is of 1Gib size. We will now expand it to double the storage capacity.
First, let’s get the volume name and inspect it through the pxctl tool.
If you have access, SSH into one of the nodes and run the following command.
$ POD=`/opt/pwx/bin/pxctl volume list --label pvc=px-mysql-pvc | grep -v ID | awk '{print $1}'`
$ /opt/pwx/bin/pxctl v i $POD
Volume : 472432194854453148
Name : pvc-d70785ca-9a86-11e8-a409-1a6d80f90d97
Size : 1.0 GiB
Format : xfs
HA : 3
IO Priority : LOW
Creation time : Aug 7 21:14:20 UTC 2018
Shared : no
Status : up
State : Attached: aks-nodepool1-28253507-2 (10.240.0.6)
Device Path : /dev/pxd/pxd472432194854453148
Labels : namespace=default,pvc=px-mysql-pvc
Reads : 63
Reads MS : 132
Bytes Read : 319488
Writes : 557
Writes MS : 59076
Bytes Written : 175054848
IOs in progress : 0
Bytes used : 126 MiB
Replica sets on nodes:
Set 0
Node : 10.240.0.5 (Pool 0)
Node : 10.240.0.4 (Pool 0)
Node : 10.240.0.6 (Pool 0)
Replication Status : Up
Volume consumers :
- Name : mysql-654cc68f68-khnpq (f65339c6-9a86-11e8-a409-1a6d80f90d97) (Pod)
Namespace : default
Running on : aks-nodepool1-28253507-2
Controlled by : mysql-654cc68f68 (ReplicaSet)
Notice the current Portworx volume. It is 1GiB. Let’s expand it to 2GiB.
$ /opt/pwx/bin/pxctl volume update $POD --size=2 Update Volume: Volume update successful for volume 472432194854453148
Check the new volume size.
$ /opt/pwx/bin/pxctl v i $POD
Volume : 472432194854453148
Name : pvc-d70785ca-9a86-11e8-a409-1a6d80f90d97
Size : 2.0 GiB
Format : xfs
HA : 3
IO Priority : LOW
Creation time : Aug 7 21:14:20 UTC 2018
Shared : no
Status : up
State : Attached: aks-nodepool1-28253507-2 (10.240.0.6)
Device Path : /dev/pxd/pxd472432194854453148
Labels : namespace=default,pvc=px-mysql-pvc
Reads : 75
Reads MS : 136
Bytes Read : 368640
Writes : 596
Writes MS : 59096
Bytes Written : 175214592
IOs in progress : 0
Bytes used : 126 MiB
Replica sets on nodes:
Set 0
Node : 10.240.0.5 (Pool 0)
Node : 10.240.0.4 (Pool 0)
Node : 10.240.0.6 (Pool 0)
Replication Status : Up
Volume consumers :
- Name : mysql-654cc68f68-khnpq (f65339c6-9a86-11e8-a409-1a6d80f90d97) (Pod)
Namespace : default
Running on : aks-nodepool1-28253507-2
Controlled by : mysql-654cc68f68 (ReplicaSet)
Taking Snapshots of a Kubernetes volume and restoring the database
Portworx supports creating snapshots for Kubernetes PVCs.
Let’s create a snapshot for the PVC we created for MySQL.
cat > px-mysql-snap.yaml << EOF apiVersion: volumesnapshot.external-storage.k8s.io/v1 kind: VolumeSnapshot metadata: name: px-mysql-snapshot namespace: default spec: persistentVolumeClaimName: px-mysql-pvc EOF
$ kubectl create -f px-mysql-snap.yaml volumesnapshot.volumesnapshot.external-storage.k8s.io "px-mysql-snapshot" created
Verify the creation of volume snapshot.
$ kubectl get volumesnapshot NAME AGE px-mysql-snapshot 30s
$ kubectl get volumesnapshotdatas NAME AGE k8s-volume-snapshot-6ab731c7-9278-11e8-b018-e2f4b6cbb690 34s
With the snapshot in place, let’s go ahead and delete the database.
$ POD=`kubectl get pods -l app=mysql | grep Running | grep 1/1 | awk '{print $1}'`
$ kubectl exec -it $POD -- mysql -uroot -ppassword
drop database classicmodels;
Since snapshots are just like volumes, we can use it to start a new instance of MySQL. Let’s create a new instance of MySQL by restoring the snapshot data.
$ cat > px-mysql-snap-pvc << EOF
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
name: px-mysql-snap-clone
annotations:
snapshot.alpha.kubernetes.io/snapshot: px-mysql-snapshot
spec:
accessModes:
- ReadWriteOnce
storageClassName: stork-snapshot-sc
resources:
requests:
storage: 2Gi
EOF
$ kubectl create -f px-mysql-snap-pvc.yaml
persistentvolumeclaim "px-mysql-snap-clone" created
From the new PVC, we will create a MySQL pod.
$ cat < px-mysql-snap-restore.yaml >> EOF
apiVersion: apps/v1
kind: Deployment
metadata:
name: mysql-snap
spec:
selector:
matchLabels:
app: mysql-snap
strategy:
rollingUpdate:
maxSurge: 1
maxUnavailable: 1
type: RollingUpdate
replicas: 1
template:
metadata:
labels:
app: mysql-snap
spec:
affinity:
nodeAffinity:
requiredDuringSchedulingIgnoredDuringExecution:
nodeSelectorTerms:
- matchExpressions:
- key: px/running
operator: NotIn
values:
- "false"
- key: px/enabled
operator: NotIn
values:
- "false"
spec:
containers:
- name: mysql
image: mysql:5.6
imagePullPolicy: "Always"
env:
- name: MYSQL_ROOT_PASSWORD
value: password
ports:
- containerPort: 3306
volumeMounts:
- mountPath: /var/lib/mysql
name: mysql-data
volumes:
- name: mysql-data
persistentVolumeClaim:
claimName: px-mysql-snap-clone
EOF
$ kubectl create -f px-mysql-snap-restore.yaml deployment.extensions "mysql-snap" created
Verify that the new pod is in running state.
$ kubectl create -f px-mysql-snap-restore.yaml deployment.extensions "mysql-snap" created
Verify that the new pod is in running state.
$ kubectl get pods -l app=mysql-snap NAME READY STATUS RESTARTS AGE mysql-snap-5ddd6b6848-bb6wx 1/1 Running 0 30s
Finally, let’s access the sample data created earlier in the walkthrough.
$ POD=`kubectl get pods -l app=mysql-snap | grep Running | grep 1/1 | awk '{print $1}'`
$ kubectl exec -it $POD -- mysql -uroot -ppassword
mysql> USE `classicmodels`;
mysql> select `officeCode`, `city`, `phone` from `offices` where `country` = "USA";
+------------+---------------+-----------------+
| officeCode | city | phone |
+------------+---------------+-----------------+
| 1 | San Francisco | +1 650 219 4782 |
| 2 | Boston | +1 215 837 0825 |
| 3 | NYC | +1 212 555 3000 |
+------------+---------------+-----------------+
3 rows in set (0.00 sec)
Notice that the collection is still there with the data intact. We can also push the snapshot to an object storage location if we want to create a Disaster Recovery backup in another region. Portworx snapshots also work with any S3 compatible object storage, so the backup can go to a different cloud or even an on-premises data center.
Summary
Portworx can easily be deployed on AKS to run stateful workloads in production. Through the integration of STORK, DevOps and StorageOps teams can seamlessly run highly-available database clusters in AKS. They can perform traditional operations such as volume expansion, snapshots, backup and recovery for the cloud-native applications.
Share
Subscribe for Updates
About Us
Portworx is the leader in cloud native storage for containers.
Thanks for subscribing!
Janakiram MSV
Contributor | Certified Kubernetes Administrator (CKA) and Developer (CKAD)Explore Related Content:
- aks
- databases
- kubernetes
- mysql
