MySQL mysqldump資料匯出詳解

介紹

在日常維護工作當中經常會需要對資料進行匯出操作，而mysqldump是匯出資料過程中使用非常頻繁的一個工具；它自帶的功能引數非常多，文章中會列舉出一些常用的操作，在文章末尾會將所有的引數詳細說明列出來。

語法：

預設不帶引數的匯出，匯出文字內容大概如下：建立資料庫判斷語句-刪除表-建立表-鎖表-禁用索引-插入資料-啟用索引-解鎖表。

插入測試資料

1.匯出所有資料庫

該命令會匯出包括系統資料庫在內的所有資料庫

2.匯出db1、db2兩個資料庫的所有資料

3.匯出db1中的a1、a2表
注意匯出指定表只能針對一個資料庫進行匯出，且匯出的內容中和匯出資料庫也不一樣，匯出指定表的匯出文字中沒有建立資料庫的判斷語句，只有刪除表-建立表-匯入資料

4.條件匯出，匯出db1表a1中id=1的資料
條件匯出只能匯出單個表

5.生成新的binlog檔案,-F
有時候會希望匯出資料之後生成一個新的binlog檔案,只需要加上-F引數即可

6.只匯出表結構不匯出資料，–no-data

7.跨伺服器匯出匯入資料

將h1伺服器中的db1資料庫的所有資料匯入到h2中的db2資料庫中，db2的資料庫必須存在否則會報錯

加上-C引數可以啟用壓縮傳遞。

8.將主庫的binlog位置和檔名追加到匯出資料的檔案中，–dump-slave
該引數在在從伺服器上執行，相當於執行show slave status。當設定為1時，將會以CHANGE MASTER命令輸出到資料檔案；設定為2時，會在change前加上註釋。

該選項將會開啟–lock-all-tables，除非–single-transaction被指定。

在執行完後會自動關閉–lock-tables選項。–dump-slave預設是1

9.將當前伺服器的binlog的位置和檔名追加到輸出檔案，–master-data
改引數和–dump-slave方法一樣，只是它是記錄的是當前伺服器的binlog，相當於執行show master status。

10–opt
等同於–add-drop-table, –add-locks, –create-options, –quick, –extended-insert, –lock-tables, –set-charset, –disable-keys 該選項預設開啟, 可以用–skip-opt禁用.

11保證匯出的一致性狀態–single-transaction
該選項在匯出資料之前提交一個BEGIN SQL語句，BEGIN 不會阻塞任何應用程式且能保證匯出時資料庫的一致性狀態。它只適用於多版本儲存引擎（它不顯示加鎖通過判斷版本來對比資料），僅InnoDB。本選項和–lock-tables 選項是互斥的，因為LOCK TABLES 會使任何掛起的事務隱含提交。要想匯出大表的話，應結合使用–quick 選項。

12–lock-tables, -l
開始匯出前，鎖定所有表。用READ LOCAL鎖定表以允許MyISAM表並行插入。對於支援事務的表例如InnoDB和BDB，–single-transaction是一個更好的選擇，因為它根本不需要鎖定表。

請注意當匯出多個資料庫時，–lock-tables分別為每個資料庫鎖定表。因此，該選項不能保證匯出檔案中的表在資料庫之間的邏輯一致性。不同資料庫表的匯出狀態可以完全不同。

13匯出儲存過程和自定義函式–routines, -R

引數說明:

–all-databases , -A
匯出全部資料庫。
mysqldump -uroot -p –all-databases
–all-tablespaces , -Y
匯出全部表空間。
mysqldump -uroot -p –all-databases –all-tablespaces
–no-tablespaces , -y
不匯出任何表空間資訊。
mysqldump -uroot -p –all-databases –no-tablespaces
–add-drop-database
每個資料庫建立之前新增drop資料庫語句。
mysqldump -uroot -p –all-databases –add-drop-database
–add-drop-table
每個資料表建立之前新增drop資料表語句。(預設為開啟狀態，使用–skip-add-drop-table取消選項)

