Exploring MySQL on Kubernetes with Minkube

Exploring MySQL on Kubernetes with Minkube

In this blog post, I will show how to install the MySQL-compatible Percona XtraDB Cluster (PXC) Operator on Minikube as well as perform some basic actions.   I am by no means a Kubernetes expert and this blog post is the result of my explorations preparing for a local MySQL Meetup, so if you have some comments or suggestions on how to do things better, please comment away!

For my experiments, I used Minikube version 1.26 with the docker driver in the most basic installation on Ubuntu 22.04 LTS, though it should work with other combinations, too. You also can find the official “Running Percona XtraDB Cluster on Minikube” documentation here.

You will also need kubectl installed for this tutorial to work. Alternatively, you can use “minikube kubectl” instead of “kubectl” in the examples.

You will also need MySQL client, “jq” and sysbench utilities installed.

I also made the commands and Yaml configurations I’m using throughout this tutorial available in the minikube-pxc-tutorial GitHub repository 

Enabling Metrics Server in Minikube

As we may want to look into resource usage in some of our experiments, we can consider enabling the Metrics Server add-on which is done by running:

Getting basic MySQL up and running on Kubernetes

If you use Percona XtraDB Cluster Operator for your MySQL deployment on Kubernetes, the first thing you need to do is to install that operator:

Next, we can create our cluster which we’ll call “minimal-cluster”:

Note that the completion of this command does not mean the cluster is provisioned and ready for operation, but rather that the process is started and it may take a bit of time to complete.  You can verify that it is provisioned and ready for operation by running:

You can see three pods running – one is the MySQL (Percona XtraDB Cluster), one is pxc-0 node, and one is the HAproxy haproxy-0 node, which is used to provide high availability for “real” clusters with more than one cluster node in operation.

If you do not want to experiment with high availability options, such a single node deployment is all you need for basic development tasks.

Percona Operator for MySQL Custom Resource Manifest explained

Before we go further let’s look at the YAML file we just deployed to check what’s in it. For a complete list of supported options check out the documentation:

This resource definition corresponds to Percona XtraDB Cluster Operator version 1.11  Our cluster will be named minimal-cluster.

Once again, we specify the version of the custom resource definition and the name of the “secrets” resource that this cluster will use. It is derived from cluster name for convenience but actually could be anything.

We are deploying a single node cluster (at first) which is considered an unsafe configuration, so one needs to allow an unsafe configuration for such deployment to succeed. For production deployments, you should not allow unsafe configuration unless you really know what you’re doing.

This section automatically checks for updates every day at 4 AM and performs upgrades to the recommended version if available.

PXC is the main container of this pod containing the cluster itself – we need to store data on a persistent volume, so we’re defining it here.  Use the default volume ask for a 6GB size parameter to define how many nodes to provision and image which particular image of Percona XtraDB Cluster to deploy.

Deploy HAProxy to manage the high availability of the cluster. We only deploy one for testing purposes. In production, though, you need at least two to ensure you do not have a single point of failure.

Collect logs from the Percona XtraDB Cluster container, so they are persisted even if that container is destroyed.

Accessing the MySQL server you provisioned 

But how do we access our newly provisioned MySQL/Percona XtraDB Cluster instance?

By default, the instance is not accessible outside of the Kubernetes cluster for security purposes, which means outside of the Minikube environment, but in our case, and if we want it to be, we need to expose it:

We can see the result of this command by running:  

Unlike the rest of the services which are only accessible inside the Kubernetes cluster (hence ClusterIP type)  our “mysql-test” service has NodePort type which means it is exposed on the port on the local node.  In our case, port 3306, which is the standard port MySQL listens on, is mapped to port 30504. 

Note that IP 10.108.103.151 mentioned here is internal cluster IP and it is not accessible. To find out which IP you should utilize, you can use:

Even though this is not an HTTP protocol service, the URL is quite helpful in showing us the IP and port we can use to access MySQL. 

An alternative to exposing by running “expose service command” is you can also set haproxy.serviceType=NodePort in manifest.

Next, we need the MySQL password! To keep things secure, Percona Operator for MySQL does not have a default password but instead generates a unique secure password which is stored in Kubernetes Secrets. In fact, there are two Secrets created: one for internal use and another intended to be accessible by the user – this is the one we will use:

Let’s look deeper into minimal-cluster Secrets:

We can see Percona Operator for MySQL has a number of users created and it stores their passwords in Kubernetes Secrets.  Note those values are base64 encoded. 

Let’s now get all the MySQL connection information we discussed and store it in various variables:

