Portworx Guided Hands On-Labs. Register Now

Graphic-44

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 Azure Kubernetes Service (AKS)

Running HA MySQL on Google Kubernetes Engine (GKE)

Running HA MySQL on Amazon Elastic Container Service for Kubernetes (EKS)

Running HA MySQL on Red Hat OpenShift

Running HA MySQL on IBM Cloud Kubernetes Service (IKS)

How to Backup and Restore MySQL on Red Hat OpenShift

Running HA MySQL on IBM Cloud Private

Kubernetes Persistent Volume Tutorial by Portworx

And now, onto the post…

Rancher Kubernetes Engine (RKE) is a light-weight Kubernetes installer that supports installation on bare-metal and virtualized servers. RKE solves a common issue in the Kubernetes community: installation complexity. With RKE, Kubernetes installation is simplified, regardless of what operating systems and platforms you’re running.

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.

This tutorial is a walk-through of the steps involved in deploying and managing a highly available MySQL database on a Kubernetes cluster deployed in AWS through RKE.

In summary, to run HA MySQL on Amazon you need to:

  1. Install a Kubernetes cluster through Rancher Kubernetes Engine
  2. Install a cloud native storage solution like Portworx as a DaemonSet on Kubernetes
  3. Create a storage class defining your storage requirements like replication factor, snapshot policy, and performance profile
  4. Deploy MySQL using Kubernetes
  5. Test failover by killing or cordoning nodes in your cluster

How to set up a Kubernetes Cluster with RKE

RKE is a tool to install and configure Kubernetes in a choice of environments including bare metal, virtual machines, and IaaS. For this tutorial, we will be launching a 3-node Kubernetes cluster in Amazon EC2.

For a detailed step-by-step guide, please refer to this tutorial from The New Stack.

By the end of this step, you should have a cluster with one master and three worker nodes.

px-mysql-rke-0

Installing Portworx in Kubernetes

Installing Portworx on RKE-based Kubernetes is not different from installing it on a Kubernetes cluster setup through Kops. Portworx documentation has the steps involved in running the Portworx cluster in a Kubernetes environment deployed in AWS.

The New Stack tutorial mentioned in the previous section also covers all the steps to deploy Portworx DaemonSet in Kubernetes.

px-mysql-rke-1

Once the Kubernetes cluster is up and running, and Portworx is installed and configured, we will deploy a highly available MySQL database.

Creating a storage class for MySQL

Once the Kubernetes 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 create -f px-mysql-sc.yaml
storageclass.storage.k8s.io "px-ha-sc" created

$ 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-3a6788df-9274-11e8-8c5e-0253036635a0   1Gi        RWO            px-ha-sc       17s

Deploying MySQL on Kubernetes

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-dff54d66d-m9r6q   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	:  760417490447442140
	Name            	 :  pvc-2298b6ec-9936-11e8-8c5e-0253036635a0
	Size            	 :  1.0 GiB
	Format          	 :  xfs
	HA              	 :  3
	IO Priority     	 :  LOW
	Creation time   	 :  Aug 6 05:04:08 UTC 2018
	Shared          	 :  no
	Status          	 :  up
	State           	 :  Attached: ip-192-168-95-234.us-west-2.compute.internal
	Device Path     	 :  /dev/pxd/pxd760417490447442140
	Labels          	 :  namespace=default,pvc=px-mysql-pvc
	Reads           	 :  60
	Reads MS        	 :  20
	Bytes Read      	 :  294912
	Writes          	 :  512
	Writes MS       	 :  100644
	Bytes Written   	 :  172474368
	IOs in progress 	 :  0
	Bytes used      	 :  126 MiB
	Replica sets on nodes:
		Set  0
		  Node 		 :  192.168.95.234 (Pool 0)
		  Node 		 :  192.168.203.81 (Pool 0)
		  Node 		 :  192.168.185.157 (Pool 0)
	Replication Status	 :  Up

px-mysql-rke-2

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)

mysql> select `officeCode`,`city`,`phone`,`addressLine1`,`city` from `offices`;

Find all the offices in the 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/ip-172-31-29-132.ap-south-1.compute.internal cordoned

The above command disabled scheduling on one of the nodes.

