云原生数据库架构设计:Kubernetes环境下MySQL高可用集群部署与运维最佳实践

 
更多

云原生数据库架构设计:Kubernetes环境下MySQL高可用集群部署与运维最佳实践

引言

随着云原生技术的快速发展,Kubernetes已成为容器编排的事实标准。在云原生环境下,数据库作为应用的核心组件,其高可用性和稳定性至关重要。MySQL作为最流行的开源关系型数据库,在Kubernetes环境中部署和运维面临着新的挑战和机遇。

本文将深入探讨如何在Kubernetes环境中构建MySQL高可用集群,涵盖架构设计、部署实施、故障处理、数据保护等关键环节,为生产环境提供完整的解决方案和最佳实践。

云原生数据库架构设计原则

容器化数据库的挑战

传统数据库部署模式与容器化部署存在显著差异:

  • 有状态特性:数据库需要持久化存储,与容器的无状态特性相冲突
  • 网络要求:数据库对网络延迟和稳定性要求极高
  • 资源配置:需要精确的CPU、内存和存储资源配置
  • 数据安全:敏感数据的加密和访问控制需求

高可用架构设计要点

在Kubernetes中实现MySQL高可用需要考虑以下关键要素:

  1. 主从复制架构:确保数据冗余和读写分离
  2. 自动故障检测:实时监控数据库实例状态
  3. 快速故障切换:最小化服务中断时间
  4. 数据一致性保障:确保故障切换后数据完整性
  5. 弹性扩缩容:根据负载动态调整资源

Kubernetes环境下的MySQL高可用架构

架构组件说明

典型的Kubernetes MySQL高可用架构包含以下组件:

# 架构概览
apiVersion: v1
kind: Namespace
metadata:
  name: mysql-cluster
---
# MySQL主节点
apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: mysql-primary
  namespace: mysql-cluster
spec:
  serviceName: mysql-primary
  replicas: 1
  selector:
    matchLabels:
      app: mysql-primary
  template:
    metadata:
      labels:
        app: mysql-primary
    spec:
      containers:
      - name: mysql
        image: mysql:8.0
        ports:
        - containerPort: 3306
        env:
        - name: MYSQL_ROOT_PASSWORD
          valueFrom:
            secretKeyRef:
              name: mysql-secret
              key: root-password
        volumeMounts:
        - name: mysql-data
          mountPath: /var/lib/mysql
  volumeClaimTemplates:
  - metadata:
      name: mysql-data
    spec:
      accessModes: ["ReadWriteOnce"]
      resources:
        requests:
          storage: 100Gi
---
# MySQL从节点
apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: mysql-replica
  namespace: mysql-cluster
spec:
  serviceName: mysql-replica
  replicas: 2
  selector:
    matchLabels:
      app: mysql-replica
  template:
    metadata:
      labels:
        app: mysql-replica
    spec:
      containers:
      - name: mysql
        image: mysql:8.0
        ports:
        - containerPort: 3306
        env:
        - name: MYSQL_ROOT_PASSWORD
          valueFrom:
            secretKeyRef:
              name: mysql-secret
              key: root-password
        - name: MYSQL_REPLICATION_USER
          value: repl_user
        - name: MYSQL_REPLICATION_PASSWORD
          valueFrom:
            secretKeyRef:
              name: mysql-secret
              key: repl-password
        volumeMounts:
        - name: mysql-data
          mountPath: /var/lib/mysql
  volumeClaimTemplates:
  - metadata:
      name: mysql-data
    spec:
      accessModes: ["ReadWriteOnce"]
      resources:
        requests:
          storage: 100Gi

存储设计考虑

在Kubernetes中,MySQL的持久化存储设计需要特别注意:

# 动态存储配置
apiVersion: storage.k8s.io/v1
kind: StorageClass
metadata:
  name: mysql-storage
provisioner: kubernetes.io/aws-ebs
parameters:
  type: gp2
  fsType: ext4
reclaimPolicy: Retain
allowVolumeExpansion: true
volumeBindingMode: WaitForFirstConsumer
---
# 持久卷声明
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: mysql-pvc
  namespace: mysql-cluster
