Setting up and testing MySQL master-slave setup on K8S platform(En)

The previous article explained how to set up a basic standalone MySQL on Kubernetes and integrate phpMyAdmin (PMA) for graphical management. This article takes it a step further by implementing MySQL replication architecture (master-slave) and verifying its success.

Typically, in production environments, applications like these wouldn’t solely rely on a standalone setup. At the very least, they would utilize MySQL replication or MySQL cluster architecture as mentioned in this article to ensure data safety or high availability.

This article will be divided into the following sections for explanation:

1. What is MySQL replication?
2. Deploying MySQL replication (master-slave)
3. Function verification
4. Conclusion

1. What is MySQL replication?

Here is a simple explanation of MySQL replication. Essentially, it involves synchronizing and backing up data across multiple nodes to achieve high availability.

This approach offers the following advantages:

1. Scalability: Distributing the load across multiple slaves enhances performance. For applications with high read/write demands, replication can increase processing capacity by adding more slave nodes. Since writes are only submitted on the master, the performance improvement for high write demands may not be as significant.

2. Data Security: Slaves can interrupt their replication process without affecting the master, allowing them to perform backup tasks independently. Performing the same action on the master would require changing the master status to readonly, which could impact service availability.

3. Analysis: Data is written on the master but can be analyzed on the slave without disrupting service operations. Typically, when using MySQL for data analysis tasks, the slave is utilized as the data access point.

4. Redundancy: Placing slave nodes closer to locations with high read demands can increase data access efficiency, eliminating the need to continuously request data from a remote master node. This provides significant benefits for disaster recovery.

※ Here are three commonly used architectures, briefly explained as following:

1. Master-slave: This is the most commonly used architecture in practice, primarily aimed at addressing high read pressure in a cost-effective manner. Typically, the delay between master and slave is not significant, and efficiency in handling read demands can be increased by adding more slaves.

2. Master-master: To reduce downtime caused by maintenance on a single master node, a dual-master architecture can be established. In practice, this involves two MySQL servers treating each other as their own master while acting as slaves themselves.

3. Master-slave-slave: This architecture is employed when there is exceptionally high read pressure. It reduces the pressure on the master, which has to handle both requests and data replication to slaves, by implementing multi-level replication. In this setup, slaves are responsible for replicating data to the next level of slaves.

2. Deploying MySQL replication (master-slave)

#-----------------------------------------
# S2-1. Add helm source
#-----------------------------------------
[master]# helm repo add bitnami https://charts.bitnami.com/bitnami
[master]# helm search repo bitnami/mysql -l
[master]# helm3 pull bitnami/mysql  --version 9.3.4 --untar
[master]# ls mysql

#-----------------------------------------
# S2-2. Edit value.yaml
#-----------------------------------------
[master]# vim values.ysml
...
#Change architecture
architecture: replication

#DB accounts(root,user,replicastion user)、password & DB
auth:
  rootPassword: ""
  createDatabase: true
  database: "testapp"
  username: "app"
  password: ""
  replicationUser: replicator
  replicationPassword: ""
  
primary:
  persistence:
    enabled: true
    storageClass: "managed-nfs-storage"
    accessModes:
      - ReadWriteOnce
    size: 8Gi
  service:
    type: NodePort
    ports:
      mysql: 3306
    nodePorts:
      mysql: "31006"
      
secondary:
  persistence:
    enabled: true
    storageClass: "managed-nfs-storage"
    accessModes:
      - ReadWriteOnce
    size: 8Gi
  service:
    type: NodePort
    ports:
      mysql: 3306
    nodePorts:
      mysql: "31005"
metrics:
  enabled: true

#-----------------------------------------
# S2-3. Upload image to Harbor (optional)
#-----------------------------------------
[master]# docker pull bitnami/mysql:8.0.36-debian-12-r8
[master]# docker tag bitnami/mysql:8.0.36-debian-12-r8 harbor1.test.example.poc/mysql/mysql:8.0.36-debian-12-r8
[master]# docker push harbor1.test.example.poc/mysql/mysql:8.0.36-debian-12-r8

[master]# docker pull bitnami/mysqld-exporter:0.15.1-debian-12-r8
[master]# docker tag bitnami/mysqld-exporter:0.15.1-debian-12-r8 harbor1.test.example.poc/mysql/mysqld-exporter:0.15.1-debian-12-r8
[master]# docker push harbor1.test.example.poc/mysql/mysqld-exporter:0.15.1-debian-12-r8

#-----------------------------------------
# S2-4. Deploy
#-----------------------------------------
[master]# helm install mysql ./mysql --namespace mysql-replication --create-namespace
[master]# helm list -n mysql-replication
[master]# kubectl get sts,pod -n mysql-replication -l app.kubernetes.io/name=mysql

3. Function verification

#-----------------------------------------
# S3-1. Obtain MySQL root, app, replication password
#-----------------------------------------
[master]# echo -n "MYSQL_ROOT_PASSWORD=";kubectl get secret --namespace mysql-replication mysql -o jsonpath="{.data.mysql-root-password}" | base64 -d;echo
MYSQL_ROOT_PASSWORD=FLSDMzpjO3

[master]# echo -n "MYSQL_PASSWORD=";kubectl get secret --namespace mysql-replication mysql -o jsonpath="{.data.mysql-password}" | base64 -d;echo
MYSQL_PASSWORD=gAV87AooJk

[master]# echo -n "MYSQL_REPLICATION_PASSWORD=";kubectl get secret --namespace mysql-replication  mysql -o jsonpath="{.data.mysql-replication-password}" | base64 -d;echo
MYSQL_REPLICATION_PASSWORD=U2pYaNkDO2

#-----------------------------------------
# S3-2. Using PMA to read and write from the master service, 
# verifying if the slave node is readable
#-----------------------------------------
CREATE TABLE replication (
  id int(11) NOT NULL  PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255)  NOT NULL,
  INDEX name_in (name)
);

INSERT INTO testapp.replication(name) VALUES('Albert')

CREATE DATABASE testdev;
CREATE USER 'testdev'@'%' IDENTIFIED BY 'testdev.Albert.top';  
GRANT ALL ON dev.* TO 'testdev'@"%";
FLUSH PRIVILEGES;

#-----------------------------------------
# S3-3. Query inserted data
#-----------------------------------------
[master]# kubectl run mysql-client --rm --tty -i --restart='Never' --image  harbor1.test.example.poc/mysql/mysql:8.0.36-debian-12-r8 --namespace mysql-replication --env MYSQL_ROOT_PASSWORD=FLSDMzpjO3 --command -- bash
(connect to secondary service(read))

1001@mysql-client:/$ mysql -h mysql-secondary -u app -p
mysql> status
mysql> select * from testapp.replication

4. Conclusion

This article explained MySQL replication and implemented the master-slave mechanism on the Kubernetes platform. This approach leverages the benefits of master-slave architecture while adding an additional layer of value through native Kubernetes mechanisms. As more applications migrate to container platforms in the future, this architecture can serve as a deployment reference.

That’s it for this share. See you next time!