1. 目录结构准备

mkdir -p /home/compose/mysql/{data,logs,config}

2. MySQL 配置文件

创建 /home/compose/mysql/config/my.cnf 配置文件：

[mysqld]
# 基本设置
port = 3306
bind-address = 0.0.0.0
socket = /var/run/mysqld/mysqld.sock

# 数据存储设置
datadir = /var/lib/mysql
default-storage-engine = InnoDB

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

# 连接设置
max_connections = 200
max_connect_errors = 6000
open_files_limit = 65535
table_open_cache = 128

# InnoDB 设置
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

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

# 安全设置
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO

3. Docker Compose 配置

创建 /home/compose/mysql/docker-compose.yml 文件：

version: '3.8'

services:
  mysql:
    image: mysql:8.0
    container_name: mysql-production
    restart: always
    ports:
      - "3306:3306"
    volumes:
      - ./data:/var/lib/mysql
      - ./config/my.cnf:/etc/mysql/my.cnf
      - ./logs:/var/log/mysql
      - /etc/localtime:/etc/localtime:ro
    environment:
      MYSQL_ROOT_PASSWORD: "${MYSQL_ROOT_PASSWORD}"
      MYSQL_DATABASE: "${MYSQL_DATABASE}"
      MYSQL_USER: "${MYSQL_USER}"
      MYSQL_PASSWORD: "${MYSQL_PASSWORD}"
      TZ: "Asia/Shanghai"
    command:
      - '--character-set-server=utf8mb4'
      - '--collation-server=utf8mb4_unicode_ci'
      - '--sql-mode=STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO'
    healthcheck:
      test: ["CMD", "mysqladmin", "ping", "-h", "localhost"]
      interval: 30s
      timeout: 10s
      retries: 3
    networks:
      - mysql-network
    security_opt:
      - no-new-privileges:true
    ulimits:
      memlock: -1
    cap_drop:
      - ALL
    cap_add:
      - CHOWN
      - SETGID
      - SETUID
      - DAC_OVERRIDE

volumes:
  mysql-data:
    driver: local

networks:
  mysql-network:
    driver: bridge
    ipam:
      config:
        - subnet: 172.20.0.0/16

4. 环境变量配置

MYSQL_ROOT_PASSWORD=your_strong_root_password_here
MYSQL_DATABASE=your_database_name
MYSQL_USER=your_app_user
MYSQL_PASSWORD=your_app_user_password

5. 权限设置

# 设置目录权限
chmod 755 /home/compose/mysql
chmod 750 /home/compose/mysql/data
chmod 755 /home/compose/mysql/config
chmod 755 /home/compose/mysql/logs

# 设置所有者（根据实际运行docker的用户调整）
chown -R 999:999 /home/compose/mysql/data
chown -R 999:999 /home/compose/mysql/logs

6. 启动和管理命令

启动服务

cd /home/compose/mysql
docker-compose up -d

查看服务状态

docker-compose ps
docker-compose logs -f

停止服务

docker-compose down

7.验证部署

# 检查容器状态
docker-compose ps

# 进入容器检查MySQL状态
docker exec -it mysql-production mysql -u root -p -e "SHOW STATUS LIKE 'Uptime';"

# 检查数据库
docker exec -it mysql-production mysql -u root -p -e "SHOW DATABASES;"