$ kubectl get nodes
NAME                                           STATUS                     ROLES               AGE   VERSION
ip-172-31-24-121.ap-south-1.compute.internal   Ready                      worker              47h   v1.13.4
ip-172-31-26-49.ap-south-1.compute.internal    Ready                      controlplane,etcd   47h   v1.13.4
ip-172-31-28-65.ap-south-1.compute.internal    Ready                      worker              47h   v1.13.4
ip-172-31-29-132.ap-south-1.compute.internal   Ready,SchedulingDisabled   worker              47h   v1.13.4

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, co-locates the pod 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-172-31-24-121.ap-south-1.compute.internal
$ kubectl uncordon ${NODE}
node/ip-172-31-29-132.ap-south-1.compute.internal 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 Kubernetes 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	:  760417490447442140
	Name            	 :  pvc-3a6788df-9274-11e8-8c5e-0253036635a0
	Size            	 :  1.0 GiB
	Format          	 :  xfs
	HA              	 :  3
	IO Priority     	 :  LOW
	Creation time   	 :  Jul 28 14:40:52 UTC 2018
	Shared          	 :  no
	Status          	 :  up
	State           	 :  Attached: ip-192-168-95-234.us-west-2.compute.internal
	Device Path     	 :  /dev/pxd/pxd150455926773027922
	Labels          	 :  namespace=default,pvc=px-mysql-pvc
	Reads           	 :  188
	Reads MS        	 :  104
	Bytes Read      	 :  8458240
	Writes          	 :  23
	Writes MS       	 :  128
	Bytes Written   	 :  2347008
	IOs in progress 	 :  0
	Bytes used      	 :  126 MiB
	Replica sets on nodes:
		Set  0
		  Node 		 :  192.168.95.234 (Pool 0)
		  Node 		 :  192.168.203.81 (Pool 0)
		  Node 		 :  192.168.185.157 (Pool 0)
	Replication Status	 :  Up

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 150455926773027922

Check the new volume size.

$ /opt/pwx/bin/pxctl v i $POD
Volume	:  760417490447442140
	Name            	 :  pvc-3a6788df-9274-11e8-8c5e-0253036635a0
	Size            	 :  2.0 GiB
	Format          	 :  xfs
	HA              	 :  3
	IO Priority     	 :  LOW
	Creation time   	 :  Jul 28 14:40:52 UTC 2018
	Shared          	 :  no
	Status          	 :  up
	State           	 :  Attached: ip-192-168-95-234.us-west-2.compute.internal
	Device Path     	 :  /dev/pxd/pxd150455926773027922
	Labels          	 :  namespace=default,pvc=px-mysql-pvc
	Reads           	 :  200
	Reads MS        	 :  104
	Bytes Read      	 :  8507392
	Writes          	 :  60
	Writes MS       	 :  164
	Bytes Written   	 :  2498560
	IOs in progress 	 :  0
	Bytes used      	 :  126 MiB
	Replica sets on nodes:
		Set  0
		  Node 		 :  192.168.95.234 (Pool 0)
		  Node 		 :  192.168.203.81 (Pool 0)
		  Node 		 :  192.168.185.157 (Pool 0)
	Replication Status	 :  Up

px-mysql-rke-3

Taking Snapshots of a Kubernetes volume and restoring the database

Portworx supports creating snapshots for Kubernetes PVCs.
Let’s create a snapshot of 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 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 walk-through.

$ 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 Amazon S3 if we want to create a Disaster Recovery backup in another Amazon 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 be easily deployed with RKE to run stateful workloads in production on Kubernetes. Through the integration of STORK, DevOps and StorageOps teams can seamlessly run highly available database clusters in Kubernetes. 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.

gP_biIhl

Janakiram MSV

Contributor | Certified Kubernetes Administrator (CKA) and Developer (CKAD)
Explore Related Content:
  • databases
  • kubernetes
  • mysql
  • rancher
  • rancher kubernetes engine
  • RKE
link
June 18, 2024 How To
SUSECON Demos - Disaster Recovery, Autopilot and More
Chris Crow
Chris Crow
link
April 29, 2024 How To
Using ArgoCD to Deploy Portworx
Chris Crow
Chris Crow
link
px_containers
April 3, 2023 How To
Run Kafka on Kubernetes with Portworx Services
Eric Shanks
Eric Shanks