45.k8s安装MySQL MGR集群

  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

###定义ConfigMap,把脚本和配置文件放到ConfigMap###
apiVersion: v1
kind: ConfigMap
metadata:
  name: mysql-configmap
  #labels:
  #  app: mysql
data:

    
  ###mysql的配置文件,添加基本的配置###
  mysqld.cnf: |+
        [mysqld]
        user=mysql
        max_connections = 2000
        default_password_lifetime=0
        character_set_server=utf8mb4
        lower_case_table_names=1
        binlog_expire_logs_seconds=8553600
        max_allowed_packet = 128M
        innodb_buffer_pool_size=500M
        binlog_cache_size = 2M
        innodb_open_files = 4096
        table_definition_cache = 4096
        table_open_cache_instances = 128
        thread_cache_size = 64
        innodb_online_alter_log_max_size=100M
        default_authentication_plugin=mysql_native_password
                

---

###声明 Deployment####
apiVersion: apps/v1
kind: Deployment
metadata:
  name: mysql
  labels:
    app: mysql
spec:
  replicas: 1
  selector:
    matchLabels:
      app: mysql
  template:
    metadata:
      labels:
        app: mysql
    spec:
      containers:
      - name: mysql
        ###mysql的镜像,使用最新的8.0创建###
        image: mysql:8.0
        ###设置mysql可以使用的cpu和内存###
        resources:
          limits:
            cpu: "4"
            memory: 8Gi
          requests:
            cpu: "4"
            memory: 8Gi
        ###设置mysql的端口###    
        ports:
        - containerPort: 3306
          name: client
        - containerPort: 33060
          name: xclient
        - containerPort: 33061
          name: mgr

      
        env:
        ###设置root的密码###
        - name: MYSQL_ROOT_PASSWORD
          value: "Root+123456789"
          
          
        ######健康检查 开始######
        ###pod启动之后,需要检查应用服务是否已经启动,服务正常才加入到service.这里是调用mysql-cli的ping方法,检查mysql是否正常启动###
        livenessProbe:
          exec:
            command:
            - /bin/sh
            - "-c"
            - MYSQL_PWD="${MYSQL_ROOT_PASSWORD}"
            - mysql -h 127.0.0.1 -u root -e "SELECT 1"
          initialDelaySeconds: 30
          timeoutSeconds: 5
          successThreshold: 1
          failureThreshold: 3
        readinessProbe:
          exec:
            command:
            - /bin/sh
            - "-c"
            - MYSQL_PWD="${MYSQL_ROOT_PASSWORD}"
            - mysql -h 127.0.0.1 -u root -e "SELECT 1"
          initialDelaySeconds: 10
          timeoutSeconds: 1
          successThreshold: 1
          failureThreshold: 3
        ######健康检查 结束######  
          
          
        ###挂载的卷###      
        volumeMounts:
        - name: conf
          mountPath: /etc/mysql/conf.d
        - name: mysql-pvc
         ###指定子目录,会在cephfs里创建子文件夹####
          subPath: basesoft/mysql/pvc 
          mountPath: /var/lib/mysql          
          
 ###卷的描述
      volumes:
            ###卷名称,对应上面的挂载名称###
      - name: mysql-pvc
        persistentVolumeClaim:
          ###对应cephfs-pvc.yaml的metadata.name###
          claimName: k8s-pvc
      ###和上面的volumeMounts -name对应####
      - name: conf
        ###来自于configMap###
        configMap:
          ###configMap.metadata.name是mysql-mgr###
          name: mysql-configmap
---

###创建 mysql 服务####
apiVersion: v1
kind: Service
metadata:
  name: mysql
  labels:
    app: mysql
spec:
  ports:
  - port: 3306
    targetPort: 3306
    nodePort: 30306 ###node的端口,用于开发人员直连###
    name: client
  - port: 33060
    targetPort: 33060
    name: xclient
  - port: 33061
    targetPort: 33061
    name: mgr
    
  ###使用NodePort方式,也固定虚拟IP###
  clusterIP: 10.98.239.104 
  selector:
    app: mysql
  ###节点类型是NodePort###
  type: NodePort

