備份核心工具：mysqldump

mysqldump 採用 SQL 層面備份，輸出可直接執行的 SQL 腳本，跨版本升級相容性佳，是最常用的備份方式。

Windows 環境備份範例

# 基本備份單一資料庫
mysqldump -uroot -p123456 database_name > d:/backup_20240101.sql

# 備份指定表
mysqldump -uroot -p123456 database_name table_name > d:/backup_table.sql

Linux 環境進階備份策略

MyISAM 表備份（含鎖表、觸發器、存儲過程）

/usr/local/mysql/bin/mysqldump -uyejr -pyejr \
  --default-character-set=utf8 \
  --opt --extended-insert=false \
  --triggers -R --hex-blob -x \
  db_name > db_name.sql

• --opt：啟用 --quick --add-drop-table --add-locks --extended-insert --lock-tables
• --extended-insert=false：單行 INSERT，利於差異比對與部分恢復
• -x / --lock-all-tables：全域鎖定，確保一致性

InnoDB 表備份（熱備份，不鎖表）

/usr/local/mysql/bin/mysqldump -uyejr -pyejr \
  --default-character-set=utf8 \
  --opt --extended-insert=false \
  --triggers -R --hex-blob \
  --single-transaction \
  db_name > db_name.sql

--single-transaction 利用 MVCC 實現一致性快照，適用於 InnoDB 熱備份。

線上備份並記錄 Binlog 位置（適合主從搭建）

/usr/local/mysql/bin/mysqldump -uyejr -pyejr \
  --default-character-set=utf8 --opt \
  --master-data=1 --single-transaction --flush-logs \
  db_name > db_name.sql

--master-data=1 會在輸出檔案頭部寫入 CHANGE MASTER TO 語句，含當前 Binlog 檔名與位置。

資料庫還原操作

步驟 1：刪除並重建資料庫

mysqladmin -uroot -p123456 drop database_name
mysqladmin -uroot -p123456 create database_name

步驟 2：導入 SQL 腳本（兩種方式）

方式 A：管道導入（推薦）

/usr/local/mysql/bin/mysql -uyejr -pyejr db_name < db_name.sql

方式 B：SOURCE 命令（需進入 MySQL Client）

mysql -uroot -p
USE db_name;
SOURCE /tmp/db_name.sql;

注意：SOURCE 需指定絕對路徑，且檔案需為 mysqld 運行用戶可讀。

mysqldump 核心參數速查表

分類	參數	說明
連線選項	-u	用戶名
	-p	密碼（建議不寫在命令列，交互輸入）
	-h / -P	主機 IP / 埠號（大寫 P）
輸出內容控制	--add-drop-database	建庫前加 DROP DATABASE
	--add-drop-table	建表前加 DROP TABLE
	-n / --no-create-db	不包含 CREATE DATABASE
	-t / --no-create-info	不包含 CREATE TABLE
-d / --no-data	僅導出結構，無數據
輸出格式	--compact	精簡輸出（刪除註釋等）
	-c / --complete-insert	INSERT 包含欄位名
	-T / --tab=path	導出為純文本資料檔 + 建表 SQL 兩個檔案
字元集	--default-character-set=name	指定導出端字元集

常見組合範例

• 備份所有資料庫：mysqldump --all-databases > all.sql
• 備份多個指定庫：mysqldump --databases db1 db2 > multi.sql
• 僅導出結構：mysqldump --no-data --databases db1 > struct.sql
• 壓縮備份：mysqldump -u... db_name | gzip > db_name.sql.gz