title: 45.k8s安装MySQL MGR集群  
CreateTime: 2019-07-11 00:00:45  
UpdateTime: 2019-07-11 00:00:45  
CategoryName: cloudnative  
---

---
title: "45.k8s安装MySQL MGR集群"
date: 2019-07-11T00:00:45+08:00
draft: false
tags: ["mysql"]
categories: ["cloudnative"]
author: "springrain"
---

## 参考资料  
https://kubernetes.io/docs/tasks/run-application/run-replicated-stateful-application/  
https://mysqlhighavailability.com/setting-up-mysql-group-replication-with-mysql-docker-images/  
https://github.com/gites/k8s-mysql-group-replication/blob/master/README.md  
https://segmentfault.com/a/1190000014966962  

## 单实例MySQL

```yaml
###定义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

```

## MySQL MGR

1.  创建cephfs账号和文件夹
```shell
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
```
2.  创建mysqlmgr

```yaml
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
```

<font color="red">###33061端口无法访问,bug!!!###</font>  
https://bugs.mysql.com/bug.php?id=94004  

<font color="red">
默认的官方mysql镜像ipv6是关闭的,日了狗了,搞了两天......  
cat /proc/sys/net/ipv6/conf/all/disable_ipv6
</font>

![](/public/45/image3.jpeg)

```shell
###进入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;

```