宿主機                         容器內(nèi)
──────────────────────────────────────────
/data/mysql8/conf/  ──bind──? /etc/mysql/conf.d/
  ├── max_allowed_packet.cnf       (由 my.cnf 中 !includedir 自動加載)
  └── performance.cnf

/data/mysql8/data/  ──bind──? /var/lib/mysql/

!includedir /etc/mysql/conf.d/

宿主機文件                     掛載關(guān)系        容器內(nèi)加載路徑
────────────────────────────────────────────────────────────
/data/mysql8/conf/     ──bind mount──?  /etc/mysql/conf.d/
  └─ performance.cnf                      └─ 被 my.cnf 的 !includedir 自動加載

docker inspect mysql8 --format '{{json .Mounts}}' | python3 -m json.tool

[
    {
        "Type": "bind",
        "Source": "/data/mysql8/conf",
        "Destination": "/etc/mysql/conf.d",
        "RW": true
    },
    {
        "Type": "bind",
        "Source": "/data/mysql8/data",
        "Destination": "/var/lib/mysql",
        "RW": true
    }
]

# Step 1: 在宿主機創(chuàng)建配置文件
cat > /data/mysql8/conf/performance.cnf << 'EOF'
[mysqld]
innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances = 2
innodb_log_file_size = 256M
# ... 其他參數(shù) ...
max_allowed_packet = 512M
EOF

# Step 2: 驗證容器內(nèi)可見
docker exec mysql8 cat /etc/mysql/conf.d/performance.cnf

# Step 3: 優(yōu)雅關(guān)閉 + 重啟（innodb_log_file_size 變更需額外處理）
docker exec mysql8 mysql -uroot -p -e "SET GLOBAL innodb_fast_shutdown=0;"
docker exec mysql8 rm -f /var/lib/mysql/ib_logfile*
docker restart mysql8

# Step 4: 驗證參數(shù)已從配置文件加載
docker exec mysql8 mysql -uroot -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
# 預(yù)期: 2147483648 (2G)

# 重啟
docker restart mysql8

# 驗證關(guān)鍵參數(shù)仍為優(yōu)化值
docker exec mysql8 mysql -uroot -p -e "
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'innodb_log_file_size';
SHOW VARIABLES LIKE 'max_allowed_packet';
"

/data/mysql8/conf/
├── max_allowed_packet.cnf    # [mysqld] max_allowed_packet=512M
└── performance.cnf           # [mysqld] 全部 InnoDB/連接/緩存優(yōu)化參數(shù)

值為 0：每秒寫入日志并刷盤（性能最高，可能丟 1 秒數(shù)據(jù)）
值為 1：每次提交都寫入并刷盤（最安全，性能最低）  ← 默認(rèn)
值為 2：每次提交寫入，每秒刷盤（平衡方案）        ← 當(dāng)前

服務(wù)器總內(nèi)存:     7.7 GB
系統(tǒng)預(yù)留:         ~1.0 GB
其他服務(wù):         ~3.0 GB
MySQL Buffer Pool: 2.0 GB
剩余可用:         ~1.7 GB

[mysqld]

# ===== InnoDB 核心優(yōu)化 =====
# Buffer Pool: 最重要參數(shù)，設(shè)為可用內(nèi)存 60-70%
# 當(dāng)前服務(wù)器 7.7G 總內(nèi)存，分配 2G 給 MySQL
innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances = 2

# Redo Log: 增大減少 checkpoint 頻率
innodb_log_file_size = 256M
innodb_log_buffer_size = 64M

# IO 容量: SSD 推薦 1000-2000，HDD 保持 200
innodb_io_capacity = 1000
innodb_io_capacity_max = 3000

# 刷新策略: 1=每次刷盤(最安全), 2=每秒刷盤(平衡)
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

# ===== 連接與線程 =====
thread_cache_size = 32
max_connections = 300
wait_timeout = 3600
interactive_timeout = 3600

# ===== 表緩存 =====
table_open_cache = 4000
table_definition_cache = 4000

# ===== 臨時表 =====
tmp_table_size = 64M
max_heap_table_size = 64M

# ===== 排序與 JOIN 緩沖 (per-session) =====
sort_buffer_size = 512K
join_buffer_size = 512K
read_buffer_size = 256K
read_rnd_buffer_size = 512K

# ===== 慢查詢?nèi)罩?=====
slow_query_log = ON
long_query_time = 2
slow_query_log_file = /var/lib/mysql/slow.log

# ===== Binlog =====
binlog_cache_size = 1M
binlog_expire_logs_seconds = 604800

# ===== 其他 =====
max_allowed_packet = 512M

# 1. 優(yōu)雅關(guān)閉 MySQL (完整刷盤)
docker exec mysql8 mysql -uroot -p -e "SET GLOBAL innodb_fast_shutdown=0;"

# 2. 刪除舊 redo log 文件
docker exec mysql8 rm -f /var/lib/mysql/ib_logfile*

# 3. 重啟容器（會自動創(chuàng)建新大小的 redo log）
docker restart mysql8

# 確認(rèn)所有參數(shù)已生效
docker exec mysql8 mysql -uroot -p -e "
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'innodb_log_file_size';
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
SHOW VARIABLES LIKE 'innodb_io_capacity';
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'max_allowed_packet';
"