[mysql]定制封装MySQL的docker镜像

前言

基于MySQL的原版镜像做一些个性化配置修改，封装/etc/my.cnf文件到镜像中，并且支持通过环境变量修改innodb_buffer_pool_size、server_id以及自动配置innodb_read_io_threads和innodb_write_io_threads。

主要思路：备份并替换默认的docker-entrypoint.sh文件，根据环境变量修改my.cnf，并调用原来的启动文件。

根据CPU核数修改innodb_read_io_threads和innodb_write_io_threads，核数低于10时使用默认的4，高于10时对半分。

Dockerfile

FROM mysql:8.0.31

ADD docker-entrypoint.sh /usr/local/bin/
# docker-entrypoint_origin.sh是原始的启动文件，只是拷贝出来改了个名字
ADD docker-entrypoint_origin.sh /usr/local/bin/
ADD my.cnf /etc/

ENV MYSQL_INNODB_BUFFER_SIZE=512M
ENV MYSQL_SERVER_ID=100

• 打包命令

# 版本号的前三位表示基础MySQL版本
docker build -t custom-mysql:8.0.31.1 .

my.cnf

[mysqld]

skip-host-cache
skip-name-resolve
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
secure-file-priv=/var/lib/mysql-files
user=mysql

# custom variables
interactive_timeout = 600
wait_timeout = 600
default-time-zone='+8:00'
collation_server=utf8mb4_general_ci
max_allowed_packet = 32M
lower_case_table_names = 1
max_connections = 3000
innodb_buffer_pool_size = MYSQL_INNODB_BUFFER_SIZE
innodb_read_io_threads = MYSQL_READ_IOTHREADS
innodb_write_io_threads = MYSQL_WRITE_IOTHREADS

log_bin = mysql-bin
binlog_format = row
binlog_cache_size = 2M
expire_logs_days = 15
max_binlog_size = 1G

server_id = MYSQL_SERVER_ID
gtid_mode = on
enforce_gtid_consistency = on
log-slave-updates = 1
binlog-ignore-db = mysql,information_schema,sys,performance_schema
sync_binlog = 1
auto_increment_offset = 1
auto_increment_increment = 2

# slow query log
log-output=FILE
slow-query-log=1
long_query_time=5
slow_query_log_file=/var/lib/mysql/slow-query.log

pid-file=/var/run/mysqld/mysqld.pid
[client]
socket=/var/run/mysqld/mysqld.sock

!includedir /etc/mysql/conf.d/

docker-entrypoint.sh

#!/bin/bash
set -eo pipefail
shopt -s nullglob

# custom parameters
if [ -n "$MYSQL_SERVER_ID" ]; then
    sed -i "s/MYSQL_SERVER_ID/$MYSQL_SERVER_ID/g"  /etc/my.cnf
fi

function optimizeIO() {
    if [ -n "$MYSQL_INNODB_BUFFER_SIZE" ]; then
        sed -i "s/MYSQL_INNODB_BUFFER_SIZE/$MYSQL_INNODB_BUFFER_SIZE/g" /etc/my.cnf
    fi
    
    # 根据CPU核数自动配置io读写线程数
    local cpuCores=$(cat /proc/cpuinfo | grep processor | wc -l)
    local iothreads=4
    if [ ${cpuCores} -gt 10 ]; then
        local iothreads=$((${cpuCores}/2))
    fi
    sed -i "s/MYSQL_READ_IOTHREADS/${iothreads}/g" /etc/my.cnf
    sed -i "s/MYSQL_WRITE_IOTHREADS/${iothreads}/g" /etc/my.cnf
}
optimizeIO

# 调用原始的启动脚本
sh /usr/local/bin/docker-entrypoint_origin.sh $@

docker-compose示例

version: "3"
services:
  mysql:
    image: custom-mysql:8.0.31.1
    container_name: mysql
    ports:
      - 3306:3306
    environment:
      - MYSQL_ROOT_PASSWORD=123456
      - MYSQL_INNODB_BUFFER_SIZE=1G
      - MYSQL_SERVER_ID=100