mysql 生成docker 单机

docker compose 

services:
  mysql:
    image: mysql:8.0.36
    container_name: mysql-Sync
    restart: always
    stop_grace_period: 180s          # ★ 默认 10s 会被 SIGKILL（等于断电）；160G buffer pool 刷脏页需要时间
    environment:
      MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD}
      TZ: Asia/Shanghai
    ports:
      - "3306:3306"
    volumes:
      - ./data:/var/lib/mysql                  # 数据目录（含 undo / #innodb_redo / ibdata）
      - ./binlog:/var/lib/binlog         # binlog 落盘到宿主独立目录
      - ./relaylog:/var/lib/relaylog     # relay log 落盘（从库）
      - ./tmp:/var/lib/mysql-tmp               # tmpdir 落盘，避免大排序/临时表写爆 Docker 根盘
      - ./log:/var/log                         # 慢日志等
      - ./conf/:/etc/mysql/conf.d/             # 目录挂载（改文件后 restart 即可生效；新增挂载才需 force-recreate）
      - /etc/timezone:/etc/timezone:ro
      - /etc/localtime:/etc/localtime:ro
    ulimits:
      nofile:
        soft: 1048576
        hard: 1048576
    mem_limit: 220g                  
    mem_swappiness: 0
    cpus: "40"
    logging:
      driver: json-file
      options:
        max-size: "200m"
        max-file: "5"
    healthcheck:
      test: ["CMD-SHELL", 'mysqladmin ping -h127.0.0.1 -uroot -p"$MYSQL_ROOT_PASSWORD" --silent']
      interval: 30s
      timeout: 10s
      retries: 5
      start_period: 180s

my.cnf

[mysqld]

########################  1. 身份 / 时区 / 字符集  ########################
lower_case_table_names = 1
server-id                      = 16              
port                           = 3306
default_time_zone              = '+08:00'       
log_timestamps                 = SYSTEM        
#character_set_server           = utf8mb4
character_set_server           = utf8mb3
#collation_server               = utf8mb4_0900_ai_ci
collation_server               = utf8mb3_general_ci
skip_name_resolve              = ON             

########################  2. 连接 / 线程（40 核）  ########################
max_connections                = 2000
max_connect_errors             = 1000000         # 防连接池重连被误判为恶意主机而拉黑
back_log                       = 4096
thread_cache_size              = 128
open_files_limit               = 1048576         # 需 compose ulimits nofile 同步放开，否则被截断
table_open_cache               = 10000
table_open_cache_instances     = 32
table_definition_cache         = 4000
wait_timeout                   = 1800
interactive_timeout            = 1800
net_read_timeout               = 60
net_write_timeout              = 120
max_allowed_packet             = 512M

########################  3. InnoDB·内存（256G 预算核心）  ########################
innodb_buffer_pool_size        = 160G            # ★ 必须 = instances × chunk(128M) 的整数倍；160G/2G=80 ✔
innodb_buffer_pool_instances   = 16              # 160G ÷ 16 = 10G/实例；改动需重启
innodb_redo_log_capacity       = 16G             # 8.0.30+ 用本参数取代 innodb_log_file_size（不要再写后者）
innodb_log_buffer_size         = 256M            # 大事务/大 blob 减少 redo 刷盘次数
innodb_flush_log_at_trx_commit = 1               # ★ 零丢失红线，禁止改 0/2
innodb_doublewrite             = ON              # ★ 防部分页写，零丢失红线
innodb_flush_method            = O_DIRECT        # 绕开 OS 双缓冲；ZFS/btrfs 请改 fsync
innodb_flush_neighbors         = 1               # SSD/NVMe 必须 0；HDD 可设 1
innodb_io_capacity             = 1000            # 见文末按盘型取值表
innodb_io_capacity_max         = 8000
innodb_max_dirty_pages_pct     = 90
innodb_max_dirty_pages_pct_lwm = 10
innodb_old_blocks_time         = 1000
innodb_adaptive_hash_index     = ON
innodb_stats_persistent        = ON
innodb_autoinc_lock_mode       = 2
innodb_lock_wait_timeout       = 50
innodb_print_all_deadlocks     = ON              # 死锁全量入 error log，排障必备
innodb_undo_log_truncate       = ON
innodb_temp_data_file_path     = ibtmp1:12M:autoextend:max:64G   # 防 ibtmp1 无限膨胀撑爆数据盘
innodb_parallel_read_threads   = 8               # 8.0.14+ 并行聚簇索引读

