k8s部署mysql双主双从

# 编写master所需yaml文件

apiVersion: apps/v1
kind: StatefulSet
metadata:
  namespace: '{{ .Release.Namespace }}'
  name: 'mysql-master'
  labels:
    app: mysql-master
    appCluster: mysql-master-cluster
spec:
  serviceName: "yozo-db-master"
  replicas: 1
  selector:
    matchLabels:
      app: mysql-master
      appCluster: mysql-master-cluster
  template:
    metadata:
      labels:
        app: mysql-master
        appCluster: mysql-master-cluster
    spec:
      initContainers:
      - name: initdb
        image: {{ .Values.global.images.registry }}/{{ .Values.busybox.registry.image }}:{{ .Values.busybox.registry.tag }}
        imagePullPolicy: {{ .Values.global.images.pullPolicy }}
        {{- if .Values.busybox.resources }}
        resources: {{- toYaml .Values.busybox.resources | nindent 12 }}
        {{- end }}
        securityContext:
          runAsUser: 0
        command:
          - sh
          - -c
          - |
            cp -r /data/mysql/initdb/*  /tmp/mysql/docker-entrypoint-initdb.d/
            chown -R 1099.1099  /tmp/mysql/docker-entrypoint-initdb.d/
        volumeMounts:
        - mountPath: /tmp/mysql/docker-entrypoint-initdb.d
          name: mysql-initdb
      securityContext:
        runAsUser: 1099
        runAsGroup: 1099
        fsGroup: 1099
      containers:
      - name: mysql
        image: {{ .Values.global.images.registry }}/{{ .Values.mysql.registry.image }}:{{ .Values.mysql.registry.tag }}
        imagePullPolicy: {{ .Values.global.images.pullPolicy }}
        livenessProbe:
          failureThreshold: 6
          initialDelaySeconds: 60
          periodSeconds: 30
          successThreshold: 1
          tcpSocket:          
            port: 3306
          timeoutSeconds: 1
        readinessProbe:
          tcpSocket:          
            port: 3306
          failureThreshold: 8
          initialDelaySeconds: 60
          periodSeconds: 30
          successThreshold: 1
        {{- if .Values.mysql.resources }}
        resources: {{- toYaml .Values.mysql.resources | nindent 12 }}
        {{- end }}
        env:
          - name: LANG
            value: "C.UTF-8"
          - name: MYSQL_ROOT_PASSWORD
            value: "{{ .Values.mysql.access.passwd }}"
          - name: MYSQL_ROOT_USER
            value: "{{ .Values.mysql.access.user }}"
          - name: MYSQL_REPLICATION_USER  
            value: "repl"
          - name: MYSQL_REPLICATION_PASSWORD  
            value: "Yozosoft2023" 
          - name: TZ
            value: "Asia/Shanghai"
        ports:
        - name: mysql
          protocol: "TCP"
          containerPort: 3306
        volumeMounts:
        - mountPath: /docker-entrypoint-initdb.d
          name: mysql-initdb
        - mountPath: /bitnami/mysql/data
          name: mysql-data
          subPath: common/mysql-master/data
        - mountPath: /opt/bitnami/mysql/logs
          name: mysql-logs
          subPath: common/mysql-master/logs
        - mountPath: /etc/localtime
          name: localtime
          subPath: Shanghai
        - name: mysql-master-conf
          mountPath: /opt/bitnami/mysql/conf/my_custom.cnf
          subPath: my_custom.cnf
      volumes:
      - name: mysql-initdb
        emptyDir: {}
      - name: localtime
        configMap:
          name: localtime-config 
      - name: mysql-data
        persistentVolumeClaim: 
          claimName: yozo-data-pvc
      - name: mysql-logs
        persistentVolumeClaim: 
          claimName: yozo-log-pvc
      - name: mysql-master-conf
        configMap:
          name: mysql-master-config
      restartPolicy: Always

---
apiVersion: v1
kind: Service
metadata:
  namespace: '{{ .Release.Namespace }}'
  name: 'yozo-db-master'
  labels:
    app: mysql-master
    appCluster: mysql-master-cluster
spec:
  selector:
    app: mysql-master
    appCluster: mysql-master-cluster
  ports:
    - protocol: TCP
      port: 3306
      targetPort: 3306
    {{ if .Values.loadbalancer.enabled }}
  type: LoadBalancer
    {{- else }}
  type: ClusterIP
    {{ end }}

---
apiVersion: v1
kind: ConfigMap
metadata:
  namespace: '{{ .Release.Namespace }}'
  name: mysql-master-config
data:
  my_custom.cnf: |
    [mysqld]
    lower_case_table_names = 1
    max_connections = 4000
    log_bin = /opt/bitnami/mysql/logs/mysql-bin
    server_id = 1   #另一台写2
    
    connect_timeout = 60
    max_prepared_stmt_count = 65535
    key_buffer_size = 512M
    sort_buffer_size = 8M
    join_buffer_size = 8M
    thread_cache_size = 32
    interactive_timeout = 3600
    wait_timeout = 1800
    table_open_cache = 2048
    open_files_limit = 65535
    back_log = 600
    max_connect_errors = 6000
    max_allowed_packet = 128M
    tmp_table_size = 256M
    max_heap_table_size = 512M
    bulk_insert_buffer_size = 64M
    external-locking = FALSE
    query_cache_size = 512M
    query_cache_limit = 4M
    query_cache_min_res_unit = 2k
    read_buffer_size = 8M
    read_rnd_buffer_size = 8M
    net_buffer_length = 256k
    default-storage-engine = InnoDB
    innodb_buffer_pool_size = 4G
    innodb_buffer_pool_chunk_size = 256M
    innodb_buffer_pool_instances = 8
    innodb_log_file_size = 512M
    innodb_flush_log_at_trx_commit = 2
    innodb_flush_method = O_DIRECT
    thread_stack = 256K
    transaction_isolation = READ-COMMITTED
    explicit_defaults_for_timestamp = 1
    slow_query_log = 1
    long_query_time = 8.0
    slow_query_log_file = /opt/bitnami/mysql/logs/slow-query.log
    binlog_format = row
    expire_logs_days = 30
    binlog-ignore-db = information_schema
    binlog-ignore-db = performance_schema
    binlog-ignore-db = sys
    log_bin_trust_function_creators = 1
    
    optimizer_switch = "index_merge=off"
    optimizer_switch = "index_merge_sort_union=off"

# 编写slave所需yaml文件

apiVersion: apps/v1
kind: StatefulSet
metadata:
  namespace: '{{ .Release.Namespace }}'
  name: 'mysql-slave'
  labels:
    app: mysql-slave
    appCluster: mysql-slave-cluster
spec:
  serviceName: "yozo-db-slave"
  replicas: 1
  selector:
    matchLabels:
      app: mysql-slave
      appCluster: mysql-slave-cluster
  template:
    metadata:
      labels:
        app: mysql-slave
        appCluster: mysql-slave-cluster
    spec:
      initContainers:
      - name: initdb
        image: {{ .Values.global.images.registry }}/{{ .Values.busybox.registry.image }}:{{ .Values.busybox.registry.tag }}
        imagePullPolicy: {{ .Values.global.images.pullPolicy }}
        {{- if .Values.busybox.resources }}
        resources: {{- toYaml .Values.busybox.resources | nindent 12 }}
        {{- end }}
        securityContext:
          runAsUser: 0
        command:
          - sh
          - -c
          - |
            cp -r /data/mysql/initdb/*  /tmp/mysql/docker-entrypoint-initdb.d/
            chown -R 1099.1099  /tmp/mysql/docker-entrypoint-initdb.d/
        volumeMounts:
        - mountPath: /tmp/mysql/docker-entrypoint-initdb.d
          name: mysql-initdb
      securityContext:
        runAsUser: 1099
        runAsGroup: 1099
        fsGroup: 1099
      containers:
      - name: mysql
        image: {{ .Values.global.images.registry }}/{{ .Values.mysql.registry.image }}:{{ .Values.mysql.registry.tag }}
        imagePullPolicy: {{ .Values.global.images.pullPolicy }}
        livenessProbe:
          failureThreshold: 6
          initialDelaySeconds: 60
          periodSeconds: 30
          successThreshold: 1
          tcpSocket:          
            port: 3306
          timeoutSeconds: 1
        readinessProbe:
          tcpSocket:          
            port: 3306
          failureThreshold: 8
          initialDelaySeconds: 60
          periodSeconds: 30
          successThreshold: 1
        {{- if .Values.mysql.resources }}
        resources: {{- toYaml .Values.mysql.resources | nindent 12 }}
        {{- end }}
        env:
          - name: LANG
            value: "C.UTF-8"
          - name: MYSQL_ROOT_PASSWORD
            value: "{{ .Values.mysql.access.passwd }}"
          - name: MYSQL_ROOT_USER
            value: "{{ .Values.mysql.access.user }}"
          - name: MYSQL_REPLICATION_USER  
            value: "repl"
          - name: MYSQL_REPLICATION_PASSWORD  
            value: "Yozosoft2023" 
          - name: TZ
            value: "Asia/Shanghai"
        ports:
        - name: mysql
          protocol: "TCP"
          containerPort: 3306
        volumeMounts:
        - mountPath: /docker-entrypoint-initdb.d
          name: mysql-initdb
        - mountPath: /bitnami/mysql/data
          name: mysql-data
          subPath: common/mysql-slave/data
        - mountPath: /opt/bitnami/mysql/logs
          name: mysql-logs
          subPath: common/mysql-slave/logs
        - mountPath: /etc/localtime
          name: localtime
          subPath: Shanghai
        - name: mysql-slave-conf
          mountPath: /opt/bitnami/mysql/conf/my_custom.cnf
          subPath: my_custom.cnf
      volumes:
      - name: mysql-initdb
        emptyDir: {}
      - name: localtime
        configMap:
          name: localtime-config 
      - name: mysql-data
        persistentVolumeClaim: 
          claimName: yozo-data-pvc
      - name: mysql-logs
        persistentVolumeClaim: 
          claimName: yozo-log-pvc
      - name: mysql-slave-conf
        configMap:
          name: mysql-slave-config
      restartPolicy: Always

---
apiVersion: v1
kind: Service
metadata:
  namespace: '{{ .Release.Namespace }}'
  name: 'yozo-db-slave'
  labels:
    app: mysql-slave
    appCluster: mysql-slave-cluster
spec:
  selector:
    app: mysql-slave
    appCluster: mysql-slave-cluster
  ports:
    - protocol: TCP
      port: 3306
      targetPort: 3306
    {{ if .Values.loadbalancer.enabled }}
  type: LoadBalancer
    {{- else }}
  type: ClusterIP
    {{ end }}

---
apiVersion: v1
kind: ConfigMap
metadata:
  namespace: '{{ .Release.Namespace }}'
  name: mysql-slave-config
data:
  my_custom.cnf: |
    [mysqld]
    lower_case_table_names = 1
    max_connections = 4000
    log_bin = /opt/bitnami/mysql/logs/mysql-bin
    server_id = 2
    
    connect_timeout = 60
    max_prepared_stmt_count = 65535
    key_buffer_size = 512M
    sort_buffer_size = 8M
    join_buffer_size = 8M
    thread_cache_size = 32
    interactive_timeout = 3600
    wait_timeout = 1800
    table_open_cache = 2048
    open_files_limit = 65535
    back_log = 600
    max_connect_errors = 6000
    max_allowed_packet = 128M
    tmp_table_size = 256M
    max_heap_table_size = 512M
    bulk_insert_buffer_size = 64M
    external-locking = FALSE
    query_cache_size = 512M
    query_cache_limit = 4M
    query_cache_min_res_unit = 2k
    read_buffer_size = 8M
    read_rnd_buffer_size = 8M
    net_buffer_length = 256k
    default-storage-engine = InnoDB
    innodb_buffer_pool_size = 4G
    innodb_buffer_pool_chunk_size = 256M
    innodb_buffer_pool_instances = 8
    innodb_log_file_size = 512M
    innodb_flush_log_at_trx_commit = 2
    innodb_flush_method = O_DIRECT
    thread_stack = 256K
    transaction_isolation = READ-COMMITTED
    explicit_defaults_for_timestamp = 1
    slow_query_log = 1
    long_query_time = 8.0
    slow_query_log_file = /opt/bitnami/mysql/logs/slow-query.log
    binlog_format = row
    expire_logs_days = 30
    binlog-ignore-db = information_schema
    binlog-ignore-db = performance_schema
    binlog-ignore-db = sys
    log_bin_trust_function_creators = 1
    
    optimizer_switch = "index_merge=off"
    optimizer_switch = "index_merge_sort_union=off"

#启动完成后，进入双pod
 

注:生产环境中密码采用高级别的密码，将'%'换成另一节点的ip
grant replication  slave,reload,super  on *.*  to 'repl'@'%' identified by 'Yozosoft2023';

flush privileges;

#初始化bin-log日志 （双节点）

reset master;

show master status;

#配置主从同步信息（podA）

change master to master_host='mysql-slave-0.yozo-db-slave.yozo.svc.cluster.local',master_port=3306,master_user='repl',master_password='Yozosoft2023',master_log_file='mysql-bin.000001',master_log_pos=154;

这里的master_host是指podB的dns地址，master_port是B上的mysql容器启动时赋予的端口，master_uesr是刚创建用于同步的用户名，master_password是执行同步的用户密码，master_log_file是主机B的bin-log日志，master_log_pos是从主机B bin-log日志开始同步的位置。

#配置主从同步信息（podB）

change master to master_host='mysql-master-0.yozo-db-master.yozo.svc.cluster.local',master_port=3306,master_user='repl',master_password='Yozosoft2023',master_log_file='mysql-bin.000001',master_log_pos=154;

这里的master_host是指podA的dns地址，master_port是A上的mysql容器启动时赋予的端口，master_uesr是刚创建用于同步的用户名，master_password是执行同步的用户密码，master_log_file是主机B的bin-log日志，master_log_pos是从主机B bin-log日志开始同步的位置。

flush privileges;

#开启主从同步（双节点）

start slave;

查看是否成功：

show slave status\G

# 这样配置的集群即使pod重启ip变化也不会断开主从配置