【MYSQL】mysqldump 備份指令說明與範例

備份指令 :mysqldump

DB→資料庫-TEST、BOOK

TABLE→TEST資料內的資料表-TEST1、TEST2
               BOOK資料內的資料表-TBOOK1、TBOOK2、TBOOK3


備份單一資料庫(DB:TEST)

指令：mysqldump -uroot -p --databases database_name > backup_name.sql


範例# mysqldump -uroot -p --databases TEST > TEST.sql

備份單一資料表(DB:TEST內的TEST1)

指令：mysqldump -uroot -p --databases table_name > backup_name.sql

範例#  mysqldump -u root -p TEST TEST1 > TEST_TEST1.sql

備份多個資料庫(DB:TEST及BOOK)

指令：mysqldump -uroot -p --databases database1 database2 > backup_name.sql

範例# mysqldump -u root -p --databases TEST BOOK > TEST_BOOK.sql

備份多個資料表(DB:BOOK內的TBOOK1及TBOOK2)

指令：mysqldump -uroot -p database_name  table1 table2 > backup_name.sql

範例# mysqldump -uroot -p BOOK TBOOK1 TBOOK2 > TBOOK1_2.sql

全部備份--all-databases即表示全部DB

指令：mysqldump -uroot -p --all-databases > all_db_backup.sql

範例# mysqldump -uroot -p --all-databases > all_db_backup.sql

參數說明:(藍色常用)

-u  --user: 指定連接的用戶名。
-p  --password: 指定用戶的密碼。
-S  --socket: 指定socket文件連接，本地登錄才會使用。
-h  --host: 指定連接的服務器名稱或者IP。
-P  --port=: 連接數據庫監聽的端口。
--default-character-set: 設置字符集，默認是UTF8。
-A  --all-databases: 導出所有數據庫。不過默認情況下是不會導出information_schema庫。
-B  --databases: 導出指定的某個/或者某幾個數據庫，參數後面所有名字參量都被看作數據庫名。
--tables: 導出指定表對象，參數格式為"庫名表名"，默認該參數將覆蓋-B/--databases參數。
-w --where: 只導出符合條件的記錄。
-l --lock-tables: 默認參數，鎖定讀取的表對象，想導出一致性備份的話最後使用該參數，會導致無法對錶執行寫入操作。
--single-transaction: 

該選項在導出數據之前提交一個BEGIN SQL語句，BEGIN不會阻塞任何應用程序且能保證導出時數據庫的一致性狀態。它只適用於多版本存儲引擎，僅InnoDB。本選項和--lock-tables選項是互斥的，因為LOCK TABLES會使任何掛起的事務隱含提交，使用參數--single-transaction會自動關閉該選項。在InnoDB導出時會建立一致性快照，在保證導出數據的一致性前提下，又不會堵塞其他會話的讀寫操作，相比--lock-tables參數來說鎖定粒度要低，造成的影響也要小很多。指定這個參數後，其他連接不能執行ALTER TABLE、DROP TABLE 、RENAME TABLE、TRUNCATE TABLE這類語句，事務的隔離級別無法控制DDL語句。

-d --no-data: 只導出表結構，不導出表數據。
-t --no-create-info: 只導出數據，而不添加CREATE TABLE 語句。
-f --force:即使遇到SQL錯誤，也繼續執行，功能類似Oracle exp命令中的ignore參數。
-F --flush-logs: 在執行導出前先刷新日誌文件，視操作場景，有可能會觸發多次刷新日誌文件。一般來說，如果是全庫導出，建議先刷新日誌文件，否則就不用了。
--master -data[=#]: 該選項將二進制日誌的位置和文件名寫入到輸出中。該選項要求有RELOAD權限，並且必須啟用二進制日誌。如果該選項值等於1，位置和文件名被寫入CHANGE MASTER語句形式的轉儲輸出，如果你使用該SQL轉儲主服務器以設置從服務器，從服務器從主服務器二進制日誌的正確位置開始。如果選項值等於2，CHANGE MASTER語句被寫成SQL註釋。如果value被省略，這是默認動作。

--master-data選項會啟用--lock-all-tables，除非還指定--single-transaction(在這種情況下，只在剛開始轉儲時短時間獲得全局讀鎖定。又見--single-transaction。在任何一種情況下，日誌相關動作發生在轉儲時。該選項自動關閉--lock-tables。

所以，我在INNODB引擎的數據庫備份時，我會同時使用--master-data=2和--single-transaction兩個選項。

-x --lock-all-tables: 在導出任務執行期間鎖定所有數據庫中的所有表，以保證數據的一致性。這是一個全局鎖定，並且自動關閉--single-transaction 和--lock-tables 選項。這個參數副作用比較大，這是全庫鎖定，備份執行過程中，該庫無法進行讀寫操作，不是所有業務場景都能接受的。請慎用。
-n --no-create-db: 不生成建庫的語句CREATE DATABASE … IF EXISTS，即使指定--all-databases或--databases這類參數。
--triggers: 導出表的觸發器腳本，默認就是啟用狀態。使用--skip-triggers禁用它。
-R --routines:導出存儲過程以及自定義函數。

在轉儲的數據庫中轉儲存儲程序(函數和程序)。

-E --events :輸出event。
--ignore-table: 指定的表對像不做導出，參數值的格式為[db_nametblname]，注意每次只能指定一個值，如果有多個表對像都不進行導出操作的話，那就需要指定多個--ignore-table參數，並為每個參數指定不同的參數值。
--add-drop-database: 在任何創建庫語句前，附加DROP DATABASE 語句。
--add-drop-table: 在任何創建表語句前，附加DROP TABLE語句。這個參數是默認啟用狀態，可以使用-- skip-add-drop-table參數禁用該參數。
--add-drop-trigger: 創建任何觸發器前，附加DROP TRIGGER語句。
--add-locks: 在生成的INSERT語句前附加LOCK語句，該參數默認是啟用狀態。使用--skip-add-locks參數禁用。
-K --disable-keys:在導出的文件中輸出'/!40000 ALTER TABLE tb_name DISABLE KEYS */;以及

'/!40000 ALTER TABLE tb_name ENABLE KEYS */; '等信息。這兩段信息會分別放在INSERT語句的前後，也就是說，在插入數據前先禁用索引，等完成數據插入後再啟用索引，目的是為了加快導入的速度。該參數默認就是啟用狀態。可以通過--skip-disable-keys參數來禁用。

--opt: 功能等同於同時指定了--add-drop-table --add-locks --create- options  --quick --extended-insert --lock-tables --set-charset以及--disable-keys這些參數。默認就是啟用狀態。使用--skip-opt來禁用該參數。
--skip-opt: 禁用--opt選項，相當於同時禁用--add-drop-table --add-locks --create-options --quick --extended-insert --lock-tables --set-charset 及--disable-keys這些參數。
-q --quick: 導出時不會將數據加載到緩存，而是直接輸出。默認就是啟用狀態。可以使用--skip-quick 來禁用該參數。