# MySQL 按以下顺序读取配置文件
/etc/my.cnf
/etc/mysql/my.cnf
/usr/local/mysql/etc/my.cnf
~/.my.cnf

# 查看 MySQL 读取了哪些配置文件
mysql --verbose --help | grep "Default options" -A 1

# Docker 中挂载配置
docker run ... -v /host/my.cnf:/etc/mysql/conf.d/custom.cnf mysql:8.0

# my.cnf 结构
[mysqld]          # mysqld 服务器进程
key = value

[mysql]           # mysql 客户端
prompt = 'mysql[\d]> '

[client]          # 所有客户端程序
host = 127.0.0.1
port = 3306

-- 查看当前值
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW GLOBAL VARIABLES LIKE 'max_connections';
SHOW SESSION VARIABLES LIKE 'sort_buffer_size';

-- 动态修改（部分参数支持，不需要重启）
SET GLOBAL max_connections = 500;
SET SESSION sort_buffer_size = 4 * 1024 * 1024;

-- MySQL 8.0 持久化动态修改（写入 mysqld-auto.cnf，重启生效）
SET PERSIST max_connections = 500;
SET PERSIST_ONLY innodb_buffer_pool_size = 4 * 1024 * 1024 * 1024;
-- PERSIST: 立即生效 + 持久化
-- PERSIST_ONLY: 只持久化，下次重启生效

-- 查看持久化配置
SELECT * FROM performance_schema.persisted_variables;

-- 删除持久化配置
RESET PERSIST max_connections;

# ==========================================
# 建议设为物理内存的 60~80%
# 8GB 内存机器：设 5~6GB
# 16GB 内存机器：设 10~12GB
# ==========================================
innodb_buffer_pool_size = 8G

# 多实例（每个实例独立的 LRU 链表，减少锁竞争）
# 推荐：innodb_buffer_pool_size >= 1G 时使用多实例
# 每个实例至少 1GB
innodb_buffer_pool_instances = 8

# 预热（启动时从文件加载 Buffer Pool 内容，减少冷启动时间）
innodb_buffer_pool_load_at_startup  = ON
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_dump_pct         = 25  # 导出 LRU 链表前 25%

# 以下参数是每个连接都会分配的内存，要考虑 max_connections 的放大效应
# 总内存消耗 ≈ max_connections × (sort_buffer + join_buffer + read_buffer + read_rnd_buffer)

sort_buffer_size      = 2M    # 排序缓冲（默认 256KB，过大对慢查询反而有害）
join_buffer_size      = 2M    # JOIN 缓冲（BNL 使用，默认 256KB）
read_buffer_size      = 1M    # 顺序读缓冲（MyISAM 和部分 InnoDB 操作）
read_rnd_buffer_size  = 1M    # 随机读缓冲
tmp_table_size        = 32M   # 内存临时表大小（超出转磁盘）
max_heap_table_size   = 32M   # 内存 HEAP 表大小（与 tmp_table_size 取较小值）

# 注意：sort_buffer 等是按需分配，不是启动就占用

# 键值缓存（MyISAM 索引缓存，InnoDB 用户可设小）
key_buffer_size       = 32M    # 默认 8M，MyISAM 用户调大，InnoDB 用户保持小

# 查询缓存（MySQL 8.0 已移除）
query_cache_type      = OFF    # 关闭
query_cache_size      = 0

# binlog 缓存
binlog_cache_size     = 1M     # 每个事务的 Binlog 写入缓存
max_binlog_cache_size = 2G     # 单个事务的最大 Binlog 缓存

# 线程缓存
thread_cache_size     = 100    # 空闲线程缓存数量（减少线程创建开销）

# MySQL 内存使用估算公式
# 固定内存:
#   innodb_buffer_pool_size
#   innodb_log_buffer_size
#   key_buffer_size
#   query_cache_size（5.7）
#
# 动态内存（per connection）:
#   thread_stack（512KB/连接）
#   sort_buffer_size（按需，最大 sort_buffer_size/连接）
#   join_buffer_size
#   read_buffer_size
#
# 估算总使用：
#   固定部分 + max_connections × 每连接估算

# 简单估算脚本
mysql -u root -p -e "
SELECT
  @@innodb_buffer_pool_size / 1024 / 1024 / 1024 AS buffer_pool_GB,
  @@max_connections AS max_conn,
  ROUND(@@max_connections * (@@sort_buffer_size + @@join_buffer_size + @@read_buffer_size) / 1024 / 1024 / 1024, 2) AS conn_mem_GB
\G"

# ==========================================
# 连接配置
# ==========================================