1
2
3
4
5
6
7
8

ceph fs authorize cephfs client.mysql-mgr /mysql-mgr rw

mkdir /mnt/mycephfs/mysql-mgr

rm -rf /mnt/mycephfs/mysql-mgr/*
mkdir /mnt/mycephfs/mysql-mgr/data-mysql-mgr-0
mkdir /mnt/mycephfs/mysql-mgr/data-mysql-mgr-1
mkdir /mnt/mycephfs/mysql-mgr/data-mysql-mgr-2

  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
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318

apiVersion: v1
kind: Secret
metadata:
  name: mysql-mgr-ceph-secret
data:
###通过ceph auth get-key client.mysql-mgr |base64 获取 ###
  key: QVFCZEgwbGNWbEVSSkJBQXQ4MjAwdVdINzhzbFl5ZmZFZWNNdVE9PQ==
---



apiVersion: v1
kind: PersistentVolume
metadata:
  name: mysql-mgr-pv-0
  labels:
    pv: mysql-mgr-pv-0
spec:
  capacity:
    storage: 1Gi
  accessModes:
    - ReadWriteMany
  cephfs:
    monitors:
      - node1:6789
      - node2:6789
      - node3:6789
    ###可以配置cephfs的子目录,绑定不同的用户,用于权限隔离####
    path: /mysql-mgr/data-mysql-mgr-0
    ###ceph的账号###
    user: mysql-mgr
    secretRef:
    ###和mysql-mgr-secret.yaml中的metadata.name保持一致###
      name: mysql-mgr-ceph-secret
    readOnly: false
    ###回收策略:Retain手动回收,Recycle需要擦出后才能再使用,Delete相关联的存储资产被删除####
  persistentVolumeReclaimPolicy: Recycle
--- 



apiVersion: v1
kind: PersistentVolume
metadata:
  name: mysql-mgr-pv-1
  labels:
    pv: mysql-mgr-pv-1
spec:
  capacity:
    storage: 1Gi
  accessModes:
    - ReadWriteMany
  cephfs:
    monitors:
      - node1:6789
      - node2:6789
      - node3:6789
    ###可以配置cephfs的子目录,绑定不同的用户,用于权限隔离####
    path: /mysql-mgr/data-mysql-mgr-1
    ###ceph的账号###
    user: mysql-mgr
    secretRef:
    ###和mysql-mgr-secret.yaml中的metadata.name保持一致###
      name: mysql-mgr-ceph-secret
    readOnly: false
    ###回收策略:Retain手动回收,Recycle需要擦出后才能再使用,Delete相关联的存储资产被删除####
  persistentVolumeReclaimPolicy: Recycle
--- 



apiVersion: v1
kind: PersistentVolume
metadata:
  name: mysql-mgr-pv-2
  labels:
    pv: mysql-mgr-pv-2
spec:
  capacity:
    storage: 1Gi
  accessModes:
    - ReadWriteMany
  cephfs:
    monitors:
      - node1:6789
      - node2:6789
      - node3:6789
    ###可以配置cephfs的子目录,绑定不同的用户,用于权限隔离####
    path: /mysql-mgr/data-mysql-mgr-2
    ###ceph的账号###
    user: mysql-mgr
    secretRef:
    ###和mysql-mgr-secret.yaml中的metadata.name保持一致###
      name: mysql-mgr-ceph-secret
    readOnly: false
    ###回收策略:Retain手动回收,Recycle需要擦出后才能再使用,Delete相关联的存储资产被删除####
  persistentVolumeReclaimPolicy: Recycle

---

###mysql的bug:https://bugs.mysql.com/bug.php?id=94004###
###如果禁用了IPV6,无法创建MGR的端口,官方的docker镜像是禁用IPV6的###
###cat /proc/sys/net/ipv6/conf/all/disable_ipv6###






###定义ConfigMap,把脚本和配置文件放到ConfigMap###
apiVersion: v1
kind: ConfigMap
metadata:
  name: mysql-mgr
  labels:
    app: mysql-mgr
data:
###根据podname修改mysqld.cnf的参数###
###ConfigMap的文件都是只读,无法修改,把/etc/mysql/conf.d/目录挂载到emptyDir,把ConfigMap中的mysqld.cnf文件复制到/etc/mysql/conf.d/,然后sed替换变量###
###主要是server-id和loose-group_replication_local_address需要使用hostname变量###
  fix-ip.sh: |
    #!/bin/sh
    set -ex
    
     [[ `hostname` =~ -([0-9]+)$ ]] || exit 1
     ordinal=${BASH_REMATCH[1]}
     
     cat /mnt/config-map/mysqld.cnf >>  /etc/mysql/conf.d/mysqld.cnf
     
     sed -i "s/sedtempserverid/$((100 + $ordinal))/g" /etc/mysql/conf.d/mysqld.cnf
     sed -i "s/sedtempgroupreplicationlocaladdress/`hostname`.mysql-mgr/g" /etc/mysql/conf.d/mysqld.cnf
     
     
     chown -R mysql:mysql /etc/mysql/conf.d
         
  ###mysql的配置文件,添加基本的配置###
  mysqld.cnf: |+
        [mysqld]
        user=mysql
        #datadir=/var/lib/mysql
        #socket=/var/lib/mysql/mysql.sock
        #log-error=/var/log/mysqld.log
        #pid-file=/var/run/mysqld/mysqld.pid
        server-id=sedtempserverid
        gtid_mode = on
        enforce_gtid_consistency=ON
        master_info_repository=TABLE
        relay_log_info_repository=TABLE
        relay_log_recovery=ON
        binlog_format=ROW
        log_bin = mysql-bin 
        log_bin_index = mysql-bin.index
        relay-log=relay-bin
        relay-log-index=relay-bin.index
        log_slave_updates=1
        plugin-load='group_replication.so'
        transaction_write_set_extraction = XXHASH64
        loose-group_replication_group_name       = "94d832dc-f9eb-11e8-a6be-fa163edb2b17"
        loose-group_replication_start_on_boot    = ON
        loose-group_replication_bootstrap_group  = OFF
        loose-group_replication_local_address    = sedtempgroupreplicationlocaladdress:33061
        loose-group_replication_group_seeds      = mysql-mgr-0.mysql-mgr:33061,mysql-mgr-1.mysql-mgr:33061,mysql-mgr-2.mysql-mgr:33061
        loose-group_replication_ip_whitelist = '127.0.0.1/8,192.168.0.0/16,10.0.67.0/24'
        loose-group_replication_single_primary_mode = off
        loose-group_replication_enforce_update_everywhere_checks = on
        loose-group_replication_flow_control_certifier_threshold=250000
        loose-group_replication_flow_control_applier_threshold=250000
        binlog_checksum = NONE
        max_connections = 2000
        default_password_lifetime=0
        character_set_server=utf8mb4
        lower_case_table_names=1
        binlog_expire_logs_seconds=8553600
        innodb_flush_log_at_trx_commit=2
        max_allowed_packet = 128M
        innodb_buffer_pool_size=500M
        binlog_cache_size = 2M
        innodb_open_files = 4096
        table_definition_cache = 4096
        table_open_cache_instances = 128
        thread_cache_size = 64
        innodb_online_alter_log_max_size=100M
                

---



###创建一个Headless(无头)Service,就是没有clusterIP,默认访问域名是:mysql-mgr###
###podName是StatefulSet.metadata.name-序号,例如本例:mysql-mgr-0###
###服务下pod的默认访问域名是podName-Service域名,例如本例:mysql-mgr-0-mysql-mgr###
###资料参考:https://kubernetes.io/docs/concepts/workloads/controllers/statefulset/   ###
apiVersion: v1
kind: Service
metadata:
  name: mysql-mgr
  labels:
    app: mysql-mgr
spec:
  ports:
  - port: 3306
    targetPort: 3306
    name: client
  - port: 33060
    targetPort: 33060
    name: xclient
  - port: 33061
    targetPort: 33061
    name: mgr
  ###无头Service###
  clusterIP: None 
  selector:
    app: mysql-mgr
---


###创建StatefulSet,用于有状态的服务###
###注意:StatefulSet默认使用StorageClass进行PVC的创建,cephfs还没有稳定的StorageClass,所以手动创建pv和pvc,用于绑定####
apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: mysql-mgr
  labels:
    app: mysql-mgr
spec:
  ###服务名称###
  serviceName: mysql-mgr
  ###创建3个副本###
  replicas: 3
  selector:
    matchLabels:
      ###mysql容器的标签###
      app: mysql-mgr
  template:
    metadata:
      labels:
        ###mysql容器的标签###
        app: mysql-mgr
    spec:
      initContainers:
      - name: init-mysql
        image: mysql:8.0
        command: ["bash", "/mnt/config-map/fix-ip.sh"]
        ###挂载的卷###      
        volumeMounts:
        - name: conf
          mountPath: /etc/mysql/conf.d
        - name: conf-map
          mountPath: /mnt/config-map
          
      containers:
      - name: mysql-mgr
        ###mysql的镜像,使用最新的8.0创建###
        image: mysql:8.0
        ###设置mysql可以使用的cpu和内存###
        resources:
          limits:
            cpu: "2"
            memory: 4Gi
          requests:
            cpu: "2"
            memory: 4Gi
        ###设置mysql的端口###    
        ports:
        - containerPort: 3306
          name: client
        - containerPort: 33060
          name: xclient
        - containerPort: 33061
          name: mgr
        ###执行的命令,设置ip的fix-ip.sh,启动mysqld
        #command: ["mysqld"]
       
        ###设置环境变量,用于获取pod的Name###
        env:
        ###fix-ip.sh脚本中,用于获取${POD_Name},使用环境变量获取POD_Name###
        - name: POD_Name
          valueFrom:
            fieldRef:
              fieldPath: metadata.name
        ###设置root的密码###
        - name: MYSQL_ROOT_PASSWORD
          value: "Root+123456789"
        
        ###挂载的卷###      
        volumeMounts:
        - name: conf
          mountPath: /etc/mysql/conf.d
        - name: conf-map
          mountPath: /mnt/config-map
        - name: data
          mountPath: /var/lib/mysql          
      ###卷的描述
      volumes:
      - name: conf
        emptyDir: {}
      ###和上面的volumeMounts -name对应####
      - name: conf-map
        ###来自于configMap###
        configMap:
          ###configMap.metadata.name是mysql-mgr###
          name: mysql-mgr
          ###默认可执行的权限,这样才能执行fix-ip.sh这个脚本###
          ##defaultMode: 0755
  ###PVC的创建模板,名称为 {volumeClaimTemplates.metadata.name}-{serviceName}-{序号},例如本例中的pvc名称是 data-mysql-mgr-0 /1......###
  volumeClaimTemplates:
  - metadata:
      ###模板的名称###
      name: data
      labels:
        name: mysql-mgr
    spec:
      ###访问模式###
      accessModes: [ "ReadWriteOnce" ]
      resources:
        requests:
          ###Gi是二进制1024Mi,G是十进制1000M###
          storage: 1Gi

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

###进入mysql-mgr-0###
kubectl exec -it mysql-mgr-0 -- /bin/bash

###查看配置文件是否正确###
cat /etc/mysql/conf.d/mysqld.cnf

###登陆mysql###
mysql -uroot -pRoot+1qazxsw2

###执行初始化复制###
create user 'mysqlmgruser'@'%' identified with 'mysql_native_password' by '5j2;hoqbkbUF';

grant replication slave on *.* to 'mysqlmgruser'@'%' with grant option;
reset master;
change master to
master_user='mysqlmgruser',master_password='5j2;hoqbkbUF' for channel 'group_replication_recovery';

###配置文件里已经配置了加载###
#install PLUGIN group_replication SONAME 'group_replication.so';

set global group_replication_bootstrap_group=ON;
start group_replication;
set global group_replication_bootstrap_group=OFF;