spec:
  accessModes:
    - ReadWriteOnce
  storageClassName: mysql-storage
  resources:
    requests:
      storage: 100Gi

MySQL主从复制配置

主节点配置

主节点需要启用二进制日志并配置复制用户:

# /etc/mysql/conf.d/master.cnf
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW
log-slave-updates = 1
gtid-mode = ON
enforce-gtid-consistency = ON
binlog-checksum = CRC32
master-info-repository = TABLE
relay-log-info-repository = TABLE
binlog-rows-query-log-events = 1
report-host = mysql-primary-0.mysql-primary.mysql-cluster.svc.cluster.local

从节点配置

从节点配置需要指定主节点信息:

# /etc/mysql/conf.d/slave.cnf
[mysqld]
server-id = 2
log-bin = mysql-bin
binlog-format = ROW
log-slave-updates = 1
gtid-mode = ON
enforce-gtid-consistency = ON
read-only = 1
relay-log = relay-bin
report-host = mysql-replica-0.mysql-replica.mysql-cluster.svc.cluster.local

复制用户创建

在主节点上创建复制用户:

CREATE USER 'repl_user'@'%' IDENTIFIED BY 'repl_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
FLUSH PRIVILEGES;

自动故障检测与切换

健康检查探针配置

配置合理的健康检查探针是实现自动故障检测的基础:

# 健康检查配置
livenessProbe:
  exec:
    command:
    - mysqladmin
    - ping
    - -h
    - localhost
    - -u
    - root
    - -p${MYSQL_ROOT_PASSWORD}
  initialDelaySeconds: 30
  periodSeconds: 10
  timeoutSeconds: 5
  failureThreshold: 3

readinessProbe:
  exec:
    command:
    - mysql
    - -h
    - localhost
    - -u
    - root
    - -p${MYSQL_ROOT_PASSWORD}
    - -e
    - SELECT 1
  initialDelaySeconds: 5
  periodSeconds: 2
  timeoutSeconds: 1

Orchestrator配置

使用Orchestrator实现MySQL集群的自动故障检测和切换:

# Orchestrator部署配置
apiVersion: apps/v1
kind: Deployment
metadata:
  name: orchestrator
  namespace: mysql-cluster
spec:
  replicas: 1
  selector:
    matchLabels:
      app: orchestrator
  template:
    metadata:
      labels:
        app: orchestrator
    spec:
      containers:
      - name: orchestrator
        image: openarkcode/orchestrator:latest
        ports:
        - containerPort: 3000
        volumeMounts:
        - name: config
          mountPath: /etc/orchestrator.conf.json
          subPath: orchestrator.conf.json
        - name: data
          mountPath: /var/lib/orchestrator
      volumes:
      - name: config
        configMap:
          name: orchestrator-config
      - name: data
        emptyDir: {}
---
# Orchestrator配置文件
apiVersion: v1
kind: ConfigMap
metadata:
  name: orchestrator-config
  namespace: mysql-cluster
data:
  orchestrator.conf.json: |
    {
      "Debug": false,
      "ListenAddress": ":3000",
      "MySQLTopologyUser": "orchestrator",
      "MySQLTopologyPassword": "orchestrator_password",
      "BackendDB": "sqlite",
      "SQLite3DataFile": "/var/lib/orchestrator/orchestrator.db",
      "RaftEnabled": true,
      "RaftDataDir": "/var/lib/orchestrator",
      "RaftBind": "0.0.0.0:10008",
      "DefaultInstancePort": 3306,
      "DiscoverByShowSlaveHosts": true,
      "InstancePollSeconds": 5,
      "UnseenInstanceForgetHours": 240,
      "SnapshotTopologiesIntervalHours": 0,
      "FailMasterPromotionIfSQLThreadNotUpToDate": true,
      "FailMasterPromotionOnLagMinutes": 10,
      "RecoveryPeriodBlockSeconds": 3600,
      "RecoveryIgnoreHostnameFilters": [],
      "DetachLostSlavesAfterMasterFailover": true,
      "ApplyMySQLPromotionAfterMasterFailover": true,
      "PreventCrossDataCenterMasterFailover": false,
      "MasterFailoverLostInstancesDowntimeMinutes": 10
    }