# 最大连接数
max_connections       = 1000
# 注意：不是越大越好，每个连接消耗内存，且线程切换有开销
# 公式：max_connections ≈ (可用内存 - 固定内存) / 单连接内存
# 建议：不超过 2000，超过考虑 ProxySQL 连接池

# 超级用户保留连接数（保证管理员能连上）
max_user_connections  = 950   # 普通用户最大连接数
# max_connections - max_user_connections 保留给 SUPER 权限用户

# 待连接队列大小（SYN 队列）
back_log              = 1000   # 高并发下需要增大

# ==========================================
# 超时配置（非常重要！避免连接泄漏）
# ==========================================

# 非交互连接（程序连接）的空闲超时
wait_timeout          = 600    # 秒，默认 28800（8小时）太长
# 建议：比应用层连接池的 MaxLifetime 大，比如设 600s

# 交互连接（mysql 命令行）的空闲超时
interactive_timeout   = 600

# 连接建立超时（网络层）
connect_timeout       = 10

# 等待锁的超时
innodb_lock_wait_timeout = 10  # 默认 50s，太长

# 慢查询超时（不是真的超时，只是记录）
long_query_time       = 1      # 超过 1s 记录慢查询

# ==========================================
# 线程配置
# ==========================================

# 线程缓存（减少频繁创建/销毁线程的开销）
thread_cache_size     = 100
# 监控：Threads_created 状态变量，增长快则增大 thread_cache_size

# 线程栈大小（每个线程的栈内存）
thread_stack          = 512K   # 默认 1M，可以适当减小

# 并发线程控制（InnoDB 层）
innodb_thread_concurrency = 0  # 0=不限制（默认），或设为 CPU 核数×2

# ==========================================
# InnoDB 存储
# ==========================================

# 每表独立表空间（强烈推荐）
innodb_file_per_table = ON

# 系统表空间初始大小（不重要，innodb_file_per_table=ON 后数据在各自 .ibd）
innodb_data_file_path = ibdata1:12M:autoextend

# 临时表空间
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:5G

# Double Write Buffer（防止部分写，必须开）
innodb_doublewrite = ON

# ==========================================
# InnoDB I/O 配置
# ==========================================

# 后台 I/O 线程数（读/写线程各自数量）
innodb_read_io_threads  = 4   # 默认 4，SSD 可以调大
innodb_write_io_threads = 4

# 最大每秒 I/O 操作数（IOPS 限制）
innodb_io_capacity      = 200   # HDD: 200, SSD: 2000~10000
innodb_io_capacity_max  = 400   # 紧急刷盘时的最大 IOPS，通常设 2×io_capacity

# 刷新脏页的算法
innodb_flush_method     = O_DIRECT   # 绕过 OS 缓存（推荐，避免双重缓存）
                                      # Windows 用 normal

# ==========================================
# InnoDB Redo Log
# ==========================================

# MySQL 8.0.30 以前
innodb_log_file_size    = 256M     # 每个文件大小（推荐 256M~2G）
innodb_log_files_in_group = 2      # 文件数量

# MySQL 8.0.30+（推荐使用这个）
innodb_redo_log_capacity = 1G      # 总大小

# 刷盘策略（最关键！）
innodb_flush_log_at_trx_commit = 1  # 1=最安全，0/2=高性能但有丢失风险

# Redo Log Buffer 大小
innodb_log_buffer_size  = 64M     # 默认 16M，大事务可以调大

# ==========================================
# InnoDB 行锁与事务
# ==========================================

# 行锁等待超时
innodb_lock_wait_timeout = 10

# 死锁检测（高并发可关闭，改用超时）
innodb_deadlock_detect   = ON

# Rollback 段数量（并发事务多时增大）
innodb_rollback_segments = 128    # 默认 128，最大 128

# ==========================================
# InnoDB 统计信息
# ==========================================

# 持久化统计信息（重启后不需要重新计算）
innodb_stats_persistent = ON
innodb_stats_persistent_sample_pages = 20  # 采样页数，越大越准确但越慢

# 自动更新统计信息（数据变化超过 10% 时）
innodb_stats_auto_recalc = ON

# ==========================================
# InnoDB Change Buffer
# ==========================================

# 写缓冲最大占 Buffer Pool 的比例
innodb_change_buffer_max_size = 25  # 默认 25%，读多写少可降低，写多可增大

# ==========================================
# 表打开缓存
# ==========================================

# 文件描述符缓存（表越多设越大）
table_open_cache       = 4000
table_definition_cache = 2000
open_files_limit       = 65535  # OS 级别的文件描述符限制

# ==========================================
# Binlog
# ==========================================

