萬盛學電腦網

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

MySQL 數據備份恢復三大核心方案:mysqldump、mysqlhotcopy、物理文件複製實戰

方案一:mysqldump 邏輯備份(最通用、跨版本)

常用命令模板

# 單庫備份
mysqldump -h 192.168.1.100 -u root -p ucenter > /backup/uc_20230306.sql

# 單表備份
mysqldump -u root -p dbname tablename > /backup/table.sql

# 全庫備份
mysqldump -u root -p --all-databases > /backup/full.sql

# 只備份結構 (含 DROP TABLE)
mysqldump -u root -p -d --add-drop-table dbname > /backup/struct.sql

# 排除指定表
mysqldump -u root -p dbname --ignore-table=dbname.log_table > /backup/no_log.sql

關鍵參數

  • --single-transaction:InnoDB 一致性熱備份(不鎖表)
  • --quick:流式輸出,不緩存全量結果集
  • --master-data=2:記錄 binlog 位點,便於主從搭建
  • --routines --triggers --events:包含存儲程序

恢復/導入

# 方式 1:客戶端 source
mysql -u root -p dbname < backup.sql
# 或進入 mysql> SOURCE /path/backup.sql;

# 方式 2:管道導入
mysql -u root -p target_db < backup.sql

方案二:mysqlhotcopy 物理熱備份(僅 MyISAM、本地、Linux)

# 備份單庫
mysqlhotcopy -h localhost -u root -p pass dbname /backup/

# 備份多庫
mysqlhotcopy -u root -p pass db1 db2 /backup/

# 正則匹配庫
mysqlhotcopy -u root -p pass 'db_.*' /backup/

優點:極快(文件級複製)、自動鎖表刷新日志。

缺點:僅支持 MyISAM、需本地執行、Perl 腳本依賴、MySQL 5.7+ 已移除。

恢復

cp -rf /backup/dbname /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql/dbname
systemctl restart mysqld

方案三:物理文件直接複製(停機冷備份/遷移)

Windows

  1. 停止服務:net stop mysql
  2. 複製 datadir 目錄(含 .frm/.MYD/.MYI 或 .ibd)
  3. 啟動服務

Linux

# 1. 找數據目錄
find / -name "*.ibd" -o -name "*.frm" 2>/dev/null | head -5

# 2. 停庫複製
systemctl stop mysqld
rsync -av /var/lib/mysql/ /backup/mysql_$(date +%F)/
systemctl start mysqld

文件轉 SQL(異構遷移)

  1. 新建空庫 mysqlbak
  2. 複製文件至 datadir/mysqlbak/
  3. chown -R mysql:mysql
  4. 啟動 MySQL,mysqldump mysqlbak > /backup/migrate.sql

方案對比選型表

維度mysqldumpmysqlhotcopy物理複製
引擎支持所有僅 MyISAM所有
熱備份是 (--single-transaction)是否 (需停機)
跨版本/跨平台優差差 (同版本同架構)
備份速度中快最快
恢復速度慢 (重放 SQL)快快
壓縮體積小 (可 gzip)大 (原始文件)大

生產環境建議策略

  • 日備:mysqldump --single-transaction --master-data=2 --quick | gzip
  • 周全備 + 日增備:配合 binlog 增量
  • 物理備份:Percona XtraBackup (InnoDB 熱備標準工具,替代 mysqlhotcopy)
  • 異地備份:上傳至對象存儲/異地服務器
  • 定期演練:季度恢復演練,驗證備份可用性
copyright © 萬盛學電腦網 all rights reserved