MySQL运维-容器化部署

清峰大约 7 分钟

MySQL运维-容器化部署

业务场景引入

容器化部署已成为现代数据库运维的重要方式：

快速部署：标准化环境快速创建MySQL实例
资源隔离：多租户环境资源隔离和管理
弹性伸缩：根据负载自动调整资源分配
DevOps集成：与CI/CD流水线无缝集成

Docker部署MySQL

基础容器部署

# 自定义MySQL Dockerfile
FROM mysql:8.0

# 设置环境变量
ENV MYSQL_ROOT_PASSWORD=root_password
ENV MYSQL_DATABASE=ecommerce
ENV MYSQL_USER=app_user
ENV MYSQL_PASSWORD=app_password

# 复制配置文件
COPY my.cnf /etc/mysql/conf.d/custom.cnf

# 复制初始化脚本
COPY init-scripts/ /docker-entrypoint-initdb.d/

# 创建数据目录
RUN mkdir -p /var/lib/mysql-files

# 设置权限
RUN chown -R mysql:mysql /var/lib/mysql-files

# 暴露端口
EXPOSE 3306

# docker-compose.yml
version: '3.8'

services:
  mysql-master:
    build: .
    container_name: mysql-master
    environment:
      MYSQL_ROOT_PASSWORD: root_password
      MYSQL_DATABASE: ecommerce
      MYSQL_USER: app_user
      MYSQL_PASSWORD: app_password
    ports:
      - "3306:3306"
    volumes:
      - mysql_data:/var/lib/mysql
      - mysql_logs:/var/log/mysql
      - ./conf:/etc/mysql/conf.d
      - ./init-scripts:/docker-entrypoint-initdb.d
    networks:
      - mysql_network
    restart: unless-stopped
    command: --server-id=1 --log-bin=mysql-bin --gtid-mode=ON --enforce-gtid-consistency=ON

  mysql-slave:
    image: mysql:8.0
    container_name: mysql-slave
    environment:
      MYSQL_ROOT_PASSWORD: root_password
    ports:
      - "3307:3306"
    volumes:
      - mysql_slave_data:/var/lib/mysql
      - ./conf:/etc/mysql/conf.d
    networks:
      - mysql_network
    restart: unless-stopped
    command: --server-id=2 --read-only=1 --log-bin=mysql-bin --gtid-mode=ON --enforce-gtid-consistency=ON
    depends_on:
      - mysql-master

volumes:
  mysql_data:
  mysql_slave_data:
  mysql_logs:

networks:
  mysql_network:
    driver: bridge

配置文件优化

# conf/my.cnf - 容器化MySQL配置
[mysqld]
# 基础配置
user = mysql
port = 3306
socket = /var/run/mysqld/mysqld.sock
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp

# 字符集配置
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# 连接配置
max_connections = 1000
max_connect_errors = 1000
table_open_cache = 1024
max_allowed_packet = 64M

# InnoDB配置
innodb_buffer_pool_size = 1G
innodb_buffer_pool_instances = 4
innodb_log_file_size = 256M
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

# 复制配置
gtid-mode = ON
enforce-gtid-consistency = ON
binlog-format = ROW
sync_binlog = 1

# 安全配置
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO
skip-name-resolve = 1

# 日志配置
log-error = /var/log/mysql/error.log
slow-query-log = 1
slow-query-log-file = /var/log/mysql/slow.log
long_query_time = 2

[client]
default-character-set = utf8mb4

初始化脚本

-- init-scripts/01-create-user.sql
-- 创建应用用户
CREATE USER IF NOT EXISTS 'app_user'@'%' IDENTIFIED BY 'app_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON ecommerce.* TO 'app_user'@'%';

-- 创建复制用户
CREATE USER IF NOT EXISTS 'repl_user'@'%' IDENTIFIED BY 'repl_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';

-- 创建监控用户
CREATE USER IF NOT EXISTS 'monitor_user'@'%' IDENTIFIED BY 'monitor_password';
GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'monitor_user'@'%';
GRANT SELECT ON performance_schema.* TO 'monitor_user'@'%';

FLUSH PRIVILEGES;

-- init-scripts/02-create-tables.sql
USE ecommerce;

CREATE TABLE IF NOT EXISTS users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    status ENUM('PENDING', 'PAID', 'SHIPPED', 'DELIVERED', 'CANCELLED') DEFAULT 'PENDING',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    INDEX idx_user_id (user_id),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Kubernetes部署