log_bin              = mysql-bin
binlog_format        = ROW
binlog_row_image     = MINIMAL          # 只记录变更的列，减少日志量
max_binlog_size      = 1G
binlog_expire_logs_seconds = 604800     # 7天（MySQL 8.0）
expire_logs_days     = 7               # 7天（MySQL 5.7）
sync_binlog          = 1               # 每次提交都 fsync（最安全）

# Binlog 校验
binlog_checksum      = CRC32            # 校验和（防止传输损坏）

# ==========================================
# 慢查询日志
# ==========================================

slow_query_log          = ON
slow_query_log_file     = /var/log/mysql/slow.log
long_query_time         = 1             # 超过 1 秒记录
log_queries_not_using_indexes = ON      # 未用索引的查询也记录
log_throttle_queries_not_using_indexes = 10  # 每分钟最多记录 10 条
min_examined_row_limit  = 100           # 扫描行数少于 100 不记录

# ==========================================
# 错误日志
# ==========================================

log_error            = /var/log/mysql/error.log
log_error_verbosity  = 2               # 2=错误+警告

# ==========================================
# 通用查询日志（调试用，一般关闭）
# ==========================================

general_log          = OFF
general_log_file     = /var/log/mysql/general.log

# ==========================================
# 通用复制配置
# ==========================================

server_id            = 1               # 每台服务器唯一 ID
log_bin              = mysql-bin
log_slave_updates    = ON              # 从库的变更也写 Binlog（级联复制/MGR必须）
relay_log            = relay-bin
relay_log_purge      = ON
relay_log_recovery   = ON             # 崩溃恢复重新获取 Relay Log

# ==========================================
# GTID 配置（强烈推荐）
# ==========================================

gtid_mode            = ON
enforce_gtid_consistency = ON

# ==========================================
# 主库配置
# ==========================================

binlog_format        = ROW
sync_binlog          = 1

# 半同步复制（可选）
# rpl_semi_sync_master_enabled = 1
# rpl_semi_sync_master_timeout = 1000

# ==========================================
# 从库配置
# ==========================================

read_only            = ON
super_read_only      = ON             # 防止 super 用户写

# 并行复制（提升从库吞吐）
slave_parallel_type     = LOGICAL_CLOCK
slave_parallel_workers  = 8
slave_preserve_commit_order = ON      # 保持事务提交顺序（避免数据不一致）

# 复制过滤（按需配置）
# replicate_do_db       = myapp       # 只复制指定库
# replicate_ignore_table = myapp.cache_table  # 忽略某张表

# 主从信息存储方式（推荐 TABLE，避免文件损坏）
master_info_repository   = TABLE
relay_log_info_repository = TABLE

# ==========================================
# 网络安全
# ==========================================

# 绑定地址（不要绑定 0.0.0.0 除非必要）
bind_address         = 0.0.0.0   # 生产建议改为具体 IP 或只开内网
port                 = 3306

# SSL/TLS（MySQL 8.0 默认开启）
ssl_ca               = /etc/mysql/ssl/ca.pem
ssl_cert             = /etc/mysql/ssl/server-cert.pem
ssl_key              = /etc/mysql/ssl/server-key.pem
# require_secure_transport = ON  # 强制所有连接使用 SSL

# ==========================================
# SQL 安全
# ==========================================

# 防止误操作（没有 WHERE 的 UPDATE/DELETE 会报错）
sql_safe_updates     = ON   # 建议在应用测试/开发开启

# 禁用 LOCAL INFILE（防止文件读取攻击）
local_infile         = OFF

# 禁止符号链接（防止权限绕过）
symbolic_links       = OFF

# 密码策略
# validate_password.policy = MEDIUM   # MySQL 8.0 默认
# validate_password.length = 8

# ==========================================
# 文件系统安全
# ==========================================

# 限制 LOAD DATA 和 SELECT ... INTO OUTFILE 的目录
secure_file_priv     = /tmp   # 或设为空字符串禁止，或指定目录

# ==========================================
# 账号安全
# ==========================================

# 失败登录锁定（MySQL 8.0.19+）
# CREATE USER 'app'@'%' IDENTIFIED BY '...'
#   FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;

# SQL Mode 控制 MySQL 的行为和数据校验严格程度
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

-- 查看当前 SQL Mode
SELECT @@sql_mode;
SHOW VARIABLES LIKE 'sql_mode';

-- 会话级别修改
SET SESSION sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO';

-- 临时关闭 ONLY_FULL_GROUP_BY（迁移时临时用）
SET SESSION sql_mode = sys.list_drop(@@SESSION.sql_mode, 'ONLY_FULL_GROUP_BY');

# performance_schema 开启（默认 ON）
performance_schema = ON

