您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 簡易備份與還原實戰手冊（Linux 環境）

編輯：MySQL 資料庫相關知識

前言

資料庫備份是 DBA 的生命線。本文針對 Linux 環境 MySQL，提供冷備份、熱備份、還原、定時排程完整操作流程，並含真實故障修復案例（phpBB 論壇 MyISAM 表損壞）。

一、環境與前提

OS：Linux（示例為 RedHat/CentOS）
MySQL 數據目錄：/var/lib/mysql
具備 root 權限、基本 Shell 操作能力
操作前務必驗證現有備份可用性

二、故障修復實戰：MyISAM 表損壞修復

# 1. 停止服務（避免寫入衝突）
systemctl stop mysqld
# 或：mysqladmin -u root -p shutdown

# 2. 修復損壞表（以 phpbb2_users 為例）
cd /var/lib/mysql/phpbb2
myisamchk -r phpbb2_users.MYI   # 普通修復
# 若失敗，強制修復
myisamchk -o phpbb2_users.MYI

# 3. 重複執行直到無錯誤輸出
# 4. 重啟服務驗證
systemctl start mysqld
mysql -u root -p -e "CHECK TABLE phpbb2_users"

建議： 定期排程 mysqlcheck -A -r 全庫檢查修復，避免積累損壞。

三、冷備份（停機備份）——最簡單可靠

# 1. 停止 MySQL
systemctl stop mysqld

# 2. 打包數據目錄（以 phpbb2 庫為例）
tar czvf /backup/phpbb2_db_$(date +%F).tgz /var/lib/mysql/phpbb2

# 3. 打包程式碼（如有變更）
tar czvf /backup/phpbb2_code_$(date +%F).tgz /var/www/html/phpbb2

# 4. 重啟 MySQL
systemctl start mysqld

優點：文件級一致性 100%，無鎖無阻塞。缺點：需停機（秒級～分級）。

四、熱備份（線上備份）——不停機

1. mysqldump 邏輯備份

# 單庫備份（含結構+數據）
mysqldump -u root -p --single-transaction --routines --triggers --events phpbb2 > /backup/phpbb2_$(date +%F).sql

# 全庫備份
mysqldump -u root -p --all-databases --single-transaction > /backup/full_$(date +%F).sql

# 壓縮存儲
mysqldump ... | gzip > /backup/db_$(date +%F).sql.gz

關鍵參數：--single-transaction（InnoDB 一致性視圖）、--master-data=2（記錄 Binlog 位置便於 PITR）。

2. Percona XtraBackup 物理熱備（推薦生產）

# 安裝
yum install percona-xtrabackup-80

# 全備
xtrabackup --backup --target-dir=/backup/full_$(date +%F) --user=root --password=xxx

# 增備
xtrabackup --backup --target-dir=/backup/inc_$(date +%F) --incremental-basedir=/backup/full_2024-01-01 --user=root --password=xxx

# 還原準備
xtrabackup --prepare --target-dir=/backup/full_2024-01-01
# 停止 MySQL、清空 datadir、複製回、修正權限、啟動

五、還原操作

1. 邏輯備份還原

# 建庫
mysql -u root -p -e "CREATE DATABASE phpbb2 CHARACTER SET utf8mb4;"

# 導入
mysql -u root -p phpbb2 < /backup/phpbb2_2024-01-01.sql
# 或壓縮文件
zcat /backup/phpbb2_2024-01-01.sql.gz | mysql -u root -p phpbb2

2. 物理備份還原（冷備/xtrabackup）

systemctl stop mysqld
rm -rf /var/lib/mysql/*   # 清空或備份舊目錄
tar xzvf /backup/phpbb2_db_2024-01-01.tgz -C /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld

六、定時備份排程

# 編輯 crontab
crontab -e

# 每天凌晨 2:30 全庫邏輯備份
30 2 * * * /usr/bin/mysqldump -u root -p'pass' --all-databases --single-transaction | gzip > /backup/full_$(date +\%F).sql.gz

# 每小時增量物理備份（需配合 xtrabackup 腳本）
0 * * * * /scripts/xtrabackup_inc.sh

七、備份驗證與最佳實踐清單

✅ 定期（月度）演練「全量還原至測試環境」驗證備份可用性。
✅ 異地備份：將備份同步至異地存儲（S3、NFS、異地服務器）。
✅ 保留策略：每日保留 7 天、每週保留 4 週、每月保留 12 月。
✅ 監控告警：備份腳本返回碼、文件大小、耗時納入監控。
✅ 權限最小化：備份賬號僅需 SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER, RELOAD, REPLICATION CLIENT。