mysql主主备份

Ethereal Lv4
  2024-09-26 22:51:31 2024-09-26 22:51 Created   2024-09-26 22:58:46 2024-09-26 22:58 Updated  

1. 配置文件

configMap.yaml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
apiVersion: v1
kind: ConfigMap
metadata:
  name: {{ include "ips-db.fullname" . }}-configmap
data:
  master.cnf: |
    # Master配置
    [mysqld]
    log-bin=mysqllog
    skip-name-resolve
    replicate-ignore-db=mysql
    replicate-ignore-db = information_schema
    replicate-ignore-db = performance_schema
    replicate-ignore-db = sys
    auto_increment_increment={{ .Values.replicaCount }}
  slave.cnf: |
    # Slave配置
    [mysqld]
    super-read-only
    skip-name-resolve
    log-bin=mysql-bin
    replicate-ignore-db=mysql
    replicate-ignore-db = information_schema
    replicate-ignore-db = performance_schema
    replicate-ignore-db = sys

service.yaml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
apiVersion: v1
kind: Service
metadata:
  name: {{ include "ips-db.fullname" . }}
  labels:
    {{- include "ips-db.labels" . | nindent 4 }}
spec:
  type: {{ .Values.service.type }}
  ports:
    - port: {{ .Values.service.port }}
      targetPort: http
      protocol: TCP
      name: http
  selector:
    {{- include "ips-db.selectorLabels" . | nindent 4 }}

statefulset.yaml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: {{ include "ips-db.fullname" . }}
  labels:
    {{- include "ips-db.labels" . | nindent 4 }}
