MySQL持久化参数深度解析与面试指南

事务提交 → 写入binlog buffer → 写入OS cache → sync_binlog控制 → 刷写到磁盘

# 生产环境推荐配置
sync_binlog = 1                    # 最高安全级别

# 高并发场景可考虑
sync_binlog = 10                   # 平衡性能与安全

# 开发测试环境
sync_binlog = 0                    # 最佳性能

值为0: 事务提交 → redo log buffer → (每秒) → OS cache → 磁盘
值为1: 事务提交 → redo log buffer → OS cache → 立即刷盘
值为2: 事务提交 → redo log buffer → OS cache → (每秒) → 磁盘

innodb_doublewrite = 1             # 开启双写缓冲（推荐）

binlog_cache_size = 32K            # 单个事务binlog缓存
max_binlog_cache_size = 4G         # 最大binlog缓存
binlog_stmt_cache_size = 32K       # 非事务语句缓存

innodb_log_file_size = 256M        # 每个redo log文件大小
innodb_log_files_in_group = 2      # redo log文件数量
innodb_log_buffer_size = 16M       # redo log缓冲区大小

innodb_flush_method = O_DIRECT     # 绕过OS缓存直接写磁盘
innodb_io_capacity = 200           # IO能力配置
innodb_io_capacity_max = 2000      # 最大IO能力

[mysqld]
# 最高安全级别配置
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
innodb_doublewrite = 1
innodb_flush_method = O_DIRECT

# 适当提高缓冲区减少性能影响
innodb_log_buffer_size = 64M
binlog_cache_size = 64K

[mysqld]
# 平衡配置
sync_binlog = 10
innodb_flush_log_at_trx_commit = 2
innodb_doublewrite = 1

# 性能优化
innodb_log_buffer_size = 32M
binlog_cache_size = 32K
innodb_io_capacity = 1000

[mysqld]
# 性能优先配置
sync_binlog = 1000
innodb_flush_log_at_trx_commit = 0
innodb_doublewrite = 0

# 大缓冲区配置
innodb_log_buffer_size = 128M
binlog_cache_size = 128K
innodb_io_capacity = 2000

[mysqld]
# 主库配置
sync_binlog = 1                    # 保证binlog可靠性
innodb_flush_log_at_trx_commit = 1 # 保证事务可靠性

# 从库可以适当放松
# sync_binlog = 10
# innodb_flush_log_at_trx_commit = 2

配置组合                           TPS      数据安全性
sync_binlog=0, innodb=0           10000    最低
sync_binlog=10, innodb=2          8000     中等  
sync_binlog=1, innodb=1           5000     最高

-- 监控binlog使用情况
SHOW GLOBAL STATUS LIKE 'Binlog_cache%';
SHOW GLOBAL STATUS LIKE 'Binlog_stmt_cache%';

-- 监控InnoDB日志
SHOW GLOBAL STATUS LIKE 'Innodb_log%';
SHOW GLOBAL STATUS LIKE 'Innodb_os_log%';

-- 监控IO性能
SHOW GLOBAL STATUS LIKE 'Innodb_data_fsyncs';
SHOW GLOBAL STATUS LIKE 'Innodb_os_log_fsyncs';

apiVersion: v1
kind: ConfigMap
metadata:
  name: mysql-config-production
data:
  my.cnf: |
    [mysqld]
    # 基础配置
    server-id = 1
    bind-address = 0.0.0.0
    
    # 持久化安全配置
    sync_binlog = 1
    innodb_flush_log_at_trx_commit = 1
    innodb_doublewrite = 1
    innodb_flush_method = O_DIRECT
    
    # 性能优化
    innodb_buffer_pool_size = 2G
    innodb_log_buffer_size = 64M
    innodb_log_file_size = 512M
    innodb_log_files_in_group = 2
    
    # binlog配置
    log-bin = mysql-bin
    binlog_format = ROW
    binlog_cache_size = 64K
    max_binlog_cache_size = 2G
    expire_logs_days = 7
    
    # IO配置
    innodb_io_capacity = 1000
    innodb_io_capacity_max = 2000
    innodb_read_io_threads = 8
    innodb_write_io_threads = 8