[client]
# 客户端配置

[mysql]
# mysql 命令行配置

[mysqld]
# 服务器核心配置

[mysqldump]
# 备份工具配置

[mysqld]
# ============ 基础设置 ============
port = 3306
bind_address = 0.0.0.0
basedir = /usr/local/mysql
datadir = /var/lib/mysql
pid_file = /var/run/mysql/mysql.pid
socket = /tmp/mysql.sock
user = mysql
character_set_server = utf8mb4
collation_server = utf8mb4_unicode_ci

# ============ 连接设置 ============
max_connections = 500
max_connect_errors = 10000
wait_timeout = 600
interactive_timeout = 28800
# 连接分配
back_log = 500
# 线程缓存
thread_cache_size = 100

# ============ 存储引擎 ============
default_storage_engine = InnoDB

# ============ 日志设置 ============
log_error = /var/log/mysql/error.log
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = ON

# ============ Binlog ============
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
expire_logs_days = 7
max_binlog_size = 500M
binlog_cache_size = 64K
sync_binlog = 1          # 每次提交都刷盘（最安全）

# ============ InnoDB 引擎 ============
# 缓冲池
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4
innodb_log_file_size = 512M
innodb_log_files_in_group = 2
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_io_capacity = 2000
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_purge_threads = 4

# ============ 排序/临时区 ============
sort_buffer_size = 256K
join_buffer_size = 256K
tmp_table_size = 32M
max_heap_table_size = 32M

# ============ 其他 ============
max_allowed_packet = 64M
sql_mode = STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

-- 查看当前配置
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';        -- 缓冲池大小
SHOW VARIABLES LIKE 'innodb_buffer_pool_instances';    -- 实例数
SHOW VARIABLES LIKE 'innodb_buffer_pool_chunk_size';   -- 块大小

-- 查看缓冲池使用状态
SHOW ENGINE INNODB STATUS\G
-- BUFFER POOL AND MEMORY 部分
-- Database pages：总页面数
-- Free pages：空闲页面数
-- Modified pages：脏页数
-- Buffer pool hit rate：缓存命中率

# 专用 MySQL 服务器：分配物理内存的 60%~80%
# 共享服务器：分配物理内存的 30%~50%

# 16GB 内存的服务器
innodb_buffer_pool_size = 10G
innodb_buffer_pool_instances = 8   # 每个实例约 1.25G

# 32GB 内存的服务器
innodb_buffer_pool_size = 20G
innodb_buffer_pool_instances = 16

# 64GB 内存的服务器
innodb_buffer_pool_size = 40G
innodb_buffer_pool_instances = 16

-- 缓存命中率应 > 99%
SELECT
    (1 - (innodb_buffer_pool_reads / (innodb_buffer_pool_read_requests + 1))) * 100
    AS buffer_pool_hit_rate
FROM performance_schema.global_status
WHERE variable_name IN ('innodb_buffer_pool_reads', 'innodb_buffer_pool_read_requests');

-- 查看配置
SHOW VARIABLES LIKE 'innodb_log_file_size';      -- Redo log 文件大小
SHOW VARIABLES LIKE 'innodb_log_files_in_group';  -- 文件数量
SHOW VARIABLES LIKE 'innodb_log_buffer_size';     -- log 缓冲区

-- 查看写入量
SHOW ENGINE INNODB STATUS\G
-- Log sequence number：当前 LSN（已经写入的日志量）
-- Log flushed up to：已经刷盘的 LSN

# Redo log 总大小 = innodb_log_file_size × innodb_log_files_in_group
# 建议总大小能容纳 1 小时的写入量

# OLTP 场景
innodb_log_file_size = 1G
innodb_log_files_in_group = 2

# 写入密集型
innodb_log_file_size = 2G
innodb_log_files_in_group = 3

-- 查看当前连接数
SHOW STATUS LIKE 'Threads_connected';     -- 当前连接数
SHOW STATUS LIKE 'Threads_running';       -- 活跃连接数
SHOW STATUS LIKE 'Max_used_connections';  -- 历史最大连接数

-- 查看最大连接数配置
SHOW VARIABLES LIKE 'max_connections';

-- 如果 Max_used_connections 接近 max_connections，需要增加
-- 设置公式：max_connections = 峰值连接数 * 1.5

[mysqld]
# 最大连接数（默认 151）
# 如果太小：业务高峰期会报 "Too many connections"
# 如果太大：消耗内存过多
max_connections = 500

# 每个连接占用内存估算：
# sort_buffer_size + join_buffer_size + read_buffer_size + ...
# = 256K + 256K + 128K + 128K = 768K 每个连接

# 500 连接 × 768K = 384MB（连接占用总内存）

# 连接超时
wait_timeout = 600          # 非交互连接超时（秒）
interactive_timeout = 28800  # 交互连接超时（秒）

# 连接排队数
back_log = 500

# 连接线程缓存
thread_cache_size = 100

-- 监控连接状态
SELECT
    COUNT(*) AS total_connections,
    SUM(CASE WHEN command = 'Sleep' THEN 1 ELSE 0 END) AS sleep,
    SUM(CASE WHEN command = 'Query' THEN 1 ELSE 0 END) AS active_query,
    SUM(CASE WHEN command = 'Locked' THEN 1 ELSE 0 END) AS locked
FROM information_schema.PROCESSLIST;

-- 查找长连接（超过 1 小时且处于 Sleep 状态）
SELECT id, user, host, db, time, command, state
FROM information_schema.PROCESSLIST
WHERE command = 'Sleep' AND time > 3600;

[mysqld]
# 读 IO 线程（负责从磁盘读取数据页）
innodb_read_io_threads = 4

