您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 核心運維筆記：目錄結構、字符集、Binlog、主從、備份恢復全景指南

編輯：MySQL 綜合教學

前言

本文彙總 MySQL DBA 日常必備的核心知識點：安裝目錄差異、配置文件加載邏輯、字符集最佳實踐、Binlog 管理與基於時間點恢復 (PITR)、主從搭建與故障處理、分庫分表基礎概念，形成結構化速查手冊。

一、 MySQL 關鍵目錄與安裝差異

安裝方式	數據目錄 (datadir)	配置文件 (my.cnf) 位置	備註
RPM / YUM	/var/lib/mysql	/etc/my.cnf (若無，需從 /usr/share/mysql/my-*.cnf 複製)	systemd 管理，路徑固定
源碼編譯	/usr/local/mysql/data (默認)	/usr/local/mysql/my.cnf 或 /etc/my.cnf	需手動創建配置、systemd unit

配置文件搜索順序：/etc/my.cnf → /etc/mysql/my.cnf → MYSQL_HOME/my.cnf → datadir/my.cnf → ~/.my.cnf。後讀取的覆蓋先讀取的同名參數。

二、字符集配置鐵律 (UTF8MB4 全棧)


[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci  -- 或 utf8mb4_0900_ai_ci (8.0 推薦)
init_connect = 'SET NAMES utf8mb4'     -- 連接初始化強制字符集
skip-character-set-client-handshake    -- 強制忽略客戶端聲明字符集 (慎用)

核心原則：客戶端、連接層、服務端、庫/表/列定義 全鏈路統一 utf8mb4。utf8 是 utf8mb3 別名，**不支援 Emoji/生僻字**，生產環境嚴禁使用。

三、 Binlog 核心管理與 PITR 實戰

1. 啟用 Binlog


[mysqld]
log-bin = mysql-bin
binlog-format = ROW
expire_logs_days = 7          -- 自動清理，防止磁碟爆滿
max_binlog_size = 1G
sync_binlog = 1               -- 雙1配合 innodb_flush_log_at_trx_commit=1 保證不丟數據

2. 常用 Binlog 查看命令


SHOW MASTER STATUS;           -- 當前寫入文件及位點
SHOW BINARY LOGS;             -- 所有 Binlog 列表
SHOW MASTER LOGS;             -- 同上

3. mysqlbinlog 解析工具實戰


# 忽略字符集報錯
mysqlbinlog --no-defaults /var/lib/mysql/mysql-bin.000001

# 指定時間範圍導出 SQL
mysqlbinlog --no-defaults \
  --start-datetime='2024-01-01 09:30:00' \
  --stop-datetime='2024-01-01 10:00:00' \
  /var/lib/mysql/mysql-bin.000003 > /tmp/incr_0930_1000.sql

# 指定位點範圍導出 (更精確)
mysqlbinlog --no-defaults \
  --start-position=12345 \
  --stop-position=67890 \
  /var/lib/mysql/mysql-bin.000003 > /tmp/incr_pos.sql

4. 生產級 PITR (時間點恢復) 標準流程

場景：09:30 全量備份 + Flush Logs → 10:00 數據損壞 → 需恢復至 10:00 前狀態。

全量備份 (09:30)：


mysqldump -uroot -p -l -F --single-transaction --master-data=2 business_db > /backup/full_0930.sql
# -l: 鎖表 (MyISAM) / --single-transaction (InnoDB 免鎖)
# -F: Flush Logs，產生新 Binlog 文件，增量起點清晰

增量 Binlog 收集 (09:30-10:00)：


mysqlbinlog --no-defaults --stop-position=367 mysql-bin.000003 > /backup/incr_0930_1000.sql
# --stop-position 為誤操作前的位點 (通過查看 Binlog 確定)

恢復執行：


# 1. 恢復全量
mysql -uroot -p business_db < /backup/full_0930.sql

# 2. 重放增量
mysql -uroot -p business_db < /backup/incr_0930_1000.sql

5. Binlog 文件切換觸發條件

FLUSH LOGS; / mysqladmin flush-logs
MySQL 服務重啟
文件大小達到 max_binlog_size
RESET MASTER; (慎用！清空所有 Binlog，只留 000001)

四、主從復制標準化搭建 (補充 181884 細節)

從庫配置關鍵差異點


[mysqld]
server-id = 2
relay-log = relay-bin
read-only = 1                 -- 關鍵：防止誤寫入
# replicate-do-db / ignore-db 建議在 CHANGE MASTER TO 時用 REPLICATE_DO_DB 通道過濾，而非配置文件

斷電/網絡故障導致 Relay Log 損壞修復


STOP SLAVE;
CHANGE MASTER TO
  MASTER_HOST='192.168.255.1',
  MASTER_USER='user1',
  MASTER_PASSWORD='123',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000078',   -- 主庫 SHOW MASTER STATUS 獲取
  MASTER_LOG_POS=747157709;
START SLAVE;

原理：重置從庫讀取主庫 Binlog 的起始位點，繞過損壞的 Relay Log。

五、分庫分表 (Sharding) 基礎概念

維度	垂直分庫/分表	水平分庫/分表
定義	按業務模塊/列拆分	按行數據分片 (如 user_id % N)
目的	業務解耦、熱冷數據隔離	單表過大、寫入瓶頸、索引維護慢
中間件	ShardingSphere, MyCat, Vitess	同上
難點	分布式事務、跨庫 Join	分片鍵選擇、再平衡、全局唯一 ID