mysqldump 工具使用詳解

概述

mysqldump用戶端可用來轉儲資料庫或搜集資料庫進行備份或将資料轉移到另一個SQL伺服器(不一定是一個MySQL伺服器)。轉儲包含建立表和/或裝載表的SQL語句。

如果你在伺服器上進行備份，并且表均為MyISAM表，應考慮使用mysqlhotcopy，因為可以更快地進行備份和恢複。

調用方法

有3種方式來調用mysqldump：

#導出指定資料庫中的指定表，資料庫名與表明之間用空格隔開，多個表間用空格隔開。      
        
shell> mysqldump [options] db_name [tables] > filename.sql       

#到處多個資料庫，之間用空格分隔      
        
shell> mysqldump [options] ---database DB1 [DB2 DB3...] > filename.sql      

#到處所有的資料庫      
        
shell> mysqldump [options] --all--database > filename.sql      

如果沒有指定任何表或使用了---database或--all--database選項，則轉儲整個資料庫。

要想獲得你的版本的mysqldump支援的選項，執行mysqldump ---help。

如果運作mysqldump沒有--quick或--opt選項，mysqldump在轉儲結果前将整個結果集裝入記憶體。如果轉儲大資料庫可能會出現問題。該選項預設啟用，但可以用--skip-opt禁用。

如果使用最新版本的mysqldump程式生成一個轉儲重裝到很舊版本的MySQL伺服器中，不應使用--opt或-e選項。

用途

mysqldump最常用于備份一個整個的資料庫：

shell> mysqldump --opt db_name > backup-file.sql      

你可以這樣将轉儲檔案讀回到伺服器：

shell> mysql db_name < backup-file.sql      

或者為：

shell> mysql -e "source /path-to--backup/backup-file.sql" db_name      

mysqldump也可用于從一個MySQL伺服器向另一個伺服器複制資料時裝載資料庫：

shell> mysqldump --opt db_name | mysql --host=remote_host -C db_name      

可以用一個指令轉儲幾個資料庫：

shell> mysqldump ---database db_name1 [db_name2 ...] > my_databases.sql      

如果你想要轉儲所有資料庫，使用--all--database選項：

shell> mysqldump --all-databases > all_databases.sql      

如果表儲存在InnoDB存儲引擎中，mysqldump提供了一種聯機備份的途徑(參見下面的指令)。該備份隻需要在開始轉儲時對所有表進行全局讀鎖定(使用FLUSH TABLES WITH READ LOCK)。獲得鎖定後，讀取二進制日志的相應内容并将鎖釋放。是以如果并且隻有當發出FLUSH...時正執行一個長的更新語句，MySQL伺服器才停止直到長語句結束，然後轉儲則釋放鎖。是以如果MySQL伺服器隻接收到短("短執行時間")的更新語句，即使有大量的語句，也不會注意到鎖期間。

shell> mysqldump --all-databases --single-transaction > all_databases.sql      

對于點對點恢複(也稱為“前滾”，當你需要恢複舊的備份并重放該備份以後的更改時)，循環二進制日志(參見5.11.3節，“二進制日志”)或至少知道轉儲對應的二進制日志内容很有用：

shell> mysqldump --all-databases --master-data=2 > all_databases.sql      

或      

shell> mysqldump --all-databases --flush-logs --master-data=2 > all_databases.sql      

如果表儲存在InnoDB存儲引擎中，同時使用--master-data和--single-transaction提供了一個很友善的方式來進行适合點對點恢複的聯機備份。

關于備份的詳細資訊，參見5.9.1節，“資料庫備份”。

mysqldump參數

mysqldump支援下面的選項：

·        ---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 EXISTSdb_name和USEdb_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

對于每個表，用;和;語句引用INSERT語句。這樣可以更快地裝載轉儲檔案，因為在插入所有行後建立索引。該選項隻适合MyISAM表。

·        --extended-insert，-e

使用包括幾個VALUES清單的多行INSERT文法。這樣使轉儲檔案更小，重載檔案時可以加速插入。

·        --fields-terminated-by=...，--fields-enclosed-by=...，--fields-optionally-enclosed-by=...，--fields-escaped-by=...，--行-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，-hhost_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 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，-rfile

将輸出轉向給定的檔案。該選項應用在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，-Spath

當連接配接localhost(為預設主機)時使用的套接字檔案。

·        --skip--comments

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

·        --tab=path，-Tpath

産生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，-uuser_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或-Ovar_name=value文法設定變量。然而，現在不贊成使用該文法。

用途

mysqldump最常用于備份一個整個的資料庫：

shell> mysqldump --opt db_name > backup-file.sql      

你可以這樣将轉儲檔案讀回到伺服器：

shell> mysql db_name < backup-file.sql      

或者為：

shell> mysql -e "source /path-to--backup/backup-file.sql" db_name      

mysqldump也可用于從一個MySQL伺服器向另一個伺服器複制資料時裝載資料庫：

shell> mysqldump --opt db_name | mysql --host=remote_host -C db_name      

可以用一個指令轉儲幾個資料庫：

shell> mysqldump ---database db_name1 [db_name2 ...] > my_databases.sql      

如果你想要轉儲所有資料庫，使用--all--database選項：

shell> mysqldump --all-databases > all_databases.sql      

如果表儲存在InnoDB存儲引擎中，mysqldump提供了一種聯機備份的途徑(參見下面的指令)。該備份隻需要在開始轉儲時對所有表進行全局讀鎖定(使用FLUSH TABLES WITH READ LOCK)。獲得鎖定後，讀取二進制日志的相應内容并将鎖釋放。是以如果并且隻有當發出FLUSH...時正執行一個長的更新語句，MySQL伺服器才停止直到長語句結束，然後轉儲則釋放鎖。是以如果MySQL伺服器隻接收到短("短執行時間")的更新語句，即使有大量的語句，也不會注意到鎖期間。

shell> mysqldump --all-databases --single-transaction > all_databases.sql      

對于點對點恢複(也稱為“前滾”，當你需要恢複舊的備份并重放該備份以後的更改時)，循環二進制日志(參見5.11.3節，“二進制日志”)或至少知道轉儲對應的二進制日志内容很有用：

shell> mysqldump --all-databases --master-data=2 > all_databases.sql      

或      

shell> mysqldump --all-databases --flush-logs --master-data=2 > all_databases.sql      

如果表儲存在InnoDB存儲引擎中，同時使用--master-data和--single-transaction提供了一個很友善的方式來進行适合點對點恢複的聯機備份。

關于備份的詳細資訊，參見5.9.1節，“資料庫備份”。