StatefulSet部署

# mysql-statefulset.yaml
apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: mysql
  namespace: database
spec:
  serviceName: mysql-headless
  replicas: 3
  selector:
    matchLabels:
      app: mysql
  template:
    metadata:
      labels:
        app: mysql
    spec:
      containers:
      - name: mysql
        image: mysql:8.0
        ports:
        - containerPort: 3306
          name: mysql
        env:
        - name: MYSQL_ROOT_PASSWORD
          valueFrom:
            secretKeyRef:
              name: mysql-secret
              key: root-password
        - name: MYSQL_DATABASE
          value: "ecommerce"
        volumeMounts:
        - name: mysql-data
          mountPath: /var/lib/mysql
        - name: mysql-config
          mountPath: /etc/mysql/conf.d
        resources:
          requests:
            memory: "2Gi"
            cpu: "1000m"
          limits:
            memory: "4Gi"
            cpu: "2000m"
        livenessProbe:
          exec:
            command:
            - mysqladmin
            - ping
            - -h
            - localhost
          initialDelaySeconds: 30
          periodSeconds: 10
        readinessProbe:
          exec:
            command:
            - mysql
            - -h
            - localhost
            - -e
            - "SELECT 1"
          initialDelaySeconds: 5
          periodSeconds: 2
      volumes:
      - name: mysql-config
        configMap:
          name: mysql-config
  volumeClaimTemplates:
  - metadata:
      name: mysql-data
    spec:
      accessModes: ["ReadWriteOnce"]
      storageClassName: "ssd"
      resources:
        requests:
          storage: 100Gi

---
# 无头服务
apiVersion: v1
kind: Service
metadata:
  name: mysql-headless
  namespace: database
spec:
  clusterIP: None
  selector:
    app: mysql
  ports:
  - port: 3306
    targetPort: 3306

---
# 对外服务
apiVersion: v1
kind: Service
metadata:
  name: mysql-service
  namespace: database
spec:
  selector:
    app: mysql
  ports:
  - port: 3306
    targetPort: 3306
    nodePort: 30306
  type: NodePort

ConfigMap和Secret

# mysql-config.yaml
apiVersion: v1
kind: ConfigMap
metadata:
  name: mysql-config
  namespace: database