读写分离配置

ProxySQL配置

使用ProxySQL实现读写分离和负载均衡:

# ProxySQL部署
apiVersion: apps/v1
kind: Deployment
metadata:
  name: proxysql
  namespace: mysql-cluster
spec:
  replicas: 1
  selector:
    matchLabels:
      app: proxysql
  template:
    metadata:
      labels:
        app: proxysql
    spec:
      containers:
      - name: proxysql
        image: severalnines/proxysql:latest
        ports:
        - containerPort: 3306
        - containerPort: 6032
        volumeMounts:
        - name: config
          mountPath: /etc/proxysql.cnf
          subPath: proxysql.cnf
      volumes:
      - name: config
        configMap:
          name: proxysql-config
---
# ProxySQL配置
apiVersion: v1
kind: ConfigMap
metadata:
  name: proxysql-config
  namespace: mysql-cluster
data:
  proxysql.cnf: |
    datadir="/var/lib/proxysql"
    
    admin_variables=
    {
      admin_credentials="admin:admin"
      mysql_ifaces="0.0.0.0:6032"
    }
    
    mysql_variables=
    {
      threads=4
      max_connections=2048
      default_query_delay=0
      default_query_timeout=36000000
      have_compress=true
      poll_timeout=2000
      interfaces="0.0.0.0:3306"
      default_schema="information_schema"
      stacksize=1048576
      server_version="5.7.0"
      connect_timeout_server=3000
      monitor_history=600000
      monitor_connect_interval=60000
      monitor_ping_interval=10000
      ping_interval_server=120000
      ping_timeout_server=500
      commands_stats=true
      sessions_sort=true
      connect_retries_on_failure=10
    }
    
    mysql_servers =
    (
      { address="mysql-primary-0.mysql-primary", port=3306, hostgroup=1, max_connections=1000 },
      { address="mysql-replica-0.mysql-replica", port=3306, hostgroup=2, max_connections=1000 },
      { address="mysql-replica-1.mysql-replica", port=3306, hostgroup=2, max_connections=1000 }
    )
    
    mysql_users:
    (
      { username = "app_user", password = "app_password", default_hostgroup = 1, active = 1 }
    )
    
    mysql_query_rules:
    (
      {
        rule_id=1
        active=1
        match_pattern="^SELECT.*FOR UPDATE$"
        destination_hostgroup=1
        apply=1
      },
      {
        rule_id=2
        active=1
        match_pattern="^SELECT"
        destination_hostgroup=2
        apply=1
      }
    )

应用连接配置

应用程序通过ProxySQL连接数据库:

# Python应用连接示例
import pymysql

# 通过ProxySQL连接
connection = pymysql.connect(
    host='proxysql-service.mysql-cluster.svc.cluster.local',
    port=3306,
    user='app_user',
    password='app_password',
    database='myapp',
    charset='utf8mb4',
    autocommit=True
)

try:
    with connection.cursor() as cursor:
        # 写操作会自动路由到主节点
        cursor.execute("INSERT INTO users (name, email) VALUES (%s, %s)", 
                      ('John Doe', 'john@example.com'))
        
        # 读操作会自动路由到从节点
        cursor.execute("SELECT * FROM users WHERE id = %s", (1,))
        result = cursor.fetchone()
        print(result)
finally:
    connection.close()

数据备份与恢复策略

XtraBackup配置

使用Percona XtraBackup进行热备份:

# 备份Job配置
apiVersion: batch/v1
kind: Job
metadata:
  name: mysql-backup
  namespace: mysql-cluster
