Mysqldump引數詳解
Mysqldump引數詳解
引數 引數說明
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--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:o,/tmp/mysqldump.trace
mysqldump -uroot -p --all-databases --debug
mysqldump -uroot -p --all-databases --debug=” d:t:o,/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=latin1
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--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位置和檔名追加到匯出資料的檔案中。設定為1時，將會以CHANGE MASTER命令輸出到資料檔案；
設定為2時，在命令前增加說明資訊。該選項將會開啟--lock-all-tables，除非--single-transaction被指定。
該選項會自動關閉--lock-tables選項。預設值為0。
mysqldump -uroot -p --all-databases --dump-slave=1
mysqldump -uroot -p --all-databases --dump-slave=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
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--master-data
該選項將binlog的位置和檔名追加到輸出檔案中。如果為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;
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--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/”
[root@nagios_test ~]# mysqldump --help
mysqldump Ver 10.13 Distrib 5.1.66, for redhat-linux-gnu (x86_64)
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Dumping structure and contents of MySQL databases and tables.
Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]
Default options are read from the following files in the given order:
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf
The following groups are read: mysqldump client
The following options may be given as the first argument:
--print-defaults Print the program argument list and exit.
--no-defaults Don't read default options from any option file.
--defaults-file=# Only read default options from the given file #.
--defaults-extra-file=# Read this file after the global files are read.
--all Deprecated. Use --create-options instead.
-A, --all-databases Dump all the databases. This will be same as --databases
with all databases selected.
-Y, --all-tablespaces
Dump all the tablespaces.
-y, --no-tablespaces
Do not dump any tablespace information.
--add-drop-database Add a DROP DATABASE before each create.
--add-drop-table Add a DROP TABLE before each create.
--add-locks Add locks around INSERT statements.
--allow-keywords Allow creation of column names that are keywords.
--character-sets-dir=name
Directory for character set files.
-i, --comments Write additional information.
--compatible=name Change the dump to be compatible with a given mode. By
default tables are dumped in a format optimized for
MySQL. Legal modes are: ansi, mysql323, mysql40,
postgresql, oracle, mssql, db2, maxdb, no_key_options,
no_table_options, no_field_options. One can use several
modes separated by commas. Note: Requires MySQL server
version 4.1.0 or higher. This option is ignored with
earlier server versions.
--compact Give less verbose output (useful for debugging). Disables
structure comments and header/footer constructs. Enables
options --skip-add-drop-table --skip-add-locks
--skip-comments --skip-disable-keys --skip-set-charset.
-c, --complete-insert
Use complete insert statements.
-C, --compress Use compression in server/client protocol.
-a, --create-options
Include all MySQL specific create options.
-B, --databases Dump several databases. Note the difference in usage; in
this case no tables are given. All name arguments are
regarded as database names. 'USE db_name;' will be
included in the output.
-#, --debug[=#] This is a non-debug version. Catch this and exit.
--debug-check Check memory and open file usage at exit.
--debug-info Print some debug info at exit.
--default-character-set=name
Set the default character set.
--delayed-insert Insert rows with INSERT DELAYED.
--delete-master-logs
Delete logs on master after backup. This automatically
enables --master-data.
-K, --disable-keys '/*!40000 ALTER TABLE tb_name DISABLE KEYS */; and
'/*!40000 ALTER TABLE tb_name ENABLE KEYS */; will be put
in the output.
-E, --events Dump events.
-e, --extended-insert
Use multiple-row INSERT syntax that include several
VALUES lists.
--fields-terminated-by=name
Fields in the output file are terminated by the given
string.
--fields-enclosed-by=name
Fields in the output file are enclosed by the given
character.
--fields-optionally-enclosed-by=name
Fields in the output file are optionally enclosed by the
given character.
--fields-escaped-by=name
Fields in the output file are escaped by the given
character.
--first-slave Deprecated, renamed to --lock-all-tables.
-F, --flush-logs Flush logs file in server before starting dump. Note that
if you dump many databases at once (using the option
--databases= or --all-databases), the logs will be
flushed for each database dumped. The exception is when
using --lock-all-tables or --master-data: in this case
the logs will be flushed only once, corresponding to the
moment all tables are locked. So if you want your dump
and the log flush to happen at the same exact moment you
should use --lock-all-tables or --master-data with
--flush-logs.
--flush-privileges Emit a FLUSH PRIVILEGES statement after dumping the mysql
database. This option should be used any time the dump
contains the mysql database and any other database that
depends on the data in the mysql database for proper
restore.
-f, --force Continue even if we get an SQL error.
-?, --help Display this help message and exit.
--hex-blob Dump binary strings (BINARY, VARBINARY, BLOB) in
hexadecimal format.
-h, --host=name Connect to host.
--ignore-table=name Do not dump the specified table. To specify more than one
table to ignore, use the directive multiple times, once
for each table. Each table must be specified with both
database and table names, e.g.,
--ignore-table=database.table.
--insert-ignore Insert rows with INSERT IGNORE.
--lines-terminated-by=name
Lines in the output file are terminated by the given
string.
-x, --lock-all-tables
Locks all tables across all databases. This is achieved
by taking a global read lock for the duration of the
whole dump. Automatically turns --single-transaction and
--lock-tables off.
-l, --lock-tables Lock all tables for read.
--log-error=name Append warnings and errors to given file.
--master-data[=#] This causes the binary log position and filename to be
appended to the output. If equal to 1, will print it as a
CHANGE MASTER command; if equal to 2, that command will
be prefixed with a comment symbol. This option will turn
--lock-all-tables on, unless --single-transaction is
specified too (in which case a global read lock is only
taken a short time at the beginning of the dump; don't
forget to read about --single-transaction below). In all
cases, any action on logs will happen at the exact moment
of the dump. Option automatically turns --lock-tables
off.
--max_allowed_packet=#
The maximum packet length to send to or receive from
server.
--net_buffer_length=#
The buffer size for TCP/IP and socket communication.
--no-autocommit Wrap tables with autocommit/commit statements.
-n, --no-create-db Suppress the CREATE DATABASE ... IF EXISTS statement that
normally is output for each dumped database if
--all-databases or --databases is given.
-t, --no-create-info
Don't write table creation info.
-d, --no-data No row information.
-N, --no-set-names Suppress the SET NAMES statement
--opt Same as --add-drop-table, --add-locks, --create-options,
--quick, --extended-insert, --lock-tables, --set-charset,
and --disable-keys. Enabled by default, disable with
--skip-opt.
--order-by-primary Sorts each table's rows by primary key, or first unique
key, if such a key exists. Useful when dumping a MyISAM
table to be loaded into an InnoDB table, but will make
the dump itself take considerably longer.
-p, --password[=name]
Password to use when connecting to server. If password is
not given it's solicited on the tty.
-P, --port=# Port number to use for connection.
--protocol=name The protocol to use for connection (tcp, socket, pipe,
memory).
-q, --quick Don't buffer query, dump directly to stdout.
-Q, --quote-names Quote table and column names with backticks (`).
--replace Use REPLACE INTO instead of INSERT INTO.
-r, --result-file=name
Direct output to a given file. This option should be used
in MSDOS, because it prevents new line '\n' from being
converted to '\r\n' (carriage return + line feed).
-R, --routines Dump stored routines (functions and procedures).
--set-charset Add 'SET NAMES default_character_set' to the output.
Enabled by default; suppress with --skip-set-charset.
-O, --set-variable=name
Change the value of a variable. Please note that this
option is deprecated; you can set variables directly with
--variable-name=value.
--single-transaction
Creates a consistent snapshot by dumping all tables in a
single transaction. Works ONLY for tables stored in
storage engines which support multiversioning (currently
only InnoDB does); the dump is NOT guaranteed to be
consistent for other storage engines. While a
--single-transaction dump is in process, to ensure a
valid dump file (correct table contents and binary log
position), no other connection should use the following
statements: ALTER TABLE, DROP TABLE, RENAME TABLE,
TRUNCATE TABLE, as consistent snapshot is not isolated
from them. Option automatically turns off --lock-tables.
--dump-date Put a dump date to the end of the output.
--skip-opt Disable --opt. Disables --add-drop-table, --add-locks,
--create-options, --quick, --extended-insert,
--lock-tables, --set-charset, and --disable-keys.
-S, --socket=name The socket file to use for connection.
--ssl Enable SSL for connection (automatically enabled with
other flags).Disable with --skip-ssl.
--ssl-ca=name CA file in PEM format (check OpenSSL docs, implies
--ssl).
--ssl-capath=name CA directory (check OpenSSL docs, implies --ssl).
--ssl-cert=name X509 cert in PEM format (implies --ssl).
--ssl-cipher=name SSL cipher to use (implies --ssl).
--ssl-key=name X509 key in PEM format (implies --ssl).
--ssl-verify-server-cert
Verify server's "Common Name" in its cert against
hostname used when connecting. This option is disabled by
default.
-T, --tab=name Create tab-separated textfile for each table to given
path. (Create .sql and .txt files.) NOTE: This only works
if mysqldump is run on the same machine as the mysqld
server.
--tables Overrides option --databases (-B).
--triggers Dump triggers for each dumped table.
--tz-utc SET TIME_ZONE='+00:00' at top of dump to allow dumping of
TIMESTAMP data when a server has data in different time
zones or data is being moved between servers with
different time zones.
-u, --user=name User for login if not current user.
-v, --verbose Print info about the various stages.
-V, --version Output version information and exit.
-w, --where=name Dump only selected records. Quotes are mandatory.
-X, --xml Dump a database as well formed XML.
Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
--------------------------------- -----------------------------
all TRUE
all-databases FALSE
all-tablespaces FALSE
no-tablespaces FALSE
add-drop-database FALSE
add-drop-table TRUE
add-locks TRUE
allow-keywords FALSE
character-sets-dir (No default value)
comments TRUE
compatible (No default value)
compact FALSE
complete-insert FALSE
compress FALSE
create-options TRUE
databases FALSE
debug-check FALSE
debug-info FALSE
default-character-set utf8
delayed-insert FALSE
delete-master-logs FALSE
disable-keys TRUE
events FALSE
extended-insert TRUE
fields-terminated-by (No default value)
fields-enclosed-by (No default value)
fields-optionally-enclosed-by (No default value)
fields-escaped-by (No default value)
first-slave FALSE
flush-logs FALSE
flush-privileges FALSE
force FALSE
hex-blob FALSE
host (No default value)
insert-ignore FALSE
lines-terminated-by (No default value)
lock-all-tables FALSE
lock-tables TRUE
log-error (No default value)
master-data 0
max_allowed_packet 25165824
net_buffer_length 1046528
no-autocommit FALSE
no-create-db FALSE
no-create-info FALSE
no-data FALSE
order-by-primary FALSE
port 0
quick TRUE
quote-names TRUE
replace FALSE
routines FALSE
set-charset TRUE
single-transaction FALSE
dump-date TRUE
socket (No default value)
ssl FALSE
ssl-ca (No default value)
ssl-capath (No default value)
ssl-cert (No default value)
ssl-cipher (No default value)
ssl-key (No default value)
ssl-verify-server-cert FALSE
tab (No default value)
triggers TRUE
tz-utc TRUE
user (No default value)
verbose FALSE
where (No default value)
[root@nagios_test ~]#
引數 引數說明
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--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:o,/tmp/mysqldump.trace
mysqldump -uroot -p --all-databases --debug
mysqldump -uroot -p --all-databases --debug=” d:t:o,/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=latin1
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--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位置和檔名追加到匯出資料的檔案中。設定為1時，將會以CHANGE MASTER命令輸出到資料檔案；
設定為2時，在命令前增加說明資訊。該選項將會開啟--lock-all-tables，除非--single-transaction被指定。
該選項會自動關閉--lock-tables選項。預設值為0。
mysqldump -uroot -p --all-databases --dump-slave=1
mysqldump -uroot -p --all-databases --dump-slave=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=
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
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--master-data
該選項將binlog的位置和檔名追加到輸出檔案中。如果為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;
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--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/
[root@nagios_test ~]# mysqldump --help
mysqldump Ver 10.13 Distrib 5.1.66, for redhat-linux-gnu (x86_64)
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Dumping structure and contents of MySQL databases and tables.
Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]
Default options are read from the following files in the given order:
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf
The following groups are read: mysqldump client
The following options may be given as the first argument:
--print-defaults Print the program argument list and exit.
--no-defaults Don't read default options from any option file.
--defaults-file=# Only read default options from the given file #.
--defaults-extra-file=# Read this file after the global files are read.
--all Deprecated. Use --create-options instead.
-A, --all-databases Dump all the databases. This will be same as --databases
with all databases selected.
-Y, --all-tablespaces
Dump all the tablespaces.
-y, --no-tablespaces
Do not dump any tablespace information.
--add-drop-database Add a DROP DATABASE before each create.
--add-drop-table Add a DROP TABLE before each create.
--add-locks Add locks around INSERT statements.
--allow-keywords Allow creation of column names that are keywords.
--character-sets-dir=name
Directory for character set files.
-i, --comments Write additional information.
--compatible=name Change the dump to be compatible with a given mode. By
default tables are dumped in a format optimized for
MySQL. Legal modes are: ansi, mysql323, mysql40,
postgresql, oracle, mssql, db2, maxdb, no_key_options,
no_table_options, no_field_options. One can use several
modes separated by commas. Note: Requires MySQL server
version 4.1.0 or higher. This option is ignored with
earlier server versions.
--compact Give less verbose output (useful for debugging). Disables
structure comments and header/footer constructs. Enables
options --skip-add-drop-table --skip-add-locks
--skip-comments --skip-disable-keys --skip-set-charset.
-c, --complete-insert
Use complete insert statements.
-C, --compress Use compression in server/client protocol.
-a, --create-options
Include all MySQL specific create options.
-B, --databases Dump several databases. Note the difference in usage; in
this case no tables are given. All name arguments are
regarded as database names. 'USE db_name;' will be
included in the output.
-#, --debug[=#] This is a non-debug version. Catch this and exit.
--debug-check Check memory and open file usage at exit.
--debug-info Print some debug info at exit.
--default-character-set=name
Set the default character set.
--delayed-insert Insert rows with INSERT DELAYED.
--delete-master-logs
Delete logs on master after backup. This automatically
enables --master-data.
-K, --disable-keys '/*!40000 ALTER TABLE tb_name DISABLE KEYS */; and
'/*!40000 ALTER TABLE tb_name ENABLE KEYS */; will be put
in the output.
-E, --events Dump events.
-e, --extended-insert
Use multiple-row INSERT syntax that include several
VALUES lists.
--fields-terminated-by=name
Fields in the output file are terminated by the given
string.
--fields-enclosed-by=name
Fields in the output file are enclosed by the given
character.
--fields-optionally-enclosed-by=name
Fields in the output file are optionally enclosed by the
given character.
--fields-escaped-by=name
Fields in the output file are escaped by the given
character.
--first-slave Deprecated, renamed to --lock-all-tables.
-F, --flush-logs Flush logs file in server before starting dump. Note that
if you dump many databases at once (using the option
--databases= or --all-databases), the logs will be
flushed for each database dumped. The exception is when
using --lock-all-tables or --master-data: in this case
the logs will be flushed only once, corresponding to the
moment all tables are locked. So if you want your dump
and the log flush to happen at the same exact moment you
should use --lock-all-tables or --master-data with
--flush-logs.
--flush-privileges Emit a FLUSH PRIVILEGES statement after dumping the mysql
database. This option should be used any time the dump
contains the mysql database and any other database that
depends on the data in the mysql database for proper
restore.
-f, --force Continue even if we get an SQL error.
-?, --help Display this help message and exit.
--hex-blob Dump binary strings (BINARY, VARBINARY, BLOB) in
hexadecimal format.
-h, --host=name Connect to host.
--ignore-table=name Do not dump the specified table. To specify more than one
table to ignore, use the directive multiple times, once
for each table. Each table must be specified with both
database and table names, e.g.,
--ignore-table=database.table.
--insert-ignore Insert rows with INSERT IGNORE.
--lines-terminated-by=name
Lines in the output file are terminated by the given
string.
-x, --lock-all-tables
Locks all tables across all databases. This is achieved
by taking a global read lock for the duration of the
whole dump. Automatically turns --single-transaction and
--lock-tables off.
-l, --lock-tables Lock all tables for read.
--log-error=name Append warnings and errors to given file.
--master-data[=#] This causes the binary log position and filename to be
appended to the output. If equal to 1, will print it as a
CHANGE MASTER command; if equal to 2, that command will
be prefixed with a comment symbol. This option will turn
--lock-all-tables on, unless --single-transaction is
specified too (in which case a global read lock is only
taken a short time at the beginning of the dump; don't
forget to read about --single-transaction below). In all
cases, any action on logs will happen at the exact moment
of the dump. Option automatically turns --lock-tables
off.
--max_allowed_packet=#
The maximum packet length to send to or receive from
server.
--net_buffer_length=#
The buffer size for TCP/IP and socket communication.
--no-autocommit Wrap tables with autocommit/commit statements.
-n, --no-create-db Suppress the CREATE DATABASE ... IF EXISTS statement that
normally is output for each dumped database if
--all-databases or --databases is given.
-t, --no-create-info
Don't write table creation info.
-d, --no-data No row information.
-N, --no-set-names Suppress the SET NAMES statement
--opt Same as --add-drop-table, --add-locks, --create-options,
--quick, --extended-insert, --lock-tables, --set-charset,
and --disable-keys. Enabled by default, disable with
--skip-opt.
--order-by-primary Sorts each table's rows by primary key, or first unique
key, if such a key exists. Useful when dumping a MyISAM
table to be loaded into an InnoDB table, but will make
the dump itself take considerably longer.
-p, --password[=name]
Password to use when connecting to server. If password is
not given it's solicited on the tty.
-P, --port=# Port number to use for connection.
--protocol=name The protocol to use for connection (tcp, socket, pipe,
memory).
-q, --quick Don't buffer query, dump directly to stdout.
-Q, --quote-names Quote table and column names with backticks (`).
--replace Use REPLACE INTO instead of INSERT INTO.
-r, --result-file=name
Direct output to a given file. This option should be used
in MSDOS, because it prevents new line '\n' from being
converted to '\r\n' (carriage return + line feed).
-R, --routines Dump stored routines (functions and procedures).
--set-charset Add 'SET NAMES default_character_set' to the output.
Enabled by default; suppress with --skip-set-charset.
-O, --set-variable=name
Change the value of a variable. Please note that this
option is deprecated; you can set variables directly with
--variable-name=value.
--single-transaction
Creates a consistent snapshot by dumping all tables in a
single transaction. Works ONLY for tables stored in
storage engines which support multiversioning (currently
only InnoDB does); the dump is NOT guaranteed to be
consistent for other storage engines. While a
--single-transaction dump is in process, to ensure a
valid dump file (correct table contents and binary log
position), no other connection should use the following
statements: ALTER TABLE, DROP TABLE, RENAME TABLE,
TRUNCATE TABLE, as consistent snapshot is not isolated
from them. Option automatically turns off --lock-tables.
--dump-date Put a dump date to the end of the output.
--skip-opt Disable --opt. Disables --add-drop-table, --add-locks,
--create-options, --quick, --extended-insert,
--lock-tables, --set-charset, and --disable-keys.
-S, --socket=name The socket file to use for connection.
--ssl Enable SSL for connection (automatically enabled with
other flags).Disable with --skip-ssl.
--ssl-ca=name CA file in PEM format (check OpenSSL docs, implies
--ssl).
--ssl-capath=name CA directory (check OpenSSL docs, implies --ssl).
--ssl-cert=name X509 cert in PEM format (implies --ssl).
--ssl-cipher=name SSL cipher to use (implies --ssl).
--ssl-key=name X509 key in PEM format (implies --ssl).
--ssl-verify-server-cert
Verify server's "Common Name" in its cert against
hostname used when connecting. This option is disabled by
default.
-T, --tab=name Create tab-separated textfile for each table to given
path. (Create .sql and .txt files.) NOTE: This only works
if mysqldump is run on the same machine as the mysqld
server.
--tables Overrides option --databases (-B).
--triggers Dump triggers for each dumped table.
--tz-utc SET TIME_ZONE='+00:00' at top of dump to allow dumping of
TIMESTAMP data when a server has data in different time
zones or data is being moved between servers with
different time zones.
-u, --user=name User for login if not current user.
-v, --verbose Print info about the various stages.
-V, --version Output version information and exit.
-w, --where=name Dump only selected records. Quotes are mandatory.
-X, --xml Dump a database as well formed XML.
Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
--------------------------------- -----------------------------
all TRUE
all-databases FALSE
all-tablespaces FALSE
no-tablespaces FALSE
add-drop-database FALSE
add-drop-table TRUE
add-locks TRUE
allow-keywords FALSE
character-sets-dir (No default value)
comments TRUE
compatible (No default value)
compact FALSE
complete-insert FALSE
compress FALSE
create-options TRUE
databases FALSE
debug-check FALSE
debug-info FALSE
default-character-set utf8
delayed-insert FALSE
delete-master-logs FALSE
disable-keys TRUE
events FALSE
extended-insert TRUE
fields-terminated-by (No default value)
fields-enclosed-by (No default value)
fields-optionally-enclosed-by (No default value)
fields-escaped-by (No default value)
first-slave FALSE
flush-logs FALSE
flush-privileges FALSE
force FALSE
hex-blob FALSE
host (No default value)
insert-ignore FALSE
lines-terminated-by (No default value)
lock-all-tables FALSE
lock-tables TRUE
log-error (No default value)
master-data 0
max_allowed_packet 25165824
net_buffer_length 1046528
no-autocommit FALSE
no-create-db FALSE
no-create-info FALSE
no-data FALSE
order-by-primary FALSE
port 0
quick TRUE
quote-names TRUE
replace FALSE
routines FALSE
set-charset TRUE
single-transaction FALSE
dump-date TRUE
socket (No default value)
ssl FALSE
ssl-ca (No default value)
ssl-capath (No default value)
ssl-cert (No default value)
ssl-cipher (No default value)
ssl-key (No default value)
ssl-verify-server-cert FALSE
tab (No default value)
triggers TRUE
tz-utc TRUE
user (No default value)
verbose FALSE
where (No default value)
[root@nagios_test ~]#
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29500582/viewspace-1369396/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- mysqldump 引數詳解（基於MySQL 5.6）MySql
- mysqldump匯出引數詳細解釋MySql
- mysqldump引數詳細說明MySql
- Mysqldump引數大全MySql
- MySQL mysqldump命令的引數詳細說明MySql
- mysqldump引數說明MySql
- mysqldump 引數說明MySql
- mysqldump引數選項MySql
- mysqldump --result-file引數MySql
- ajax 引數詳解
- DockerFile引數詳解Docker
- dd引數詳解
- vmstat 引數詳解
- mysqldump --login-path引數使用MySql
- OGG引數詳解
- tar命令引數詳解
- 函式引數詳解函式
- Oracle UNDO引數詳解Oracle
- jqGrid引數詳解
- $.ajax()方法引數詳解
- DataGuard引數配置詳解
- redis info引數詳解Redis
- ES常用引數詳解
- struts配置引數詳解
- 【轉】Cmd引數詳解
- flash wmode引數詳解
- GCC引數詳解(轉)GC
- fstab引數詳解(轉)
- lsblk命令引數詳解
- mysqldump --single-transaction引數的作用MySql
- variables_order引數詳解
- oracle rac 核心引數詳解Oracle
- Flink Checkpoint 引數詳解
- find 命令的引數詳解
- Mysql配置引數詳解（一）MySql
- Prometheus hashmod 配置引數詳解Prometheus
- SQL*Plus Set引數詳解SQL
- ntpq –p 各引數詳解