Docker 部署 MySQL

概述

MySQL 是一个开源的关系型数据库管理系统，使用 Docker 部署 MySQL 可以简化安装配置、提高部署效率，并确保环境一致性。本文档介绍如何使用 Docker 部署和管理 MySQL 数据库。

方案一:基础部署

1. 使用官方镜像

# 拉取最新版本的 MySQL 镜像
docker pull mysql:latest

# 运行基础 MySQL 容器
docker run -d \
  --name mysql-server \
  -p 3306:3306 \
  -e MYSQL_ROOT_PASSWORD=your_password \
  mysql:latest

2. 自定义配置部署

创建自定义的 MySQL 配置文件：

# my.cnf
[mysqld]
# 基础配置
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql

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

# 连接配置
max_connections = 1000
max_connect_errors = 100000
open_files_limit = 65535
table_open_cache = 1024
max_allowed_packet = 16M
binlog_cache_size = 1M
max_heap_table_size = 8M
tmp_table_size = 16M

# 查询缓存
query_cache_type = 1
query_cache_size = 16M
query_cache_limit = 2M

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

# InnoDB 配置
innodb_buffer_pool_size = 256M
innodb_log_file_size = 64M
innodb_file_per_table = 1
innodb_open_files = 400
innodb_io_capacity = 400
innodb_flush_method = O_DIRECT

# 安全配置
skip-name-resolve
skip-external-locking

[mysql]
default-character-set = utf8mb4

[client]
default-character-set = utf8mb4

使用自定义配置运行容器：

# 创建配置目录
mkdir -p mysql/conf.d
mkdir -p mysql/data
mkdir -p mysql/logs

# 复制配置文件
cp my.cnf mysql/conf.d/

# 运行容器
docker run -d \
  --name mysql-custom \
  -p 3306:3306 \
  -e MYSQL_ROOT_PASSWORD=your_secure_password \
  -e MYSQL_DATABASE=your_database \
  -e MYSQL_USER=your_user \
  -e MYSQL_PASSWORD=your_password \
  -v $(pwd)/mysql/conf.d:/etc/mysql/conf.d:ro \
  -v $(pwd)/mysql/data:/var/lib/mysql \
  -v $(pwd)/mysql/logs:/var/log/mysql \
  mysql:8.0

方案二:Docker Compose 部署

基础配置

# docker-compose.yml
version: "3.8"

services:
  mysql:
    image: mysql:8.0
    container_name: mysql-server
    restart: unless-stopped
    ports:
      - "3306:3306"
    environment:
      MYSQL_ROOT_PASSWORD: your_root_password
      MYSQL_DATABASE: your_database
      MYSQL_USER: your_user
      MYSQL_PASSWORD: your_password
      MYSQL_CHARACTER_SET_SERVER: utf8mb4
      MYSQL_COLLATION_SERVER: utf8mb4_unicode_ci
    volumes:
      - ./mysql/data:/var/lib/mysql
      - ./mysql/conf.d:/etc/mysql/conf.d:ro
      - ./mysql/logs:/var/log/mysql
      - ./mysql/init:/docker-entrypoint-initdb.d:ro
    networks:
      - database
    command: --default-authentication-plugin=mysql_native_password

networks:
  database:
    driver: bridge

volumes:
  mysql_data:

生产环境配置

# docker-compose.prod.yml
version: "3.8"

