萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL my.cnf 日誌配置全解析:錯誤、慢查詢、二進制、通用日誌最佳實踐

MySQL 五大日誌類型速覽

日誌類型參數名用途建議
錯誤日誌log-error啟動/關閉/錯誤/崩潰信息必開,指定專用路徑
通用查詢日誌general_log所有連接/查詢語句僅診斷時臨時開啟,極耗 IO
慢查詢日誌slow_query_log超過閾值的查詢生產必開,長期保留
二進制日誌log-bin數據變更事件,主從/恢復核心主庫必開,Row 格式
中繼日誌relay-log從庫接收的主庫 Binlog從庫自動管理

生產環境推薦配置範例

[mysqld]
## 基礎路徑
basedir = /usr/local/mysql
datadir = /data/mysql
tmpdir = /data/tmp
socket = /data/mysql/mysql.sock
pid-file = /data/mysql/mysql.pid

## 字符集與時區
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
default-time-zone = '+08:00'

## 安全與連接
skip-name-resolve
skip-external-locking
max_connections = 3000
max_connect_errors = 10000
wait_timeout = 28800
interactive_timeout = 28800

## 緩存與緩衝區
max_allowed_packet = 64M
thread_stack = 256K
thread_cache_size = 200
sort_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 8M
join_buffer_size = 8M

## 二進制日誌(主庫核心)
log-bin = /data/binlog/mysql-bin
log-bin-index = /data/binlog/mysql-bin.index
binlog_format = ROW
binlog_cache_size = 4M
max_binlog_size = 1G
expire_logs_days = 14
sync_binlog = 1                 # 最高持久性,配合電池備寫緩存 RAID 卡可設 0
binlog_rows_query_log_events = ON  # 5.6+ 記錄原始 SQL,利於審計

## 慢查詢日誌
slow_query_log = 1
slow_query_log_file = /data/log/slow.log
long_query_time = 1             # 1 秒閾值
log_queries_not_using_indexes = OFF  # 不記錄未用索引查詢,避免噪音
log_output = FILE               # 也可設 TABLE 寫入 mysql.slow_log 表

## 錯誤日誌
log-error = /data/log/mysql.err
log-warnings = 2                # 記錄更多警告(如連接中斷)

## 通用日誌(默認關閉)
general_log = 0
general_log_file = /data/log/general.log

## InnoDB 核心(關鍵調優點)
innodb_file_per_table = 1
innodb_buffer_pool_size = 8G        # 物理內存 60-70%
innodb_buffer_pool_instances = 8
innodb_log_file_size = 1G
innodb_log_files_in_group = 2
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1  # ACID 核心
innodb_flush_method = O_DIRECT      # 繞過 OS 緩存,直寫磁盤
innodb_io_capacity = 2000           # SSD 建議 2000-5000
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_thread_concurrency = 0       # 0=自動,高併發可不限制

## 復制相關(從庫)
relay-log = /data/relay/relay-bin
relay-log-index = /data/relay/relay-bin.index
relay-log-purge = 1
slave_skip_errors = 1062,1032       # 忽略主鍵衝突/找不到行,謹慎使用

關鍵參數決策指南

binlog_format 三選一

  • ROW:記錄行變更,最安全,主從一致性最高,Binlog 較大,生產首選。
  • STATEMENT:記錄 SQL,Binlog 小,但函數/觸發器/非確定性函數易導致主從不一致。
  • MIXED:自動切換,折中方案。

sync_binlog 與 innodb_flush_log_at_trx_commit 雙 1 原則

sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

雙 1 保證「提交即持久」,單點故障最多丟 1 秒數據(OS cache 未刷盤部分)。若有電池備寫緩存 RAID 卡,可設 sync_binlog=0 提升寫入吞吐。

expire_logs_days 自動清理

防止 Binlog 撐爆磁盤,配合備份策略設定(如全備週期 7 天,設 14 天)。5.7+ 可用 binlog_expire_logs_seconds 秒級控制。

日誌分析工具鏈

  • 慢查詢:pt-query-digest、mysqldumpslow、mysqlslow
  • Binlog:mysqlbinlog、binlog2sql(閃回)、canal/maxwell(CDC)
  • 錯誤/通用:ELK/Fluentd 收集,告警關鍵字:ERROR、Aborted、Deadlock

避坑提醒

  • ❌ 生產長期開啟 general_log,IO 飆升導致業務卡頓。
  • ❌ slow_query_log 未設 long_query_time,默認 10 秒漏掉大量慢查詢。
  • ❌ Binlog 與數據目錄同盤,磁盤故障雙亡;強烈建議 Binlog 獨立掛載高 IOPS 存儲。
copyright © 萬盛學電腦網 all rights reserved