Using Portworx to Deploy and Manage an HA MySQL Cluster on IBM Cloud Kubernetes Service

Compute

3 May 2019 27 min read

By:

Chris Rosen, Program Director, Offering Management
Janakiram MSV, None

IBM Cloud Kubernetes Service and Portworx

IBM Cloud Kubernetes Service is a managed Kubernetes offering running in IBM Cloud. It is designed to deliver powerful tools, intuitive user experience, and built-in security for rapid delivery of applications that can be bound to cloud services related to IBM Watson, IoT, DevOps, and data analytics. As a CNCF-certified Kubernetes provider, IBM Cloud Kubernetes Service provides intelligent scheduling, self-healing, horizontal scaling, service discovery and load balancing, automated rollouts and rollbacks, and secret and configuration management. The service also has advanced capabilities around simplified cluster management, container security, and isolation policies, the ability to design a cluster with a custom configuration and integrated operational tools for consistency in deployment.

Portworx is a Kubernetes storage and data management platform that enables enterprises to confidently run mission-critical data services on IBM Cloud Kubernetes Service (as well as IBM Cloud Private). The addition of primitives (such as stateful sets and persistent volumes) to Kubernetes has made it possible, in theory, to run stateful services like databases on Kubernetes. But, these primitives alone do not address the core business challenges associated with running data-rich applications on Kubernetes: high availability, backup and recovery, data security, SLA-management, and more. Portworx provides a single data-management layer for all stateful services that directly addresses these challenging topics.

I’m really excited to work with Portworx and Janakiram MSV (@janakiramm), architect and advisor with Portworx, on this tutorial. The rest of the blog was authored by Jani.

Tutorial

This tutorial is a walkthrough of the steps involved in deploying and managing a highly available MySQL cluster on IBM Cloud Kubernetes Service.

In summary, to run HA MySQL on IBM Cloud Kubernetes Service you need to do the following:

Launch an IBM Cloud Kubernetes Service cluster running on bare metal servers with software-defined storage (SDS).
Install a cloud-native storage solution like Portworx as a Daemonset on IBM Cloud Kubernetes Service.
Create a 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.
Expand the volume size dynamically.
Perform backup and restore through snapshots.

Launching an IBM Cloud Kubernetes Service Cluster

For running stateful workloads in a production environment backed by Portworx, it is highly recommended to launch an IBM Cloud Kubernetes Service cluster based on bare metal servers and software-defined storage. The minimum requirements of a worker node to successfully run Portworx include:

4 CPU cores
4GB memory
128GB of raw unformatted storage
10Gbps network speed

For details on launching a Kubernetes cluster with bare metal worker nodes, please refer to the documentation of IBM Cloud Kubernetes Service.

We are using an IBM Cloud Kubernetes Service cluster with four nodes, out of which three nodes are running bare metal servers with SDS based on the instance type ms2c.4x32.1.9tb.ssd.encrypted. Only these machines that meet the prerequisite would be used by Portworx.

When we filter the nodes based on the label, we see the below nodes:

$ kubectl get nodes -l beta.kubernetes.io/instance-type=ms2c.4x32.1.9tb.ssd.encrypted
NAME           STATUS   ROLES    AGE    VERSION
10.177.26.18   Ready    <none>   4d7h   v1.13.2+IKS
10.185.22.28   Ready    <none>   4d7h   v1.13.2+IKS
10.73.90.131   Ready    <none>   4d3h   v1.13.2+IKS

To exclude nodes that don’t meet Portworx prerequisites, you can apply a label to skip the installation of Portworx. For example, the below command applies a label on the node with name 10.185.22.14 which doesn’t run on a bare metal server:

$ kubectl label nodes 10.185.22.14  px/enabled=false --overwrite

Installing Portworx on IBM Cloud Kubernetes Service

Installing Portworx on IBM Cloud Kubernetes Service is not very different from installing it on any other Kubernetes cluster. It is recommended that you create an etcd instance through Compose for etcd. You can use the Helm Chart to install a Portworx cluster in IBM Cloud Kubernetes Service. Portworx documentation for IBM Cloud Kubernetes Service has the prerequisites and instructions to install and configure Portworx, STORK, and other components.

At the end of the installation, we will have Portworx Daemonset running on the nodes, excluding those that are filtered out in the previous step:

Creating a Kubernetes storage class for MySQL

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

Through Kubernetes 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"
   io_priority: "high"
   fs: "xfs"
EOF
$ kubectl create -f px-mysql-sc.yaml
storageclass.storage.k8s.io "px-ha-sc" created
$ kubectl get sc

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

Deploying MySQL on IBM Cloud Kubernetes Service

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: extensions/v1beta1
kind: Deployment
metadata:
  name: mysql
spec:
  strategy:
    rollingUpdate:
      maxSurge: 1
      maxUnavailable: 1
    type: RollingUpdate
  replicas: 1
  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:

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	:  781585151816423188
	Name            	 :  pvc-909a51d5-31a3-11e9-aa17-32e4785d9ba0
	Size            	 :  1.0 GiB
	Format          	 :  ext4
	HA              	 :  3
	IO Priority     	 :  LOW
	Creation time   	 :  Feb 16 04:30:18 UTC 2019
	Shared          	 :  no
	Status          	 :  up
	State           	 :  Attached: 6ab3face-615f-4cc7-bcfa-a1872d006e34 (10.185.22.29)
	Device Path     	 :  /dev/pxd/pxd781585151816423188
	Labels          	 :  namespace=default,pvc=px-mysql-pvc
	Reads           	 :  12
	Reads MS        	 :  36
	Bytes Read      	 :  49152
	Writes          	 :  609
	Writes MS       	 :  10256
	Bytes Written   	 :  146239488
	IOs in progress 	 :  0
	Bytes used      	 :  10 MiB
	Replica sets on nodes:
		Set 0
		  Node 		 : 10.73.90.131 (Pool 0)
		  Node 		 : 10.177.26.18 (Pool 0)
		  Node 		 : 10.185.22.29 (Pool 0)
	Replication Status	 :  Up
	Volume consumers	 :
		- Name           : mysql-76d5c4b78b-hqjcs (bb30c604-31a3-11e9-930d-4e511e6b17c9) (Pod)
		  Namespace      : default
		  Running on     : 10.185.22.29
		  Controlled by  : mysql-76d5c4b78b (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`;

7 rows in set (0.01 sec)

Find all the offices in the USA:

mysql> select `officeCode`, `city`, `phone` from `offices` where `country` = "USA";

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/10.185.22.29 cordoned

The above command disabled scheduling on one of the nodes:

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-76d5c4b78b-hqjcs" deleted

As soon as the pod is deleted, it is relocated to the node with the replicated data. STorage ORchestrator for Kubernetes (STORK), Portworx’s custom storage scheduler, allows co-locating 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:

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:

$ POD=`kubectl get pods -l app=mysql -o wide | grep -v NAME | awk '{print $1}'`
$ kubectl exec -it $POD -- mysql -uroot -ppassword

We will query the database to verify that the data is intact:

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 IBM Cloud Kubernetes Service cluster.

Expanding the Kubernetes Volume with no downtime

Currently, the Portworx volume that we created at the beginning is 1Gib in 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:


$ 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	:  781585151816423188
	Name            	 :  pvc-909a51d5-31a3-11e9-aa17-32e4785d9ba0
	Size            	 :  1.0 GiB
	Format          	 :  ext4
	HA              	 :  3
	IO Priority     	 :  LOW
	Creation time   	 :  Feb 16 04:30:18 UTC 2019
	Shared          	 :  no
	Status          	 :  up
	State           	 :  Attached: d7f53ebb-0d02-4ba4-b689-2f335e8f9379 (10.177.26.18)
	Device Path     	 :  /dev/pxd/pxd781585151816423188
	Labels          	 :  namespace=default,pvc=px-mysql-pvc
	Reads           	 :  178
	Reads MS        	 :  400
	Bytes Read      	 :  6422528
	Writes          	 :  26
	Writes MS       	 :  164
	Bytes Written   	 :  294912
	IOs in progress 	 :  0
	Bytes used      	 :  17 MiB
	Replica sets on nodes:
		Set 0
		  Node 		 : 10.73.90.131 (Pool 0)
		  Node 		 : 10.177.26.18 (Pool 0)
		  Node 		 : 10.185.22.29 (Pool 0)
	Replication Status	 :  Up
	Volume consumers	 :
		- Name           : mysql-76d5c4b78b-vgwk6 (b1094e0f-31a4-11e9-930d-4e511e6b17c9) (Pod)
		  Namespace      : default
		  Running on     : 10.177.26.18
		  Controlled by  : mysql-76d5c4b78b (ReplicaSet)

Notice the current Portworx volume. It is 1GiB. Let’s expand it to 2GiB:

$ kubectl exec -it $PX_POD -n kube-system -- /opt/pwx/bin/pxctl volume update $VOL --size=2
Update Volume: Volume update successful for volume pvc-909a51d5-31a3-11e9-aa17-32e4785d9ba0

Check the new volume size:

kubectl exec -it $PX_POD -n kube-system -- /opt/pwx/bin/pxctl volume inspect ${VOL}
Volume	:  781585151816423188
	Name            	 :  pvc-909a51d5-31a3-11e9-aa17-32e4785d9ba0
	Size            	 :  2.0 GiB
	Format          	 :  ext4
	HA              	 :  3
	IO Priority     	 :  LOW
	Creation time   	 :  Feb 16 04:30:18 UTC 2019
	Shared          	 :  no
	Status          	 :  up
	State           	 :  Attached: d7f53ebb-0d02-4ba4-b689-2f335e8f9379 (10.177.26.18)
	Device Path     	 :  /dev/pxd/pxd781585151816423188
	Labels          	 :  namespace=default,pvc=px-mysql-pvc
	Reads           	 :  253
	Reads MS        	 :  624
	Bytes Read      	 :  6729728
	Writes          	 :  46
	Writes MS       	 :  280
	Bytes Written   	 :  1212416
	IOs in progress 	 :  0
	Bytes used      	 :  17 MiB
	Replica sets on nodes:
		Set 0
		  Node 		 : 10.73.90.131 (Pool 0)
		  Node 		 : 10.177.26.18 (Pool 0)
		  Node 		 : 10.185.22.29 (Pool 0)
	Replication Status	 :  Up
	Volume consumers	 :
		- Name           : mysql-76d5c4b78b-vgwk6 (b1094e0f-31a4-11e9-930d-4e511e6b17c9) (Pod)
		  Namespace      : default
		  Running on     : 10.177.26.18
		  Controlled by  : mysql-76d5c4b78b (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 Kubernetes 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-ce24471e-31a5-11e9-993d-eaa7125cd4d9   41s
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: extensions/v1beta1
kind: Deployment
metadata:
  name: mysql-snap
spec:
  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 a Running state:

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";

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 Amazon S3-compatible object storage service if we want to create a disaster recovery backup in another region or location. Since Portworx snapshots work with any S3 compatible object storage, the backup can go to a different cloud or even an on-premises data center.

Summary

Portworx can be easily deployed on IBM Cloud Kubernetes Service to run stateful workloads in production. Through the integration of STORK, DevOps and StorageOps teams can seamlessly run highly available database clusters in IBM Cloud Kubernetes Service. They can perform traditional operations such as volume expansion, backup, and recovery for the cloud-native applications in an automated and efficient manner.

Chris Rosen

Program Director, Offering Management

Janakiram MSV

None

Follow IBM Cloud

Be the first to hear about news, product updates, and innovation from IBM Cloud.

Email subscribe RSS

Using Portworx to Deploy and Manage an HA MySQL Cluster on IBM Cloud Kubernetes Service

IBM Cloud Kubernetes Service and Portworx

Tutorial

Launching an IBM Cloud Kubernetes Service Cluster

Installing Portworx on IBM Cloud Kubernetes Service

Creating a Kubernetes storage class for MySQL

Creating a MySQL PVC on Kubernetes

Deploying MySQL on IBM Cloud Kubernetes Service

Failing over MySQL pod on Kubernetes

Populating sample data

Simulating node failure

Verifying that the data is intact

Performing storage operations on MySQL

Expanding the Kubernetes Volume with no downtime

Taking Snapshots of a Kubernetes volume and restoring the database

Summary

Chris Rosen

Janakiram MSV

Follow IBM Cloud

IBM Cloud Technologies

Related Articles

2021 Planning: Top 4 Cloud Trends Tech Leaders Need to Know

New Liberty-for-Java Buildpack v3.54 is Available on IBM Cloud

Kubernetes Persistent Volumes Backed by IBM Cloud Object Storage Buckets