services:
  mysql:
    image: mysql:8.0
    container_name: mysql-prod
    restart: unless-stopped
    ports:
      - "3306:3306"
    environment:
      MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD}
      MYSQL_DATABASE: ${MYSQL_DATABASE}
      MYSQL_USER: ${MYSQL_USER}
      MYSQL_PASSWORD: ${MYSQL_PASSWORD}
      MYSQL_CHARACTER_SET_SERVER: utf8mb4
      MYSQL_COLLATION_SERVER: utf8mb4_unicode_ci
    volumes:
      - mysql_data:/var/lib/mysql
      - ./mysql/conf.d:/etc/mysql/conf.d:ro
      - ./mysql/logs:/var/log/mysql
      - ./mysql/backup:/backup
    networks:
      - database
    command: >
      --default-authentication-plugin=mysql_native_password
      --innodb-buffer-pool-size=1G
      --innodb-log-file-size=256M
      --max-connections=1000
      --query-cache-size=32M
    healthcheck:
      test: ["CMD", "mysqladmin", "ping", "-h", "localhost"]
      timeout: 20s
      retries: 10

  mysql-backup:
    image: mysql:8.0
    container_name: mysql-backup
    restart: "no"
    volumes:
      - ./mysql/backup:/backup
      - mysql_data:/var/lib/mysql:ro
    environment:
      MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD}
    command: >
      sh -c "
        mysqldump --all-databases --single-transaction --routines --triggers > /backup/backup_$$(date +%Y%m%d_%H%M%S).sql
      "

networks:
  database:
    driver: bridge

volumes:
  mysql_data:
    driver: local

数据持久化

卷挂载

# 创建数据目录
mkdir -p /opt/mysql/data
mkdir -p /opt/mysql/conf
mkdir -p /opt/mysql/logs

# 运行容器并挂载卷
docker run -d \
  --name mysql-persistent \
  -p 3306:3306 \
  -e MYSQL_ROOT_PASSWORD=your_password \
  -v /opt/mysql/data:/var/lib/mysql \
  -v /opt/mysql/conf:/etc/mysql/conf.d \
  -v /opt/mysql/logs:/var/log/mysql \
  mysql:8.0

数据备份与恢复

# 备份数据库
docker exec mysql-server mysqldump -u root -p your_database > backup.sql

# 备份所有数据库
docker exec mysql-server mysqldump -u root -p --all-databases > all_databases.sql

# 恢复数据库
docker exec -i mysql-server mysql -u root -p your_database < backup.sql

# 使用 Docker 卷备份
docker run --rm -v mysql_data:/data -v $(pwd):/backup alpine tar czf /backup/mysql_backup.tar.gz -C /data .

安全配置

基础安全设置

# 创建安全配置文件
cat > mysql-security.cnf << EOF
[mysqld]
# 禁用远程 root 登录
skip-grant-tables = 0

# 设置最大连接数
max_connections = 100

# 设置连接超时
connect_timeout = 10
wait_timeout = 28800
interactive_timeout = 28800

# 禁用本地文件加载
local_infile = 0

# 设置密码策略
validate_password.policy = MEDIUM
validate_password.length = 8
validate_password.mixed_case_count = 1
validate_password.number_count = 1
validate_password.special_char_count = 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

# 二进制日志
log_bin = mysql-bin
binlog_format = ROW
expire_logs_days = 7
max_binlog_size = 100M
EOF

用户权限管理

-- 创建专用用户
CREATE USER 'app_user'@'%' IDENTIFIED BY 'strong_password';

-- 授予最小权限
GRANT SELECT, INSERT, UPDATE, DELETE ON your_database.* TO 'app_user'@'%';

-- 创建只读用户
CREATE USER 'readonly_user'@'%' IDENTIFIED BY 'readonly_password';
GRANT SELECT ON your_database.* TO 'readonly_user'@'%';

-- 刷新权限
FLUSH PRIVILEGES;

性能优化

内存配置

# 根据服务器内存调整配置
[mysqld]
# InnoDB 缓冲池大小 (建议为总内存的 70-80%)
innodb_buffer_pool_size = 1G

# 查询缓存大小
query_cache_size = 32M
query_cache_type = 1

# 临时表大小
tmp_table_size = 64M
max_heap_table_size = 64M

# 连接缓存
table_open_cache = 2000
table_definition_cache = 1000

网络优化

# 使用 Docker 网络
docker network create mysql-network