mysqldump -uroot -p –all-databases (預設新增drop語句)
mysqldump -uroot -p –all-databases –skip-add-drop-table (取消drop語句)
–add-locks
在每個表匯出之前增加LOCK TABLES並且之後UNLOCK TABLE。(預設為開啟狀態，使用–skip-add-locks取消選項)
mysqldump -uroot -p –all-databases (預設新增LOCK語句)
mysqldump -uroot -p –all-databases –skip-add-locks (取消LOCK語句)

–allow-keywords
允許建立是關鍵詞的列名字。這由表名字首於每個列名做到。
mysqldump -uroot -p –all-databases –allow-keywords
–apply-slave-statements
在’CHANGE MASTER’前新增’STOP SLAVE’，並且在匯出的最後新增’START SLAVE’。
mysqldump -uroot -p –all-databases –apply-slave-statements
–character-sets-dir
字符集檔案的目錄
mysqldump -uroot -p –all-databases –character-sets-dir=/usr/local/mysql/share/mysql/charsets
–comments
附加註釋資訊。預設為開啟，可以用–skip-comments取消

mysqldump -uroot -p –all-databases (預設記錄註釋)
mysqldump -uroot -p –all-databases –skip-comments (取消註釋)
–compatible
匯出的資料將和其它資料庫或舊版本的MySQL 相相容。值可以為ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options、no_field_options等，
要使用幾個值，用逗號將它們隔開。它並不保證能完全相容，而是儘量相容。
mysqldump -uroot -p –all-databases –compatible=ansi
–compact
匯出更少的輸出資訊(用於除錯)。去掉註釋和頭尾等結構。可以使用選項：–skip-add-drop-table –skip-add-locks –skip-comments –skip-disable-keys

mysqldump -uroot -p –all-databases –compact
–complete-insert, -c
使用完整的insert語句(包含列名稱)。這麼做能提高插入效率，但是可能會受到max_allowed_packet引數的影響而導致插入失敗。
mysqldump -uroot -p –all-databases –complete-insert
–compress, -C
在客戶端和伺服器之間啟用壓縮傳遞所有資訊
mysqldump -uroot -p –all-databases –compress
–create-options, -a
在CREATE TABLE語句中包括所有MySQL特性選項。(預設為開啟狀態)
mysqldump -uroot -p –all-databases
–databases, -B
匯出幾個資料庫。引數後面所有名字參量都被看作資料庫名。
mysqldump -uroot -p –databases test mysql
–debug
輸出debug資訊，用於除錯。預設值為：d:t,/tmp/mysqldump.trace

mysqldump -uroot -p –all-databases –debug
mysqldump -uroot -p –all-databases –debug=” d:t,/tmp/debug.trace”
–debug-check
檢查記憶體和開啟檔案使用說明並退出。
mysqldump -uroot -p –all-databases –debug-check
–debug-info
輸出除錯資訊並退出
mysqldump -uroot -p –all-databases –debug-info
–default-character-set
設定預設字符集，預設值為utf8
mysqldump -uroot -p –all-databases –default-character-set=utf8
–delayed-insert
採用延時插入方式（INSERT DELAYED）匯出資料
mysqldump -uroot -p –all-databases –delayed-insert
–delete-master-logs
master備份後刪除日誌. 這個引數將自動啟用–master-data。

