作者 | 吳守陽

審校 | 重樓

簡介

MySQLDump 是 MySQL 數據庫管理系統提供的一個實用工具，用于創建數據庫的邏輯備份。它通過生成 SQL 腳本文件，將數據庫中的數據和結構導出，以便進行備份和恢復。這個工具非常適用于需要遷移數據庫、備份數據、或者將數據庫內容導出為 SQL 文件的場景。

工作原理

MySQLDump 的工作原理如下：

1. 連接數據庫：MySQLDump 通過指定的用戶憑證連接到 MySQL 服務器。
2. 鎖定表：為了保證數據一致性，MySQLDump 在導出數據之前會鎖定表或使用事務（具體取決于使用的選項，如--lock-tables 或--single-transaction）。
3. 導出數據和結構：MySQLDump 生成包含CREATE TABLE 和INSERT INTO 語句的 SQL 文件，這些語句可以重建表的結構并重新插入數據。
4. 寫入文件：最終生成的 SQL 腳本文件被寫入到指定的輸出文件中，或者直接輸出到標準輸出（如控制臺）。

優點

1. 簡單易用：MySQLDump 提供了多種選項，可以靈活地導出數據和表結構。
2. 廣泛支持：它支持幾乎所有的 MySQL 和 MariaDB 版本。
3. 易于恢復：導出的 SQL 文件可以直接用于恢復數據庫，通過簡單的 MySQL 命令即可重新導入數據。
4. 兼容性好：SQL 文件是純文本格式，易于編輯和傳輸。

缺點

1. 性能問題：對于大型數據庫，MySQLDump 的備份和恢復速度可能較慢，尤其是在網絡較慢或磁盤性能不佳的環境下。
2. 數據一致性問題：在備份過程中，如果沒有正確使用事務或鎖定表，可能會導致數據不一致。
3. 無增量備份：MySQLDump 不支持增量備份，每次備份都需要導出整個數據庫。

使用場景

1. 小型數據庫備份：對于小型數據庫，MySQLDump 是一個簡單且高效的備份工具。
2. 數據庫遷移：在將數據庫從一個服務器遷移到另一個服務器時，MySQLDump 可以生成完整的 SQL 腳本，方便在新服務器上重建數據庫。
3. 開發和測試：在開發和測試環境中，可以使用MySQLDump 創建數據庫的快照，以便隨時恢復到某個特定狀態。
4. 數據導出：將數據庫數據導出為 SQL 文件，以便進行數據分析、歸檔或傳輸。

備份示例

備份整個數據庫帶備份點：

mysqldump -u username -p password --single-transaction --source-data=2 --all-databases > all_databases.sql

備份整個數據庫帶備份點、GTID點位：

mysqldump -u username -p password --single-transaction --source-data=2 --set-gtid-purged=on --all-databases > all_databases.sql

備份整個數據庫將行合并一起批量 INSERT：

mysqldump -u username -p password --single-transaction --extended-insert --all-databases > all_databases.sql

備份整個數據庫將每行數據拆分成一個INSERT：

mysqldump -u username -p password --single-transaction --skip-extended-insert --all-databases > all_databases.sql

備份整個數據庫使用完整的insert語句(包含列名稱)：

mysqldump -u username -p password --single-transaction --complete-insert --all-databases > all_databases.sql

備份整個數據庫包括觸發器、存儲過程、事件：

mysqldump -u username -p password --single-transaction --triggers --routines --events --all-databases > all_databases.sql

備份整個數據庫：

mysqldump -u username -p password --single-transaction --all-databases > all_databases.sql

備份單個數據庫：

mysqldump -u username -p password --single-transaction database_name > database_name.sql

備份多個指定的數據庫：

mysqldump -u username -p password --single-transaction --databases database1 database2 > databases.sql

備份單個表：

mysqldump -u username -p password --single-transaction database_name table_name > table_name.sql

備份多個指定的表：

mysqldump -u username -p password --single-transaction database_name table1 table2 > tables.sql

僅備份表結構：

mysqldump -u username -p password --single-transaction --no-data database_name > schema.sql

僅備份數據：

mysqldump -u username -p password --single-transaction --no-create-info database_name > data.sql

使用 gzip 壓縮備份文件：

mysqldump -u username -p password --single-transaction database_name | gzip > database_name.sql.gz

備份并排除某些表：

