k8s中实现mysql主备

一、k8s中实现mysql主备

1.1 环境信息

1.2 部署nfs-provisioner

说明：
  使用statefulSet部署双机MySQL，所以需要提供storageClass，这里使用nfs-provisioner。

1.2.1 安装nfs

这里nfs安装在node1节点上
mkdir /mnt/nfs && sh nfs_install.sh /mnt/nfs 192.168.0.0/24

nfs_install.sh

#!/bin/bash

### How to install it? ###
### 安装nfs-server，需要两个参数：1、挂载点  2、允许访问nfs-server的网段 ###

### How to use it? ###
### Client节点`yum -y install nfs-utils rpcbind`，然后挂载nfs-server目录到本地 ###
### 如：echo "192.168.0.20:/mnt/data01  /mnt/data01  nfs  defaults  0 0" >> /etc/fstab && mount -a ###

mount_point=$1
subnet=$2

function nfs_server() {
  systemctl stop firewalld
  systemctl disable firewalld
  setenforce 0
  sed -i 's/^SELINUX.*/SELINUX\=disabled/' /etc/selinux/config
  yum -y install nfs-utils rpcbind
  mkdir -p $mount_point
  echo "$mount_point ${subnet}(rw,sync,no_root_squash)" >> /etc/exports
  systemctl start rpcbind && systemctl enable rpcbind
  systemctl restart nfs-server && systemctl enable nfs-server
  chown -R nfsnobody:nfsnobody $mount_point
}

function usage() {
echo "Require 2 argument: [mount_point] [subnet]
eg: sh $0 /mnt/data01 192.168.10.0/24"
}

declare -i arg_nums
arg_nums=$#
if [ $arg_nums -eq 2 ];then
  nfs_server
else
  usage
  exit 1
fi
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

1.2.2 部署nfs-provisioner

master1节点上执行 kubectl create namespace devops && kubectl apply -f nfs-provisioner.yaml

nfs-provisioner.yaml

apiVersion: v1
kind: ServiceAccount
metadata:
  name: nfs-provisioner
  namespace: devops
---
apiVersion: rbac.authorization.k8s.io/v1
kind: ClusterRole
metadata:
  name: nfs-provisioner-runner
rules:
- apiGroups: [""]
  resources: ["persistentvolumes"]
  verbs: ["get", "list", "watch", "create", "delete"]
- apiGroups: [""]
  resources: ["persistentvolumeclaims"]
  verbs: ["get", "list", "watch", "update"]
- apiGroups: ["storage.k8s.io"]
  resources: ["storageclasses"]
  verbs: ["get", "list", "watch"]
- apiGroups: [""]
  resources: ["events"]
  verbs: ["create", "update", "patch"]
- apiGroups: [""]
  resources: ["services", "endpoints"]
  verbs: ["get"]
---
apiVersion: rbac.authorization.k8s.io/v1
kind: ClusterRoleBinding
metadata:
  name: run-nfs-provisioner
roleRef:
  apiGroup: rbac.authorization.k8s.io
  kind: ClusterRole
  name: nfs-provisioner-runner
subjects:
- kind: ServiceAccount
  name: nfs-provisioner
  namespace: devops
---
apiVersion: rbac.authorization.k8s.io/v1
kind: Role
metadata:
  name: leader-locking-nfs-provisioner
  namespace: devops
rules:
- apiGroups: [""]
  resources: ["endpoints"]
  verbs: ["get", "list", "watch", "create", "update", "patch"]
---
apiVersion: rbac.authorization.k8s.io/v1
kind: RoleBinding
metadata:
  name: leader-locking-nfs-provisioner
  namespace: devops
roleRef:
  apiGroup: rbac.authorization.k8s.io
  kind: Role
  name: leader-locking-nfs-provisioner
subjects:
- kind: ServiceAccount
  name: nfs-provisioner
  namespace: devops
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: nfs-provisioner
  namespace: devops
spec:
  selector:
    matchLabels:
       app: nfs-provisioner
  replicas: 1
  strategy:
    type: Recreate
  template:
    metadata:
      labels:
        app: nfs-provisioner
    spec:
      serviceAccountName: nfs-provisioner
      containers:
        - name: nfs-provisioner
          image: docker.io/gmoney23/nfs-client-provisioner:latest
          volumeMounts:
            - name: nfs-client-root
              mountPath: /persistentvolumes
          env:
            - name: PROVISIONER_NAME
              value: example.com/nfs
            - name: NFS_SERVER
              value: 192.168.0.21
            - name: NFS_PATH
              value: /mnt/nfs
      volumes:
        - name: nfs-client-root
          nfs:
            server: 192.168.0.21
            path: /mnt/nfs
