MySQL備份 (mysqldump) 教學

資料來源:https://dotblogs.com.tw/michaelchen/2015/01/06/mysql_backup
http://jax-work-archive.blogspot.tw/2009/07/mysqldump-51-mysql.html

http://seanlook.com/2014/12/05/mysql_mysqldump_options_examples/

WINDOWS工具下載收藏: https://github.com/jash-git/Windows_MySQL_Tool_-bat

[匯出單一資料庫]

 mysqldump -u root -e -q -p --default-character-set=utf8 database_name > back.sql

[匯出單一資料表]

 mysqldump -u root -e -q -p --default-character-set=utf8 database_name datatable_name > back.sql

[匯出全部資料庫]

 mysqldump -u root -e -q -p --default-character-set=utf8 --all-databases > back.sql

[匯入單一資料庫]

    mysql -u root -p database_name < back.sql --default-character-set=utf8

[匯入單一資料表]

    mysql -u root -p database_name datatable_name < back.sql --default-character-set=utf8

[匯入全部資料庫]

    mysql -u root -p < back.sql --default-character-set=utf8

[直接匯入到另外一台資料庫]

 mysqldump -u root -e -q --password=XXX --all-databases --default-character-set=utf8 | mysql -u root -h IP位置 --password=XXXX --default-character-set=utf8

官網參數介紹
    https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html

常用參數官方說明        

    效能:    
    --extended-insert, -e
        Write INSERT statements using multiple-row syntax that includes several VALUES lists. This results in a smaller dump file and speeds up inserts when the file is reloaded.    
        
    --quick, -q
        This option is useful for dumping large tables. It forces mysqldump to retrieve rows for a table from the server a row at a time rather than retrieving the entire row set and buffering it in memory before writing it out.

    --force, -f
        Ignore all errors; continue even if an SQL error occurs during a table dump.
        One use for this option is to cause mysqldump to continue executing even when it encounters a view that has become invalid because the definition refers to a table that has been dropped. Without --force, mysqldump exits with an error message. With --force, mysqldump prints the error message, but it also writes an SQL comment containing the view definition to the dump output and continues executing.        

    資料類:
    --no-data, -d
        Do not write any table row information (that is, do not dump table contents). This is useful if you want to dump only the CREATE TABLE statement for the table (for example, to create an empty copy of the table by loading the dump file).

 