mysqldump -uroot -p –all-databases –delete-master-logs
–disable-keys
對於每個表，用/*!40000 ALTER TABLE tbl_name DISABLE KEYS */;和/*!40000 ALTER TABLE tbl_name ENABLE KEYS */;語句引用INSERT語句。這樣可以更快地匯入dump出來的檔案，因為它是在插入所有行後建立索引的。該選項只適合MyISAM表，預設為開啟狀態。
mysqldump -uroot -p –all-databases
–dump-slave
該選項將主的binlog位置和檔名追加到匯出資料的檔案中(show slave status)。設定為1時，將會以CHANGE MASTER命令輸出到資料檔案；設定為2時，會在change前加上註釋。該選項將會開啟–lock-all-tables，除非–single-transaction被指定。該選項會自動關閉–lock-tables選項。預設值為0。
mysqldump -uroot -p –all-databases –dump-slave=1
mysqldump -uroot -p –all-databases –dump-slave=2
–master-data
該選項將當前伺服器的binlog的位置和檔名追加到輸出檔案中(show master status)。如果為1，將會輸出CHANGE MASTER 命令；如果為2，輸出的CHANGE MASTER命令前新增註釋資訊。該選項將開啟–lock-all-tables 選項，除非–single-transaction也被指定（在這種情況下，全域性讀鎖在開始匯出時獲得很短的時間；其他內容參考下面的–single-transaction選項）。該選項自動關閉–lock-tables選項。

mysqldump -uroot -p –host=localhost –all-databases –master-data=1;
mysqldump -uroot -p –host=localhost –all-databases –master-data=2;
–events, -E
匯出事件。
mysqldump -uroot -p –all-databases –events
–extended-insert, -e
使用具有多個VALUES列的INSERT語法。這樣使匯出檔案更小，並加速匯入時的速度。預設為開啟狀態，使用–skip-extended-insert取消選項。
mysqldump -uroot -p –all-databases
mysqldump -uroot -p –all-databases–skip-extended-insert (取消選項)
–fields-terminated-by
匯出檔案中忽略給定欄位。與–tab選項一起使用，不能用於–databases和–all-databases選項

mysqldump -uroot -p test test –tab=”/home/mysql” –fields-terminated-by=”#”
–fields-enclosed-by
輸出檔案中的各個欄位用給定字元包裹。與–tab選項一起使用，不能用於–databases和–all-databases選項
mysqldump -uroot -p test test –tab=”/home/mysql” –fields-enclosed-by=”#”
–fields-optionally-enclosed-by
輸出檔案中的各個欄位用給定字元選擇性包裹。與–tab選項一起使用，不能用於–databases和–all-databases選項
mysqldump -uroot -p test test –tab=”/home/mysql” –fields-enclosed-by=”#” –fields-optionally-enclosed-by =”#”
–fields-escaped-by
輸出檔案中的各個欄位忽略給定字元。與–tab選項一起使用，不能用於–databases和–all-databases選項
mysqldump -uroot -p mysql user –tab=”/home/mysql” –fields-escaped-by=”#”
–flush-logs
開始匯出之前重新整理日誌。
請注意：假如一次匯出多個資料庫(使用選項–databases或者–all-databases)，將會逐個資料庫重新整理日誌。除使用–lock-all-tables或者–master-data外。在這種情況下，日誌將會被重新整理一次，相應的所以表同時被鎖定。因此，如果打算同時匯出和重新整理日誌應該使用–lock-all-tables 或者–master-data 和–flush-logs。

mysqldump -uroot -p –all-databases –flush-logs
–flush-privileges
在匯出mysql資料庫之後，發出一條FLUSH PRIVILEGES 語句。為了正確恢復，該選項應該用於匯出mysql資料庫和依賴mysql資料庫資料的任何時候。
mysqldump -uroot -p –all-databases –flush-privileges
–force
在匯出過程中忽略出現的SQL錯誤。
mysqldump -uroot -p –all-databases –force
–help
顯示幫助資訊並退出。
mysqldump –help
–hex-blob
使用十六進位制格式匯出二進位制字串欄位。如果有二進位制資料就必須使用該選項。影響到的欄位型別有BINARY、VARBINARY、BLOB。
mysqldump -uroot -p –all-databases –hex-blob
–host, -h
需要匯出的主機資訊
mysqldump -uroot -p –host=localhost –all-databases
–ignore-table
不匯出指定表。指定忽略多個表時，需要重複多次，每次一個表。每個表必須同時指定資料庫和表名。例如：–ignore-table=database.table1 –ignore-table=database.table2 ……

