Helm deploys mysql k8s master-slave cluster

Helm is the package manager of k8s. Using helm deployment can simplify the Yaml files that need to be written during the deployment process. This article uses bitnami’s helm warehouse to deploy mysql one-master and two-slave clusters.

Prerequisites: a harbor docker private server, a k8s cluster, and an available storage class

Add the bitnami repository and find

helm repo add bitnami https://charts.bitnami.com/bitnami
helm repo update
?
[kmning@k8s-register-node ~]$ helm search repo mysql
NAME CHART VERSION APP VERSION DESCRIPTION
bitnami/mysql 9.8.1 8.0.33 MySQL is a fast, reliable, scalable, and easy t...
bitnami/phpmyadmin 11.0.0 5.2.1 phpMyAdmin is a free software tool written in P...
bitnami/mariadb 12.1.2 10.11.2 MariaDB is an open source, community-developed... ?
bitnami/mariadb-galera 8.1.1 10.11.2 MariaDB Galera is a multi-primary database clus...

Choose the right version

software	version
chart	9.8.1
mysql	5.7.26

Pull chat to local

helm pull bitnami/mysql --version 9.8.1
?
tar -zxvf mysql-9.8.1.tgz
cp mysql/values.yaml ./values-mysql.yaml

Modify the mirror address and other configurations

global:
  imageRegistry: "k8s-register-node.com:443"
  imagePullSecrets: []
  storageClass: "managed-nfs-storage"
  
#All places where images are used are changed to private servers
metrics:
  enabled: false
  image:
    registry: k8s-register-node.com:443
    repository: lib-proxy/bitnami/mysqld-exporter
    tag: 0.14.0-debian-11-r112

mysql version setting

image:
  registry: k8s-register-node.com:443
  repository: lib-proxy/bitnami/mysql
  tag: 5.7.42-debian-11-r5

Account password and enable master-slave mode

architecture: replication
auth:
  rootPassword: "rootpwd"
  createDatabase: true
  database: "my_database"
  username: "kmning"
  password: "kmningpwd"
  replicationUser: kmning
  replicationPassword: "kmningpwd"

master node

primary:
  name: primary
  command: []
  args: []
  lifecycleHooks: {}
  hostAliases: []
  configuration: |-
    [mysqld]
    default_authentication_plugin=mysql_native_password
    skip-name-resolve
    explicit_defaults_for_timestamp
    basedir=/opt/bitnami/mysql
    plugin_dir=/opt/bitnami/mysql/lib/plugin
    port=3306
    socket=/opt/bitnami/mysql/tmp/mysql.sock
    datadir=/bitnami/mysql/data
    tmpdir=/opt/bitnami/mysql/tmp
    max_allowed_packet=16M
    bind-address=*
    pid-file=/opt/bitnami/mysql/tmp/mysqld.pid
    log-error=/opt/bitnami/mysql/logs/mysqld.log
    character-set-server=UTF8
    collation-server=utf8_general_ci
    slow_query_log=0
    slow_query_log_file=/opt/bitnami/mysql/logs/mysqld.log
    long_query_time=10.0

    [client]
    port=3306
    socket=/opt/bitnami/mysql/tmp/mysql.sock
    default-character-set=UTF8
    plugin_dir=/opt/bitnami/mysql/lib/plugin

    [manager]
    port=3306
    socket=/opt/bitnami/mysql/tmp/mysql.sock
    pid-file=/opt/bitnami/mysql/tmp/mysqld.pid
  existingConfigmap: ""
  updateStrategy:
    type: RollingUpdate
  persistence:
    enabled: true
    existingClaim: ""
    subPath: ""
    storageClass: "managed-nfs-storage"
    annotations: {}
    accessModes:
      - ReadWriteOnce
    size: 200Gi
    selector: {}
  extraVolumes: []

slave node

secondary:
  name: secondary
  replicaCount: 2
  hostAliases: []
  command: []
  args: []
  lifecycleHooks: {}
  configuration: |-
    [mysqld]
    default_authentication_plugin=mysql_native_password
    skip-name-resolve
    explicit_defaults_for_timestamp
    basedir=/opt/bitnami/mysql
    plugin_dir=/opt/bitnami/mysql/lib/plugin
    port=3306
    socket=/opt/bitnami/mysql/tmp/mysql.sock
    datadir=/bitnami/mysql/data
    tmpdir=/opt/bitnami/mysql/tmp
    max_allowed_packet=16M
    bind-address=*
    pid-file=/opt/bitnami/mysql/tmp/mysqld.pid
    log-error=/opt/bitnami/mysql/logs/mysqld.log
    character-set-server=UTF8
    collation-server=utf8_general_ci
    slow_query_log=0
    slow_query_log_file=/opt/bitnami/mysql/logs/mysqld.log
    long_query_time=10.0

    [client]
    port=3306
    socket=/opt/bitnami/mysql/tmp/mysql.sock
    default-character-set=UTF8
    plugin_dir=/opt/bitnami/mysql/lib/plugin

    [manager]
    port=3306
    socket=/opt/bitnami/mysql/tmp/mysql.sock
    pid-file=/opt/bitnami/mysql/tmp/mysqld.pid
  existingConfigmap: ""
  persistence:
    enabled: true
    existingClaim: ""
    subPath: ""
    storageClass: "managed-nfs-storage"
    annotations: {}
    accessModes:
      - ReadWriteOnce
    size: 8Gi
    selector: {}
  extraVolumes: []