We’re using these specific names as “mysql” command line client (but not mysqladmin) and will use those variables by default, making it quite convenient. 

Let’s check if MySQL is accessible using those credentials:

Works like a charm!

Running Sysbench on MySQL on Kubernetes

Now that we know how to access MySQL, let’s create a test database and load some data:

And now we can run a test:

Great, we can see our little test cluster can run around 30,000 queries per second!

Let’s make MySQL on Kubernetes highly available!

Let’s see if we can convert our single-node Percona XtraDB Cluster to fake a highly available cluster (fake – because with MinKube running on a single node, we’re not going to be protected from actual hardware failures).

To achieve this we can simply scale our cluster to three nodes:

As usual, Kubernetes “cluster scaled” here does not mean what cluster actually got scaled but what new desired state was configured and the operator started to work on scaling the cluster.

You can wait for a few minutes and then notice that one of the pods is stuck in a pending state and it does not look like it’s progressing:

To find out what’s going on we can describe the pod:

There is a lot of information in the output but for our purpose, it is the events section that is most important.

What the warning message here is saying is only one node is available while anti-affinity rules prevent scheduling more pods on this node.  This makes sense – in a real production environment, it would not be acceptable to schedule more than one Percona XtraDB Cluster pod on the same physical server as this would end up being a single point of failure. 

This is for production, though, but for testing, we can allow running multiple pods on the same physical machine, as one physical machine is all we have. So we modify the resource definition by disabling anti-affinity protection:

Let’s apply this modified configuration to the cluster.  (You can find this and another YAML file in the GitHub repository).

And try scaling it again:

Instead of constantly checking for provisioning of all new nodes to be complete, we can watch as pods progress through initialization and check if there is any problem:

Great! We see two additional “pxc” pods were provisioned and running!

Setting up resource limits for your MySQL on Kubernetes

As of now, we have not specified any resource limits (besides persistent volume size) for our MySQL deployment. This means it will use all resources currently available on the host, and while this might be the preferred way for testing an environment for production, you’re likely to be looking for both more predictable performance and more resource isolation so that a single cluster can’t oversaturate the node degrading performance of other pods running on the same node.

In order to place resource limits you need to place an appropriate section in the custom resource definition:

This resource definition means we’re requesting at least 1GB of memory and 0.2 CPU core, and limit resources this instance will be used to 0.5 CPU core and the same 1GB of memory.

Note that if the “requests” conditions can’t be satisfied, i.e. if the required amount of CPU or memory is not available in the cluster, the pod will not be scheduled, waiting for resources to become available (possibly forever).

Let’s apply those limits to our cluster:

If we look at what happens to the pods after we apply this command, we see:

So changing resource limits requires cluster restart.

If you set resource limits, the operator will automatically set some of MySQL configuration parameters (for example max_connections) according to requests (resources guaranteed to be available).  If no resource constraints are specified, the operator will not perform any automatic configuration.

If we run the same Sysbench test again, we will see:

A quite more modest result than running with no limits. 

We can validate actual CPU usage by the pods as we run benchmarks by running:

We can observe a few interesting things here: 

• The limit for Percona XtraDB Cluster looks well respected.
• We can see only one of the Percona XtraDB Cluster nodes getting the load. This is expected as HAProxy does not load balance queries among cluster nodes, just ensures high availability. If you want to see queries load balanced, you either can use a separate port for load balanced reads or deploy ProxySQL for intelligent query routing. 
• HAProxy pod required about 50% of the resources of Percona XtraDB Cluster pod (this is a worst-case scenario of very simple in-memory queries), so do not underestimate its resource usage needs!

Pausing and resuming MySQL on Kubernetes 

If you have a MySQL instance that you use for testing/development, or otherwise do not need it running all the time, if you are running Linux you can just Stop MySQL Process, or Stop MySQL Container if you’re running Docker.  How do you achieve the same in Kubernetes? 

First, you need to be careful – if you delete the cluster instance, it will destroy both compute and storage resources and you will lose all the data.  Instead of deleting you need to pause the cluster. 

To pause the cluster, you can just add a pause option to the cluster custom resource definition:

Let’s apply CR with this option enabled and see what happens:

After the pause process is complete, we will not see any cluster pods running, just operator:

However, you still will see persistent volume claims which hold cluster data present:

We can also see the cluster is in a paused state by this command:

We can unpause the cluster by applying the same CR with the value  pause: false and the cluster will be back online in a couple of minutes:

MySQL on Kubernetes backup (and restore)

