创建目录和授权

mkdir /data/mysql/{data,conf} -p
cd /data
chown -R 999.999 mysql

compose配置

services:
  openresty:
    image: mysql:8.0.40
    container_name: mysql
    restart: always
    ports:
    - 30306:3306
    environment:
      MYSQL_ROOT_PASSWORD: 123456
    volumes:
      - /etc/localtime:/etc/localtime
      - /data/mysql/data:/var/lib/mysql
      - /data/mysql/conf/my.cnf:/etc/my.cnf
    networks:
      - asia
    healthcheck:
      test: ["CMD", "mysqladmin", "ping", "-h", "127.0.0.1", "--silent"]
      interval: 3s
      retries: 5
      start_period: 30s
    deploy:
      resources:
        limits:
          cpus: '6'  #限制 0.5 个 CPU
          memory: 12288M  #内存限制 512MB
        reservations:
          cpus: '0.5'  #预留 0.25 个 CPU
          memory: 500M  #预留内存 256MB
    sysctls:
      - net.core.somaxconn=60000
    ulimits:
      nproc: 50000
      nofile:
        soft: 5000000
        hard: 5000000

networks:
  asia:
    name: asia

配置文件

[client]
port  = 3306
socket  = /var/lib/mysql/mysql.sock

[mysql]
prompt = "\u@mysqldb \R:\m:\s [\d]> "
no_auto_rehash
loose-skip-binary-as-hex

[mysqld]
user  = mysql
port  = 3306
server_id = 3306
socket  = /var/lib/mysql/mysql.sock
pid_file = /var/run/mysqld/mysqldb.pid
character_set_server = UTF8MB4
skip_name_resolve = 1
max_allowed_packet = 200M
sql_mode= "STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
default-authentication-plugin=mysql_native_password

#performance setttings
lock_wait_timeout = 3600
open_files_limit    = 65535
back_log = 1024
max_connections = 40960
max_connect_errors = 10000
table_open_cache = 2048
table_definition_cache = 1024
thread_stack = 512K
sort_buffer_size = 4M
join_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 64M
thread_cache_size = 768
tmp_table_size = 32M
max_heap_table_size = 32M

#log settings
log_timestamps = SYSTEM
log_queries_not_using_indexes = 1
log_throttle_queries_not_using_indexes = 60
min_examined_row_limit = 100
log_bin = mysql-bin
binlog_format = ROW
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G
binlog_rows_query_log_events = 1
binlog_expire_logs_seconds = 604800
binlog_checksum = CRC32
gtid_mode = ON
enforce_gtid_consistency = TRUE

#innodb settings
transaction_isolation = REPEATABLE-READ
innodb_buffer_pool_size = 12288M
innodb_buffer_pool_instances = 4
innodb_data_file_path = ibdata1:12M:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 32M
innodb_log_file_size = 1G
binlog_stmt_cache_size = 1048576
sync_binlog = 0

#pfs settings
performance_schema = 1
#performance_schema_instrument = '%memory%=on'
performance_schema_instrument = '%lock%=on'

[mysqldump]
quick