# 写 IO 线程（负责将脏页写入磁盘）
innodb_write_io_threads = 4

# Purge 线程（清理 undo log）
innodb_purge_threads = 4

# IO 容量（磁盘每秒可执行 IOPS）
innodb_io_capacity = 2000       -- SSD 建议 2000~5000
innodb_io_capacity_max = 3000   -- 最大 IOPS

[mysqld]
# 控制脏页刷盘行为
innodb_flush_log_at_trx_commit = 1  -- 每次提交刷盘（最安全）
innodb_flush_method = O_DIRECT       -- 绕过 OS 缓存，直接写磁盘

# 脏页比例（达到此比例强制刷盘）
innodb_max_dirty_pages_pct = 75
innodb_max_dirty_pages_pct_lwm = 10  -- 低于此比例停止主动刷盘

# 刷盘线程数
innodb_page_cleaners = 4

[mysqld]
# InnoDB 并发线程数上限
# 0 表示不限制（根据操作系统调度）
innodb_thread_concurrency = 0

# 并发中等待锁的事务数量
innodb_commit_concurrency = 0

# 死锁检测（默认 ON，高并发场景可关闭）
innodb_deadlock_detect = ON

-- 查看文件排序比例（越低越好）
SHOW STATUS LIKE 'Sort_scan';
SHOW STATUS LIKE 'Sort_merge_passes';
-- Sort_merge_passes 过高说明 sort_buffer_size 太小

-- 查看创建的临时表情况
SHOW STATUS LIKE 'Created_tmp_tables';
SHOW STATUS LIKE 'Created_tmp_disk_tables';
-- Created_tmp_disk_tables 过多说明 tmp_table_size 太小

[mysqld]
# 排序缓冲区（每个连接独占，不能太大）
sort_buffer_size = 256K

# JOIN 缓冲区
join_buffer_size = 256K

# 临时表大小
tmp_table_size = 32M
max_heap_table_size = 32M

-- 好：用索引排序（Using index for order by 或 filesort 可以避免）
EXPLAIN SELECT * FROM users ORDER BY create_time DESC;
-- Extra: Using filesort（需要优化）

-- 优化：给排序字段建索引
CREATE INDEX idx_create_time ON users(create_time);

-- 如果无法避免 filesort，适当增大 sort_buffer_size
SET SESSION sort_buffer_size = 1M;

# 1. 文件系统
# 使用 XFS 或 ext4

# 2. IO 调度器
# SSD 建议 noop 或 none
echo noop > /sys/block/sda/queue/scheduler

# 3. 磁盘预读
blockdev --setra 4096 /dev/sda

# 4. 系统限制（打开文件数）
# /etc/security/limits.conf
mysql soft nofile 65535
mysql hard nofile 65535

# 5. swap 设置
# 尽量不用 swap，或降低 swappiness
sysctl vm.swappiness=1

# 6. 大页内存（可选）
# 减少 TLB miss
echo never > /sys/kernel/mm/transparent_hugepage/enabled

[mysqld]
# 基础
port = 3306
user = mysql
character_set_server = utf8mb4
collation_server = utf8mb4_unicode_ci

# InnoDB 核心（分配 10GB）
innodb_buffer_pool_size = 10G
innodb_buffer_pool_instances = 8
innodb_log_file_size = 1G
innodb_log_files_in_group = 2
innodb_log_buffer_size = 32M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_io_capacity = 2000
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_purge_threads = 2

# 连接（预留 3GB 给连接和其他操作）
max_connections = 500
thread_cache_size = 100
back_log = 500

# 连接内存（每个连接约 1MB）
# sort_buffer_size = 256K
# join_buffer_size = 256K

# 查询缓存（MySQL 8.0 已废弃，不开启）

# 日志
slow_query_log = ON
long_query_time = 2

# Binlog
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
expire_logs_days = 7
sync_binlog = 1

# 临时表
tmp_table_size = 64M
max_heap_table_size = 64M

# 其他
max_allowed_packet = 64M
sql_mode = STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
open_files_limit = 65535

-- 运行 1 天后检查

-- 1. Buffer Pool 命中率（应 > 99%）
SELECT CONCAT(ROUND(
    (1 - (SELECT variable_value FROM performance_schema.global_status
          WHERE variable_name = 'innodb_buffer_pool_reads')
    /
    (SELECT variable_value FROM performance_schema.global_status
     WHERE variable_name = 'innodb_buffer_pool_read_requests')) * 100, 2), '%')
    AS buffer_pool_hit_rate;

-- 2. 慢查询比例
SELECT
    (SELECT variable_value FROM performance_schema.global_status
     WHERE variable_name = 'Slow_queries') /
    (SELECT variable_value FROM performance_schema.global_status
     WHERE variable_name = 'Questions') * 100 AS slow_query_pct;

-- 3. 磁盘临时表比例
SELECT CONCAT(ROOT(
    (SELECT variable_value FROM performance_schema.global_status
     WHERE variable_name = 'Created_tmp_disk_tables')
    /
    (SELECT variable_value FROM performance_schema.global_status
     WHERE variable_name = 'Created_tmp_tables'
     AND variable_value > 0) * 100, 2), '%')
    AS disk_tmp_table_pct;

-- 4. 最大连接数使用率
SELECT CONCAT(ROUND(
    (SELECT variable_value FROM performance_schema.global_status
     WHERE variable_name = 'Max_used_connections')
    /
    (SELECT variable_value FROM performance_schema.global_variables
     WHERE variable_name = 'max_connections') * 100, 2), '%')
    AS connection_usage;