helm install redis cluster

kubectl create ns mysql
helm -n mysql install mysql-cluster mysql-9.8.1.tgz -f values-mysql.yaml \
--set useBundledSystemChart=true

Print after installation

kmning@k8s-master-1:~/mysql-k8s-cluster$ helm -n mysql install mysql-cluster mysql-9.8.1.tgz -f values-mysql.yaml \
> --set useBundledSystemChart=true
NAME: mysql-cluster
LAST DEPLOYED: Thu May 4 05:48:32 2023
NAMESPACE: mysql
STATUS: deployed
REVISION: 1
TEST SUITE: None
NOTES:
CHART NAME: mysql
CHART VERSION: 9.8.1
APP VERSION: 8.0.33

** Please be patient while the chart is being deployed **

Tip:

  Watch the deployment status using the command: kubectl get pods -w --namespace mysql

Services:

  echo Primary: mysql-cluster-primary.mysql.svc.cluster.local:3306
  echo Secondary: mysql-cluster-secondary.mysql.svc.cluster.local:3306

Execute the following to get the administrator credentials:

  echo Username: root
  MYSQL_ROOT_PASSWORD=$(kubectl get secret --namespace mysql mysql-cluster -o jsonpath="{.data.mysql-root-password}" | base64 -d)

To connect to your database:

  1. Run a pod that you can use as a client:

      kubectl run mysql-cluster-client --rm --tty -i --restart='Never' --image k8s-register-node.com:443/lib-proxy/bitnami/mysql:5.7.42-debian-11 -r5 --namespace mysql --env MYSQL_ROOT_PASSWORD=$MYSQL_ROOT_PASSWORD --command --bash

  2. To connect to primary service (read/write):

      mysql -h mysql-cluster-primary.mysql.svc.cluster.local -uroot -p "$MYSQL_ROOT_PASSWORD"

  3. To connect to secondary service (read-only):

      mysql -h mysql-cluster-secondary.mysql.svc.cluster.local -uroot -p "$MYSQL_ROOT_PASSWORD"

The above print prompts how to obtain the root password and how to connect to the mysql master-slave information on the k8s cluster. Obviously, using helm to deploy mysql master-slave, we avoid editing stateful set configuration files, Service configuration files, and even master-slave settings do not need us to deal with.

Observe whether the pods of the master node and the slave node are started correctly. If it is found that the slave node cannot be started correctly, it should be that the user does not have the remote login permission, just enter the master node and start it.

 kubectl exec -it mysql-cluster-primary-0 -n mysql -- bash
I have no name!@mysql-cluster-primary-0:/$ mysql -h mysql-cluster-primary.mysql.svc.cluster.local -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 311
Server version: 5.7.42-log MySQL Community Server (GPL)

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

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> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> GRANT ALL PRIVILEGES ON *.* TO 'kmning'@'%' IDENTIFIED BY 'kmningpwd' WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'rootpwd' WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

View the master-slave synchronization status of the slave node

kubectl exec -it mysql-cluster-secondary-0 -n mysql -- bash
I have no name!@mysql-cluster-secondary-0:/$ mysql -uroot -h mysql-cluster-secondary.mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1118
Server version: 5.7.42-log MySQL Community Server (GPL)

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

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> show slave status\G;
*************************** 1. row ********************* *****
               Slave_IO_State: Waiting for master to send event
                  Master_Host: mysql-cluster-primary
                  Master_User: kmning
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 2632
               Relay_Log_File: mysql-relay-bin.000019
                Relay_Log_Pos: 2845
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 2632
              Relay_Log_Space: 3265
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 124
                  Master_UUID: 6f965f32-ea3f-11ed-869f-2a649e004961
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

ERROR:
No query specified

It can be seen that the master and slave are normal. The application connects to mysql directly using the master node. Connect to the master node and use the service name to connect, as follows

jdbc:mysql://mysql-cluster-primary.mysql.svc.cluster.local:3306/yourdb?rewriteBatchedStatements=true &characterEncoding=UTF-8 &connectTimeout=1000 &socketTimeout=3000 &autoReconnect=true &useUnicode=true &useSSL=false &characterSetResults=UTF-8 &serverTimezone=Asia/Shanghai