kubernetes部署 MySQL MGR StatefulSet 和 MySQL Router

最新推荐文章于 2024-05-08 18:06:52 发布

shark_西瓜甜

最新推荐文章于 2024-05-08 18:06:52 发布

阅读量215

点赞数 1

分类专栏： kubernetes 文章标签： kubernetes mysql adb

本文链接：https://blog.csdn.net/qq_22648091/article/details/136863720

版权

kubernetes 专栏收录该内容

23 篇文章 2 订阅

订阅专栏

MySQL MBR StatefuSet

kind: StatefulSet
apiVersion: apps/v1
metadata:
  name: mysql
  namespace: middleware
  labels:
    app: mysql
spec:
  replicas: 3
  selector:
    matchLabels:
      app: mysql
  template:
    metadata:
      creationTimestamp: null
      labels:
        app: mysql
    spec:
      volumes:
        - name: host-time
          hostPath:
            path: /etc/localtime
            type: ''
        - name: volume-confmap
          configMap:
            name: mysql-innodb-config
            defaultMode: 420
        - name: mysqlconf
          emptyDir: {}
      initContainers:
        - name: init-config
          image: 'busybox:1.35'
          command:
            - sh
          args:
            - '-c'
            - >-
              cp /config-map/my.cnf /mnt/my.cnf;

              ordinal=$(echo $HOSTNAME |grep  '[0-9]' -o)

              echo -e "\nserver-id=$((100 + $ordinal))" >> /mnt/my.cnf

              echo -e
              "\nreport_host=${HOSTNAME}.${MY_POD_SVC}.${MY_POD_NAMESPACE}" >>
              /mnt/my.cnf
          env:
            - name: MY_POD_NAMESPACE
              valueFrom:
                fieldRef:
                  apiVersion: v1
                  fieldPath: metadata.namespace
            - name: MY_POD_SVC
              value: mysql-svc
          resources: {}
          volumeMounts:
            - name: volume-confmap
              readOnly: true
              mountPath: /config-map
            - name: mysqlconf
              mountPath: /mnt
          terminationMessagePath: /dev/termination-log
          terminationMessagePolicy: File
          imagePullPolicy: IfNotPresent
      containers:
        - name: mysql
          image: 'mysql/mysql-server:8.0.28'
          ports:
            - name: tcp-3306
              containerPort: 3306
              protocol: TCP
            - name: tcp-33060
              containerPort: 33060
              protocol: TCP
            - name: tcp-33061
              containerPort: 33061
              protocol: TCP
          env:
            - name: MYSQL_ROOT_PASSWORD
              value: QFedu123_
            - name: MYSQL_ROOT_HOST
              value: 127.0.0.1
          resources: {}
          volumeMounts:
            - name: host-time
              mountPath: /etc/localtime
            - name: mysql-data
              mountPath: /var/lib/mysql
              subPath: mysql
            - name: mysqlconf
              readOnly: true
              mountPath: /etc/my.cnf
              subPath: my.cnf
          terminationMessagePath: /dev/termination-log
          terminationMessagePolicy: File
          imagePullPolicy: IfNotPresent
        - name: set-cluster
          image: 'mysql/mysql-server:8.0.28'
          command:
            - bash
          args:
            - '-c'
            - >-

              # 首先判断 mysql是否已经是可用状态

              until mysql -uroot -pQFedu123_ -h 127.0.0.1 -e 'SELECT 1'

              do
                  sleep 2
              done


              echo '判断是否存在 cfAdmin 用户'

              name=$(mysql -uroot -pQFedu123_ -h 127.0.0.1 -NB -e "select user
              from mysql.user where user='cfAdmin';")

              if [ $name ];then
                  echo '集群配置管理员 cfAdmin 用户存在，集群已经创建过'
                  sh
              else
                  echo '集群配置管理员 cfAdmin 不存在， 创建此用户'
                  mysqlsh --uri root:QFedu123_@localhost -- dba configure-instance --clusterAdmin=cfAdmin --clusterAdminPassword=cfAdmin_F127
                  if [ $? -eq 0 ];then
                      echo '集群配置管理员 cfAdmin 已创建完成，接下来准备创建集群'
                  else
                      echo '集群配置管理员 cfAdmin 创建失败，容器退出'
                      exit 1
                  fi
              fi

              echo "主机名:$HOSTNAME"

              pod_prefix=$(echo $HOSTNAME |awk -F '-[0123456789]' '{print $1}')

              echo "判断当前 Pod 是否是 ${pod_prefix}-0"

              if [ ${HOSTNAME} == ${pod_prefix}-0 ];then
                  echo "本 Pod 是 ${pod_prefix}-0，接着判断是否创建过集群"
                  mysqlsh --uri cfAdmin:cfAdmin_F127@localhost:3306 -- cluster status
                  if [ $? -ne 0 ];then
                      echo '没有创建过集群，开始创建集群，并初始化为主节点'
                      mysqlsh --uri cfAdmin:cfAdmin_F127@localhost:3306 -- dba create-cluster InnoDB_Cluster8 --multiPrimary=false --memberWeight=100 --ipAllowlist="10.244.0.0/16"
                  fi
                  echo '获取当前集群状态'
                  mysqlsh --uri cfAdmin:cfAdmin_F127@localhost:3306 -- cluster status
                  sh
              else
                  echo "本 Pod 不是 ${pod_prefix}-0，将其添加到集群"
                  pod_id=$(echo $HOSTNAME |awk -F- '{print $NF}')
                  echo "开始查找集群内可通信的节点"
                  for i in $(seq 0 $((pod_id - 1)))
                  do
                      befor_pod=${pod_prefix}-$((pod_id - 1)).${MY_POD_SVC}
                      mysql -ucfAdmin -pcfAdmin_F127 -h${befor_pod} -NB -e "select 1"
                      if [ $? -eq 0 ];then
                          echo "已找到集群内可通信的节点: ${befor_pod}"
                          break
                      fi
                  done
                  echo "从 ${befor_pod} 获取集群中主节点信息"
                  node_primary=''
                  while [[ -z $node_primary ]]; do
                      node_primary=$(mysqlsh --uri cfAdmin:cfAdmin_F127@${befor_pod}:3306 -- cluster status|awk '/SourceMember/ {print $NF}'|tr -d '"')
                      sleep 2
                  done
                  echo "当前主节点是 ${node_primary} 开始将 ${HOSTNAME} 添加到集群"
                  mysqlsh --uri cfAdmin:cfAdmin_F127@${node_primary} -- cluster add-instance ${HOSTNAME}.${MY_POD_SVC}.${MY_POD_NAMESPACE}:3306 --ipAllowlist=10.244.0.0/16 --memberWeight=60 --recoveryMethod=incremental

                  echo "判断是否创建过集群管理员账户"
                  name=$(mysqlsh --uri cfAdmin:cfAdmin_F127@${node_primary} --sql -e "select user from mysql.user where user='icAdmin';" |awk 'NR==2 {print $1}')
                  if [ -z "${name}" ];then
                      echo "集群管理员账户不存在:${name}，开始创建管理员账户"
                      mysqlsh --uri cfAdmin:cfAdmin_F127@${node_primary} -- cluster setup-admin-account icAdmin@10.%  --password='icAdmin_F127'
                  else
                      echo "集群管理员账户已存在:${name}"
                  fi
                  echo '获取当前集群状态'
                  mysqlsh --uri icAdmin:icAdmin_F127@${node_primary} -- cluster status
                  sh
              fi
          env:
            - name: MY_POD_NAMESPACE
              valueFrom:
                fieldRef:
                  apiVersion: v1
                  fieldPath: metadata.namespace
            - name: MY_POD_SVC
              value: mysql-svc
          resources: {}
          terminationMessagePath: /dev/termination-log
          terminationMessagePolicy: File
          imagePullPolicy: IfNotPresent
          stdin: true
      restartPolicy: Always
      terminationGracePeriodSeconds: 30
      dnsPolicy: ClusterFirst
      serviceAccountName: default
      serviceAccount: default
      securityContext: {}
      schedulerName: default-scheduler
  volumeClaimTemplates:
    - kind: PersistentVolumeClaim
      apiVersion: v1
      metadata:
        name: mysql-data
        namespace: middleware
        creationTimestamp: null
      spec:
        accessModes:
          - ReadWriteOnce
        resources:
          requests:
            storage: 30Gi
        storageClassName: nfs-storage
        volumeMode: Filesystem
  serviceName: mysql-svc
  podManagementPolicy: OrderedReady
  updateStrategy:
    type: RollingUpdate
    rollingUpdate:
      partition: 0
  revisionHistoryLimit: 10
---
kind: Service
apiVersion: v1
metadata:
  name: mysql-svc
  namespace: middleware
  labels:
    app: mysql
spec:
  ports:
    - name: tcp-3306
      protocol: TCP
      port: 3306
      targetPort: 3306
    - name: tcp-33060
      protocol: TCP
      port: 33060
      targetPort: 33060
    - name: tcp-33061
      protocol: TCP
      port: 33061
      targetPort: 33061
  selector:
    app: mysql
  clusterIP: None
  clusterIPs:
    - None
  type: ClusterIP
  sessionAffinity: None
  ipFamilies:
    - IPv4
  ipFamilyPolicy: SingleStack
  internalTrafficPolicy: Cluster

  #####备注：这个只是模板需要改的信息较多 1、pod的名称 2、service的 这两项都可统一替换 3、配置文件需要提前创建configmap

my.cnf

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

[mysqld]
user=mysql

character_set_server=utf8mb4
collation-server = utf8mb4_unicode_ci

default-time_zone = '+8:00'
lower_case_table_names = 1

# 开启事务功能
event_scheduler=ON

# 最大连接数
max_connections=2000

# 允许最大数据包大小
max_allowed_packet = 500M

disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
# 优化iO
sync_binlog=1000

innodb_lru_scan_depth=100  # from 1024 to conserve 90% CPU cycles used for function
innodb_io_capacity=1900  # from 200 to allow more IOPSecond to your storage device
innodb_flush_neighbors=2  # from 0 to expedite writing to current extent
#innodb_max_dirty_pages_pct_lwm=1  # from 10 percent to expedite writes
#innodb_max_dirty_pages_pct=1  # from 90 percent to reduce innodb_buffer_pool_pages_dirty count
innodb_change_buffer_max_size=50  # from 25 percent to expedite your high volume activity

transaction_isolation=REPEATABLE-READ
innodb_max_dirty_pages_pct=50
innodb_adaptive_flushing=ON
innodb_flush_method=O_DIRECT

lower_case_table_names=1
innodb_fast_shutdown=0
innodb_force_recovery=0
innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1

innodb_flush_log_at_trx_commit=1
innodb_log_file_size=1G
innodb_log_files_in_group=3
innodb_log_group_home_dir=./

read_buffer_size=8M
optimizer_switch="index_condition_pushdown=on,mrr=on,mrr_cost_based=on,batched_key_access=off,block_nested_loop=on"
read_rnd_buffer_size=8M
innodb_old_blocks_pct=35
innodb_buffer_pool_size=1G
innodb_buffer_pool_instances=64
innodb_log_buffer_size=32M

bulk_insert_buffer_size=128M
innodb_change_buffer_max_size=50
innodb_doublewrite=on
innodb_adaptive_hash_index=on
innodb_file_per_table=1
innodb_data_file_path=ibdata1:1024M:autoextend
innodb_page_size=16k
innodb_lock_wait_timeout=35
innodb_rollback_on_timeout=on
innodb_sync_spin_loops=100
innodb_spin_wait_delay=30
innodb_lru_scan_depth=4000

innodb_thread_concurrency=0
innodb_write_io_threads=2
innodb_read_io_threads=2
innodb_purge_threads=2
innodb_io_capacity=800
innodb_io_capacity_max=1600

log_bin=bin-log
binlog_format=ROW
binlog_checksum=NONE

# 针对单次操作语句过多造成主从复制延迟
max_binlog_cache_size = 1024M

#执行导入与导出文件的安全目录
#secure_file_priv='/apps/mysql/outdata'


allow-suspicious-udfs=FALSE
local_infile=FALSE
skip-grant-tables=FALSE

##标记relay log 允许的最大值，如果该值为0，则默认值为max_binlog_size(1G)；如果不为0，则max_relay_log_size则为最大的relay_log文件大小；
max_relay_log_size=0
##relay-log日志记录的是从服务器I/O线程将主服务器的二进制日志读取过来记录到从服务器本地文件，
relay_log=mysql-relay-bin
##binlog传到备机被写道relaylog里，备机的slave sql线程从relaylog里读取然后应用到本地。
relay_log_index=mysql-relay-bin.index

#慢查询sql日志设置
# 开启慢查询sql日志
#slow_query_log=1
# (自行修改)
#slow_query_log_file=slow_query_log.log

# 未使用索引的语句也记录
#log_queries_not_using_indexes=ON


#将所有到达MySQL Server的SQL语句记录下来,默认关闭
#general_log=ON
#general_log路径  # (自行修改)
#general_log_file=/apps/mysql/syslog/general_log.log


allow-suspicious-udfs=OFF
local_infile=OFF
# 禁止以--skip-grant-tables选项启动数据库
skip-grant-tables=OFF
# 不要对表使用软连接

relay_log_recovery=ON

local_infile=0

# 以--safe-user-create模式启动数据库
safe-user-create

# 加密方式
block_encryption_mode = 'aes-128-ecb'

# 配置密码策略(复杂度)
#plugin_load_add='validate_password.so'
#validate-password=FORCE_PLUS_PERMANENT
#validate_password_length=14
#validate_password_mixed_case_count=1
#validate_password_number_count=1
#validate_password_policy=MEDIUM
#validate_password_special_char_count=1
## 密码过期的策略 90天
#default_password_lifetime=90

# 使用SHA256哈希算法加密密码
#old_passwords=2
#default-authentication-plugin=sha256_password


#只有授权用户才能执行show databases命令
#skip_show_database=1

#MySQL连接闲置超过一定时间后(单位：秒)将会被强行关闭
#MySQL默认的wait_timeout值为8个小时, interactive_timeout参数需要同时配置才能生效
interactive_timeout=7200
wait_timeout=7200
# 60秒内没有收到 master的任何数据 slave认为连接断开，会进行重连
replica_net_timeout = 60

# 配置防暴力破解
#plugin_load_add='connection_control.so'
# 用来控制登录失败的次数及延迟响应时间
#connection-control=FORCE_PLUS_PERMANENT
# 将登录失败的操作记录至IS库中
#connection-control-failed-login-attempts=FORCE_PLUS_PERMANENT
# 设定允许失败的次数
#connection-control-failed-connections-threshold=3
# 设定最小延迟登录时间为1000 毫秒
#connection-control-min-connection-delay=1000
# 最大延迟登录时间  单位：毫秒
#connection-control-max-connection-delay=2147483647


# 为mysql配置加密通信

#ssl_ca="/etc/cert/cacert.pem"
#ssl_cert="/etc/cert/server.crt"
#ssl_key="/etc/cert/server.key"


# 选择安全性强的加密算法套件
#ssl-cipher=DHE-RSA-AES256-SHA

# 强制要求客户端使用加密连接
#require_secure_transport=ON

# 加密协议版本
#tls_version=TLSv1.2

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

# GROUP_CONCAT函数 拼接最大长度 datav 大屏使用
group_concat_max_len = 102400

# 优化
table_open_cache = 2048


# 集群设置
log-bin=/var/lib/mysql/bin-log

# 二进制日志保留时间 10 天
binlog_expire_logs_seconds = 864000
binlog_format = ROW
binlog_transaction_dependency_tracking = WRITESET
enforce_gtid_consistency = ON
gtid_mode = ON
# 默认情况下，要接受 CREATE FUNCTION 语句，必须至少显式指定 DETERMINISTIC, NO SQL,
# 或者 READS SQL # DATA 中的一个。否则在复制时会发生如下错误：
# ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL,
# or READS SQL DATA in its declaration and binary logging is enabled
# (you *might* want to use the less safe log_bin_trust_function_creators variable)
# 正确示例:
# CREATE FUNCTION f1(i INT)
# RETURNS INT
# DETERMINISTIC
# READS SQL DATA
# BEGIN
#   RETURN i;
#   END;
# 可以设置如下值避免此检查
log_bin_trust_function_creators = 1

# mysql8
replica_parallel_type = LOGICAL_CLOCK
replica_parallel_workers = 4
replica_preserve_commit_order = ON
report_port = 3306

auto_increment_increment = 1
auto_increment_offset = 2
binlog_checksum = NONE

# 从服务器将从主服务器接收到的更新也写入自己的二进制日志中，默认是不会
log_slave_updates = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE

############################
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

MySQL Router

kind: Deployment
apiVersion: apps/v1
metadata:
  name: mysqlrouter
  namespace: middleware
  labels:
    app: mysqlrouter
spec:
  replicas: 1
  selector:
    matchLabels:
      app: mysqlrouter
  template:
    metadata:
      creationTimestamp: null
      labels:
        app: mysqlrouter
    spec:
      volumes:
        - name: host-time
          hostPath:
            path: /etc/localtime
            type: ''
      containers:
        - name: mysqlrouter
          image: 'mysql/mysql-router:8.0.28'
          ports:
            - name: tcp-6446
              containerPort: 6446
              protocol: TCP
            - name: tcp-6447
              containerPort: 6447
              protocol: TCP
            - name: tcp-6448
              containerPort: 6448
              protocol: TCP
            - name: tcp-6449
              containerPort: 6449
              protocol: TCP
            - name: tcp-8443
              containerPort: 8443
              protocol: TCP
          env:
            - name: MYSQL_HOST
              value: mysql-0.mysql-svc.middleware
            - name: MYSQL_PORT
              value: '3306'
            - name: MYSQL_USER
              value: icAdmin
            - name: MYSQL_PASSWORD
              value: icAdmin_F127
            - name: MYSQL_ROUTER_BOOTSTRAP_EXTRA_OPTIONS
              value: >-
                --client-ssl-mode=PASSTHROUGH
                --conf-set-option=DEFAULT.max_total_connections=5000
                --conf-set-option=routing:InnoDB_Cluster8_ro.routing_strategy=round-robin
          resources: {}
          volumeMounts:
            - name: host-time
              readOnly: true
              mountPath: /etc/localtime
          imagePullPolicy: IfNotPresent
      restartPolicy: Always
      terminationGracePeriodSeconds: 30
      affinity:
        podAntiAffinity:
          preferredDuringSchedulingIgnoredDuringExecution:
            - weight: 100
              podAffinityTerm:
                labelSelector:
                  matchLabels:
                    app: mysqlrouter
                topologyKey: kubernetes.io/hostname
      schedulerName: default-scheduler
  strategy:
    type: RollingUpdate
    rollingUpdate:
      maxUnavailable: 25%
      maxSurge: 25%
  revisionHistoryLimit: 10
  progressDeadlineSeconds: 600
---
kind: Service
apiVersion: v1
metadata:
  name: mysqlrouter
  namespace: middleware
  labels:
    app: mysqlrouter
spec:
  ports:
    - name: tcp-6446
      protocol: TCP
      port: 6446
      targetPort: 6446
    - name: tcp-6447
      protocol: TCP
      port: 6447
      targetPort: 6447
  selector:
    app: mysqlrouter
  type: NodePort