# 控制内存占用
performance_schema_max_table_instances = 12500
performance_schema_max_table_handles = 40000

-- 查看 performance_schema 内存使用
SELECT SUM_NUMBER_OF_BYTES_ALLOC / 1024 / 1024 AS mb
FROM performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME = 'memory/performance_schema/table_shares';

-- 启用/禁用特定监控
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'statement/%';

UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME LIKE 'events_statements%';

# ==========================================
# 生产环境 MySQL 8.0 配置模板
# 机器规格：8 核 16GB 内存
# ==========================================

[mysqld]
# ---- 基础 ----
user             = mysql
basedir          = /usr/local/mysql
datadir          = /data/mysql
socket           = /tmp/mysql.sock
pid_file         = /data/mysql/mysql.pid
port             = 3306
server_id        = 1

# ---- 字符集 ----
character_set_server     = utf8mb4
collation_server         = utf8mb4_unicode_ci
init_connect             = 'SET NAMES utf8mb4'

# ---- 时区 ----
default_time_zone        = '+8:00'

# ---- 连接 ----
max_connections          = 1000
max_user_connections     = 950
back_log                 = 1000
wait_timeout             = 600
interactive_timeout      = 600
connect_timeout          = 10
thread_cache_size        = 100

# ---- 内存 ----
innodb_buffer_pool_size     = 10G         # 16G * 62.5%
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup  = ON
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_dump_pct         = 25
sort_buffer_size            = 2M
join_buffer_size            = 2M
tmp_table_size              = 64M
max_heap_table_size         = 64M
thread_stack                = 512K

# ---- InnoDB I/O ----
innodb_file_per_table        = ON
innodb_flush_method          = O_DIRECT
innodb_read_io_threads       = 8          # SSD 适当调大
innodb_write_io_threads      = 8
innodb_io_capacity           = 4000       # SSD
innodb_io_capacity_max       = 8000
innodb_doublewrite           = ON

# ---- InnoDB Redo Log ----
innodb_redo_log_capacity     = 2G         # MySQL 8.0.30+
innodb_flush_log_at_trx_commit = 1        # 双 1 配置
innodb_log_buffer_size       = 64M

# ---- InnoDB 事务 ----
innodb_lock_wait_timeout     = 10
innodb_deadlock_detect       = ON
innodb_stats_persistent      = ON
innodb_stats_auto_recalc     = ON

# ---- 表缓存 ----
table_open_cache             = 4000
table_definition_cache       = 2000
open_files_limit             = 65535

# ---- Binlog ----
log_bin                      = /data/binlog/mysql-bin
binlog_format                = ROW
binlog_row_image             = MINIMAL
max_binlog_size              = 1G
binlog_expire_logs_seconds   = 604800     # 7 天
sync_binlog                  = 1          # 双 1 配置
binlog_checksum              = CRC32
binlog_cache_size            = 2M

# ---- 慢查询 ----
slow_query_log               = ON
slow_query_log_file          = /data/mysql/slow.log
long_query_time              = 1
log_queries_not_using_indexes = ON
log_throttle_queries_not_using_indexes = 10
min_examined_row_limit       = 100

# ---- 错误日志 ----
log_error                    = /data/mysql/error.log
log_error_verbosity          = 2

# ---- GTID 复制 ----
gtid_mode                    = ON
enforce_gtid_consistency     = ON
log_slave_updates            = ON
relay_log                    = /data/relaylog/relay-bin
relay_log_purge              = ON
relay_log_recovery           = ON
master_info_repository       = TABLE
relay_log_info_repository    = TABLE

# ---- SQL Mode ----
sql_mode                     = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

# ---- 安全 ----
local_infile                 = OFF
symbolic_links               = OFF
secure_file_priv             = /tmp

# ---- performance_schema ----
performance_schema           = ON

[mysql]
prompt                       = 'MySQL [\d]> '
no_auto_rehash

[client]
port                         = 3306
socket                       = /tmp/mysql.sock
default_character_set        = utf8mb4

# 语法检查（不启动服务）
mysqld --defaults-file=/etc/my.cnf --validate-config

# 查看所有非默认值配置
mysqld --print-defaults

-- 启动后检查关键参数
SELECT
  VARIABLE_NAME, VARIABLE_VALUE
FROM performance_schema.global_variables
WHERE VARIABLE_NAME IN (
  'innodb_buffer_pool_size',
  'max_connections',
  'innodb_flush_log_at_trx_commit',
  'sync_binlog',
  'binlog_format',
  'gtid_mode',
  'transaction_isolation',
  'sql_mode'
)
ORDER BY VARIABLE_NAME;