作者 | 吳守陽

審校 | 重樓

簡介

MySQLDump 是 MySQL 數(shù)據(jù)庫管理系統(tǒng)提供的一個實用工具，用于創(chuàng)建數(shù)據(jù)庫的邏輯備份。它通過生成 SQL 腳本文件，將數(shù)據(jù)庫中的數(shù)據(jù)和結(jié)構(gòu)導出，以便進行備份和恢復。這個工具非常適用于需要遷移數(shù)據(jù)庫、備份數(shù)據(jù)、或者將數(shù)據(jù)庫內(nèi)容導出為 SQL 文件的場景。

工作原理

MySQLDump 的工作原理如下：

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

優(yōu)點

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

缺點

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

使用場景

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

備份示例

備份整個數(shù)據(jù)庫帶備份點：

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

備份整個數(shù)據(jù)庫帶備份點、GTID點位：

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

備份整個數(shù)據(jù)庫將行合并一起批量 INSERT：

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

備份整個數(shù)據(jù)庫將每行數(shù)據(jù)拆分成一個INSERT：

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

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

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

備份整個數(shù)據(jù)庫包括觸發(fā)器、存儲過程、事件：

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

備份整個數(shù)據(jù)庫：

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

備份單個數(shù)據(jù)庫：

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

備份多個指定的數(shù)據(jù)庫：

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

僅備份表結(jié)構(gòu)：

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

僅備份數(shù)據(jù)：

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條件關(guān)閉GTID點位：

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

備份整個數(shù)據(jù)庫將INSERT替換為REPLACE INTO：

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

備份整個數(shù)據(jù)庫避免備份語句寫入slowlog：

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

參數(shù)

--print-defaults

打印程序參數(shù)列表并退出。

--no-defaults

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

--defaults-file=#

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

--defaults-extra-file=#

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

--defaults-group-suffix=#

也讀取group與suffix拼接的組。

--login-path=#

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

--no-login-paths

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

-A, --all-databases

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

-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

允許創(chuàng)建關(guān)鍵字列名。

--apply-replica-statements

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

--apply-slave-statements

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

--bind-address=name

綁定到的IP地址。

--character-sets-dir=name

字符集文件目錄。

--column-statistics

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

-i, --comments

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

--compatible=name

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

--compact

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

-c, --complete-insert

使用完整的INSERT語句。

-C, --compress

在服務(wù)器/客戶端協(xié)議中使用壓縮。

-a, --create-options

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

-B, --databases

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

-#, --debug[=#]

這是非調(diào)試版本。捕獲此錯誤并退出。

--debug-check

這是非調(diào)試版本。捕獲此錯誤并退出。

--debug-info

這是非調(diào)試版本。捕獲此錯誤并退出。

--default-character-set=name

設(shè)置默認字符集。

--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[=#]

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

--dump-slave[=#]

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

-E, --events

轉(zhuǎn)儲事件。

-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

輸出文件中的字段以給定字符轉(zhuǎn)義。

-F, --flush-logs

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

--flush-privileges

在轉(zhuǎn)儲MySQL數(shù)據(jù)庫后發(fā)出FLUSH PRIVILEGES語句。應(yīng)在轉(zhuǎn)儲包含MySQL數(shù)據(jù)庫及任何其他依賴于MySQL數(shù)據(jù)庫數(shù)據(jù)的數(shù)據(jù)庫時使用。

-f, --force

即使遇到SQL錯誤也繼續(xù)。

-?, --help

顯示此幫助消息并退出。

--hex-blob

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

-h, --host=name

連接的主機。

--ignore-error=name

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

--ignore-table=name

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

--include-source-host-port

在使用--dump-replica生成的轉(zhuǎn)儲中的“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

鎖定所有數(shù)據(jù)庫中的所有表。通過在整個轉(zhuǎn)儲期間獲取全局讀鎖來實現(xiàn)。自動關(guān)閉--single-transaction和--lock-tables。

-l, --lock-tables

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

--log-error=name

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

--mysqld-long-query-time=#

為此轉(zhuǎn)儲會話設(shè)置long_query_time。省略標志意味著使用服務(wù)器值。

--source-data[=#]

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

--master-data[=#]

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

--max-allowed-packet=#

發(fā)送到服務(wù)器或從服務(wù)器接收的最大數(shù)據(jù)包長度。

--net-buffer-length=#

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

--no-autocommit

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

-n, --no-create-db

如果給出了--all-databases或--databases，則不輸出每個轉(zhuǎn)儲數(shù)據(jù)庫的CREATE DATABASE ... IF EXISTS語句。

-t, --no-create-info

不輸出表創(chuàng)建信息。

-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

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

-p, --password[=name]

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

-,, --password1[=name]

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

-,, --password2[=name]

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

-,, --password3[=name]

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

- P, --port=＃

連接時要使用的端口號。

--protocol=name

連接使用的協(xié)議（tcp，socket，pipe，memory）。

-q, --quick

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

-Q, --quote-names

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

--replace

使用REPLACE INTO而不是INSERT INTO。

-r, --result-file=name

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

-R, --routines

轉(zhuǎn)儲存儲過程和函數(shù)。

--set-charset

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

--set-gtid-purged[=name]

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

--single-transaction

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

--dump-date

在輸出的末尾加上轉(zhuǎn)儲日期。默認情況下啟用，使用--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

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

--get-server-public-key

獲取服務(wù)器的公共密鑰。

--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

要使用的會話數(shù)據(jù)文件以啟用SSL會話重用。

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

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

--tls-sni-servername=name

要傳遞給服務(wù)器的SNI服務(wù)器名稱。

-T, --tab=name

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

--tables

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

--output-as-version[=name]

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

--triggers

為每個轉(zhuǎn)儲的表轉(zhuǎn)儲觸發(fā)器。默認情況下啟用，使用--skip-triggers禁用。

--tz-utc

在轉(zhuǎn)儲的頂部設(shè)置'TIME_ZONE='+00:00'，允許在服務(wù)器具有不同時區(qū)數(shù)據(jù)或在具有不同時區(qū)的服務(wù)器之間移動數(shù)據(jù)時轉(zhuǎn)儲TIMESTAMP數(shù)據(jù)。默認情況下啟用，使用--skip-tz-utc。

-u, --user=name

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

-v, --verbose

打印有關(guān)各個階段的詳細信息。

-V, --version

輸出版本信息并退出。

-w, --where=name

僅轉(zhuǎn)儲所選記錄。引號是必需的。

-X, --xml

將數(shù)據(jù)庫轉(zhuǎn)儲為格式良好的XML。

--plugin-dir=name

指定客戶端插件的目錄。

--default-auth=name

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

--enable-cleartext-plugin

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

-M, --network-timeout

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

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

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

--compression-algorithms=name

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

--zstd-compression-level=#

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

--skip-generated-invisible-primary-key

控制是否轉(zhuǎn)儲生成的不可見主鍵和鍵列。

--init-command=name

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

--init-command-add=name

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

--ignore-views

跳過轉(zhuǎn)儲表視圖。

總結(jié)

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

作者介紹

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