mysqldump -uroot -p –host=localhost –all-databases –ignore-table=mysql.user
–include-master-host-port
在–dump-slave產生的’CHANGE MASTER TO..’語句中增加’MASTER_HOST=，MASTER_PORT=’
mysqldump -uroot -p –host=localhost –all-databases –include-master-host-port
–insert-ignore
在插入行時使用INSERT IGNORE語句.
mysqldump -uroot -p –host=localhost –all-databases –insert-ignore
–lines-terminated-by
輸出檔案的每行用給定字串劃分。與–tab選項一起使用，不能用於–databases和–all-databases選項。
mysqldump -uroot -p –host=localhost test test –tab=”/tmp/mysql” –lines-terminated-by=”##”
–lock-all-tables, -x
提交請求鎖定所有資料庫中的所有表，以保證資料的一致性。這是一個全域性讀鎖，並且自動關閉–single-transaction 和–lock-tables 選項。
mysqldump -uroot -p –host=localhost –all-databases –lock-all-tables
–lock-tables, -l
開始匯出前，鎖定所有表。用READ LOCAL鎖定表以允許MyISAM表並行插入。對於支援事務的表例如InnoDB和BDB，–single-transaction是一個更好的選擇，因為它根本不需要鎖定表。
請注意當匯出多個資料庫時，–lock-tables分別為每個資料庫鎖定表。因此，該選項不能保證匯出檔案中的表在資料庫之間的邏輯一致性。不同資料庫表的匯出狀態可以完全不同。

mysqldump -uroot -p –host=localhost –all-databases –lock-tables
–log-error
附加警告和錯誤資訊到給定檔案
mysqldump -uroot -p –host=localhost –all-databases –log-error=/tmp/mysqldump_error_log.err
–max_allowed_packet
伺服器傳送和接受的最大包長度。
mysqldump -uroot -p –host=localhost –all-databases –max_allowed_packet=10240
–net_buffer_length
TCP/IP和socket連線的快取大小。
mysqldump -uroot -p –host=localhost –all-databases –net_buffer_length=1024
–no-autocommit
使用autocommit/commit 語句包裹表。
mysqldump -uroot -p –host=localhost –all-databases –no-autocommit
–no-create-db, -n
只匯出資料，而不新增CREATE DATABASE 語句。

mysqldump -uroot -p –host=localhost –all-databases –no-create-db
–no-create-info, -t
只匯出資料，而不新增CREATE TABLE 語句。
mysqldump -uroot -p –host=localhost –all-databases –no-create-info
–no-data, -d
不匯出任何資料，只匯出資料庫表結構。
mysqldump -uroot -p –host=localhost –all-databases –no-data
–no-set-names, -N
等同於–skip-set-charset
mysqldump -uroot -p –host=localhost –all-databases –no-set-names
–opt
等同於–add-drop-table, –add-locks, –create-options, –quick, –extended-insert, –lock-tables, –set-charset, –disable-keys 該選項預設開啟, 可以用–skip-opt禁用.

mysqldump -uroot -p –host=localhost –all-databases –opt
–order-by-primary
如果存在主鍵，或者第一個唯一鍵，對每個表的記錄進行排序。在匯出MyISAM表到InnoDB表時有效，但會使得匯出工作花費很長時間。
mysqldump -uroot -p –host=localhost –all-databases –order-by-primary
–password, -p
連線資料庫密碼
–pipe(windows系統可用)
使用命名管道連線mysql
mysqldump -uroot -p –host=localhost –all-databases –pipe
–port, -P
連線資料庫埠號
–protocol
使用的連線協議，包括：tcp, socket, pipe, memory.

