MySQL 常見問題解決方案：資料庫複寫、備份與還原實戰指南

19.1 資料庫複寫策略

複寫優勢

• 高可用：主庫故障可快速切換至從庫；
• 讀寫分離：非更新查詢導向從庫，降低主庫負載；
• 延遲從庫：設定 MASTER_DELAY 防誤操作同步（如誤 DROP TABLE）。

基於二進位日誌的傳統複寫

# Master 開啟 binlog
[mysqld]
log-bin=mysql-bin
server-id=1

# Slave 同步
mysql -u repl -p -h master_host < master_binlog.000001

限制：複寫以 SQL 陳述式為基礎，以下函數在主從間可能產生不同結果，不可用於複寫環境：

• DATABASE()、USER()、SYSTEM_USER()、SESSION_USER()
• VERSION()、RAND()、GET_LOCK()/RELEASE_LOCK()

安全函數：NOW()、LAST_INSERT_ID()、所有時間函數。

時間戳記增量同步（無刪除操作的表）

SELECT * FROM tbl WHERE last_modified > 'last_repl_time'
INTO OUTFILE '/tmp/incr.txt';
# 導入從庫
LOAD DATA INFILE '/tmp/incr.txt' REPLACE INTO TABLE tbl;

19.2 資料庫備份與還原

物理備份（檔案層級）

# 鎖定表確保一致性
mysql> FLUSH TABLES WITH READ LOCK;
# 複製資料目錄
cp -r /var/lib/mysql /backup/mysql_$(date +%F)
mysql> UNLOCK TABLES;

適用於 MyISAM；InnoDB 建議用 xtrabackup 熱備份。

邏輯備份：mysqldump

# 完整備份（含結構與資料）
mysqldump --single-transaction --routines --triggers --events --all-databases > full_$(date +%F).sql

# 僅備份結構
mysqldump --no-data --all-databases > schema_only.sql

# 並行備份加速（MySQL 8.0+ / mydumper）
mydumper --threads=4 --outputdir=/backup

增量備份：binlog

# 啟用 binlog 後，定期 flush
mysqladmin flush-logs

# 還原流程
# 1. 還原全量備份
mysql < full_20240101.sql
# 2. 依序重放 binlog
mysqlbinlog mysql-bin.000001 mysql-bin.000002 | mysql

表損壞修復

# MyISAM
myisamchk -r /var/lib/mysql/db/tbl.MYI

# InnoDB（極少損壞，若發生）
innodb_force_recovery=1..6 於 my.cnf 啟動後 dump 重建

選擇性備份與還原

# 備份單表
SELECT * INTO OUTFILE '/backup/tbl.txt' FROM tbl;
# 還原（需主鍵/唯一鍵避免重複）
LOAD DATA INFILE '/backup/tbl.txt' REPLACE INTO TABLE tbl;