MySQL主从

• ConfigMap
• pv和pvc
• Service
• StatefulSet
• 主从同步

MySQL 示例部署包含一个ConfigMap、两个存储挂载pv和pvc、两个 Service 与一个 StatefulSet。

ConfigMap

cat mysql-master-cnf.yaml

#master--my.cnf
apiVersion: v1
kind: ConfigMap
metadata:
  name: mysql-master-cnf
  namespace: bc-cnp
data:
  my.cnf: |-
    [client]
    default-character-set=utf8
    [mysql]
    default-character-set=utf8
    [mysqld]
    init_connect='SET collation_connection = utf8_unicode_ci'
    init_connect='SET NAMES utf8'
    character-set-server=utf8
    collation-server=utf8_unicode_ci
    skip-character-set-client-handshake
    skip-name-resolve
    server_id=1
    log-bin=mysql-bin
    read-only=0
    replicate-ignore-db=mysql
    replicate-ignore-db=sys
    replicate-ignore-db=information_schema
    replicate-ignore-db=performance_schema

添加配置

kubectl apply -f mysql-master-cnf.yaml
kubectl get cm -n bc-cnp

pv和pvc

cat mysql-sc.yaml

---
#master--pv
apiVersion: v1
kind: PersistentVolume
metadata:
  name: mysql-pv-master
spec:
  accessModes:
    - ReadWriteOnce
  capacity:
    storage: 10Gi
  local:
    path: /home/k8s/master/data
  nodeAffinity:
    required:
      nodeSelectorTerms:
      - matchExpressions:
        - key: kubernetes.io/hostname
          operator: In
          values:
          - paas-cnp-k8s-kce-01  
---
#master--pvc
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: mysql-pvc-master
  namespace: bc-cnp
spec:
  accessModes:
    - ReadWriteOnce
  resources:
    requests:
      storage: 10Gi
  volumeName: mysql-pv-master

kubectl apply -f mysql-sc.yaml
kubectl get pvc -n bc-cnp

Service

#master--headless service
apiVersion: v1
kind: Service
metadata:
  namespace: bc-cnp
  labels:
    app: mysql-master
  annotations:
    kubesphere.io/serviceType: statefulservice
    kubesphere.io/alias-name: mysql主节点
  name: mysql-master
spec:
  sessionAffinity: ClientIP
  selector:
    app: mysql-master
  ports:
    - name: tcp-3306
      protocol: TCP
      port: 3306
      targetPort: 3306
    - name: tcp-33060
      protocol: TCP
      port: 33060
      targetPort: 33060
  clusterIP: None
  sessionAffinityConfig:
    clientIP:
      timeoutSeconds: 10800

---
#master--nodePort service
apiVersion: v1
kind: Service
metadata:
  name: mysql-master-front
  labels:
    app: mysql-master
  namespace: bc-cnp
spec:
  selector:
    app: mysql-master
  type: NodePort
  ports:
    - name: ''
      port: 3306
      protocol: TCP
      targetPort: 3306
      nodePort: 30001  
  sessionAffinity: None

kubectl apply -f mysql-master-services.yaml 

StatefulSet

#master--statefulset
apiVersion: apps/v1
kind: StatefulSet
metadata:
  namespace: bc-cnp
  labels:
    app: mysql-master
  name: mysql-master
  annotations:
    kubesphere.io/alias-name: mysql master
spec:
  replicas: 1
  selector:
    matchLabels:
      app: mysql-master
  template:
    metadata:
      labels:
        app: mysql-master
    spec:
      containers:
        - name: master-container
         # type: worker
          imagePullPolicy: IfNotPresent
          resources:
            requests:
              cpu: '2'
              memory: 8Gi
            limits:
              cpu: '4'
              memory: 16Gi
          image: nexus.cmss.com:8086/cnp/mysql:8.0.18
          env:
            - name: MYSQL_ROOT_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: mysql-secret
                  key: MYSQL_ROOT_PASSWORD
          volumeMounts:
            - name: master-cnf-volume
              readOnly: false
              mountPath: /etc/mysql
            - name: master-data-volume
              readOnly: false
              mountPath: /var/lib/mysql-files
      serviceAccount: default
      affinity:
        podAntiAffinity:
          preferredDuringSchedulingIgnoredDuringExecution:
            - weight: 100
              podAffinityTerm:
                labelSelector:
                  matchLabels:
                    app: mysql-master
                topologyKey: kubernetes.io/hostname
      volumes:
        - name: master-cnf-volume
          configMap:
            name: mysql-master-cnf
            items:
              - key: my.cnf
                path: my.cnf
        - name: master-data-volume
          persistentVolumeClaim:
            claimName: mysql-pvc-master
  serviceName: mysql-master

kubectl apply -f mysql-master-statefulset.yaml
kubectl get pods -nbc-cnp

主从同步

进入mysql-master容器内部

# 1.进入mysql内部
>  mysql -uroot -pdsjbi@Min1a
#切换到 mysql DB
mysql> USE mysql;   
# 查看root用户是否具备远程访问权限
mysql> select Host,User,authentication_string,password_expired,password_last_changed from user; 
# 2.授权 root可以远程访问（主从无关，如root没有访问权限，执行以下命令，方便我们远程连接MySQL）
mysql> create user 'root'@'%' identified by 'dsjbi@Min1a';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> grant all privileges on *.* to 'root'@'%';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
# 3.添加用来同步的用户
mysql> GRANT REPLICATION SLAVE ON *.* to 'backup'@'%' identified by 'dsjbi@Min1a';
Query OK, 0 rows affected, 1 warning (0.01 sec)
# 4.查看master状态
mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-bin.000003
         Position: 2688
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

然后进入到mysql-slave内部

# 进入mysql内部
mysql -uroot -pdsjbi@Min1a
# 设置主库连接  主库 dns: mysql-master.default.svc.cluster.local
change master to master_host='mysql-master.bc-cnp.svc.cluster.local',master_user='backup',master_password='dsjbi@Min1a',master_log_file='mysql_bin.000003',master_log_pos=0,master_port=3306;
# 启动从库同步
start slave;
# 查看从从库状态
show slave status\G;

## 如果同步不成功，尝试执行以下命令，再次查看。
stop slave;
reset slave;
start slave;