data:
  my.cnf: |
    [mysqld]
    server-id = ${HOSTNAME##*-}
    log-bin = mysql-bin
    gtid-mode = ON
    enforce-gtid-consistency = ON
    binlog-format = ROW
    
    # 性能配置
    innodb_buffer_pool_size = 2G
    innodb_buffer_pool_instances = 4
    max_connections = 1000
    
    # 字符集
    character-set-server = utf8mb4
    collation-server = utf8mb4_unicode_ci

---
apiVersion: v1
kind: Secret
metadata:
  name: mysql-secret
  namespace: database
type: Opaque
data:
  root-password: cm9vdF9wYXNzd29yZA==  # base64编码的密码
  app-password: YXBwX3Bhc3N3b3Jk

Operator部署

# mysql-operator.yaml
apiVersion: mysql.oracle.com/v2
kind: InnoDBCluster
metadata:
  name: mysql-cluster
  namespace: database
spec:
  secretName: mysql-secret
  tlsUseSelfSigned: true
  instances: 3
  router:
    instances: 2
  datadirVolumeClaimTemplate:
    accessModes:
      - ReadWriteOnce
    resources:
      requests:
        storage: 100Gi
    storageClassName: ssd
  mycnf: |
    [mysqld]
    innodb_buffer_pool_size = 2G
    max_connections = 1000
    character-set-server = utf8mb4
    collation-server = utf8mb4_unicode_ci

容器监控与管理

Prometheus监控

# mysql-exporter.yaml
apiVersion: apps/v1
kind: Deployment
metadata:
  name: mysql-exporter
  namespace: database
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: "monitor_user:monitor_password@(mysql-service:3306)/"
        resources:
          requests:
            memory: "64Mi"
            cpu: "50m"
          limits:
            memory: "128Mi"
            cpu: "100m"

---
apiVersion: v1
kind: Service
metadata:
  name: mysql-exporter-service
  namespace: database
  labels:
    app: mysql-exporter
spec:
  selector:
    app: mysql-exporter
  ports:
  - port: 9104
    targetPort: 9104

健康检查脚本

#!/bin/bash
# 容器MySQL健康检查

check_mysql_container() {
    local container_name=$1
    
    echo "检查容器 $container_name 健康状态..."
    
    # 检查容器运行状态
    container_status=$(docker ps --filter "name=$container_name" --format "{{.Status}}")
    echo "容器状态: $container_status"
    
    # 检查MySQL服务
    docker exec $container_name mysqladmin ping -h localhost
    if [ $? -eq 0 ]; then
        echo "✓ MySQL服务正常"
    else
        echo "✗ MySQL服务异常"
        return 1
    fi
    
    # 检查复制状态（如果是从库）
    if [[ "$container_name" == *"slave"* ]]; then
        slave_status=$(docker exec $container_name mysql -e "SHOW SLAVE STATUS\G" | grep -E "(Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master)")
        echo "复制状态:"
        echo "$slave_status"
    fi
    
    # 检查资源使用
    docker stats --no-stream $container_name
}

# 检查所有MySQL容器
for container in $(docker ps --filter "ancestor=mysql" --format "{{.Names}}"); do
    check_mysql_container $container
    echo "---"
done

自动化部署脚本

#!/usr/bin/env python3
import docker
import yaml
import time
import mysql.connector

class MySQLContainerManager:
    def __init__(self):
        self.client = docker.from_env()
    
    def deploy_mysql_cluster(self, config_file):
        """部署MySQL集群"""
        with open(config_file, 'r') as f:
            config = yaml.safe_load(f)
        
        # 创建网络
        network_name = config['network']['name']
        try:
            network = self.client.networks.get(network_name)
        except docker.errors.NotFound:
            network = self.client.networks.create(
                network_name,
                driver=config['network']['driver']
            )
        
        # 部署主库
        master_config = config['services']['master']
        master_container = self._deploy_container(
            name=master_config['name'],
            image=master_config['image'],
            environment=master_config['environment'],
            ports=master_config['ports'],
            volumes=master_config['volumes'],
            network=network_name,
            command=master_config.get('command')
        )
        
        # 等待主库启动
        self._wait_for_mysql(master_config['name'])
        
        # 配置复制用户
        self._setup_replication_user(master_config)
        
        # 部署从库
        for slave_config in config['services']['slaves']:
            slave_container = self._deploy_container(
                name=slave_config['name'],
                image=slave_config['image'],
                environment=slave_config['environment'],
                ports=slave_config['ports'],
                volumes=slave_config['volumes'],
                network=network_name,
                command=slave_config.get('command')
            )
            
            # 等待从库启动
            self._wait_for_mysql(slave_config['name'])
            
            # 配置主从复制
            self._setup_replication(master_config, slave_config)
    
    def _deploy_container(self, name, image, environment, ports, volumes, network, command=None):
        """部署单个容器"""
        try:
            # 检查容器是否已存在
            existing_container = self.client.containers.get(name)
            print(f"容器 {name} 已存在，停止并删除...")
            existing_container.stop()
            existing_container.remove()
        except docker.errors.NotFound:
            pass
        
        # 创建并启动容器
        container = self.client.containers.run(
            image=image,
            name=name,
            environment=environment,
            ports=ports,
            volumes=volumes,
            network=network,
            command=command,
            detach=True,
            restart_policy={"Name": "unless-stopped"}
        )
        
        print(f"容器 {name} 部署成功")
        return container
    
    def _wait_for_mysql(self, container_name, timeout=60):
        """等待MySQL服务启动"""
        start_time = time.time()
        while time.time() - start_time < timeout:
            try:
                result = self.client.containers.get(container_name).exec_run(
                    "mysqladmin ping -h localhost -u root -proot_password"
                )
                if result.exit_code == 0:
                    print(f"MySQL容器 {container_name} 启动成功")
                    return True
            except:
                pass
            time.sleep(2)
        
        raise Exception(f"MySQL容器 {container_name} 启动超时")
    
    def _setup_replication_user(self, master_config):
        """在主库创建复制用户"""
        container_name = master_config['name']
        
        mysql_cmd = [
            "mysql", "-u", "root", f"-p{master_config['environment']['MYSQL_ROOT_PASSWORD']}",
            "-e", "CREATE USER IF NOT EXISTS 'repl'@'%' IDENTIFIED BY 'repl_password'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'; FLUSH PRIVILEGES;"
        ]
        
        result = self.client.containers.get(container_name).exec_run(mysql_cmd)
        if result.exit_code == 0:
            print("复制用户创建成功")
        else:
            raise Exception(f"复制用户创建失败: {result.output.decode()}")
    
    def _setup_replication(self, master_config, slave_config):
        """配置主从复制"""
        # 获取主库状态
        master_status_cmd = [
            "mysql", "-u", "root", f"-p{master_config['environment']['MYSQL_ROOT_PASSWORD']}",
            "-e", "SHOW MASTER STATUS;"
        ]
        
        result = self.client.containers.get(master_config['name']).exec_run(master_status_cmd)
        master_status = result.output.decode().strip().split('\n')[-1].split('\t')
        
        binlog_file = master_status[0]
        binlog_pos = master_status[1]
        
        # 配置从库
        slave_cmd = [
            "mysql", "-u", "root", f"-p{slave_config['environment']['MYSQL_ROOT_PASSWORD']}",
            "-e", f"""
            CHANGE MASTER TO
                MASTER_HOST='{master_config['name']}',
                MASTER_USER='repl',
                MASTER_PASSWORD='repl_password',
                MASTER_LOG_FILE='{binlog_file}',
                MASTER_LOG_POS={binlog_pos};
            START SLAVE;
            """
        ]
        
        result = self.client.containers.get(slave_config['name']).exec_run(slave_cmd)
        if result.exit_code == 0:
            print(f"从库 {slave_config['name']} 复制配置成功")
        else:
            raise Exception(f"从库复制配置失败: {result.output.decode()}")

# 使用示例
if __name__ == "__main__":
    manager = MySQLContainerManager()
    manager.deploy_mysql_cluster('mysql-cluster-config.yaml')

数据持久化

存储卷管理

# storage-class.yaml
apiVersion: storage.k8s.io/v1
kind: StorageClass
metadata:
  name: mysql-ssd
provisioner: kubernetes.io/aws-ebs
parameters:
  type: gp3
  iops: "3000"
  throughput: "125"
volumeBindingMode: WaitForFirstConsumer
allowVolumeExpansion: true

---
# 持久化卷声明模板
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: mysql-data-pvc
  namespace: database
spec:
  accessModes:
    - ReadWriteOnce
  resources:
    requests:
      storage: 100Gi
  storageClassName: mysql-ssd

备份策略

#!/bin/bash
# 容器MySQL备份脚本

CONTAINER_NAME="mysql-master"
BACKUP_DIR="/backup/mysql-container"
DATE=$(date +%Y%m%d_%H%M%S)

# 创建备份目录
mkdir -p ${BACKUP_DIR}/${DATE}

# 容器内备份
docker exec ${CONTAINER_NAME} mysqldump \
    --single-transaction \
    --routines \
    --triggers \
    --all-databases \
    --master-data=2 | gzip > ${BACKUP_DIR}/${DATE}/full_backup_${DATE}.sql.gz

# 备份配置文件
docker exec ${CONTAINER_NAME} cat /etc/mysql/my.cnf > ${BACKUP_DIR}/${DATE}/my.cnf

# 验证备份
if [ -f "${BACKUP_DIR}/${DATE}/full_backup_${DATE}.sql.gz" ]; then
    echo "备份成功: ${BACKUP_DIR}/${DATE}/full_backup_${DATE}.sql.gz"
    
    # 上传到对象存储
    aws s3 cp ${BACKUP_DIR}/${DATE}/full_backup_${DATE}.sql.gz \
        s3://mysql-backup-bucket/container-backups/
else
    echo "备份失败"
    exit 1
fi

# 清理本地备份（保留7天）
find ${BACKUP_DIR} -type d -name "20*" -mtime +7 -exec rm -rf {} \;

总结与最佳实践

容器化优势

标准化部署：统一的镜像和配置
资源隔离：更好的多租户支持
弹性伸缩：根据负载动态调整
DevOps集成：与CI/CD流水线集成

注意事项

数据持久化：确保数据卷的高可用性
性能调优：容器资源限制和MySQL参数优化
网络安全：容器间网络隔离和访问控制
监控运维：完善的监控和日志收集

容器化部署为MySQL提供了更灵活的部署和管理方式，但需要注意数据持久化和性能优化。