mysqldump -uroot -p –host=localhost –all-databases –protocol=tcp
–quick, -q
不緩衝查詢，直接匯出到標準輸出。預設為開啟狀態，使用–skip-quick取消該選項。
mysqldump -uroot -p –host=localhost –all-databases
mysqldump -uroot -p –host=localhost –all-databases –skip-quick
–quote-names,-Q
使用（`）引起表和列名。預設為開啟狀態，使用–skip-quote-names取消該選項。
mysqldump -uroot -p –host=localhost –all-databases
mysqldump -uroot -p –host=localhost –all-databases –skip-quote-names
–replace
使用REPLACE INTO 取代INSERT INTO.
mysqldump -uroot -p –host=localhost –all-databases –replace
–result-file, -r
直接輸出到指定檔案中。該選項應該用在使用回車換行對（\\r\\n）換行的系統上（例如：DOS，Windows）。該選項確保只有一行被使用。
mysqldump -uroot -p –host=localhost –all-databases –result-file=/tmp/mysqldump_result_file.txt
–routines, -R
匯出儲存過程以及自定義函式。
mysqldump -uroot -p –host=localhost –all-databases –routines
–set-charset
新增’SET NAMES default_character_set’到輸出檔案。預設為開啟狀態，使用–skip-set-charset關閉選項。

mysqldump -uroot -p –host=localhost –all-databases
mysqldump -uroot -p –host=localhost –all-databases –skip-set-charset
–single-transaction
該選項在匯出資料之前提交一個BEGIN SQL語句，BEGIN 不會阻塞任何應用程式且能保證匯出時資料庫的一致性狀態。它只適用於多版本儲存引擎，僅InnoDB。本選項和–lock-tables 選項是互斥的，因為LOCK TABLES 會使任何掛起的事務隱含提交。要想匯出大表的話，應結合使用–quick 選項。
mysqldump -uroot -p –host=localhost –all-databases –single-transaction
–dump-date
將匯出時間新增到輸出檔案中。預設為開啟狀態，使用–skip-dump-date關閉選項。
mysqldump -uroot -p –host=localhost –all-databases
mysqldump -uroot -p –host=localhost –all-databases –skip-dump-date
–skip-opt
禁用–opt選項.
mysqldump -uroot -p –host=localhost –all-databases –skip-opt
–socket,-S
指定連線mysql的socket檔案位置，預設路徑/tmp/mysql.sock
mysqldump -uroot -p –host=localhost –all-databases –socket=/tmp/mysqld.sock
–tab,-T
為每個表在給定路徑建立tab分割的文字檔案。注意：僅僅用於mysqldump和mysqld伺服器執行在相同機器上。

mysqldump -uroot -p –host=localhost test test –tab=”/home/mysql”
–tables
覆蓋–databases (-B)引數，指定需要匯出的表名。
mysqldump -uroot -p –host=localhost –databases test –tables test
–triggers
匯出觸發器。該選項預設啟用，用–skip-triggers禁用它。
mysqldump -uroot -p –host=localhost –all-databases –triggers
–tz-utc
在匯出頂部設定時區TIME_ZONE=’+00:00′ ，以保證在不同時區匯出的TIMESTAMP 資料或者資料被移動其他時區時的正確性。
mysqldump -uroot -p –host=localhost –all-databases –tz-utc
–user, -u
指定連線的使用者名稱。
–verbose, –v
輸出多種平臺資訊。

–version, -V
輸出mysqldump版本資訊並退出
–where, -w
只轉儲給定的WHERE條件選擇的記錄。請注意如果條件包含命令解釋符專用空格或字元，一定要將條件引用起來。
mysqldump -uroot -p –host=localhost –all-databases –where=” user=’root’”
–xml, -X
匯出XML格式.
mysqldump -uroot -p –host=localhost –all-databases –xml
–plugin_dir
客戶端外掛的目錄，用於相容不同的外掛版本。
mysqldump -uroot -p –host=localhost –all-databases –plugin_dir=”/usr/local/lib/plugin”
–default_auth
客戶端外掛預設使用許可權。
mysqldump -uroot -p –host=localhost –all-databases –default-auth=”/usr/local/lib/plugin/”

總結

文章中列舉了一些常用的匯出操作，還有很多其它的引數也會經常用到，包括“–add-drop-database”，“–apply-slave-statements”，“–triggers”等。