spec:
  serviceName: {{ include "ips-db.fullname" . }}
  {{- if not .Values.autoscaling.enabled }}
  replicas: {{ .Values.replicaCount }}
  {{- end }}
  selector:
    matchLabels:
      {{- include "ips-db.selectorLabels" . | nindent 6 }}
  template:
    metadata:
      {{- with .Values.podAnnotations }}
      annotations:
        {{- toYaml . | nindent 8 }}
      {{- end }}
      labels:
        {{- include "ips-db.labels" . | nindent 8 }}
        {{- with .Values.podLabels }}
        {{- toYaml . | nindent 8 }}
        {{- end }}
    spec:
      initContainers:
      - name: init-mysql
        image: "{{ .Values.image.repository }}:{{ .Values.image.tag | default .Chart.AppVersion }}"
        env:
          - name: MYSQL_ROOT_PASSWORD
            value: "c0fe91d7d275420d93bffda45990dfdb"
        command:
        - bash
        - "-c"
        - |
          set -ex
          # 从Pod的序号，生成server-id
          hostname=$(cat /etc/hostname)
          [[ $hostname =~ -([0-9]+)$ ]] || exit 1
          ordinal=${BASH_REMATCH[1]}
          echo [mysqld] > /mnt/conf.d/server-id.cnf
          # 由于server-id不能为0，因此给ID加100来避开它
          echo server-id=$((100 + $ordinal)) >> /mnt/conf.d/server-id.cnf
          # 如果Pod的序号为0,1,2，说明它是Master节点，从ConfigMap里把Master的配置文件拷贝到/mnt/conf.d目录下
          # 否则，拷贝ConfigMap里的Slave的配置文件
          if [[ ${ordinal} -lt {{ .Values.replicaCount }} ]]; then
            cp /mnt/config-map/master.cnf /mnt/conf.d
            offsetid=$(($ordinal + 1))
            echo "auto_increment_offset=${offsetid}" >> /mnt/conf.d/master.cnf
          else
            cp /mnt/config-map/slave.cnf /mnt/conf.d
          fi
        volumeMounts:
        - name: conf
          mountPath: /mnt/conf.d
        - name: config-map
          mountPath: /mnt/config-map
      containers:
      - name: {{ .Chart.Name }}
        image: "{{ .Values.image.repository }}:{{ .Values.image.tag | default .Chart.AppVersion }}"
        securityContext:
          {{- toYaml .Values.securityContext | nindent 12 }}
        imagePullPolicy: {{ .Values.image.pullPolicy }}
        env:
        {{- if not .Values.dbFirstCreate }}
        - name: DATABASE_ALREADY_EXISTS
          value: "1"
        {{- end }}
        - name: MYSQL_ROOT_PASSWORD
          value: "c0fe91d7d275420d93bffda45990dfdb"
        - name: MYSQL_DATABASE
          value: "mstor"
        livenessProbe:
          {{- toYaml .Values.livenessProbe | nindent 12 }}
        readinessProbe:
          {{- toYaml .Values.readinessProbe | nindent 12 }}
        resources:
          {{- toYaml .Values.resources | nindent 12 }}
        ports:
        - name: http
          containerPort: {{ .Values.service.port }}
          protocol: TCP
        volumeMounts:
        - name: data
          mountPath: /var/lib/mysql
          subPath: mysql
        - name: conf
          mountPath: /etc/mysql/conf.d
      - name: sync
        image: "{{ .Values.image.repository }}:{{ .Values.image.tag | default .Chart.AppVersion }}"
        env:
          - name: MYSQL_ROOT_PASSWORD
            value: "c0fe91d7d275420d93bffda45990dfdb"
        command:
          - bash
          - "-c"
          - |
            set -ex
            hostname=$(cat /etc/hostname)
            [[ $hostname =~ -([0-9]+)$ ]] || exit 1
            ordinal=${BASH_REMATCH[1]}

            nextid=$((($ordinal + 1) % {{ .Values.replicaCount }}))

            MASTER_HOST={{ include "ips-db.fullname" . }}-${nextid}.{{ include "ips-db.fullname" . }}

            cd /var/lib/mysql
            echo "Waiting for mysqld to be ready（accepting connections）"
            until mysql -uroot -p${MYSQL_ROOT_PASSWORD} -h127.0.0.1 -e "SELECT 1"; do sleep 1; done
            until mysql -uroot -p${MYSQL_ROOT_PASSWORD} -h${MASTER_HOST} -e "SELECT 1"; do sleep 1; done

            result=$(mysql -uroot -p${MYSQL_ROOT_PASSWORD} -h127.0.0.1 -e "SHOW REPLICA STATUS\G")
            if [[ $result == *"Replica_IO_Running: Yes"* && $result == *"Replica_SQL_Running: Yes"* ]]; then
              echo "recover START SLAVE "
            else
              mysql -uroot -p${MYSQL_ROOT_PASSWORD} -h127.0.0.1 -e "CHANGE REPLICATION SOURCE TO SOURCE_HOST='${MASTER_HOST}',SOURCE_USER='root',SOURCE_PASSWORD='${MYSQL_ROOT_PASSWORD}',SOURCE_CONNECT_RETRY=10;START REPLICA ;"
            fi
            # 这个是为了hold这个容器的进程，如果不hold住的话，pod会重启
            # 当然这里可以使用更优雅的方式，比如TTL等，这里不做探讨
            while true; do sleep 3600; done
        volumeMounts:
        - name: data
          mountPath: /var/lib/mysql
          subPath: mysql
        - name: conf
          mountPath: /etc/mysql/conf.d
      volumes:
      - name: conf
        emptyDir: {}
      - name: config-map
        configMap:
          name: {{ include "ips-db.fullname" . }}-configmap
  volumeClaimTemplates:
  - metadata:
      name: data
    spec:
      accessModes:
      - ReadWriteMany
      storageClassName: "local"
      resources:
        requests:
          storage: 10000Gi

values.yaml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
# Default values for ips-db.
# This is a YAML-formatted file.
# Declare variables to be passed into your templates.

replicaCount: 3

image:
  repository: 124.70.110.25:30002/docker-io/library/mysql
  pullPolicy: IfNotPresent
  # Overrides the image tag whose default is the chart appVersion.
  tag: ""

imagePullSecrets: []
nameOverride: ""
fullnameOverride: ""

dbFirstCreate: true

serviceAccount:
  # Specifies whether a service account should be created
  create: false
  # Automatically mount a ServiceAccount's API credentials?
  automount: true
  # Annotations to add to the service account
  annotations: {}
  # The name of the service account to use.
  # If not set and create is true, a name is generated using the fullname template
  name: ""

podAnnotations: {}
podLabels: {}

podSecurityContext: {}
  # fsGroup: 2000

