MySQL mysqldump 數據導出全攻略：常用場景、核心參數與跨實例遷移實戰

MySQL mysqldump 數據導出全攻略：常用場景、核心參數與跨實例遷移實戰

mysqldump 是 MySQL 官方邏輯備份工具，參數極其豐富。本文梳理 13 個高頻使用場景，附帶完整參數速查表，幫助 DBA 快速定位所需命令。

一、 基礎語法與測試環境

mysqldump [OPTIONS] database [tables]
mysqldump [OPTIONS] --databases DB1 [DB2...]
mysqldump [OPTIONS] --all-databases

測試數據準備

CREATE DATABASE db1 DEFAULT CHARSET utf8;
USE db1;
CREATE TABLE a1(id int); INSERT INTO a1 VALUES(1),(2);
CREATE TABLE a2(id int); INSERT INTO a2 VALUES(2);
CREATE TABLE a3(id int); INSERT INTO a3 VALUES(3);

CREATE DATABASE db2 DEFAULT CHARSET utf8;
USE db2;
CREATE TABLE b1(id int); INSERT INTO b1 VALUES(1);
CREATE TABLE b2(id int); INSERT INTO b2 VALUES(2);

二、 13 個高頻導出場景實戰

1. 導出所有資料庫（含系統庫）

mysqldump -uroot -proot --all-databases > /tmp/all.sql

2. 導出指定多個資料庫

mysqldump -uroot -proot --databases db1 db2 > /tmp/user.sql

3. 導出單庫指定表

mysqldump -uroot -proot --databases db1 --tables a1 a2 > /tmp/db1.sql

輸出內容無 CREATE DATABASE 判斷，僅含 DROP TABLE、CREATE TABLE、INSERT。

4. 條件導出（單表 Where 過濾）

mysqldump -uroot -proot --databases db1 --tables a1 --where="id=1" > /tmp/a1.sql

5. 導出後生成新 Binlog（-F）

mysqldump -uroot -proot --databases db1 -F > /tmp/db1.sql

配合主從切換場景，確保備份位點對應新 binlog 文件。

6. 僅導出表結構（--no-data / -d）

mysqldump -uroot -proot --no-data --databases db1 > /tmp/db1.sql

7. 跨服務器直連導入（管道流式傳輸）

# h1 -> h2，目標庫 db2 必須預先存在
mysqldump --host=h1 -uroot -proot --databases db1 | mysql --host=h2 -uroot -proot db2

# 啟用壓縮傳輸（-C），跨公網/低帶寬場景極大提速
mysqldump --host=192.168.80.137 -uroot -proot -C --databases test | mysql --host=192.168.80.133 -uroot -proot test

8. 記錄從庫 Binlog 位點（--dump-slave）

在從庫執行，相當於 SHOW SLAVE STATUS。

-- 1: 輸出 CHANGE MASTER 命令
mysqldump -uroot -proot --dump-slave=1 --databases db1 > /tmp/db1.sql



-- 2: 註釋形式輸出
mysqldump -uroot -proot --dump-slave=2 --databases db1 > /tmp/db1.sql

會隱式開啟 --lock-all-tables，除非指定 --single-transaction。

9. 記錄主庫 Binlog 位點（--master-data）

在主庫執行，相當於 SHOW MASTER STATUS。參數含義同 --dump-slave。

10. 保證一致性備份（--single-transaction）

mysqldump -uroot -proot --single-transaction --quick --databases db1 > /tmp/db1.sql

• 啟動事務快照（REPEATABLE READ），不阻塞寫入，僅適用 InnoDB。
• 與 --lock-tables 互斥。
• 大表必加 --quick 避免緩存撐爆內存。

11. 鎖表備份（--lock-tables / -l）

MyISAM 表適用。InnoDB 優先用 --single-transaction。多庫備份時無法保證庫間邏輯一致性。

12. 導出存儲過程/函數/觸發器（--routines / -R）

mysqldump -uroot -proot --routines --databases db1 > /tmp/db1.sql

13. 優化導出性能（--opt 默認開啟）

--opt 等價於：--add-drop-table --add-locks --create-options --quick --extended-insert --lock-tables --set-charset --disable-keys。
大表導出建議顯式組合：--single-transaction --quick --extended-insert --set-charset。

三、 核心參數速查表（節選高頻）

四、 生產環境備份腳本範例

#!/bin/bash
# 每日全量備份，排除系統庫
FILE="mysql_$(date +%F)"
BACKDIR="/mysqlbackup"
mysql -uroot -e 'show databases' | egrep -v "Database|information_schema|performance_schema|mysql|sys" \
| xargs mysqldump -uroot --single-transaction --quick --extended-insert --databases > $BACKDIR/$FILE.sql
# 壓縮存儲
gzip $BACKDIR/$FILE.sql
# 保留 7 天
find $BACKDIR -mtime +7 -name "*.gz" -delete

五、 總結

• 邏輯備份首選 mysqldump，物理備份看 xtrabackup。
• InnoDB 生產環境強制使用 --single-transaction --quick。
• 跨機房遷移用 管道流式 + 壓縮，避免落地文件二次傳輸。
• 備份腳本納入監控，驗證備份文件大小、校驗和、還原演練。

作者：pursuer.chen | 博客：https://www.cnblogs.com/chenmh