# MySQL

# 【MySQL】 mysqldump參數大全

###  

[IT 研究室 ( 前IT DBA's 資訊站): \[轉載\] MySQL mysqldump參數大全 (jaychu649.blogspot.com)](https://jaychu649.blogspot.com/2018/05/mysql-mysqldump.html)

要注意的是--master-data、--dump-slave兩個參數的差別, 以前從未注意過--dump-slave參數

\--master-data用於在master端dump資料，用於建立slave  
\--dump-slave使用者在slave端dump資料，建立新的slave,至少是第2個slave,也就是已經有A-->B,現在從B上匯出資料建立A-->C的複製

**<span lang="EN-US">--all-databases , -A</span>**<span lang="EN-US">  
</span>匯出全部資料庫。<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --all-databases</span>`<span lang="EN-US">  
**--all-tablespaces , -Y**  
</span>匯出全部表空間。<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --all-databases --all-tablespaces</span>`<span lang="EN-US">  
**--no-tablespaces , -y**  
</span>不匯出任何表空間資訊。<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --all-databases --no-tablespaces</span>`<span lang="EN-US">  
**--add-drop-database**  
</span>每個資料庫創建之前添加<span lang="EN-US">drop</span>資料庫語句。<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --all-databases --add-drop-database</span>`<span lang="EN-US">  
**--add-drop-table**  
</span>每個資料表創建之前添加<span lang="EN-US">drop</span>資料表語句。<span lang="EN-US">(</span>預設為打開狀態，使用<span lang="EN-US">--skip-add-drop-table</span>取消選項<span lang="EN-US">)</span><span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --all-databases (</span>``預設添加``<span lang="EN-US">drop</span>``語句``<span lang="EN-US">)</span>`<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --all-databases –skip-add-drop-table (</span>``取消``<span lang="EN-US">drop</span>``語句``<span lang="EN-US">)</span>`<span lang="EN-US">  
**--add-locks**  
</span>在每個表匯出之前增加<span lang="EN-US">LOCK TABLES</span>並且之後<span lang="EN-US">UNLOCK TABLE</span>。<span lang="EN-US">(</span>預設為打開狀態，使用<span lang="EN-US">--skip-add-locks</span>取消選項<span lang="EN-US">)</span><span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --all-databases (</span>``預設添加``<span lang="EN-US">LOCK</span>``語句``<span lang="EN-US">)</span>`<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --all-databases –skip-add-locks (</span>``取消``<span lang="EN-US">LOCK</span>``語句``<span lang="EN-US">)</span>`<span lang="EN-US">  
**--allow-keywords**  
</span>允許創建是關鍵字的列名字。這由表名首碼於每個列名做到。<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --all-databases --allow-keywords</span>`<span lang="EN-US">  
**--apply-slave-statements**  
</span>在<span lang="EN-US">'CHANGE MASTER'</span>前添加<span lang="EN-US">'STOP SLAVE'</span>，並且在匯出的最後添加<span lang="EN-US">'START SLAVE'</span>。<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --all-databases --apply-slave-statements</span>`<span lang="EN-US">  
**--character-sets-dir**  
</span>字元集檔的目錄<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --all-databases --character-sets-dir=/usr/local/mysql/share/mysql/charsets</span>`<span lang="EN-US">  
**--comments**  
</span>附加注釋資訊。默認為打開，可以用<span lang="EN-US">--skip-comments</span>取消<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --all-databases (</span>``預設記錄注釋``<span lang="EN-US">)</span>`<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --all-databases --skip-comments (</span>``取消注釋``<span lang="EN-US">)</span>`<span lang="EN-US">  
**--compatible**  
</span>匯出的資料將和其它資料庫或舊版本的<span lang="EN-US">MySQL </span>相相容。值可以為<span lang="EN-US">ansi</span>、<span lang="EN-US">mysql323</span>、<span lang="EN-US">mysql40</span>、<span lang="EN-US">postgresql</span>、<span lang="EN-US">oracle</span>、<span lang="EN-US">mssql</span>、<span lang="EN-US">db2</span>、<span lang="EN-US">maxdb</span>、<span lang="EN-US">no\_key\_options</span>、<span lang="EN-US">no\_tables\_options</span>、<span lang="EN-US">no\_field\_options</span>等，<span lang="EN-US">  
</span>要使用幾個值，用逗號將它們隔開。它並不保證能完全相容，而是儘量相容。<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --all-databases --compatible=ansi</span>`<span lang="EN-US">  
**--compact**  
</span>匯出更少的輸出資訊<span lang="EN-US">(</span>用於調試<span lang="EN-US">)</span>。去掉注釋和頭尾等結構。可以使用選項：<span lang="EN-US">--skip-add-drop-table --skip-add-locks --skip-comments --skip-disable-keys</span><span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --all-databases --compact</span>`<span lang="EN-US">  
**--complete-insert, -c**  
</span>使用完整的<span lang="EN-US">insert</span>語句<span lang="EN-US">(</span>包含列名稱<span lang="EN-US">)</span>。這麼做能提高插入效率，但是可能會受到<span lang="EN-US">max\_allowed\_packet</span>參數的影響而導致插入失敗。<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --all-databases --complete-insert</span>`<span lang="EN-US">  
**--compress, -C**  
</span>在用戶端和伺服器之間啟用壓縮傳遞所有資訊<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --all-databases --compress</span>`<span lang="EN-US">  
**--create-options, -a**  
</span>在<span lang="EN-US">CREATE TABLE</span>語句中包括所有<span lang="EN-US">MySQL</span>特性選項。<span lang="EN-US">(</span>預設為打開狀態<span lang="EN-US">)</span><span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --all-databases</span>`<span lang="EN-US">  
**--databases, -B**  
</span>匯出幾個資料庫。參數後面所有名字參量都被看作資料庫名。<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --databases test mysql</span>`<span lang="EN-US">  
**--debug**  
</span>輸出<span lang="EN-US">debug</span>資訊，用於調試。預設值為：<span lang="EN-US">d:t:o,/tmp/mysqldump.trace</span><span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --all-databases --debug</span>`<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --all-databases --debug="d:t:o,/tmp/debug.trace"</span>`<span lang="EN-US">  
**--debug-check**  
</span>檢查記憶體和打開檔使用說明並退出。<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --all-databases --debug-check</span>`<span lang="EN-US">  
**--debug-info**  
</span>輸出調試資訊並退出<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --all-databases --debug-info</span>`<span lang="EN-US">  
**--default-character-set**  
</span>設置預設字元集，預設值為<span lang="EN-US">utf8</span><span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --all-databases --default-character-set=latin1</span>`<span lang="EN-US">  
**--delayed-insert**  
</span>採用延時插入方式（<span lang="EN-US">INSERT DELAYED</span>）匯出資料<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --all-databases --delayed-insert</span>`<span lang="EN-US">  
**--delete-master-logs**  
master</span>備份後刪除日誌<span lang="EN-US">. </span>這個參數將自動啟動<span lang="EN-US">--master-data</span>。<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --all-databases --delete-master-logs</span>`<span lang="EN-US">  
**--disable-keys**  
</span>對於每個表，用<span lang="EN-US">/*!40000 ALTER TABLE tbl\_name DISABLE KEYS \*/;*</span>*和<span lang="EN-US">/</span>*<span lang="EN-US">!40000 ALTER TABLE tbl\_name ENABLE KEYS \*/;</span>語句引用<span lang="EN-US">INSERT</span>語句。這樣可以更快地導入<span lang="EN-US">dump</span>出來的檔，因為它是在插入所有行後創建索引的。該選項只適合<span lang="EN-US">MyISAM</span>表，預設為打開狀態。<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --all-databases</span>`<span lang="EN-US">  
**--dump-slave**  
</span>該選項將導致主的<span lang="EN-US">binlog</span>位置和檔案名追加到匯出資料的檔中。設置為<span lang="EN-US">1</span>時，將會以<span lang="EN-US">CHANGE MASTER</span>命令輸出到資料檔案；設置為<span lang="EN-US">2</span>時，在命令前增加說明資訊。該選項將會打開<span lang="EN-US">--lock-all-tables</span>，除非<span lang="EN-US">--single-transaction</span>被指定。該選項會自動關閉<span lang="EN-US">--lock-tables</span>選項。預設值為<span lang="EN-US">0</span>。<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --all-databases --dump-slave=1</span>`<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --all-databases --dump-slave=2</span>`<span lang="EN-US">  
**--events, -E**  
</span>匯出事件。<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --all-databases --events</span>`<span lang="EN-US">  
**--extended-insert, -e**  
</span>使用具有多個<span lang="EN-US">VALUES</span>列的<span lang="EN-US">INSERT</span>語法。這樣使匯出檔更小，並加速導入時的速度。預設為打開狀態，使用<span lang="EN-US">--skip-extended-insert</span>取消選項。<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --all-databases</span>`<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --all-databases--skip-extended-insert (</span>``取消選項``<span lang="EN-US">)</span>`<span lang="EN-US">  
**--fields-terminated-by**  
</span>匯出檔中忽略給定欄位。與<span lang="EN-US">--tab</span>選項一起使用，不能用於<span lang="EN-US">--databases</span>和<span lang="EN-US">--all-databases</span>選項<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p test test --tab="/home/mysql" --fields-terminated-by="#"</span>`<span lang="EN-US">  
**--fields-enclosed-by**  
</span>輸出檔中的各個欄位用給定字元包裹。與<span lang="EN-US">--tab</span>選項一起使用，不能用於<span lang="EN-US">--databases</span>和<span lang="EN-US">--all-databases</span>選項<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p test test --tab="/home/mysql" --fields-enclosed-by="#"</span>`<span lang="EN-US">  
**--fields-optionally-enclosed-by**  
</span>輸出檔中的各個欄位用給定字元選擇性包裹。與<span lang="EN-US">--tab</span>選項一起使用，不能用於<span lang="EN-US">--databases</span>和<span lang="EN-US">--all-databases</span>選項<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p test test --tab="/home/mysql" --fields-enclosed-by="#" --fields-optionally-enclosed-by ="#"</span>`<span lang="EN-US">  
**--fields-escaped-by**  
</span>輸出檔中的各個欄位忽略給定字元。與<span lang="EN-US">--tab</span>選項一起使用，不能用於<span lang="EN-US">--databases</span>和<span lang="EN-US">--all-databases</span>選項<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p mysql user --tab="/home/mysql" --fields-escaped-by="#"</span>`<span lang="EN-US">  
**--flush-logs**  
</span>開始匯出之前刷新日誌。<span lang="EN-US">  
</span>請注意：假如一次匯出多個資料庫<span lang="EN-US">(</span>使用選項<span lang="EN-US">--databases</span>或者<span lang="EN-US">--all-databases)</span>，將會逐個資料庫刷新日誌。除使用<span lang="EN-US">--lock-all-tables</span>或者<span lang="EN-US">--master-data</span>外。在這種情況下，日誌將會被刷新一次，相應的所以表同時被鎖定。因此，如果打算同時匯出和刷新日誌應該使用<span lang="EN-US">--lock-all-tables </span>或者<span lang="EN-US">--master-data </span>和<span lang="EN-US">--flush-logs</span>。<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --all-databases --flush-logs</span>`<span lang="EN-US">  
**--flush-privileges**  
</span>在匯出<span lang="EN-US">mysql</span>資料庫之後，發出一條<span lang="EN-US">FLUSH PRIVILEGES </span>語句。為了正確恢復，該選項應該用於匯出<span lang="EN-US">mysql</span>資料庫和依賴<span lang="EN-US">mysql</span>資料庫資料的任何時候。<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --all-databases --flush-privileges</span>`<span lang="EN-US">  
**--force**  
</span>在匯出過程中忽略出現的<span lang="EN-US">SQL</span>錯誤。<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --all-databases --force</span>`<span lang="EN-US">  
**--help**  
</span>顯示説明資訊並退出。<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump --help</span>`<span lang="EN-US">  
**--hex-blob**  
</span>使用十六進位格式匯出二進位字元串欄位。如果有二進位資料就必須使用該選項。影響到的欄位類型有<span lang="EN-US">BINARY</span>、<span lang="EN-US">VARBINARY</span>、<span lang="EN-US">BLOB</span>。<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --all-databases --hex-blob</span>`<span lang="EN-US">  
**--host, -h**  
</span>需要匯出的主機資訊<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --host=localhost --all-databases</span>`<span lang="EN-US">  
**--ignore-table**  
</span>不匯出指定表。指定忽略多個表時，需要重複多次，每次一個表。每個表必須同時指定資料庫和表名。例如：<span lang="EN-US">--ignore-table=database.table1 --ignore-table=database.table2 ……</span><span lang="EN-US">  
mysqldump -uroot -p --host=localhost --all-databases --ignore-table=mysql.user  
**--include-master-host-port**  
</span>在<span lang="EN-US">--dump-slave</span>產生的<span lang="EN-US">'CHANGE MASTER TO..'</span>語句中增加<span lang="EN-US">'MASTER\_HOST=</span>，<span lang="EN-US">MASTER\_PORT='  
</span>`<span lang="EN-US">mysqldump -uroot -p --host=localhost --all-databases --include-master-host-port</span>`<span lang="EN-US">  
**--insert-ignore**  
</span>在插入行時使用<span lang="EN-US">INSERT IGNORE</span>語句<span lang="EN-US">.  
</span>`<span lang="EN-US">mysqldump -uroot -p --host=localhost --all-databases --insert-ignore</span>`<span lang="EN-US">  
**--lines-terminated-by**  
</span>輸出檔的每行用給定字串劃分。與<span lang="EN-US">--tab</span>選項一起使用，不能用於<span lang="EN-US">--databases</span>和<span lang="EN-US">--all-databases</span>選項。<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --host=localhost test test --tab="/tmp/mysql" --lines-terminated-by="##"</span>`<span lang="EN-US">  
**--lock-all-tables, -x**  
</span>提交請求鎖定所有資料庫中的所有表，以保證資料的一致性。這是一個全域讀鎖，並且自動關閉<span lang="EN-US">--single-transaction </span>和<span lang="EN-US">--lock-tables </span>選項。<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --host=localhost --all-databases --lock-all-tables</span>`<span lang="EN-US">  
**--lock-tables, -l**  
</span>開始匯出前，鎖定所有表。用<span lang="EN-US">READ LOCAL</span>鎖定表以允許<span lang="EN-US">MyISAM</span>表並行插入。對於支援事務的表例如<span lang="EN-US">InnoDB</span>和<span lang="EN-US">BDB</span>，<span lang="EN-US">--single-transaction</span>是一個更好的選擇，因為它根本不需要鎖定表。<span lang="EN-US">  
</span>請注意當匯出多個資料庫時，<span lang="EN-US">--lock-tables</span>分別為每個資料庫鎖定表。因此，該選項不能保證匯出檔中的表在資料庫之間的邏輯一致性。不同資料庫表的匯出狀態可以完全不同。<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --host=localhost --all-databases --lock-tables</span>`<span lang="EN-US">  
**--log-error**  
</span>附加警告和錯誤資訊到給定檔<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --host=localhost --all-databases --log-error=/tmp/mysqldump_error_log.err</span>`<span lang="EN-US">  
**--master-data**  
</span>該選項將<span lang="EN-US">binlog</span>的位置和檔案名追加到輸出檔中。如果為<span lang="EN-US">1</span>，將會輸出<span lang="EN-US">CHANGE MASTER </span>命令；如果為<span lang="EN-US">2</span>，輸出的<span lang="EN-US">CHANGE MASTER</span>命令前添加注釋資訊。該選項將打開<span lang="EN-US">--lock-all-tables </span>選項，除非<span lang="EN-US">--single-transaction</span>也被指定（在這種情況下，全域讀鎖在開始匯出時獲得很短的時間；其他內容參考下面的<span lang="EN-US">--single-transaction</span>選項）。該選項自動關閉<span lang="EN-US">--lock-tables</span>選項。<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --host=localhost --all-databases --master-data=1;</span>`<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --host=localhost --all-databases --master-data=2;</span>`<span lang="EN-US">  
**--max\_allowed\_packet**  
</span>伺服器發送和接受的最大包長度。<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --host=localhost --all-databases --max_allowed_packet=10240</span>`<span lang="EN-US">  
**--net\_buffer\_length**  
TCP/IP</span>和<span lang="EN-US">socket</span>連接的緩存大小。<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --host=localhost --all-databases --net_buffer_length=1024</span>`<span lang="EN-US">  
**--no-autocommit**  
</span>使用<span lang="EN-US">autocommit/commit </span>語句包裹表。<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --host=localhost --all-databases --no-autocommit</span>`<span lang="EN-US">  
**--no-create-db, -n**  
</span>只匯出數據，而不添加<span lang="EN-US">CREATE DATABASE </span>語句。<span lang="EN-US">  
mysqldump -uroot -p --host=localhost --all-databases --no-create-db  
**--no-create-info, -t**  
</span>只匯出數據，而不添加<span lang="EN-US">CREATE TABLE </span>語句。<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --host=localhost --all-databases --no-create-info</span>`<span lang="EN-US">  
**--no-data, -d**  
</span>不匯出任何資料，只匯出資料庫表結構。<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --host=localhost --all-databases --no-data</span>`<span lang="EN-US">  
**--no-set-names, -N**  
</span>等同于**<span lang="EN-US">--skip-set-charset</span>**<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --host=localhost --all-databases --no-set-names</span>`<span lang="EN-US">  
**--opt**  
</span>等同於<span lang="EN-US">--add-drop-table, --add-locks, --create-options, --quick, --extended-insert, --lock-tables, --set-charset, --disable-keys </span>該選項預設開啟<span lang="EN-US">, </span>可以用<span lang="EN-US">--skip-opt</span>禁用<span lang="EN-US">.  
</span>`<span lang="EN-US">mysqldump -uroot -p --host=localhost --all-databases --opt</span>`<span lang="EN-US">  
**--order-by-primary**  
</span>如果存在主鍵，或者第一個唯一鍵，對每個表的記錄進行排序。在匯出<span lang="EN-US">MyISAM</span>表到<span lang="EN-US">InnoDB</span>表時有效，但會使得匯出工作花費很長時間。<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --host=localhost --all-databases --order-by-primary</span>`<span lang="EN-US">  
**--password, -p**  
</span>連接資料庫密碼<span lang="EN-US">  
**--pipe(windows**</span>**系統可用<span lang="EN-US">)</span>**<span lang="EN-US">  
</span>使用具名管道連接<span lang="EN-US">mysql  
</span>`<span lang="EN-US">mysqldump -uroot -p --host=localhost --all-databases --pipe</span>`<span lang="EN-US">  
**--port, -P**  
</span>連接資料庫埠號<span lang="EN-US">  
**--protocol**  
</span>使用的連線協定，包括：<span lang="EN-US">tcp, socket, pipe, memory.  
</span>`<span lang="EN-US">mysqldump -uroot -p --host=localhost --all-databases --protocol=tcp</span>`<span lang="EN-US">  
**--quick, -q**  
</span>不緩衝查詢，直接匯出到標準輸出。預設為打開狀態，使用<span lang="EN-US">--skip-quick</span>取消該選項。<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --host=localhost --all-databases</span>`<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --host=localhost --all-databases --skip-quick</span>`<span lang="EN-US">  
**--quote-names,-Q**  
</span>使用（<span lang="EN-US">'</span>）引起表和列名。預設為打開狀態，使用<span lang="EN-US">--skip-quote-names</span>取消該選項。<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --host=localhost --all-databases</span>`<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --host=localhost --all-databases --skip-quote-names</span>`<span lang="EN-US">  
**--replace**  
</span>使用<span lang="EN-US">REPLACE INTO </span>取代<span lang="EN-US">INSERT INTO.  
</span>`<span lang="EN-US">mysqldump -uroot -p --host=localhost --all-databases --replace</span>`<span lang="EN-US">  
**--result-file, -r**  
</span>直接輸出到指定檔中。該選項應該用在使用回車換行對（<span lang="EN-US">\\r\\n</span>）換行的系統上（例如：<span lang="EN-US">DOS</span>，<span lang="EN-US">Windows</span>）。該選項確保只有一行被使用。<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --host=localhost --all-databases --result-file=/tmp/mysqldump_result_file.txt</span>`<span lang="EN-US">  
**--routines, -R**  
</span>匯出存儲過程以及自訂函數。<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --host=localhost --all-databases --routines</span>`<span lang="EN-US">  
**--set-charset**  
</span>添加<span lang="EN-US">'SET NAMES default\_character\_set'</span>到輸出檔。預設為打開狀態，使用<span lang="EN-US">--skip-set-charset</span>關閉選項。<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --host=localhost --all-databases</span>`<span lang="EN-US">  
m</span>`<span lang="EN-US">ysqldump -uroot -p --host=localhost --all-databases --skip-set-charset</span>`<span lang="EN-US">  
**--single-transaction**  
</span>該選項在匯出資料之前提交一個<span lang="EN-US">BEGIN SQL</span>語句，<span lang="EN-US">BEGIN </span>不會阻塞任何應用程式且能保證匯出時資料庫的一致性狀態。它只適用於多版本存儲引擎，僅<span lang="EN-US">InnoDB</span>。本選項和<span lang="EN-US">--lock-tables </span>選項是互斥的，因為<span lang="EN-US">LOCK TABLES </span>會使任何掛起的事務隱含提交。要想匯出大表的話，應結合使用<span lang="EN-US">--quick </span>選項。<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --host=localhost --all-databases --single-transaction</span>`<span lang="EN-US">  
**--dump-date**  
</span>將匯出時間添加到輸出檔中。預設為打開狀態，使用<span lang="EN-US">--skip-dump-date</span>關閉選項。<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --host=localhost --all-databases</span>`<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --host=localhost --all-databases --skip-dump-date</span>`<span lang="EN-US">  
**--skip-opt**  
</span>禁用<span lang="EN-US">–opt</span>選項<span lang="EN-US">.  
</span>`<span lang="EN-US">mysqldump -uroot -p --host=localhost --all-databases --skip-opt</span>`<span lang="EN-US">  
**--socket,-S**  
</span>指定連接<span lang="EN-US">mysql</span>的<span lang="EN-US">socket</span>檔位置，預設路徑<span lang="EN-US">/tmp/mysql.sock  
</span>`<span lang="EN-US">mysqldump -uroot -p --host=localhost --all-databases --socket=/tmp/mysqld.sock</span>`<span lang="EN-US">  
**--tab,-T**  
</span>為每個表在給定路徑創建<span lang="EN-US">tab</span>分割的文字檔。注意：僅僅用於<span lang="EN-US">mysqldump</span>和<span lang="EN-US">mysqld</span>伺服器運行在相同機器上。<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --host=localhost test test --tab="/home/mysql"</span>`<span lang="EN-US">  
**--tables**  
</span>覆蓋<span lang="EN-US">--databases (-B)</span>參數，指定需要匯出的表名。<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --host=localhost --databases test --tables test</span>`<span lang="EN-US">  
**--triggers**  
</span>匯出觸發器。該選項預設啟用，用<span lang="EN-US">--skip-triggers</span>禁用它。<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --host=localhost --all-databases --triggers</span>`<span lang="EN-US">  
**--tz-utc**  
</span>在匯出頂部設置時區<span lang="EN-US">TIME\_ZONE='+00:00' </span>，以保證在不同時區匯出的<span lang="EN-US">TIMESTAMP </span>資料或者資料被移動其他時區時的正確性。<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --host=localhost --all-databases --tz-utc</span>`<span lang="EN-US">  
**--user, -u**  
</span>指定連接的用戶名。<span lang="EN-US">  
**--verbose, --v**  
</span>輸出多種平臺資訊。<span lang="EN-US">  
**--version, -V**  
</span>輸出<span lang="EN-US">mysqldump</span>版本資訊並退出<span lang="EN-US">  
**--where, -w**  
</span>只轉儲給定的<span lang="EN-US">WHERE</span>條件選擇的記錄。請注意如果條件包含命令解釋符專用空格或字元，一定要將條件引用起來。<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --host=localhost --all-databases --where=" user='root'"</span>`<span lang="EN-US">  
**--xml, -X**  
</span>匯出<span lang="EN-US">XML</span>格式<span lang="EN-US">.  
</span>`<span lang="EN-US">mysqldump -uroot -p --host=localhost --all-databases --xml</span>`<span lang="EN-US">  
**--plugin\_dir**  
</span>用戶端外掛程式的目錄，用於相容不同的外掛程式版本。<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --host=localhost --all-databases --plugin_dir="/usr/local/lib/plugin"</span>`<span lang="EN-US">  
**--default\_auth**  
</span>用戶端外掛程式默認使用權限。<span lang="EN-US">  
</span>`<span lang="EN-US">mysqldump -uroot -p --host=localhost --all-databases --default-auth="/usr/local/lib/plugin/<PLUGIN>"</span>`

# MySQL_帳號管理

### 1.新增帳號

#### 新增帳號

```SQL
--新增使用者 test, 密碼 newpassword, 允許任何來源 %
CREATE USER 'test'@'%' IDENTIFIED BY 'newpassword';

--新增使用者 test, 密碼 newpassword, 只允許本機(localhost)
CREATE USER 'test'@'localhost' IDENTIFIED BY 'newpassword';
```

#### 賦予權限

```SQL
--所有權限
grant all privileges ON *.* TO 'test'@'%';

--db1 所有查詢權限
grant select ON 'db1'.* TO 'test'@'%';

--更新權限
flush privileges;
```

---

### 2.修改密碼

#### 查詢帳號

```shell
#先找到user

mysql> select host,user from user;
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| localhost | bookstackuser |
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
+-----------+---------------+
4 rows in set (0.00 sec)

```

#### 修改密碼

```shell
#修改密碼
mysql> alter user 'root'@'localhost' identified by 'sa1234Asdf';
#更新
mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)

```

---

### 3.忘記密碼(密碼回復)

1. 修改設定檔 /etc/my.cnf
2. 服務重啟登入
3. 修改密碼
4. 恢復設定檔 /etc/my.cnf
5. 重啟服務

#### 修改設定檔 /etc/my.conf

```shell
#新增skip-grant-tables
[mysqld]
skip-grant-tables

```

#### 服務重啟登入

```shell
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# mysql
```

#### 修改密碼

```shell
#修改密碼
mysql> alter user 'root'@'localhost' identified by 'newpassword'; 

#如果出現以下錯誤
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
## 先更新權限在修改密碼
mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)


#更新權限
mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)

```

#### 恢復設定檔 /etc/my.conf

```shell
#移除(註解) skip-grant-tables
[mysqld]
#skip-grant-tables

```

#### 重啟服務

```shell
[root@localhost ~]# systemctl restart mysqld
```