securityContext: {}
  # capabilities:
  #   drop:
  #   - ALL
  # readOnlyRootFilesystem: true
  # runAsNonRoot: true
  # runAsUser: 1000

service:
  type: NodePort
  port: 3306

ingress:
  enabled: false
  className: ""
  annotations: {}
    # kubernetes.io/ingress.class: nginx
    # kubernetes.io/tls-acme: "true"
  hosts:
    - host: chart-example.local
      paths:
        - path: /
          pathType: ImplementationSpecific
  tls: []
  #  - secretName: chart-example-tls
  #    hosts:
  #      - chart-example.local

resources: {}
  # We usually recommend not to specify default resources and to leave this as a conscious
  # choice for the user. This also increases chances charts run on environments with little
  # resources, such as Minikube. If you do want to specify resources, uncomment the following
  # lines, adjust them as necessary, and remove the curly braces after 'resources:'.
  # limits:
  #   cpu: 100m
  #   memory: 128Mi
  # requests:
  #   cpu: 100m
  #   memory: 128Mi

livenessProbe:
  exec:
    command: ["mysqladmin", "ping", "-h", "localhost", "-u", "root", "-pc0fe91d7d275420d93bffda45990dfdb"]
  initialDelaySeconds: 30
  periodSeconds: 10

readinessProbe:
  exec:
    command: ["mysqladmin", "ping", "-h", "localhost", "-u", "root", "-pc0fe91d7d275420d93bffda45990dfdb"]
  initialDelaySeconds: 10
  periodSeconds: 10

autoscaling:
  enabled: false
  minReplicas: 1
  maxReplicas: 100
  targetCPUUtilizationPercentage: 80
  # targetMemoryUtilizationPercentage: 80

# Additional volumes on the output Deployment definition.
volumes: 
- name: mysql-persistent-storage
  persistentVolumeClaim:
    claimName: stor-manager-pvc

# Additional volumeMounts on the output Deployment definition.
volumeMounts: 
- name: mysql-persistent-storage
  mountPath: /var/lib/mysql
  subPath: mysql

nodeSelector: {}

tolerations: []

affinity: {}

pv

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
apiVersion: v1
kind: PersistentVolume
metadata:
  namespace: dev-ips-server
  name: db-2
spec:
  capacity:
    storage: 10000Gi
  volumeMode: Filesystem
  # PersistentVolume
  accessModes:
  - ReadWriteMany
  persistentVolumeReclaimPolicy: Retain
  storageClassName: "local"
  hostPath:
    path: /zion0/svcdata/k8s/stor-manager-ips-server/db-2
    type: DirectoryOrCreate

2. 部署命令

1
2
3
4
kubectl apply -f ./ips-pv/f002_dashboard_pvc.yml
helm install ips-db ips-db/ -n dev-ips-server
kubectl wait --for=condition=ready pod -l app.kubernetes.io/name=ips-db -n dev-ips-server --timeout=300s
# kubectl rollout restart deployment ips-server -n dev-ips-server

3. 删除命令

1
helm uninstall ips-db -n dev-ips-server

4. 参考

【k8s】k8s构建mysql双主集群 - 周周周文阳 - 博客园 (cnblogs.com)

Mysql设置auto_increment_increment和auto_increment_offset_auto-increment-increment-CSDN博客

Kubernetes StatefulSet之volumeClaimTemplates-CSDN博客

mysql8.4:搭建主从复制(mysql 8.4.1) - 刘宏缔的架构森林 - 博客园 (cnblogs.com)

MySQL命令 SHOW MASTER STATUS 及 CHANGE MASTER TO 命令详解_show master status;-CSDN博客

多云多集群K8S管理平台 | 容器云PaaS | 容器混合云解决方案 | Rancher

  • Title: mysql主主备份
  • Author: Ethereal
  • Created at: 2024-09-26 22:51:31
  • Updated at: 2024-09-26 22:58:46
  • Link: https://ethereal-o.github.io/2024/09/26/mysql主主备份/
  • License: This work is licensed under CC BY-NC-SA 4.0.
 Comments
On this page
mysql主主备份
  1. 1. 配置文件
  2. 2. 部署命令
  3. 3. 删除命令
  4. 4. 参考