spec:
  template:
    spec:
      containers:
      - name: xtrabackup
        image: percona/percona-xtradb-cluster-operator:1.10.0-xtrabackup
        command:
        - bash
        - -c
        - |
          set -ex
          xtrabackup --backup --datadir=/var/lib/mysql \
            --target-dir=/backup/$(date +%Y%m%d-%H%M%S) \
            --user=root --password=$MYSQL_ROOT_PASSWORD \
            --host=mysql-primary-0.mysql-primary
          tar -czf /backup/backup-$(date +%Y%m%d-%H%M%S).tar.gz /backup/$(date +%Y%m%d-%H%M%S)
        env:
        - name: MYSQL_ROOT_PASSWORD
          valueFrom:
            secretKeyRef:
              name: mysql-secret
              key: root-password
        volumeMounts:
        - name: mysql-data
          mountPath: /var/lib/mysql
        - name: backup-storage
          mountPath: /backup
      restartPolicy: Never
      volumes:
      - name: mysql-data
        persistentVolumeClaim:
          claimName: mysql-primary-mysql-data-0
      - name: backup-storage
        persistentVolumeClaim:
          claimName: backup-pvc

定时备份配置

使用CronJob实现定时备份:

# 定时备份配置
apiVersion: batch/v1
kind: CronJob
metadata:
  name: mysql-backup-cron
  namespace: mysql-cluster
spec:
  schedule: "0 2 * * *"  # 每天凌晨2点执行
  jobTemplate:
    spec:
      template:
        spec:
          containers:
          - name: backup
            image: percona/percona-xtradb-cluster-operator:1.10.0-xtrabackup
            command:
            - bash
            - -c
            - |
              DATE=$(date +%Y%m%d-%H%M%S)
              BACKUP_DIR="/backup/$DATE"
              mkdir -p $BACKUP_DIR
              
              # 执行备份
              xtrabackup --backup --datadir=/var/lib/mysql \
                --target-dir=$BACKUP_DIR \
                --user=root --password=$MYSQL_ROOT_PASSWORD \
                --host=mysql-primary-0.mysql-primary
              
              # 创建压缩包
              tar -czf "/backup/backup-$DATE.tar.gz" $BACKUP_DIR
              
              # 清理7天前的备份
              find /backup -name "backup-*.tar.gz" -mtime +7 -delete
            env:
            - name: MYSQL_ROOT_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: mysql-secret
                  key: root-password
            volumeMounts:
            - name: mysql-data
              mountPath: /var/lib/mysql
            - name: backup-storage
              mountPath: /backup
          restartPolicy: OnFailure
          volumes:
          - name: mysql-data
            persistentVolumeClaim:
              claimName: mysql-primary-mysql-data-0
          - name: backup-storage
            persistentVolumeClaim:
              claimName: backup-pvc

恢复流程

数据恢复需要谨慎操作:

#!/bin/bash
# MySQL恢复脚本

BACKUP_FILE="/backup/backup-20231201-143000.tar.gz"
RESTORE_DIR="/tmp/restore"

# 解压备份文件
tar -xzf $BACKUP_FILE -C /tmp

# 停止MySQL服务
kubectl scale statefulset mysql-primary --replicas=0 -n mysql-cluster

# 准备备份数据
xtrabackup --prepare --target-dir=$RESTORE_DIR