---
kind: StorageClass
apiVersion: storage.k8s.io/v1
metadata:
  name: nfs
provisioner: example.com/nfs
#reclaimPolicy: Retain
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

1.3 安装mysql

kubectl apply -f deploy.yaml

deploy.yaml

apiVersion: v1
kind: Namespace
metadata:
  name: mysql
  labels:
    app: mysql

---
apiVersion: v1
kind: ConfigMap
metadata:
  name: mysql
  namespace: mysql
  labels:
    app: mysql
data:
  master.cnf: |
    [client]
    default-character-set=utf8mb4
    [mysql]
    default-character-set=utf8mb4
    [mysqld]
    max_connections=2000
    default-time_zone='+8:00'
    character-set-server=utf8mb4
    collation-server=utf8mb4_unicode_ci
    innodb_buffer_pool_size=536870912
    datadir=/var/lib/mysql
    pid-file=/var/run/mysqld/mysqld.pid
    log-error=/var/lib/mysql/error.log
    log-bin=mysqllog
    skip-name-resolve
    lower-case-table-names=1
    log_bin_trust_function_creators=1
  slave.cnf: |
    [client]
    default-character-set=utf8mb4
    [mysql]
    default-character-set=utf8mb4
    [mysqld]
    max_connections=2000
    default-time_zone='+8:00'
    character-set-server=utf8mb4
    collation-server=utf8mb4_unicode_ci
    innodb_buffer_pool_size=536870912
    datadir=/var/lib/mysql
    pid-file=/var/run/mysqld/mysqld.pid
    log-error=/var/lib/mysql/error.log
    super-read-only
    skip-name-resolve
    log-bin=mysql-bin
    lower-case-table-names=1
    log_bin_trust_function_creators=1

---
apiVersion: v1
kind: Secret
metadata:
  name: mysql-secret
  namespace: mysql
  labels:
    app: mysql
type: Opaque
data:
  password: TnNiZzExMTEqQCE= # Nsbg1111*@!
  replicationUser: Y29weQ== #copy
  replicationPassword: TnNiZzExMTEqQCE= #Nsbg1111*@!

---
apiVersion: v1
kind: Service
metadata:
  name: mysql
  namespace: mysql
  labels:
    app: mysql
spec:
  selector:
    app: mysql
  clusterIP: None
  ports:
  - name: mysql
    port: 3306

---
apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: mysql
  namespace: mysql
  labels:
    app: mysql