# 运行容器并加入网络
docker run -d \
  --name mysql-optimized \
  --network mysql-network \
  -p 3306:3306 \
  -e MYSQL_ROOT_PASSWORD=your_password \
  mysql:8.0

监控与日志

日志配置

# 查看 MySQL 错误日志
docker logs mysql-server

# 查看慢查询日志
docker exec mysql-server tail -f /var/log/mysql/slow.log

# 查看 MySQL 状态
docker exec mysql-server mysqladmin -u root -p status

性能监控

-- 查看当前连接数
SHOW STATUS LIKE 'Threads_connected';

-- 查看查询缓存命中率
SHOW STATUS LIKE 'Qcache_hits';
SHOW STATUS LIKE 'Qcache_inserts';

-- 查看 InnoDB 状态
SHOW ENGINE INNODB STATUS;

-- 查看慢查询
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';

常见问题解决

1. 容器启动失败

# 查看容器日志
docker logs mysql-server

# 检查端口占用
netstat -tlnp | grep 3306

# 检查数据目录权限
ls -la /opt/mysql/data

2. 连接超时

# 检查网络连接
docker exec mysql-server mysqladmin ping -h localhost

# 检查防火墙设置
sudo ufw status
sudo iptables -L

3. 数据损坏恢复

# 停止容器
docker stop mysql-server

# 备份数据目录
cp -r /opt/mysql/data /opt/mysql/data_backup

# 使用 MySQL 修复工具
docker run --rm -v /opt/mysql/data:/var/lib/mysql mysql:8.0 mysqlcheck -r --all-databases

最佳实践

1. 版本选择

  • 生产环境建议使用 LTS 版本 (如 8.0.x)
  • 定期更新到最新的补丁版本
  • 避免使用 latest 标签

2. 资源限制

# 在 docker-compose.yml 中添加资源限制
services:
  mysql:
    # ... 其他配置
    deploy:
      resources:
        limits:
          memory: 2G
          cpus: "2.0"
        reservations:
          memory: 1G
          cpus: "1.0"

3. 备份策略

#!/bin/bash
# 自动备份脚本
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="/opt/mysql/backup"

# 创建备份
docker exec mysql-server mysqldump -u root -p your_database > $BACKUP_DIR/backup_$DATE.sql

# 压缩备份
gzip $BACKUP_DIR/backup_$DATE.sql

# 删除7天前的备份
find $BACKUP_DIR -name "backup_*.sql.gz" -mtime +7 -delete

4. 高可用配置

# 主从复制配置
version: "3.8"

services:
  mysql-master:
    image: mysql:8.0
    container_name: mysql-master
    environment:
      MYSQL_ROOT_PASSWORD: master_password
      MYSQL_REPLICATION_USER: repl_user
      MYSQL_REPLICATION_PASSWORD: repl_password
    volumes:
      - ./master.cnf:/etc/mysql/conf.d/master.cnf:ro
      - master_data:/var/lib/mysql
    networks:
      - mysql_network

  mysql-slave:
    image: mysql:8.0
    container_name: mysql-slave
    environment:
      MYSQL_ROOT_PASSWORD: slave_password
    volumes:
      - ./slave.cnf:/etc/mysql/conf.d/slave.cnf:ro
      - slave_data:/var/lib/mysql
    depends_on:
      - mysql-master
    networks:
      - mysql_network

networks:
  mysql_network:
    driver: bridge

volumes:
  master_data:
  slave_data:

总结

使用 Docker 部署 MySQL 提供了以下优势：

  1. 环境一致性: 确保开发、测试、生产环境的一致性
  2. 快速部署: 简化安装和配置过程
  3. 易于管理: 统一的容器管理方式
  4. 资源隔离: 避免系统依赖冲突
  5. 版本控制: 便于版本升级和回滚

通过合理的配置和最佳实践，可以构建稳定、高效的 MySQL 数据库环境。

最近更新：:
Contributors: Duke