We will explore how to easily provision, backup, restore & monitor MYSQL Instances on Kubernetes the easy way using MYSQL Operator.
“Running databases in Kubernetes is one shiny disco ball that attracts a lot of limelight. Although Databases by their very nature are stateful, on the other hand, Kubernetes is more inclined towards running stateless & ephemeral applications, which makes them two different worlds apart, although to make them one ❤ we have MYSQL Operator. As there is some great amount of work done worldwide in the Open Source community by Oracle, Presslabs & Percona providing rich MySQL Operator’s which make running MYSQL on Kubernetes a hassle-free experience.”
What is MYSQL Operator?
Operator are applications written ontop of kubernetes which makes challenging & domain specific operations automated & easy. We are choosing *MYSQL Operator* from *Presslabs which makes running **MySQL as a service* with built-in High-Availability, Scalability & Monitoring quite simple. A single definition of MYSQL Cluster can include all the information needed for backup, storage along with MySQLD configuration.
Architecture of MYSQL Operator
The whole infrastructure runs on top of Kubernetes, along with **Github Orchestrator** which is an open-source tools that provides a pretty intuitive UI, also we have the MYSQL Operator which does the actual heavy lifting & provisions various MySQL Nodes & Services, what’s even greater is that each MYSQL Instances has mysqld-exporter service running which can be used for monitoring.
Reason’s to use Presslabs MYSQL Operator
Automatic on-demand & scheduled backups.
Initializing new clusters from existing backups.
Built-in High-Availability, Scalability, Self-Healing & Monitoring.
Save money & Avoid vendor lock-in’s
Best suited for Microservices Architecture.
Prerequisite
Basic understanding of MYSQL, Kubernetes & Kubernetes Operator
Installing MySQL Operator using helm
It’s a simple straightforward way to install mysql-operator with helm, run the following commands, based on the version of helm installed on your machine.
| helm repo add presslabs https://presslabs.github.io/charts | |
| helm update | |
| # (helm v2) | |
| helm install presslabs/mysql-operator --name mysql-operator --create-namespace | |
| # (helm v3) | |
| helm install mysql-operator presslabs/mysql-operator -n mysql-operator --create-namespace |
Provisioning: MYSQL Cluster
Once, we are done installing MYSQL Operator the next step is to provision MYSQL cluster instance with credentials, storage & MySQL configuration.
| apiVersion: v1 | |
| kind: Secret | |
| metadata: | |
| name: MyCluster-Secret | |
| type: Opaque | |
| data: | |
| ROOT_PASSWORD: "Y2hhbmdtZQ==" | |
| --- | |
| apiVersion: mysql.presslabs.org/v1alpha1 | |
| kind: MysqlCluster | |
| metadata: | |
| name: MyCluster | |
| spec: | |
| mysqlVersion: "5.7" | |
| replicas: 1 | |
| secretName: MyCluster-Secret | |
| mysqlConf: | |
| read_only: 0 # mysql forms a single transaction for each sql statement, autocommit for each statement | |
| automatic_sp_privileges: "ON" # automatically grants the EXECUTE and ALTER ROUTINE privileges to the creator of a stored routine | |
| auto_generate_certs: "ON" # Auto Generation of Certificate | |
| auto_increment_increment: 1 # Auto Incrementing value from +1 | |
| auto_increment_offset: 1 # Auto Increment Offset | |
| binlog-format: "STATEMENT" # contains various options such ROW(SLOW,SAFE) STATEMENT(FAST,UNSAFE), MIXED(combination of both) | |
| wait_timeout: 31536000 # 28800 number of seconds the server waits for activity on a non-interactive connection before closing it, You might encounter MySQL server has gone away error, you then tweak this value acccordingly | |
| interactive_timeout: 28800 # The number of seconds the server waits for activity on an interactive connection before closing it. | |
| max_allowed_packet: "512M" # Maximum size of MYSQL Network protocol packet that the server can create or read 4MB, 8MB, 16MB, 32MB | |
| max-binlog-size: 1073741824 # binary logs contains the events that describe database changes, this parameter describe size for the bin_log file. | |
| log_output: "TABLE" # Format in which the logout will be dumped | |
| master-info-repository: "TABLE" # Format in which the master info will be dumped | |
| relay_log_info_repository: "TABLE" # Format in which the relay info will be dumped | |
| volumeSpec: | |
| persistentVolumeClaim: | |
| accessModes: | |
| - ReadWriteMany | |
| resources: | |
| requests: | |
| storage: 10Gi |
In this case, we have configured a root password which needs to be base64 encoded, we can do that with help of base64encode, along with it we can configure mysqld configuration an persistent volume claim for storage.
List & **Describe **the available MYSQL Instances
kubectl get mysql
kubectl describe mysql/<NAME_OF_MYSQL_CLUSTER>
Connecting to a MySQL Instance
kubectl -n mysql-operator run mysql-client --image=mysql:5.7 -it --rm --restart=Never -- /bin/bash
mysql -uroot -p'changme' -h '**mycluster-mysql.default.svc.cluster.local**' -P3306
Exposing MYSQL Cluster Publically using Ingress
There are some minor tweaks needed in the Ingress configuration such as adding the port number along with service to be exposed when it comes to TCP Servies, i have already addressed it in this blog.
Setting up a Standalone MYSQL Instance on Kubernetes & exposing it using Nginx Ingress Controller.
Backups
Backups are done pretty easily in an automated fashion with On-demand or Scheduled backups. Backups are stored on an Object Storage such as AWS S3, GCloud Bucket, Google Drive & Azure Bucket. Backups are done using rclone.
Creating Backups
In order for backups to be uploaded on these object storage we need to provide the credentials for the same using Kubernetes Secrets which need to base64 encoded.
| apiVersion: v1 | |
| data: | |
| AWS_ACCESS_KEY_ID: # | |
| AWS_SECRET_ACCESS_KEY: # | |
| AWS_REGION: # | |
| S3_PROVIDER: # | |
| AZUREBLOB_ACCOUNT: # | |
| AZUREBLOB_KEY: # | |
| GCS_SERVICE_ACCOUNT_JSON_KEY: # | |
| GCS_PROJECT_ID: # | |
| kind: Secret | |
| metadata: | |
| name: mycluster-backup-secret | |
| type: Opaque |
| apiVersion: mysql.presslabs.org/v1alpha1 | |
| kind: MysqlBackup | |
| metadata: | |
| name: mycluster-backup | |
| spec: | |
| clusterName: mycluster | |
| backupURL: s3://<NAME_OF_S3_BUCKET> | |
| backupSecretName: mycluster-backup-secret |
We can list or view the status of the backups. MysqlBackups are actually runs as Jobs inside of Kubernetes Cluster.
kubectl get MysqlBackup
kubectl describe MysqlBackup/<BACKUP_NAME>
Creating Scheduled Backups
Operator provides a way in which clusters can be periodically backed up in regular intervals by specifying a cron expression.
| apiVersion: mysql.presslabs.org/v1alpha1 | |
| kind: MysqlCluster | |
| metadata: | |
| name: scheduled-mysqlcluster | |
| spec: | |
| secretName: mycluster-secret # contains Database credentials | |
| backupSchedule: "0 0 0 * * *" # Cron Expression to run dialy backup | |
| backupURL: s3://<S3_BUCKET_NAME> # S3 Endpoint to store the backup | |
| backupSecretName: mycluster-backup-seceret # Contains Authentication details for Storage Provider | |
| backupRemoteDeletePolicy: retain|delete # Backup Retention Policy |
Restoring Backups
Initializing a cluster from existing backups
There are often at times the need to spawn databases in Microservice environment which are restored from a point in-time backup or snapshot. MYSQL Operator makes it pretty easy by defining initBucketURL which points to backup archive file along with secret initBucketSecretName to access the file.
| apiVersion: mysql.presslabs.org/v1alpha1 | |
| kind: MysqlCluster | |
| metadata: | |
| name: mycluster-restored | |
| spec: | |
| mysqlVersion: "5.7" | |
| replicas: 1 | |
| secretName: mycluster-secret | |
| initBucketURL: s3://<S3_BUCKET_NAME>/mycluster-backup.xbackup.gz | |
| initBucketSecretName: mycluster-backup-secret |
Monitoring & Visualization
MYSQL Operator comes along with built-in monitoring & visualization with the help of Orchestrator and mysqld-exporter service.
Orchestrator
Orchestrator is developed by Github, which allows to provides a replication topology control & high availability and bird-eyes view of the whole MYSQL Cluster farm.
In order to access the Orchestrator we can port-forward the service
kubectl port-forward svc/mysql-operator -n mysql-operator 3003:80
mysqld-exporter
Each MYSQL Cluster which is provisioned, have mysqld-exporter pod which captures metrics/information about individual clusters. This service can be used by Prometheus to scrape the metrics & provide valuable insight about the database.
We need to define a service which exposes the mysqld-exporter pod which runs on port 9125
| apiVersion: v1 | |
| kind: Service | |
| metadata: | |
| name: mycluster-exporter-svc | |
| spec: | |
| ports: | |
| - name: prometheus | |
| port: 9125 | |
| protocol: TCP | |
| targetPort: 9125 | |
| selector: | |
| app.kubernetes.io/managed-by: mysql.presslabs.org | |
| app.kubernetes.io/name: mysql | |
| mysql.presslabs.org/cluster: mycluster | |
| type: ClusterIP |
In order to access the mysqld-exporter, lets port-forward the service.
kubectl port-forward svc/mycluster-exporter-svc 9125:9125
Final Notes
Although, we have learned to provision, scale,monitor mysql instances making production databases run on Kubernetes, the most important thing to look forward is the underlying storage for Persistent Volume as there are a couple of Storage Backends such as EFS, NFS that support RWX, but often don’t work well.We can certainly opt for EBS, Azure disk, local disk although we can still run into problems like for EBS ReadWriteMany isnt a supported. In that case we can prefer options such as Ceph, GlusterFS, Rook which are production ready but have complex side for setup & maintenance.
“if you found this article useful, feel free show some ❤️ click on ❤️ many times or share it with your friends.”
Top comments (0)