spec:
  selector:
    matchLabels:
      app: mysql
  serviceName: mysql
  replicas: 2
  template:
    metadata:
      labels:
        app: mysql
    spec:
      initContainers:
      - name: init-mysql
        image: docker.io/library/mysql:5.7.42
        command: 
        - bash
        - "-c"
        - |
          set -ex
          #从pod的hostname中通过正则获取序号，如果没有截取到就退出程序
          ordinal=`cat /etc/hostname | awk -F"-" '{print $2}'` || exit 1
          #将serverId输入到对应的配置文件中，路径可以随意（与之后的对应上就行），但是文件名不能换
          echo [mysqld] > /etc/mysql/conf.d/server-id.cnf
          # 由于server-id不能为0，因此给ID加100来避开它
          server_id=$((100 + $ordinal))
          echo "server-id=$server_id" >> /etc/mysql/conf.d/server-id.cnf
          if [[ ${ordinal} -eq 0 ]]; then
            # 如果Pod的序号为0，说明它是Master节点，从ConfigMap里把Master的配置文件拷贝到/mnt/conf.d目录下
            cp /mnt/config-map/master.cnf /etc/mysql/conf.d
          else
            # 否则，拷贝ConfigMap里的Slave的配置文件
            cp /mnt/config-map/slave.cnf /etc/mysql/conf.d
          fi
          echo "ending..."
        env:
        - name: MYSQL_ROOT_PASSWORD
          valueFrom:
            secretKeyRef:
              name: mysql-secret
              key: password
        - name: MYSQL_REPLICATION_USER
          valueFrom:
            secretKeyRef:
              name: mysql-secret
              key: replicationUser
        - name: MYSQL_REPLICATION_PASSWORD
          valueFrom:
            secretKeyRef:
              name: mysql-secret
              key: replicationPassword
        volumeMounts:
        - name: conf
          mountPath: /etc/mysql/conf.d
        - name: config-map
          mountPath: /mnt/config-map
      containers:
      - name: mysql
        image: docker.io/library/mysql:5.7.42
        lifecycle:
         postStart:
          exec:
            command:
            - bash
            - "-c"
            - |
              set -ex
              cd /var/lib/mysql
              #查看是否存在名为mysqlInitOk的文件，我们自己生产的标识文件，防止重复初始化集群
              if [ ! -f mysqlInitOk ]; then
                echo "Waiting for mysqld to be ready（accepting connections）"
                #执行一条mysql的命令，查看mysql是否初始化完毕，如果没有就反复执行直到可以运行
                  #until mysql -uroot -p${MYSQL_ROOT_PASSWORD} -e "use mysql;SELECT 1;"; do sleep 1; done
                  sleep 5s
                  echo "Initialize ready"
                  #判断是master还是slave
                  pod_seq=`cat /etc/hostname | awk -F"-" '{print $2}'`
                  if [ $pod_seq -eq 0 ];then
                    #创建主从账户
                  mysql -uroot -p${MYSQL_ROOT_PASSWORD} -e "create user '${MYSQL_REPLICATION_USER}'@'%' identified by '${MYSQL_REPLICATION_PASSWORD}';"
                  #设置权限
                  mysql -uroot -p${MYSQL_ROOT_PASSWORD} -e "grant replication slave on *.* to '${MYSQL_REPLICATION_USER}'@'%' with grant option;"
                  #刷新配置
                  mysql -uroot -p${MYSQL_ROOT_PASSWORD} -e "flush privileges;"
                  #初始化master
                  mysql -uroot -p${MYSQL_ROOT_PASSWORD} -e "reset master;"
                else
                  #设置slave连接的master
                  #mysql-0.mysql.mysql的由来{pod-name}.{service-name}.{namespace}
                  mysql -e \
                  "change master to master_host='mysql-0.mysql.mysql',master_port=3306, \
                  master_user='${MYSQL_REPLICATION_USER}',master_password='${MYSQL_REPLICATION_PASSWORD}', \
                  master_log_file='mysqllog.000001',master_log_pos=154;"
                  #重置slave
                  mysql -e "reset slave;"
                  #开始同步
                  mysql -e "start slave;"
                  #改成只读模式
                  mysql -e "set global read_only=1;"
                fi
                #运行完毕创建标识文件，防止重复初始化集群
                touch mysqlInitOk
              fi
        env:
        - name: MYSQL_ROOT_PASSWORD
          valueFrom:
            secretKeyRef:
              name: mysql-secret
              key: password
        - name: MYSQL_REPLICATION_USER
          valueFrom:
            secretKeyRef:
              name: mysql-secret
              key: replicationUser
        - name: MYSQL_REPLICATION_PASSWORD
          valueFrom:
            secretKeyRef:
              name: mysql-secret
              key: replicationPassword
        ports:
        - name: mysql
          containerPort: 3306
        volumeMounts:
        - name: data
          mountPath: /var/lib/mysql
        - name: conf
          mountPath: /etc/mysql/conf.d
        - name: run-mysql
          mountPath: /var/run/mysql
        resources:
          requests:
            cpu: 500m
            memory: 2Gi
        #设置存活探针
        livenessProbe:
          exec:
            command: ["mysqladmin", "ping", "-uroot", "-p${MYSQL_ROOT_PASSWORD}"]
          initialDelaySeconds: 30
          periodSeconds: 10
          timeoutSeconds: 5
        #设置就绪探针
        readinessProbe:
          exec:
            command: ["mysqladmin", "ping", "-uroot", "-p${MYSQL_ROOT_PASSWORD}"]
          initialDelaySeconds: 5
          periodSeconds: 10
          timeoutSeconds: 1
      volumes:
      - name: config-map
        configMap:
          name: mysql
  volumeClaimTemplates:
  - metadata:
      name: data
    spec:
      accessModes:
      - ReadWriteOnce
      storageClassName: nfs
      resources:
        requests:
          storage: 5Gi
  - metadata: 
      name: conf
    spec:
      accessModes:
      - ReadWriteOnce
      storageClassName: nfs
      resources:
        requests:
          storage: 100Mi
  - metadata: 
      name: run-mysql
    spec:
      accessModes:
      - ReadWriteOnce
      storageClassName: nfs
      resources:
        requests:
          storage: 100Mi
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
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270

1.4 备库上查看是否同步