# 恢复数据
rm -rf /var/lib/mysql/*
xtrabackup --copy-back --target-dir=$RESTORE_DIR

# 调整权限
chown -R mysql:mysql /var/lib/mysql

# 启动MySQL服务
kubectl scale statefulset mysql-primary --replicas=1 -n mysql-cluster

监控与告警配置

Prometheus监控配置

配置MySQL监控指标收集:

# ServiceMonitor配置
apiVersion: monitoring.coreos.com/v1
kind: ServiceMonitor
metadata:
  name: mysql-monitor
  namespace: mysql-cluster
spec:
  selector:
    matchLabels:
      app: mysql-exporter
  endpoints:
  - port: metrics
    interval: 30s
---
# MySQL Exporter部署
apiVersion: apps/v1
kind: Deployment
metadata:
  name: mysql-exporter
  namespace: mysql-cluster
spec:
  replicas: 1
  selector:
    matchLabels:
      app: mysql-exporter
  template:
    metadata:
      labels:
        app: mysql-exporter
    spec:
      containers:
      - name: mysql-exporter
        image: prom/mysqld-exporter:latest
        ports:
        - containerPort: 9104
        env:
        - name: DATA_SOURCE_NAME
          value: "root:$(MYSQL_ROOT_PASSWORD)@(mysql-primary-0.mysql-primary:3306)/"
        envFrom:
        - secretRef:
            name: mysql-secret
---
# 告警规则
apiVersion: monitoring.coreos.com/v1
kind: PrometheusRule
metadata:
  name: mysql-rules
  namespace: mysql-cluster
spec:
  groups:
  - name: mysql.rules
    rules:
    - alert: MySQLDown
      expr: mysql_up == 0
      for: 5m
      labels:
        severity: critical
      annotations:
        summary: "MySQL instance is down"
        description: "MySQL instance {{ $labels.instance }} is down"
    
    - alert: MySQLReplicationLag
      expr: mysql_slave_status_seconds_behind_master > 60
      for: 2m
      labels:
        severity: warning
      annotations:
        summary: "MySQL replication lag detected"
        description: "MySQL slave {{ $labels.instance }} is {{ $value }} seconds behind master"
    
    - alert: MySQLHighConnections
      expr: mysql_global_status_threads_connected / mysql_global_variables_max_connections * 100 > 80
      for: 5m
      labels:
        severity: warning
      annotations:
        summary: "MySQL high connection usage"
        description: "MySQL instance {{ $labels.instance }} connection usage is {{ $value }}%"

Grafana监控面板

创建MySQL监控仪表板:

{
  "dashboard": {
    "title": "MySQL Cluster Overview",
    "panels": [
      {
        "title": "MySQL Uptime",
        "type": "stat",
        "targets": [
          {
            "expr": "mysql_global_status_uptime",
            "legendFormat": "{{instance}}"
          }
        ]
      },
      {
        "title": "Current Connections",
        "type": "graph",
        "targets": [
          {
            "expr": "mysql_global_status_threads_connected",
            "legendFormat": "{{instance}}"
          }
        ]
      },
      {
        "title": "Replication Lag",
        "type": "graph",
        "targets": [
          {
            "expr": "mysql_slave_status_seconds_behind_master",
            "legendFormat": "{{instance}}"
          }
        ]
      },
      {
        "title": "Query Per Second",
        "type": "graph",
        "targets": [
          {
            "expr": "rate(mysql_global_status_queries[5m])",
            "legendFormat": "{{instance}}"
          }
        ]
      }
    ]
  }
}

性能调优策略

MySQL参数调优

针对Kubernetes环境优化MySQL配置:

# 性能优化配置
[mysqld]
# 基础配置
innodb_buffer_pool_size = 2G
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000

# 连接配置
max_connections = 2000
max_connect_errors = 100000
table_open_cache = 4000
table_definition_cache = 2000

# 查询优化
query_cache_type = 0
query_cache_size = 0
tmp_table_size = 256M
max_heap_table_size = 256M

# 日志配置
slow_query_log = 1
long_query_time = 1
log_queries_not_using_indexes = 1

# 复制配置
slave_parallel_workers = 4
slave_parallel_type = LOGICAL_CLOCK
binlog_group_commit_sync_delay = 10000
binlog_group_commit_sync_no_delay_count = 10

Kubernetes资源配置

合理配置资源请求和限制:

# 资源优化配置
apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: mysql-primary
spec:
  template:
    spec:
      containers:
      - name: mysql
        resources:
          requests:
            memory: "4Gi"
            cpu: "2"
          limits:
            memory: "8Gi"
            cpu: "4"
        env:
        - name: MYSQL_ROOT_PASSWORD
          valueFrom:
            secretKeyRef:
              name: mysql-secret
              key: root-password
        securityContext:
          runAsUser: 999
          runAsGroup: 999
          fsGroup: 999

存储性能优化

选择合适的存储类型和配置:

# 高性能存储配置
apiVersion: storage.k8s.io/v1
kind: StorageClass
metadata:
  name: mysql-fast-storage
provisioner: kubernetes.io/aws-ebs
parameters:
  type: io1
  iopsPerGB: "50"
  fsType: ext4
reclaimPolicy: Retain
allowVolumeExpansion: true
volumeBindingMode: WaitForFirstConsumer
mountOptions:
  - noatime
  - nodiratime

安全配置最佳实践

网络安全策略

配置网络策略限制访问:

# 网络策略配置
apiVersion: networking.k8s.io/v1
kind: NetworkPolicy
metadata:
  name: mysql-network-policy
  namespace: mysql-cluster
spec:
  podSelector:
    matchLabels:
      app: mysql
  policyTypes:
  - Ingress
  - Egress
  ingress:
  - from:
    - podSelector:
        matchLabels:
          app: mysql-exporter
    - podSelector:
        matchLabels:
          app: proxysql
    ports:
    - protocol: TCP
      port: 3306
  egress:
  - to:
    - namespaceSelector: {}
    ports:
    - protocol: TCP
      port: 53
    - protocol: UDP
      port: 53

访问控制配置

配置RBAC和用户权限:

# ServiceAccount配置
apiVersion: v1
kind: ServiceAccount
metadata:
  name: mysql-operator
  namespace: mysql-cluster
---
# Role配置
apiVersion: rbac.authorization.k8s.io/v1
kind: Role
metadata:
  name: mysql-role
  namespace: mysql-cluster
rules:
- apiGroups: [""]
  resources: ["pods", "services", "persistentvolumeclaims"]
  verbs: ["get", "list", "watch", "create", "update", "patch", "delete"]
- apiGroups: ["apps"]
  resources: ["statefulsets"]
  verbs: ["get", "list", "watch", "create", "update", "patch", "delete"]
---
# RoleBinding配置
apiVersion: rbac.authorization.k8s.io/v1
kind: RoleBinding
metadata:
  name: mysql-rolebinding
  namespace: mysql-cluster
subjects:
- kind: ServiceAccount
  name: mysql-operator
  namespace: mysql-cluster
roleRef:
  kind: Role
  name: mysql-role
  apiGroup: rbac.authorization.k8s.io

数据加密配置

配置SSL/TLS加密:

# SSL配置
[mysqld]
ssl-ca = /etc/mysql/ssl/ca.pem
ssl-cert = /etc/mysql/ssl/server-cert.pem
ssl-key = /etc/mysql/ssl/server-key.pem
require_secure_transport = ON

运维管理最佳实践

自动化运维脚本

创建常用的运维脚本:

#!/bin/bash
# MySQL集群状态检查脚本

echo "=== MySQL Cluster Status Check ==="

# 检查主节点状态
echo "Primary Node Status:"
kubectl exec -it mysql-primary-0 -n mysql-cluster -- mysql -uroot -p$MYSQL_ROOT_PASSWORD -e "SHOW MASTER STATUS\G"

# 检查从节点状态
echo "Replica Node Status:"
for i in {0..1}; do
  echo "Replica $i:"
  kubectl exec -it mysql-replica-$i -n mysql-cluster -- mysql -uroot -p$MYSQL_ROOT_PASSWORD -e "SHOW SLAVE STATUS\G" | grep -E "Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master"
done

# 检查Pod状态
echo "Pod Status:"
kubectl get pods -n mysql-cluster -o wide

# 检查PVC状态
echo "Storage Status:"
kubectl get pvc -n mysql-cluster

故障排查指南

常见故障排查步骤:

# 1. 检查Pod状态
kubectl get pods -n

打赏

本文固定链接: https://www.cxy163.net/archives/8027 | 绝缘体

该日志由 绝缘体.. 于 2020年08月26日 发表在 未分类 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: 云原生数据库架构设计:Kubernetes环境下MySQL高可用集群部署与运维最佳实践 | 绝缘体
关键字: , , , ,

云原生数据库架构设计:Kubernetes环境下MySQL高可用集群部署与运维最佳实践:等您坐沙发呢!

发表评论


快捷键:Ctrl+Enter