说明：
MySQL8.0 默认配置文件路径：
/etc/my.cnf（RHEL/CentOS/openEuler），Debian/Ubuntu 是/etc/mysql/my.cnf适配 InnoDB 为主，几乎不使用 MyISAM；适合业务库（Java 微服务、高并发 OLTP），非数仓
关闭过时参数、适配 caching_sha2_password、binlog GTID 主从、慢查询、性能监控
内存分配原则：InnoDB Buffer Pool 占物理内存 50%~60%，预留内存给 OS、连接、排序、缓存
注意：容器部署（docker）需要额外限制
mem_limit，不要让 mysql 占用超配内存生效方式：
systemctl restart mysqld，校验：mysqld --validate-config
公共前置通用段（两套共用，[mysqld_safe] / [client] / [mysql] 一致）
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
prompt="\\u@\\h [\\d] > "
[mysqld_safe]
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
模板 A：8C 16G 生产主库 [mysqld]
[mysqld]
# 基础
user = mysql
port = 3306
socket = /var/lib/mysql/mysql.sock
datadir = /var/lib/mysql
pid-file = /var/run/mysqld/mysqld.pid
server-id = 1
default-time_zone = '+8:00'
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
skip-character-set-client-handshake = ON
# 网络 & 连接
max_connections = 800
max_connect_errors = 100000
wait_timeout = 600
interactive_timeout = 600
back_log = 200
max_allowed_packet = 64M
# InnoDB 核心（16G内存：bp=8G，约50%）
default-storage-engine = InnoDB
innodb_buffer_pool_size = 8G
innodb_buffer_pool_instances = 8
innodb_log_file_size = 1G
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_file_per_table = ON
innodb_autoinc_lock_mode = 2
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON
innodb_lock_wait_timeout = 50
# 排序/连接临时缓存（不要设太大，是单线程内存）
sort_buffer_size = 2M
join_buffer_size = 2M
read_buffer_size = 1M
read_rnd_buffer_size = 2M
tmp_table_size = 64M
max_heap_table_size = 64M
# Binlog GTID 主从必备
log_bin = mysql-bin
binlog_format = ROW
binlog_row_image = MINIMAL
expire_logs_days = 7
sync_binlog = 1
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = ON
binlog_cache_size = 4M
# 慢查询 & 审计
slow_query_log = ON
slow_query_log_file = /var/log/mysql-slow.log
long_query_time = 1.0
log_queries_not_using_indexes = ON
min_examined_row_limit = 1000
# 性能监控
performance_schema = ON
performance_schema_max_memory_classes = 1000
# 认证（MySQL8 默认 caching_sha2_password）
default_authentication_plugin = caching_sha2_password
# 禁止DNS解析，加速连接
skip-name-resolve = ON
# 其它
explicit_defaults_for_timestamp = ON
transaction_isolation = READ-COMMITTED
模板 B：16C 32G 生产主库 [mysqld]
[mysqld]
# 基础
user = mysql
port = 3306
socket = /var/lib/mysql/mysql.sock
datadir = /var/lib/mysql
pid-file = /var/run/mysqld/mysqld.pid
server-id = 1
default-time_zone = '+8:00'
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
skip-character-set-client-handshake = ON
# 网络 & 连接
max_connections = 1500
max_connect_errors = 100000
wait_timeout = 600
interactive_timeout = 600
back_log = 300
max_allowed_packet = 128M
# InnoDB 核心（32G内存：bp=20G，约62%）
default-storage-engine = InnoDB
innodb_buffer_pool_size = 20G
innodb_buffer_pool_instances = 16
innodb_log_file_size = 2G
innodb_log_buffer_size = 128M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_file_per_table = ON
innodb_autoinc_lock_mode = 2
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON
innodb_lock_wait_timeout = 50
# 排序/连接临时缓存
sort_buffer_size = 2M
join_buffer_size = 2M
read_buffer_size = 1M
read_rnd_buffer_size = 2M
tmp_table_size = 128M
max_heap_table_size = 128M
# Binlog GTID 主从
log_bin = mysql-bin
binlog_format = ROW
binlog_row_image = MINIMAL
expire_logs_days = 7
sync_binlog = 1
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = ON
binlog_cache_size = 8M
# 慢查询 & 审计
slow_query_log = ON
slow_query_log_file = /var/log/mysql-slow.log
long_query_time = 1.0
log_queries_not_using_indexes = ON
min_examined_row_limit = 1000
# 性能监控
performance_schema = ON
performance_schema_max_memory_classes = 1000
# 认证
default_authentication_plugin = caching_sha2_password
# 禁止DNS解析
skip-name-resolve = ON
# 其它
explicit_defaults_for_timestamp = ON
transaction_isolation = READ-COMMITTED
从库改造要点（主库模板改这几项即可）
server-id = 2
read_only = ON
super_read_only = ON
# 从库不需要写binlog给下游，不需要log_slave_updates（除非级联复制）
log_slave_updates = OFF
重要参数解释 & 调优红线（生产避坑）
配套检查脚本
# 配置合法性校验
mysqld --validate-config
# 启动
systemctl restart mysqld
systemctl enable mysqld
# 查看bp是否生效
mysql -e "show variables like 'innodb_buffer_pool_size';"
# 查看当前连接
mysql -e "show status like 'Threads_connected';"
适配场景微调清单
SSD 盘：上面模板 io_capacity 适配 SSD；机械盘要降到
innodb_io_capacity=500ARM 信创服务器（飞腾 / 鲲鹏）：参数值通用，优先确认内核
O_DIRECT支持；部分老版本内核 O_DIRECT 异常，可以改成innodb_flush_method=fsync大事务业务：调大
binlog_cache_size，同时监控Binlog_cache_disk_use报表多、大量 group by：适度上调
tmp_table_size，监控Created_tmp_disk_tables容器部署：my.cnf 参数不变，一定要在 docker run 限制内存，防止 OOM kill
本文原创作者：易君召，详见：https://www.yijunzhao.cc/about，转载请注明出处。
原文链接
欢迎访问