If you care about your data you need backups, and Percona Operator for MySQL provides quite a few backup features including scheduled backups, point-in-time recovery, backing up to S3 compatible storage, and many others.  In this walkthrough, though, we will look at the most simple backup and restore – taking a backup to persistent volume.

First, we need the cluster configured for backups; we need to configure the storage to which the backup will be performed, as well as provide an image for the container which will be responsible for managing backups:

As before, we can apply a new configuration which includes this section by kubectl apply -f 6-1-backup-config.yaml. While we can schedule backups to take place automatically, we will focus on running backups manually. To do this, you can apply the following backup CR:

It basically defines creating a backup named “backup1” for the cluster named “minimal-cluster” and storing it on “fs-pvc” storage volume:

After the backup job has successfully completed, we can see its status. 

Let’s now mess up our database before attempting to restore:

The restore happens similar to backup by running Restore Job on the cluster with the following configuration:

We can do this by applying the configuration and checking its status:

What if you mess up the database again (not uncommon in a development environment) and want to restore the same backup again?

If you just run restore again, it will not work.

Because a job with the same name already exists in the system, you can either create a restore job with a different name or delete the restore object and run it again:

Note, while deleting the restore job does not affect backup data or a running cluster, removing “backup” removes the data stored in this backup, too:

Monitoring your deployment with Percona Monitoring and Management (PMM)

Your MySQL deployment would not be complete without setting it up with monitoring, and in Percona Operator for MySQL, Percona Monitoring and Management (PMM) support is built in and can be enabled quite easily.

According to Kubernetes best practices, credentials to access PMM are not stored in the resource definition, but rather in secrets. 

As recommended, we’re going to use PMM API Keys instead of a user name and password.  We can get them in the API Keys configuration section:

After we’ve created the API Key we can store it in our cluster’s secret:

Next, we need to add the PMM configuration section to the cluster custom resource, specifying the server where our PMM server is deployed.  

Apply complete configuration:

As deployment is complete you should see that the MySQL/PXC and HAProxy instance statistics are visible in the Percona Monitoring and Management instance you specified:

Note, as of this writing (Percona Monitoring and Management 2.28), PMM is not fully Kubernetes aware, so it will report CPU and memory usage in the context of the node, not what has been made available for a specific pod.

Configuring MySQL on Kubernetes

So far we’ve been running MySQL (Percona XtraDB Cluster) with the default settings, and even though Percona Operator for MySQL automatically configures some of the options for optimal performance, chances are you will want to change some of the configuration options.

Percona Operator for MySQL provides multiple ways to change options, and we will focus on what I consider the most simple and practical – including them in the CR definition.

Simply add as a section:

Which will be added to your MySQL configuration file, hence overriding any default values or any adjustments the operator has done during deployment.

As usual, you can apply a new configuration to see those applied by running kubectl apply -f 8-config.yaml.

If you ever misspelled a configuration option name, you might wonder what would happen.

The operator will try applying configuration to one of the nodes, which will fail with one of the nodes repeatedly crashing and restarting, but the cluster will be available in a degraded state.

To fix the issue, just correct the configuration error and reapply.

Accessing MySQL logs

To troubleshoot your MySQL on Kubernetes deployment you may need to access MySQL logs. Logs are provided in the “logs” container in each PXC node pod. (More info) To access them, you can use something like:

Note: as you can see, logs are provided in JSON format and can be processed with “jq” command, if needed. 

Deleting the MySQL deployment

Deleting your deployed cluster is very easy, so remember with great power comes great responsibility. Just one command will destroy the entire cluster, with all its data:

Note: this only applies to the PXC cluster; any backups taken from the cluster are considered separate objects and will not be deleted by this operation.

Protecting PVCs from deletion

This might NOT be good enough for your production environment and you may want to have more protection for your data from accidental loss, and Percona Operator for MySQL allows that.  If finalizers.delete-pxc-pvc is NOT configured, persistent volumes claims (PVCs) are not going to be deleted after cluster deletion, so re-creating a cluster with the same will allow you to recover your data. If this is the route you take, remember to have your own process to manage PVCs  – otherwise, you may see massive space usage from old clusters.

Summary

I hope this walkthrough was helpful to introduce you to the power of Percona Operator for MySQL and what it looks like to run MySQL on Kubernetes.  I would encourage you not to end with a read but to deploy your own Minikube and play around with the scenarios mentioned in this blog.  I’ve included all the scripts (and more) in the GitHub repository, and by practicing rather than reading alone you can learn much faster!