mysqldump -u username -p password --single-transaction --ignore-table=database_name.table_name database_name > database_name.sql

備份時不包含 CREATE DATABASE 語句：

mysqldump -u username -p password --single-transaction --no-create-db database_name > database_name.sql

備份過程中添加清除表的語句：

mysqldump -u username -p password --single-transaction --add-drop-table database_name > database_name.sql

備份時生成帶有時間戳的文件名：

mysqldump -u username -p password --single-transaction database_name > database_name_`date +%Y%m%d_%H%M%S`.sql

備份帶where條件關閉GTID點位：

mysqldump username -p password -t --single-transaction --set-gtid-purged=OFF --where="id>=2 and id <=8" database_name table_name >t1.sql

備份整個數據庫將INSERT替換為REPLACE INTO：

mysqldump -u username -p password --single-transaction --replace --all-databases > all_databases.sql

備份整個數據庫避免備份語句寫入slowlog：

mysqldump -u username -p password --single-transaction --mysqld-long-query-time=30s --all-databases > all_databases.sql

參數

--print-defaults

打印程序參數列表并退出。

--no-defaults

不從任何選項文件中讀取默認選項，除了登錄文件。

--defaults-file=#

僅從給定文件#讀取默認選項。

--defaults-extra-file=#

在全局文件讀取后再讀取這個文件#。

--defaults-group-suffix=#

也讀取group與suffix拼接的組。

--login-path=#

從登錄文件中讀取此路徑。

--no-login-paths

不從登錄路徑文件中讀取登錄路徑。

-A, --all-databases

傾倒所有數據庫。這與--databases選項相同，只是選擇了所有數據庫。

-Y, --all-tablespaces

傾倒所有表空間。

-y, --no-tablespaces

不傾倒任何表空間信息。

--add-drop-database

在每個CREATE之前添加一個DROP DATABASE。

--add-drop-table

在每個CREATE之前添加一個DROP TABLE。 (默認為開啟；使用--skip-add-drop-table禁用。)

--add-drop-trigger

在每個CREATE之前添加一個DROP TRIGGER。

--add-locks

在INSERT語句周圍添加鎖定。 (默認為開啟；使用--skip-add-locks禁用。)

--allow-keywords

允許創建關鍵字列名。

--apply-replica-statements

在“CHANGE REPLICATION SOURCE”之前添加“STOP REPLICA”，并在轉儲底部添加“START REPLICA”。使用--output-as-version切換為舊術語。

--apply-slave-statements

此選項已棄用，將在將來的版本中刪除。請改用apply-replica-statements。

--bind-address=name

綁定到的IP地址。

--character-sets-dir=name

字符集文件目錄。

--column-statistics

添加一個ANALYZE TABLE語句以重新生成任何現有的列統計信息。 (默認為開啟；使用--skip-column-statistics禁用。)

-i, --comments

寫入額外信息。 (默認為開啟；使用--skip-comments禁用。)

--compatible=name

更改轉儲以與給定模式兼容。默認情況下，表以針對MySQL優化的格式轉儲。唯一合法的模式是ANSI。需要MySQL服務器版本4.1.0或更高。

--compact

提供更少冗長的輸出（用于調試）。禁用結構注釋和頁眉/頁腳構造。啟用選項--skip-add-drop-table、--skip-add-locks、--skip-comments、--skip-disable-keys、--skip-set-charset。

-c, --complete-insert

使用完整的INSERT語句。

-C, --compress

在服務器/客戶端協議中使用壓縮。

-a, --create-options

包括所有MySQL特定的創建選項。 (默認為開啟；使用--skip-create-options禁用。)

-B, --databases

轉儲多個數據庫。注意在用法上的不同；在這種情況下不給出表。所有名稱參數都被視為數據庫名稱。將包括“USE db_name;”在輸出中。

-#, --debug[=#]

這是非調試版本。捕獲此錯誤并退出。

--debug-check

這是非調試版本。捕獲此錯誤并退出。

--debug-info

這是非調試版本。捕獲此錯誤并退出。

--default-character-set=name

設置默認字符集。

--delete-source-logs

在備份之前輪換日志，等同于FLUSH LOGS，并在備份后清除所有舊的二進制日志，等同于PURGE LOGS。這將自動啟用--source-data。

--delete-master-logs

