---
# Achieving end-to-end Database management with PERCONA XtraDB cluster
**URL:** https://www.sigmoid.com/blogs/achieving-end-to-end-database-management-with-percona-xtradb-cluster/
Date: 2023-05-13
Author: Sigmoid
Post Type: post
Summary: Why Percona XtraDb Cluster on Kubernetes? Databases on kubernetes are generally associated with several complexities and DataOps engineers are always on the...Read More...
Categories: Data Management
Featured Image: https://www.sigmoid.com/wp-content/uploads/2023/05/PERCONA-1.jpg
---
## Why Percona XtraDb Cluster on Kubernetes?
Databases on kubernetes are generally associated with several complexities and DataOps engineers are always on the look out for optimal solutions. Percona offers enterprise-grade database solutions, multiple replication, migration and support with the added advantage of being an open-source technology.
We leveraged Percona in our architectural model and this not only overcomes the issue of data losses with large data transactions working on the base of Mysql cluster solutions but also provides a high availability solution.
In this blog we are going to show an easy set up process to create a clustering solution using percona with K8s, Rancher and other open source tools. Whether it is fail-safe conditions,backup-issues or monitoring dashboards this set up provides enhanced results to optimize your cloud or on-prem environment.
### Pre requisites:
Kubernetes cluster set up
- **[https://ranchermanager.docs.rancher.com/how-to-guides/new-user-guides/kubernetes-cluster-setup/rke2-for-rancher](https://ranchermanager.docs.rancher.com/how-to-guides/new-user-guides/kubernetes-cluster-setup/rke2-for-rancher)**
Helm set up
- [**https://helm.sh/docs/intro/install/**](https://helm.sh/docs/intro/install/)
Kubectl set up
- [**https://kubernetes.io/docs/tasks/tools/install-kubectl-linux/**](https://kubernetes.io/docs/tasks/tools/install-kubectl-linux/)
### Dive Into:
- Run Kubectl get no (min 3 nodes cluster)
### Set up Percoa-operator
We are using Percona Operator for MySQL based on Percona XtraDB Cluster
It is a Kubernetes operator to automatically manage failover scenarios.
Your app shouldn't know anything about clustering, because it's sorted out transparently under kubernetes-service-hostname:3306. So the app calls this address and behind it there are 3x SQLProxy/HAProxy containers (per server). Then the query is routed to one of three MySQL containers.
When the server goes down, failed SQLProxy/HAProxy and MySQL containers are removed from Kubernetes so kubernetes-service-hostname contains two instead of three members.
When the server is back online, containers are created to have a full cluster again.
There is also a Percona operator container which automatically helps to manage pods and do other actions so the cluster is fully operating.
In terms of storage, it can be just a hostPath local directory which shows a sign of simplicity from a storage perspective. You could also use PersistentVolumeClaim and any type of Storage Class behind it or external storage such as NFS.
Create namespace for percona in you cluster
```
Kubectl create namespace percona-sql
```
Add Percona repository via Helm
```
helm repo add percona https://percona.github.io/percona-
helm-charts/
helm repo update
```
Percona operators
```
helm upgrade --install pxc-operator percona/pxc-operator --
namespace percona-sql
```
## Set up percona-sql on rke2
Create pv and storage class and pass storage class in values.yaml
```
Kubectl apply -f storagClass.yaml -n percona-sql
kind: StorageClass
apiVersion: storage.k8s.io/v1
metadata:
name: local-storage
provisioner: kubernetes.io/no-provisioner
volumeBindingMode: WaitForFirstConsumer
```
Create PV,PVC as per no of pods.
```
Kubectl apply -f percona-pv.yaml
apiVersion: v1
kind: PersistentVolume
metadata:
name: percona-pv
spec:
capacity:
storage: 10Gi
accessModes:
- ReadWriteOnce
persistentVolumeReclaimPolicy: Retain
storageClassName: local-storage-percona
local:
path: /mnt/percona/
nodeAffinity:
required:
nodeSelectorTerms:
- matchExpressions:
- key: kubernetes.io/hostname
operator: In
values:
- Node_Name
```
Install percona-sql via helm
```
helm upgrade --install pxc-db percona/pxc-db --set
haproxy.serviceType=NodePort,haproxy.replicasServiceType=No
dePort,pxc.persistence.enabled=true,pxc.persistence.storage
Class=local-
storage,pxc.persistence.accessMode=ReadWriteOnce,pxc.persis
tence.size=10Gi -n percona-sql
```
To access it via cli :
```
ROOT_PASSWORD=`kubectl -n percona-sql get secrets pxc-db -o
jsonpath="{.data.root}" | base64 --decode`
kubectl run -i --tty --rm percona-client --image=percona --
restart=Never \ -- mysql -h pxc-db-haproxy.percona-
sql.svc.cluster.local -uroot -p"$ROOT_PASSWORD"
```
## Percona Test cases: Fail-safe conditions for data loss
### 1. Pods restart
**Steps**
- kubectl delete pods -n percona-sql –all
- Kubectl get po -n percona-sql
- kubectl run -i --tty --rm percona-client -
-image=percona --restart=Never -- mysql -h pxc-db-haproxy.percona-
sql.svc.cluster.local -uroot -p"insecure-root-password"
- validate your data
**Results(Impact:Data Loss/No Data Loss)**
Impact: data Loss
(No pv attached to pvc)
### 2. pv/pvc attach detach
**Steps**
- Attach additional pv&pvc to the pxcdb and ha-proxy
- vi percona-pv.yaml
```
apiVersion: v1
kind: PersistentVolume
metadata:
name: percona-pv
spec:
capacity:
storage: 10Gi
accessModes:
- ReadWriteOnce
persistentVolumeReclaimPolicy: Retain
storageClassName: local-storage-percona
local:
path: /mnt/percona/
nodeAffinity:
required:
nodeSelectorTerms:
- matchExpressions:
- key: kubernetes.io/hostname
operator: In
values:Node_Name
-
```
-
- vi percona-pvc.yaml
```
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
name: percona-pvc
namespace: percona-sql
spec:
accessModes:
- ReadWriteOnce
resources:
requests:
storage: 10Gi
storageClassName: local-storage-percona
```
- Kubectl apply -f percona-pv.yaml -n percona-sql
Kubectl apply -f percona-pvc.yaml
-n percona-sql
- Change values.yaml and then redeploy it.
**Results(Impact:Data Loss/No Data Loss)**
Impact:No data Loss
Description: Pvc/pv retains data
### 3. Node crash/Node detach
- Each pv is attached to each node
- If one node crash due to failure other pv’s can retain data.
Impact:No data Loss if pvc is attached
### 4. Pxc cluster delete/crash
Kubectl get pxc -n percona-sql
Impact: No data Loss if external pv (disk/local storage ) is attached even after pxc operator cluster deletion if remain (claim policy should be set to retain)
## Conclusion:
We set up percona XtraDB on rke2 and tested it's fail safe conditions that generally cause the problem of data loss. Preventing initial data loss rather than having a backup and restoring it was also one of the main highlights of this approach. Feel free to reach out to us at [ www.sigmoid.com/contact-us/ ](/contact-us/) if you have any queries.
## About the author
Akansha Immanuel is a DevOps Engineer at Sigmoid. She is a tech enthusiast and a voracious reader. She believes in dynamic progression in DevOps, and has also worked on several DataOps projects for large data producers and currently exploring FinOps.
## Featured blogs
[lc_get_posts post_type="post"
posts_per_page="4" orderby="date" output_view="lc_get_posts_mycustom_view" output_number_of_columns="4"
output_wrapper_class="row" output_article_class="shadow" output_hide_elements="Excerpt"
output_excerpt_length="0" output_excerpt_text="Read More" output_heading_tag="span"
output_featured_image_format="thumbnail" output_featured_image_class="card-img-left" ]
## Share
[addtoany]
## Subscribe to get latest insights
## Talk to our experts
Get the best ROI with Sigmoid’s services in data engineering and AI
## Suggested readings
[View all](/blogs/)
#### [Apache Spark on DataProc vs Google BigQuery](/blogs/apache-spark-on-dataproc-vs-google-bigquery/)
[Read blog](/blogs/apache-spark-on-dataproc-vs-google-bigquery/)
#### [How to Optimize Nested Queries using Apache Spark](/blogs/optimize-nested-queries-using-apache-spark/)
[Read blog](/blogs/optimize-nested-queries-using-apache-spark/)
#### [ETL on Cloud: How is cloud Transforming ETL for Big Data Analytics](/blogs/etl-on-cloud-transforming-big-data-analytics/)
[Read blog](/blogs/etl-on-cloud-transforming-big-data-analytics/)
---
## Categories
- Data Management
---
## Navigation
- [Company](/about-sigmoid)
- [Newsroom](/newsroom)
- [Life at Sigmoid](/careers)
- [Takshashila](/takshashila)
- [Contact Us](/contact-us)
- [AI Strategy Blueprint your AI advantage](/enterprise-ai-strategy/)
- [Generative AI Drive innovation with Generative AI](/generative-ai/)
- [Responsible AI Build trust with ethical AI practices](/responsible-ai-in-enterprise/)
- [Agentic AI Reshape business with scalable agentic systems](/agentic-ai-solutions/)
- [AI Managed Services Ensure reliable AI performance](/ai-managed-services/)
- [Advanced Analytics Transform your business with data-driven insights](/advanced-data-analytics-solutions/)
- [Start Assessment](/agentic-ai-readiness-index/)
- [Data Strategy Strong data foundations for scalable AI](/data-analytics-strategy/)
- [Data Management Leverage data as a strategic asset](/ai-data-management-services/)
- [Data Ops Automate data for speed and quality](/data-devops/)
- [Data Engineering Deliver insights faster with scalable pipelines](/data-engineering/)
- [Cloud Transformation Modernize data to maximise efficiency](/cloud-migration/)
- [Download Whitepaper](/ebooks-whitepapers/building-data-products-in-a-data-mesh-to-drive-business-value/)
- [Data Modeling Structure data for better decisions](/data-modeling-services/)
- [Data Visualization Transform data into actionable stories](/data-visualization-service/)
- [BI Migration Enhance decision making with modern BI tools](/bi-migration/)
- [Data Observability Build trust with healthy, accurate data](/data-observability/)
- [Automated Insights Make smarter decisions with auto-generated insights](/automated-insights/)
- [Download Whitepaper](/ebooks-whitepapers/power-bi-hacks/)
- [CPG & Retail End-to-end analytics for planning, operations, and commercial excellence](/industries/cpg-analytics/)
- [Life Sciences Trusted intelligence across clinical, commercial, and operational workflows](/industries/life-sciences/)
- [Financial Services AI-powered analytics for risk, compliance and customer experience](/industries/banking-financial-analytics-services/)
- [Read case study](/case-studies/data-clean-room-enables-real-time-insights-to-improve-operational-efficiency/)
- [MediaIQ Advanced platform for in-flight marketing measurement](/accelerators/sigmoid-mediaiq-multi-touch-attribution-tool/)
- [CampaignIQ AI-driven platform for optimized campaign budget allocation](/accelerators/sigmoid-campaigniq/)
- [AssistBot GenAI email assistant that automates human-like responses](/accelerators/sigmoid-assistbot-for-ai-email-assistant/)
- [CreativeBot GenAI tool for personalized and brand-aligned creative design](/accelerators/sigmoid-creativebot/)
- [SocialBot GenAI platform to analyze digital conversations and trends](/accelerators/#marketing|socialbot)
- [DemandIQ Predict trends accurately and optimize inventory management](/accelerators/sigmoid-demandiq/)
- [NetworkIQ Track and optimize logistics operations in real-time to quickly address disruptions](/accelerators/sigmoid-networkiq/)
- [SupplyIQ End-to-end platform to optimize supply chain operations](/accelerators/sigmoid-supplyiq/)
- [ProcurementIQ Automated procurement operations for maximum savings, compliance and efficiency](/accelerators/sigmoid-procurementiq/)
- [RapidML Accelerated deployment for machine learning models](/accelerators/sigmoid-rapidml/)
- [DataGuard Comprehensive platform for proactive data quality management](/accelerators/data-quality-tool-sigmoid-dataguard/)
- [CloudPulse Cloud cost optimization platform with multi-cloud management](/accelerators/sigmoid-cloudpulse/)
- [RAPID GenAI foundation with built-in governance and cost clarity](/accelerators/sigmoid-rapid/)
- [AnalyticsBot GenAI based platform to streamline decision-making in analytics](/accelerators/sigmoid-analyticsbot/)
- [DataConnect Seamlessly ingest, integrate and harmonize data from diverse sources](/accelerators/sigmoid-dataconnect/)
- [Reconica AI-powered data harmonization and reconciliation engine](/accelerators/sigmoid-reconica/)
- [ConverseBot GenAI driven insights generation for automated insights from reports](/accelerators/#sales|conversebot)
- [iNRM Cross-lever revenue growth optimization platform](/accelerators/sigmoid-inrm/)
- [AssortmentIQ Optimize shelf layouts and assortment mix at scale with AI-based insights](/accelerators/sigmoid-assortmentiq/)
- [Read Whitepaper](/ebooks-whitepapers/building-agentic-ai-chatbots-for-business-process-transformation/)
- [Listen Podcast](/events/podcast/how-jack-in-the-box-is-redefining-personalization-and-supply-chain-with-ai/)
- [Blogs](/blogs/)
- [White Papers](/ebooks-whitepapers/)
- [Case Studies](/case-studies/)
- [Podcast](/events/podcast/#Podcasts)
- [Read Blog](/blogs/the-genai-adoption-triad-responsibility-ethics-and-explainability/)
- [ConverseBot](/accelerators/#sales|conversebot/)
---
## Footer Links
- [Talk to our AI experts](/contact-us/)
- [AI Strategy](/enterprise-ai-strategy/)
- [Agentic AI](/agentic-ai-solutions/)
- [Generative AI](/generative-ai/)
- [AI Managed Services](/ai-managed-services/)
- [Responsible AI](/responsible-ai-in-enterprise/)
- [Advanced Analytics](/advanced-data-analytics-solutions/)
- [Data Strategy](/data-analytics-strategy//)
- [Data Engineering](/data-engineering/)
- [Data Management](/ai-data-management-services/)
- [Cloud Transformation](/cloud-transformation/)
- [Data Ops](/data-devops/)
- [Data Visualization](/data-visualization-service/)
- [Automated Insights](/automated-insights/)
- [BI Migration](/bi-migration/)
- [Data Modeling](/data-modeling-services/)
- [Data Observability](/data-observability/)
- [CPG & Retail](/industries/cpg-analytics/)
- [Financial Services](/industries/banking-financial-analytics-services/)
- [Life Sciences](/industries/life-sciences/)
- [Case Studies](/case-studies/)
- [Thought Leadership](/ebooks-whitepapers/)
- [Blogs](/blogs/)
- [Company](/about-sigmoid/)
- [Newsroom](/newsroom/)
- [Accelerators](/accelerators/)
- [Careers](/careers/)
- [Privacy Policy |](/privacy-policy/)
- [Cookie Policy](/cookie-policy/)