########################  4. InnoDB·IO / 清理线程（40 核）  ########################
innodb_read_io_threads         = 8
innodb_write_io_threads        = 8
innodb_purge_threads           = 8
innodb_page_cleaners           = 8               # 必须 ≤ innodb_buffer_pool_instances
innodb_thread_concurrency      = 0               # 0 = MySQL 自适应，40 核不要人为设限
innodb_read_ahead_threshold    = 56
innodb_log_write_ahead_size    = 8192            # 4K 扇区盘配 8K，减少 read-on-write

########################  5. 临时表 / 排序 / Join（会话级内存）  ########################
tmpdir                         = /var/lib/mysql-tmp   # ★ 必须挂宿主卷；默认 /tmp 落在 overlay 会写爆 Docker 根盘
internal_tmp_mem_storage_engine = TempTable
temptable_max_ram              = 4G
temptable_max_mmap             = 1G
tmp_table_size                 = 256M
max_heap_table_size            = 256M            # 必须与 tmp_table_size 保持一致
sort_buffer_size               = 4M              # 会话级！2000 连接最坏 2000×4M = 8G
join_buffer_size               = 4M              # 会话级！最坏再 8G
read_buffer_size               = 1M
read_rnd_buffer_size           = 4M

########################  6. Binlog 持久化落盘（本方案重点）  ########################
log_bin                        = /var/lib/binlog/mysql-bin
log_bin_index                  = /var/lib/binlog/mysql-bin.index
binlog_format                  = ROW             # ROW 是 8.0 事实默认，显式写出避免被误改
binlog_row_image               = FULL            # 数据安全优先；磁盘紧张才考虑 MINIMAL（有风险）
binlog_rows_query_log_events   = ON              # 附带原始 SQL，审计/排障友好
binlog_checksum                = CRC32
max_binlog_size                = 1G              # 8.0 单文件上限即 1G
sync_binlog                    = 1               # ★ 每次提交 fsync binlog，零丢失红线
binlog_cache_size              = 2M
binlog_stmt_cache_size         = 1M
binlog_group_commit_sync_delay = 0               # 追求零丢失/低延迟保持 0；压测场景可 10~100(微秒)
binlog_group_commit_sync_no_delay_count = 0
binlog_transaction_compression = OFF             # 8.0.20+ 开启省磁盘与带宽但耗 CPU
binlog_expire_logs_seconds     = 604800          # 保留 7 天；★ 必须长于单次维护窗口
########################  7. GTID / 复制  ########################
gtid_mode                      = ON
enforce_gtid_consistency       = ON
relay_log                      = /var/lib/relaylog/relay-bin   # ★ 固定基名，避免容器重建后主机名变化引发 ERROR 1872
relay_log_recovery             = ON              # 启动时丢弃未回放 relay log 自愈（只读启动参数，改后必须重启）
relay_log_purge                = ON
replica_parallel_workers       = 16              # 从库 MTS 并行回放；0 = 关闭；40 核可 16
replica_preserve_commit_order  = ON              # ★ 保序，避免并行回放的隐式依赖导致主从数据不一致（需 LOGICAL_CLOCK）
replica_max_allowed_packet     = 1G              # ROW 复制大事务/大 blob 必需；★ 必须大于主库 max_allowed_packet（此处 512M ✔）

########################  8. 日志 / 可观测性  ########################
log_error_verbosity            = 2               # 保持 stderr 输出 → 走 docker logs；★ 不要设 log_error=文件
#slow_query_log                 = ON
#slow_query_log_file            = /var/log/mysql-slow.log          # 宿主 ./log/mysql-slow.log
#long_query_time                = 1
min_examined_row_limit         = 100             # 过滤"扫描行数很少但耗时"的噪声
local_infile                   = OFF             # 安全基线
performance_schema             = ON              # mysqld_exporter / 可观测性依赖

########################  9. 客户端段  ########################
[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4