MySQL備份和恢復資料表的方法

備份是最簡單的保護資料的方法，本節將介紹多種備份方法。為了得到一個一致的備份，在相關的表上做一個LOCK TABLES，你只需一個讀鎖定，當你在資料庫目錄中做檔案的一個複製時，這允許其他執行緒繼續查詢該表；當你恢復資料時，需要一個寫鎖定，以避免衝突。

使用SQL語句備份和恢復

你可以使用SELECT INTO OUTFILE語句備份資料，並用LOAD DATA INFILE語句恢復資料。這種方法只能匯出資料的內容，不包括表的結構，如果表的結構檔案損壞，你必須要先恢復原來的表的結構。

語法：

SELECT * INTO {OUTFILE | DUMPFILE} 'file_name' FROM tbl_name
LOAD DATA [LOW_PRIORITY] [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE]
INTO TABLE tbl_name

SELECT ... INTO OUTFILE 'file_name'格式的SELECT語句將選擇的行寫入一個檔案。檔案在伺服器主機上被建立，並且不能是已經存在的(不管別的，這可阻止資料庫表和檔案例如“/etc/passwd”被破壞)。SELECT ... INTO OUTFILE是LOAD DATA INFILE逆操作。

LOAD DATA INFILE語句從一個文字檔案中以很高的速度讀入一個表中。如果指定LOCAL關鍵詞，從客戶主機讀檔案。如果LOCAL沒指定，檔案必須位於伺服器上。(LOCAL在MySQL3.22.6或以後版本中可用。)

為了安全原因，當讀取位於伺服器上的文字檔案時，檔案必須處於資料庫目錄或可被所有人讀取。另外，為了對伺服器上檔案使用LOAD DATA INFILE，在伺服器主機上你必須有file的許可權。使用這種SELECT INTO OUTFILE語句，在伺服器主機上你必須有FILE許可權。

為了避免重複記錄，在表中你需要一個PRIMARY KEY或UNIQUE索引。當在唯一索引值上一個新記錄與一個老記錄重複時，REPLACE關鍵詞使得老記錄用一個新記錄替代。如果你指定IGNORE，跳過有唯一索引的現有行的重複行的輸入。如果你不指定任何一個選項，當找到重複索引值時，出現一個錯誤，並且文字檔案的餘下部分被忽略時。

如果你指定關鍵詞LOW_PRIORITY，LOAD DATA語句的執行被推遲到沒有其他客戶讀取表後。

使用LOCAL將比讓伺服器直接存取檔案慢些，因為檔案的內容必須從客戶主機傳送到伺服器主機。在另一方面，你不需要file許可權裝載本地檔案。如果你使用LOCAL關鍵詞從一個本地檔案裝載資料，伺服器沒有辦法在操作的當中停止檔案的傳輸，因此預設的行為好像IGNORE被指定一樣。

當在伺服器主機上尋找檔案時，伺服器使用下列規則：

◆如果給出一個絕對路徑名，伺服器使用該路徑名。 
◆如果給出一個有一個或多個前置部件的相對路徑名，伺服器相對伺服器的資料目錄搜尋檔案。 
◆如果給出一個沒有前置部件的一個檔名，伺服器在當前資料庫的資料庫目錄尋找檔案。

假定表tbl_name具有一個PRIMARY KEY或UNIQUE索引，備份一個資料表的過程如下：

1、鎖定資料表，避免在備份過程中，表被更新

mysql>LOCK TABLES READ tbl_name;

2、匯出資料

mysql>SELECT * INTO OUTFILE ‘tbl_name.bak’ FROM tbl_name;

3、解鎖表

mysql>UNLOCK TABLES;

相應的恢復備份的資料的過程如下：

1、為表增加一個寫鎖定：

mysql>LOCK TABLES tbl_name WRITE;

2、恢復資料

mysql>LOAD DATA INFILE ‘tbl_name.bak’
  ->REPLACE INTO TABLE tbl_name;

如果，你指定一個LOW_PRIORITY關鍵字，就不必如上要對錶鎖定，因為資料的匯入將被推遲到沒有客戶讀表為止：

mysql>LOAD DATA  LOW_PRIORITY  INFILE ‘tbl_name’ 
  ->REPLACE INTO TABLE tbl_name;

3、解鎖表

使用mysqlimport恢復資料

如果你僅僅恢復資料，那麼完全沒有必要在客戶機中執行SQL語句，因為你可以簡單的使用mysqlimport程式，它完全是與LOAD DATA 語句對應的，由傳送一個LOAD DATA INFILE命令到伺服器來運作。執行命令mysqlimport --help，仔細檢視輸出，你可以從這裡得到幫助。

shell> mysqlimport [options] db_name filename ...

對於在命令列上命名的每個文字檔案，mysqlimport剝去檔名的副檔名並且使用它決定哪個表匯入檔案的內容。例如，名為“patient.txt”、
“patient.text”和“patient”將全部被匯入名為patient的一個表中。
 