此選項已棄用，將在將來的版本中刪除。請改用delete-source-logs。

-K, --disable-keys

將“/*!40000 ALTER TABLE tb_name DISABLE KEYS */;”和“/*!40000 ALTER TABLE tb_name ENABLE KEYS */;”放入輸出中。 (默認為開啟；使用--skip-disable-keys禁用。)

--dump-replica[=#]

這導致源的二進制日志位置和文件名附加到轉儲的數據輸出中。將值設置為1，將其打印為CHANGE REPLICATION SOURCE命令在轉儲的數據輸出中；如果等于2，那個命令將以注釋符號前綴。此選項將打開--lock-all-tables，除非還指定了--single-transaction（在這種情況下，全局讀鎖僅在轉儲開始時短暫持有 - 不要忘記閱讀有關--single-transaction的內容）。在所有情況下，日志上的任何操作都將在轉儲的確切時刻發生。選項會自動關閉--lock-tables。

--dump-slave[=#]

此選項已棄用，將在將來的版本中刪除。請改用dump-replica。

-E, --events

轉儲事件。

-e, --extended-insert

使用包含多個VALUES列表的多行INSERT語法。 (默認為開啟；使用--skip-extended-insert禁用。)

--fields-terminated-by=name

輸出文件中的字段以給定字符串終止。

--fields-enclosed-by=name

輸出文件中的字段以給定字符封閉。

--fields-optionally-enclosed-by=name

輸出文件中的字段可以選擇以給定字符封閉。

--fields-escaped-by=name

輸出文件中的字段以給定字符轉義。

-F, --flush-logs

在開始轉儲之前在服務器中刷新日志文件。請注意，如果一次性轉儲多個數據庫（使用--databases=或--all-databases選項），則將為每個轉儲的數據庫刷新日志。例外情況是使用--lock-all-tables或--source-data：在這種情況下，日志將僅刷新一次，對應于所有表被鎖定的時刻。因此，如果您希望您的轉儲和日志刷新在完全相同的時刻發生，您應該使用--lock-all-tables或--source-data與--flush-logs。

--flush-privileges

在轉儲MySQL數據庫后發出FLUSH PRIVILEGES語句。應在轉儲包含MySQL數據庫及任何其他依賴于MySQL數據庫數據的數據庫時使用。

-f, --force

即使遇到SQL錯誤也繼續。

-?, --help

顯示此幫助消息并退出。

--hex-blob

以十六進制格式轉儲二進制字符串（BINARY、VARBINARY、BLOB）。

-h, --host=name

連接的主機。

--ignore-error=name

遇到時要忽略的錯誤號列表，用逗號分隔。

--ignore-table=name

不轉儲指定表。要忽略多個表，多次使用此指令，每次指定數據庫和表名，例如--ignore-table=database.table。

--include-source-host-port

在使用--dump-replica生成的轉儲中的“CHANGE REPLICATION SOURCE TO..”中添加“SOURCE_HOST=<host>，SOURCE_PORT=<port>”。

--include-master-host-port

此選項已棄用，將在將來的版本中刪除。請改用include-source-host-port。

--insert-ignore

使用INSERT IGNORE插入行。

--lines-terminated-by=name

輸出文件中的行以給定字符串終止。

-x, --lock-all-tables

鎖定所有數據庫中的所有表。通過在整個轉儲期間獲取全局讀鎖來實現。自動關閉--single-transaction和--lock-tables。

-l, --lock-tables

鎖定所有表以供讀取。 (默認為開啟；使用--skip-lock-tables禁用。)

--log-error=name

將警告和錯誤追加到給定文件中。

--mysqld-long-query-time=#

為此轉儲會話設置long_query_time。省略標志意味著使用服務器值。

--source-data[=#]

將二進制日志位置和文件名附加到輸出。如果等于1，則輸出為CHANGE REPLICATION SOURCE命令；如果等于2，則該命令以注釋符號為前綴。該選項會自動打開--lock-all-tables，除非同時指定了--single-transaction（在這種情況下，在轉儲的開始時會短暫地獲取全局讀鎖）。

--master-data[=#]

此選項已棄用，并將在將來的版本中移除。請改用--source-data。

--max-allowed-packet=#

發送到服務器或從服務器接收的最大數據包長度。

--net-buffer-length=#

TCP/IP和套接字通信的緩沖區大小。

--no-autocommit

在表周圍包裹autocommit/commit語句。

-n, --no-create-db

如果給出了--all-databases或--databases，則不輸出每個轉儲數據庫的CREATE DATABASE ... IF EXISTS語句。

-t, --no-create-info

不輸出表創建信息。

-d, --no-data

不包括行信息。

-N, --no-set-names

等同于--skip-set-charset。

--opt

等同于--add-drop-table、--add-locks、--create-options、--quick、--extended-insert、

--lock-tables、--set-charset和--disable-keys。默認情況下啟用，使用--skip-opt禁用。

--order-by-primary

按主鍵或第一個唯一鍵排序每個表的行。當轉儲MyISAM表以加載到InnoDB表時很有用，但會使轉儲本身需要更長時間。

-p, --password[=name]

連接到服務器時使用的密碼。如果未提供密碼，則會從tty中詢問。

-,, --password1[=name]

第一因子認證插件的密碼。

-,, --password2[=name]

第二因子認證插件的密碼。

-,, --password3[=name]

第三因子認證插件的密碼。

- P, --port=＃

連接時要使用的端口號。

--protocol=name

連接使用的協議（tcp，socket，pipe，memory）。

-q, --quick

不緩沖查詢，直接轉儲到stdout。默認情況下啟用，使用--skip-quick禁用。

-Q, --quote-names

使用反引號（`）引用表和列名。默認情況下啟用，使用--skip-quote-names禁用。

--replace

使用REPLACE INTO而不是INSERT INTO。

-r, --result-file=name

將輸出直接寫入給定的文件。此選項應在使用回車換行對（\r\n）分隔文本行的系統（例如DOS、Windows）中使用。此選項確保只使用單個換行符。

-R, --routines

轉儲存儲過程和函數。

--set-charset

將'SET NAMES default_character_set'添加到輸出中。默認情況下啟用，使用--skip-set-charset禁用。

--set-gtid-purged[=name]

將'SET @@GLOBAL.GTID_PURGED'添加到輸出中。此選項的可能值包括ON、COMMENTED、OFF和AUTO。如果使用ON并且服務器上未啟用GTIDs，則會生成錯誤。如果使用COMMENTED，則將'SET @@GLOBAL.GTID_PURGED'作為注釋添加。如果使用OFF，則此選項不起作用。如果使用AUTO并且服務器上啟用了GTIDs，則會將'SET @@GLOBAL.GTID_PURGED'添加到輸出中。如果禁用了GTIDs，則AUTO不起作用。如果未提供任何值，則將考慮默認值（AUTO）。

--single-transaction

通過在單個事務中轉儲所有表來創建一致的快照。僅適用于支持多版本控制的存儲引擎（目前僅有InnoDB）；不能保證對其他存儲引擎是一致的。在進行--single-transaction轉儲時，為確保一個有效的轉儲文件（正確的表內容和二進制日志位置），不應有其他連接使用以下語句：ALTER TABLE、DROP TABLE、RENAME TABLE、TRUNCATE TABLE，因為一致的快照與這些操作不是隔離的。此選項會自動關閉--lock-tables。

--dump-date

在輸出的末尾加上轉儲日期。默認情況下啟用，使用--skip-dump-date禁用。

--skip-opt

禁用--opt。禁用--add-drop-table、--add-locks、--create-options、--quick、--extended-insert、--lock-tables、--set-charset和--disable-keys。

-S, --socket=name

要使用的套接字文件。

--server-public-key-path=name

服務器公共RSA密鑰的文件路徑（PEM格式）。

--get-server-public-key

獲取服務器的公共密鑰。

--ssl-mode=name

SSL連接模式。

--ssl-ca=name

CA文件的路徑（PEM格式）。

--ssl-capath=name

CA目錄的路徑。

--ssl-cert=name

X509證書的路徑（PEM格式）。

--ssl-cipher=name

要使用的SSL密碼。

--ssl-key=name

X509密鑰的路徑（PEM格式）。

--ssl-crl=name

證書吊銷列表。

--ssl-crlpath=name

證書吊銷列表路徑。

--tls-version=name

要使用的TLS版本。允許的值為：TLSv1.2、TLSv1.3。

--ssl-fips-mode=name

SSL FIPS模式（僅適用于OpenSSL）。允許的值為：OFF、ON、STRICT。

--tls-ciphersuites=name

要使用的TLS v1.3密碼。

--ssl-session-data=name

要使用的會話數據文件以啟用SSL會話重用。

--ssl-session-data-continue-on-failed-reuse

如果設置為ON，則此選項將允許連接在會話數據無法重用時仍然成功。

--tls-sni-servername=name

要傳遞給服務器的SNI服務器名稱。

-T, --tab=name

為每個表創建制表符分隔的文本文件并指定路徑。注意：僅當mysqldump在與mysqld服務器相同的機器上運行時才起作用。

--tables

覆蓋選項--databases（-B）。

--output-as-version[=name]

定義轉儲中用于副本和事件命令的術語，允許創建與僅接受過時命令的較低版本兼容的轉儲。此選項的可能值為SERVER、BEFORE_8_0_23和BEFORE_8_2_0。默認為SERVER，如果設置，則讀取服務器版本并輸出與該版本兼容的命令。這意味著如果服務器低于8.2.0，則會輸出事件的DEPRECATED DISABLE ON SLAVE術語；如果低于8.0.23，則還會使用事件的DEPRECATED SLAVE/CHANGE MASTER術語。如果設置為BEFORE_8_2_0，則SHOW CREATE EVENT命令將始終顯示如何在低于8.2.0版本的服務器上創建事件。如果設置為BEFORE_8_0_23，則轉儲還將包含像START SLAVE或CHANGE MASTER TO等已棄用的副本命令。這會影響--events、--dump-replica、--source-data、--apply-replica-statements和--include-source-host-port的輸出。

--triggers

為每個轉儲的表轉儲觸發器。默認情況下啟用，使用--skip-triggers禁用。

--tz-utc

在轉儲的頂部設置'TIME_ZONE='+00:00'，允許在服務器具有不同時區數據或在具有不同時區的服務器之間移動數據時轉儲TIMESTAMP數據。默認情況下啟用，使用--skip-tz-utc。

-u, --user=name

指定用于登錄的用戶名，如果不是當前用戶。

-v, --verbose

打印有關各個階段的詳細信息。

-V, --version

輸出版本信息并退出。

-w, --where=name

僅轉儲所選記錄。引號是必需的。

-X, --xml

將數據庫轉儲為格式良好的XML。

--plugin-dir=name

指定客戶端插件的目錄。

--default-auth=name

指定默認的認證客戶端插件。

--enable-cleartext-plugin

啟用/禁用明文認證插件。

-M, --network-timeout

允許通過將max_allowed_packet設置為最大值，net_read_timeout/net_write_timeout設置為大值來轉儲大表。默認情況下啟用，使用--skip-network-timeout禁用。

--show-create-table-skip-secondary-engine

控制是否轉儲SECONDARY_ENGINE CREATE TABLE子句。對不支持服務器端選項的舊服務器沒有影響。

--compression-algorithms=name

在服務器/客戶端協議中使用的壓縮算法。有效值是任何'zstd'、'zlib'、'uncompressed'的組合。

--zstd-compression-level=#

在客戶端/服務器協議中使用的此壓縮級別，如果--compression-algorithms=zstd。有效范圍是1到22（包括）。默認值為3。

--skip-generated-invisible-primary-key

控制是否轉儲生成的不可見主鍵和鍵列。

--init-command=name

連接到MySQL服務器時要執行的單個SQL命令。在重新連接時將自動重新執行。

--init-command-add=name

添加要在連接到MySQL服務器時執行的SQL命令到列表中。在重新連接時將自動重新執行。

--ignore-views

跳過轉儲表視圖。

總結

MySQLDump 是 MySQL 數據庫管理系統中一個非常有用的工具，適用于各種備份和遷移場景。盡管它在處理大型數據庫時可能存在性能和一致性問題，但其簡單性和廣泛支持使其在許多情況下仍然是首選工具。通過合理配置選項和參數，MySQLDump 可以滿足大多數 MySQL 數據庫的備份和恢復需求。

作者介紹

吳守陽，51CTO社區編輯，擁有8年DBA工作經驗，熟練管理MySQL、Redis、MongoDB等開源數據庫。精通性能優化、備份恢復和高可用性架構設計。善于故障排除和自動化運維，保障系統穩定可靠。具備良好的團隊合作和溝通能力，致力于為企業提供高效可靠的數據庫解決方案。