網路上參數中文說明
    --help，-？
        顯示幫助消息並退出。

    --add-drop--database
        在每個CREATE DATABASE語句前新增DROP DATABASE語句。

    --add-drop-tables
        在每個CREATE TABLE語句前新增DROP TABLE語句。

    --add-locking
        用LOCK TABLES和UNLOCK TABLES語句引用每個資料表轉儲。重載轉儲檔案時插入得更快。參見7.2.16節，「INSERT語句的速度」。

    --all--database，-A
        轉儲所有資料庫中的所有資料表。與使用---database選項相同，在命令行中命名所有資料庫。

    --allow-keywords
        允許建立關鍵字列名。應在每個列名前面加上資料表名前綴。

    ---comments[={0|1}]
        如果設置為 0，禁止轉儲檔案中的其它訊息，例如程式版本、伺服器版本和主機。--skip—comments與---comments=0的結果相同。 預設值為1，即包括額外訊息。

    --compact
        產生少量輸出。該選項禁用註釋並啟用--skip-add-drop-tables、--no-set-names、--skip-disable-keys和--skip-add-locking選項。

    --compatible=name
        產生與其它資料庫系統或舊的MySQL伺服器更兼容的輸出。值可以為ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options或者no_field_options。要使用幾個值，用逗號將它們隔開。這些值與設置伺服器SQL模式的相應選項有相同的含義。參見5.3.2節，「SQL伺服器模式」。

        該選項不能保證同其它伺服器之間的相容性。它只啟用那些目前能夠使轉儲輸出更兼容的SQL模式值。例如，--compatible=oracle 不映射Oracle類型或使用Oracle註釋語法的數據類型。

    --complete-insert，-c
        使用包括列名的完整的INSERT語句。

    --compress，-C
        壓縮在客戶端和伺服器之間發送的所有訊息（如果二者均支援壓縮）。

    --create-option
        在CREATE TABLE語句中包括所有MySQL資料表選項。

    --database，-B
        轉儲幾個資料庫。通常情況，mysqldump將命令行中的第1個名字參量看作資料庫名，後面的名看作資料表名。使用該選項，它將所有名字參量看作資料庫名。CREATE DATABASE IF NOT EXISTS db_name和USE db_name語句包含在每個新資料庫前的輸出中。

    --debug[=debug_options]，-# [debug_options]
        寫調試日誌。debug_options字串通常為'd:t:o,file_name'。

    --default-character-set=charset
        使用charsetas預設字元編碼。參見5.10.1節，「數據和排序用字元編碼」。如果沒有指定，mysqldump使用utf8。

    --delayed-insert
        使用INSERT DELAYED語句插入行。

    --delete-master-logs
        在主複製伺服器上，完成轉儲操作後刪除二進制日誌。該選項自動啟用--master-data。

    --disable-keys，-K
        對於每個資料表，用/*!40000 ALTER TABLE tbl_name DISABLE KEYS */;和/*!40000 ALTER TABLE tbl_name ENABLE KEYS */;語句引用INSERT語句。這樣可以更快地裝載轉儲檔案，因為在插入所有行後建立索引。該選項只適合MyISAM資料表。

    --extended-insert，-e
        使用包括幾個VALUES列資料表的多行INSERT語法。這樣使轉儲檔案更小，重載檔案時可以加速插入。

    --fields-terminated-by=...
    --fields-enclosed-by=...
    --fields-optionally-enclosed-by=...
    --fields-escaped-by=...
    --lines-terminated-by=...
        這些選項結合-T選項使用，與LOAD DATA INFILE的相應子句有相同的含義。參見13.2.5節，「LOAD DATA INFILE語法」。

    --first-slave，-x
        不贊成使用，現在重新命名為--lock-all-tables。

    --flush-logs，-F
        開始轉儲前刷新MySQL伺服器日誌檔案。該選項要求RELOAD權限。請注意如果結合--all--database(或-A)選項使用該選項，根據每個轉儲的資料庫刷新日誌。

        例外情況是當使用--lock-all-tables或--master-data的時候：在這種情況下，日誌只刷新一次，在所有 資料表被鎖定後刷新。如果您想要同時轉儲和刷新日誌，應使用--flush-logs連同--lock-all-tables或--master-data。

    --force，-f
        在資料表轉儲過程中，即使出現SQL錯誤也繼續。

    --host=host_name，-h host_name
        從給定主機的MySQL伺服器轉儲數據。預設主機是localhost。

    --hex-blob
        使用十六進制符號轉儲二進制字串列(例如，'abc' 變為0x616263)。影響到的列有BINARY、VARBINARY、BLOB。

    --lock-all-tables，-x
        將資料庫中的所有資料表加鎖。在整體轉儲過程中通過全局讀鎖定來實現。該選項自動關閉--single-transaction和--lock-tables。

    --lock-tables，-l
        開始轉儲前鎖定所有資料表。用READ LOCAL鎖定資料表以允許並行插入MyISAM資料表。對於事務資料表例如InnoDB和BDB，--single-transaction是一個更好的選項，因為它不根本需要鎖定資料表。

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

    --master-data[=value]
        該選項將二進制日誌的位置和檔案名寫入到輸出中。該選項要求有RELOAD權限，並且必須啟用二進制日誌。如果該選項值等於1，位置和檔案名被寫入CHANGE MASTER語句形式的轉儲輸出，如果您使用該SQL轉儲主伺服器以設置從伺服器，從伺服器從主伺服器二進制日誌的正確位置開始。如果選項值等於2，CHANGE MASTER語句被寫成SQL註釋。如果value被省略，這是預設動作。

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

    --no-create-db，-n
        該選項禁用CREATE DATABASE /*!32312 IF NOT EXISTS*/ db_name語句，如果給出---database或--all--database選項，則包含到輸出中。

    --no-create-info，-t
        不寫重新建立每個轉儲資料表的CREATE TABLE語句。

    --no-data，-d
        不寫資料表的任何行訊息。如果您只想轉儲資料表的結構這很有用。

    --opt
        該選項是速記；等同於指定 --add-drop-tables --add-locking --create-option --disable-keys --extended-insert --lock-tables --quick --set-charset。

        它可以給出很快的轉儲操作並產生一個可以很快裝入MySQL伺服器的轉儲檔案。該選項預設開啟，但可以用--skip-opt禁用。要想只禁用確信用-opt啟用的選項，使用--skip形式；例如，--skip-add-drop-tables或--skip-quick。

    --password[=password]，-p[password]
        連接伺服器時使用的密碼。如果您使用短選項形式(-p)，不能在選項和密碼之間有一個空格。如果在命令行中，忽略了--password或-p選項後面的 密碼值，將提示您輸入一個。

    --port=port_num，-P port_num
        用於連接的TCP/IP端口號。

    --protocol={TCP | SOCKET | PIPE | MEMORY}
        使用的連接協議。

    --quick，-q
        該選項用於轉儲大的資料表。它強制mysqldump從伺服器一次一行地檢索資料表中的行而不是檢索所有行並在輸出前將它緩存到內存中。

    --quote-names，-Q
        用『`』字元引用資料庫、資料表和列名。如果伺服器SQL模式包括ANSI_QUOTES選項，用『"』字元引用名。預設啟用該選項。可以用--skip-quote-names禁用，但該選項應跟在其它選項後面，例如可以啟用--quote-names的--compatible。

    --result-file=file，-r file
        將輸出轉向給定的檔案。該選項應用在Windows中，因為它禁止將新行『\n』字元轉換為『\r\n』回車、返回/新行序列。

    --routines，-R
        在轉儲的資料庫中轉儲儲存程式(函數和程式)。使用---routines產生的輸出包含CREATE PROCEDURE和CREATE FUNCTION語句以重新建立子程式。但是，這些語句不包括屬性，例如子程式定義者或建立和修改時間戳。這說明當重載子程式時，對它們進行建立時定義者應設置為重載用戶，時間戳等於重載時間。

        如果您需要建立的子程式使用原來的定義者和時間戳屬性，不使用--routines。相反，使用一個具有mysql資料庫相應權限的MySQL帳號直接轉儲和重載mysql.proc資料表的內容。

        該選項在MySQL 5.1.2中新增進來。在此之前，儲存程式不轉儲。

    --set-charset
        將SET NAMES default_character_set加到輸出中。該選項預設啟用。要想禁用SET NAMES語句，使用--skip-set-charset。

    --single-transaction
        該選項從伺服器轉儲數據之前發出一個BEGIN SQL語句。它只適用於事務資料表，例如InnoDB和BDB，因為然後它將在發出BEGIN而沒有阻塞任何應用程式時轉儲一致的資料庫狀態。

        當使用該選項時，應記住只有InnoDB資料表能以一致的狀態被轉儲。例如，使用該選項時任何轉儲的MyISAM或HEAP資料表仍然可以更改狀態。

    --single-transaction選項和--lock-tables選項是互斥的，因為LOCK TABLES會使任何掛起的事務隱含提交。

        要想轉儲大的資料表，應結合--quick使用該選項。

    --socket=path，-S path
        當連接localhost(為預設主機)時使用的套接字檔案。

    --skip--comments
        參見---comments選項的描述。

    --tab=path，-T path
        產生tab分割的數據檔案。對於每個轉儲的資料表，mysqldump建立一個包含建立資料表的CREATE TABLE語句的tbl_name.sql檔案，和一個包含其數據的tbl_name.txt檔案。選項值為寫入檔案的目錄。

        預設情況，.txt數據檔案的格式是在列值和每行後面的新行之間使用tab字元。可以使用--fields-xxx和--行--xxx選項明顯指定格式。

        註釋：該選項只適用於mysqldump與mysqld伺服器在同一台機器上運行時。您必須具有FILE權限，並且伺服器必須有在您指定的目錄中有寫檔案的授權。

    --tables
        覆蓋---database或-B選項。選項後面的所有參量被看作資料表名。

    --triggers
        為每個轉儲的資料表轉儲觸發器。該選項預設啟用；用--skip-triggers禁用它。

    --tz-utc
        在轉儲檔案中加入SET TIME_ZONE='+00:00'以便TIMESTAMP列可以在具有不同時區的伺服器之間轉儲和重載。(不使用該選項，TIMESTAMP列在具有本地時區的源伺服器和目的伺服器之間轉儲和重載）。--tz-utc也可以保護由於夏令時帶來的更改。--tz-utc預設啟用。要想禁用它，使用--skip-tz-utc。該選項在MySQL 5.1.2中加入。

    --user=user_name，-u user_name
        連接伺服器時使用的MySQL帳號。

    --verbose，-v
        冗長模式。打印出程式操作的詳細訊息。

    --version，-V
        顯示版本訊息並退出。

    --where='where-condition', -w 'where-condition'
        只轉儲給定的WHERE條件選擇的記錄。請注意如果條件包含命令解釋符專用空格或字元，一定要將條件引用起來。
        例如：
        "--where=user='jimf'"

        "-wuserid>1"

        "-wuserid<1"

    --xml，-X
        將轉儲輸出寫成XML。

    --var_name=value
        用來選項設置下面的變數：

        max_allowed_packet

        客戶端/伺服器之間通信的緩存區的最大大小。最大為1GB。
        net_buffer_length

        客戶端/伺服器之間通信的緩存區的初始大小。當建立多行插入語句時(如同使用選項--extended-insert或--opt)，mysqldump建立長度達net_buffer_length的行。如果增加該變數，還應確保在MySQL伺服器中的net_buffer_length變數至少這麼大。

        還可以使用--set-variable=var_name=value或-O var_name=value語法設置變數。然而，現在不贊成使用該語法。