常用的選項為：

-C, --compress 如果客戶和伺服器均支援壓縮，壓縮兩者之間的所有資訊。
-d, --delete 在匯入文字檔案前倒空表格。

l, --lock-tables 在處理任何文字檔案前為寫入所定所有的表。這保證所有的表在伺服器上被同步。

--low-priority，--local，--replace，--ignore分別對應LOAD DATA語句的LOW_PRIORITY，LOCAL，REPLACE，IGNORE關鍵字。

例如恢復資料庫db1中表tbl1的資料，儲存資料的檔案為tbl1.bak，假定你在伺服器主機上：

shell>mysqlimport --lock-tables --replace db1 tbl1.bak

這樣在恢復資料之前現對錶鎖定，也可以利用--low-priority選項：

shell>mysqlimport --low-priority --replace db1 tbl1.bak

如果你為遠端的伺服器恢復資料，還可以這樣：

shell>mysqlimport -C --lock-tables --replace db1 tbl1.bak

當然，解壓縮要消耗CPU時間。

象其它客戶機一樣，你可能需要提供-u，-p選項以透過身分驗證，也可以在選項檔案my.cnf中儲存這些引數，具體方法和其它客戶機一樣，這裡就不詳述了。

mysql->UNLOCAK TABLES; 

用mysqldump備份資料

同mysqlimport一樣，也存在一個工具mysqldump備份資料，但是它比SQL語句多做的工作是可以在匯出的檔案中包括SQL語句，因此可以備份資料庫表的結構，而且可以備份一個資料庫，甚至整個資料庫系統。

mysqldump [OPTIONS] database [tables]
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
mysqldump [OPTIONS] --all-databases [OPTIONS]

如果你不給定任何表，整個資料庫將被傾倒。

透過執行mysqldump --help，你能得到你mysqldump的版本支援的選項表。
 
1、備份資料庫的方法

例如，假定你在伺服器主機上備份資料庫db_name

shell> mydqldump db_name

當然，由於mysqldump預設時把輸出定位到標準輸出，你需要重定向標準輸出。

例如，把資料庫備份到bd_name.bak中：

shell> mydqldump db_name>db_name.bak

你可以備份多個資料庫，注意這種方法將不能指定資料表：

shell> mydqldump --databases db1 db1>db.bak

你也可以備份整個資料庫系統的複製，不過對於一個龐大的系統，這樣做沒有什麼實際的價值：

shell> mydqldump --all-databases>db.bak

雖然用mysqldump匯出表的結構很有用，但是恢復大量資料時，眾多SQL語句使恢復的效率降低。你可以透過使用--tab選項，分開資料和建立表的SQL語句。
-T，--tab= 在選項指定的目錄裡，建立用製表符（tab）分隔列值的資料檔案和包含建立表結構的SQL語句的檔案，分別用副檔名.txt和.sql表示。該選項不能與--databases或--all-databases同時使用，並且mysqldump必須執行在伺服器主機上。

例如，假設資料庫db包括表tbl1，tbl2，你準備備份它們到/var/mysqldb
shell>mysqldump --tab=/var/mysqldb/  db

其效果是在目錄/var/mysqldb中生成4個檔案，分別是tbl1.txt、tbl1.sql、tbl2.txt和tbl2.sql。

2、mysqldump實用程式時的身份驗證的問題

同其他客戶機一樣，你也必須提供一個MySQL資料庫帳號用來匯出資料庫，如果你不是使用匿名使用者的話，可能需要手工提供引數或者使用選項檔案：

如果這樣：

shell>mysql -u root –pmypass db_name>db_name.sql
或者這樣在選項檔案中提供引數：

[mysqldump]
user=root
password=mypass

然後執行

shell>mysqldump db_name>db_name.sql

那麼一切順利，不會有任何問題，但要注意命令歷史會洩漏密碼，或者不能讓任何除你之外的使用者能夠訪問選項檔案，由於資料庫伺服器也需要這個選項檔案時，選項檔案只能被啟動伺服器的使用者（如，mysql）擁有和訪問，以免洩密。在Unix下你還有一個解決辦法，可以在自己的使用者目錄中提供個人選項檔案（~/.my.cnf）,例如，/home/some_user/.my.cnf，然後把上面的內容加入檔案中，注意防止洩密。在NT系統中，你可以簡單的讓c:\my.cnf能被指定的使用者訪問。

你可能要問，為什麼這麼麻煩呢，例如，這樣使用命令列：

shell>mysql -u root –p db_name>db_name.sql

或者在選項檔案中加入

[mysqldump]
user=root
password

然後執行命令列：

shell>mysql db_name>db_name.sql

你發現了什麼？往常熟悉的Enter password:提示並沒有出現，因為標準輸出被重定向到檔案db_name.sql中了，所以看不到往常的提示符，程式在等待你輸入密碼。在重定向的情況下，再使用互動模式，就會有問題。在上面的情況下，你還可以直接輸入密碼。然後在檔案db_name.sql檔案的第一行看到:

Enter password:#……..

你可能說問題不大，但是mysqldump之所以把結果輸出到標準輸出，是為了重定向到其它程式的標準輸入，這樣有利於編寫指令碼。例如：

用來自於一個資料庫的資訊充實另外一個MySQL資料庫也是有用的：
 
shell>mysqldump --opt database | mysql --host=remote-host -C database
如果mysqldump仍執行在提示輸入密碼的互動模式下，該命令不會成功，但是如果mysql是否執行在提示輸入密碼的互動模式下，都是可以的。

如果在選項檔案中的[client]或者[mysqldump]任何一段中指定了password選項，且不提供密碼，即使，在另一段中有提供密碼的選項password=mypass,例如

[client]
user=root
password
[mysqldump]
user=admin
password=mypass

那麼mysqldump一定要你輸入admin使用者的密碼：

mysql>mysqldump db_name

即使是這樣使用命令列：

mysql>mysqldump –u root –ppass1 db

也是這樣，不過要如果-u指定的使用者的密碼。

其它使用選項檔案的客戶程式也是這樣

3、有關生成SQL語句的最佳化控制

--add-locks  生成的SQL 語句中，在每個表資料恢復之前增加LOCK TABLES並且之後UNLOCK TABLE。(為了使得更快地插入到MySQL)。

--add-drop-table 生成的SQL 語句中，在每個create語句之前增加一個drop table。

-e, --extended-insert  使用全新多行INSERT語法。（給出更緊縮並且更快的插入語句）

下面兩個選項能夠加快備份表的速度：

-l, --lock-tables. 為開始匯出資料前，讀鎖定所有涉及的表。

-q, --quick 不緩衝查詢，直接傾倒至stdout。

理論上，備份時你應該指定上訴所有選項。這樣會使命令列過於複雜，作為代替，你可以簡單的指定一個--opt選項，它會使上述所有選項有效。

例如，你將匯出一個很大的資料庫：

shell> mysqldump --opt db_name > db_name.txt

當然，使用--tab選項時，由於不生成恢復資料的SQL語句，使用--opt時，只會加快資料匯出。

4、恢復mysqldump備份的資料

由於備份檔案是SQL語句的集合，所以需要在批處理模式下使用客戶機

如果你使用mysqldump備份單個資料庫或表，即：

shell>mysqldump --opt db_name > db_name.sql

由於db_name.sql中不包括建立資料庫或者選取資料庫的語句，你需要指定資料庫

shell>mysql db2 < db_name.sql

如果，你使用--databases或者--all-databases選項，由於匯出檔案中已經包含建立和選用資料庫的語句，可以直接使用，不比指定資料庫，例如：

shell>mysqldump --databases db_name > db_name.sql
shell>mysql

如果你使用--tab選項備份資料，資料恢復可能效率會高些

例如，備份資料庫db_name後在恢復：

shell>mysqldump --tab=/path/to/dir --opt test

如果要恢復表的結構，可以這樣：

shell>mysql < /path/to/dir/tbl1.sql

如果要恢復資料，可以這樣

shell>mysqlimport -l db /path/to/dir/tbl1.txt

如果是在Unix平臺下使用（推薦），就更方便了：

shell>ls -l *.sql | mysql db

shell>mysqlimport --lock-tables db /path/to/dir/*.txt

用直接複製的方法備份恢復

根據本章前兩節的介紹，由於MySQL的資料庫和表是直接透過目錄和表檔案實現的，因此直接複製檔案來備份資料庫資料，對MySQL來說特別方便。而且自MySQL 3.23起MyISAM表成為預設的表的型別，這種表可以為在不同的硬體體系中共享資料提供了保證。

使用直接複製的方法備份時，尤其要注意表沒有被使用，你應該首先對錶進行讀鎖定。

備份一個表，需要三個檔案：

對於MyISAM表：

tbl_name.frm  表的描述檔案
tbl_name.MYD 表的資料檔案
tbl_name.MYI  表的索引檔案

對於ISAM表：

tbl_name.frm  表的描述檔案
tbl_name.ISD 表的資料檔案
tbl_name.ISM  表的索引檔案

你直接複製檔案從一個資料庫伺服器到另一個伺服器，對於MyISAM表，你可以從執行在不同硬體系統的伺服器之間複製檔案，例如，SUN伺服器和INTEL PC機之間。

總結

本節介紹了備份恢復資料庫的多種方法，讀者可以根據需要選用，對於文中涉及到的SQL語句、工具主要有：

1、SELECT…INTO OUTFILE和LOAD DATA INFILE
2、mysqldump
3、mysqlimport

對於這些內容，讀者需要注重掌握的是mysqldump實用程式的使用，以及mysql批處理模式執行包含SQL語句的檔案的方法。這在備份和恢復資料庫表中非常常用。另外要注意的是直接複製的方法。