數(shù)據(jù)庫備份與恢復(fù)

1、備份是最簡單的保護(hù)數(shù)據(jù)的方法，本節(jié)將介紹多種備份方法。為了得到一個一致的備份，在相關(guān)的表上做一個LOCK TABLES，你只需一個讀鎖定，當(dāng)你在數(shù)據(jù)庫目錄中做文件的一個拷貝時，這允許其他線程繼續(xù)查詢該表；當(dāng)你恢復(fù)數(shù)據(jù)時，需要一個寫鎖定，以避免沖突。使用SQL語句備份和恢復(fù)你可以使用SELECT INTO OUTFILE語句備份數(shù)據(jù)，并用LOAD DATA INFILE語句恢復(fù)數(shù)據(jù)。這種方法只能導(dǎo)出數(shù)據(jù)的內(nèi)容，不包括表的結(jié)構(gòu)，如果表的結(jié)構(gòu)文件損壞，你必須要先恢復(fù)原來的表的結(jié)構(gòu)。語法：SELECT * INTO OUTFILE | DUMPFILE 'file_name' FROM

2、 tbl_nameLOAD DATA LOW_PRIORITY LOCAL INFILE 'file_name.txt' REPLACE | IGNOREINTO TABLE tbl_nameSELECT . INTO OUTFILE 'file_name'格式的SELECT語句將選擇的行寫入一個文件。文件在服務(wù)器主機上被創(chuàng)建，并且不能是已經(jīng)存在的(不管別的，這可阻止數(shù)據(jù)庫表和文件例如“/etc/passwd”被破壞)。SELECT . INTO OUTFILE是LOAD DATA INFILE逆操作。LOAD DATA INFILE語句從一個文本文件中以很高的

3、速度讀入一個表中。如果指定LOCAL關(guān)鍵詞，從客戶主機讀文件。如果LOCAL沒指定，文件必須位于服務(wù)器上。(LOCAL在MySQL或以后版本中可用。)為了安全原因，當(dāng)讀取位于服務(wù)器上的文本文件時，文件必須處于數(shù)據(jù)庫目錄或可被所有人讀取。另外，為了對服務(wù)器上文件使用LOAD DATA INFILE，在服務(wù)器主機上你必須有file的權(quán)限。使用這種SELECT INTO OUTFILE語句，在服務(wù)器主機上你必須有FILE權(quán)限。為了避免重復(fù)記錄，在表中你需要一個PRIMARY KEY或UNIQUE索引。當(dāng)在唯一索引值上一個新記錄與一個老記錄重復(fù)時，REPLACE關(guān)鍵詞使得老記錄用一個新記錄替代。如果你

4、指定IGNORE，跳過有唯一索引的現(xiàn)有行的重復(fù)行的輸入。如果你不指定任何一個選項，當(dāng)找到重復(fù)索引值時，出現(xiàn)一個錯誤，并且文本文件的余下部分被忽略時。如果你指定關(guān)鍵詞LOW_PRIORITY，LOAD DATA語句的執(zhí)行被推遲到?jīng)]有其他客戶讀取表后。 使用LOCAL將比讓服務(wù)器直接存取文件慢些，因為文件的內(nèi)容必須從客戶主機傳送到服務(wù)器主機。在另一方面，你不需要file權(quán)限裝載本地文件。如果你使用LOCAL關(guān)鍵詞從一個本地文件裝載數(shù)據(jù)，服務(wù)器沒有辦法在操作的當(dāng)中停止文件的傳輸，因此缺省的行為好像IGNORE被指定一樣。當(dāng)在服務(wù)器主機上尋找文件時，服務(wù)器使用下列規(guī)則：如果給出一個絕對路徑名，服務(wù)器使

5、用該路徑名。 如果給出一個有一個或多個前置部件的相對路徑名，服務(wù)器相對服務(wù)器的數(shù)據(jù)目錄搜索文件。 如果給出一個沒有前置部件的一個文件名，服務(wù)器在當(dāng)前數(shù)據(jù)庫的數(shù)據(jù)庫目錄尋找文件。假定表tbl_name具有一個PRIMARY KEY或UNIQUE索引，備份一個數(shù)據(jù)表的過程如下：1、鎖定數(shù)據(jù)表，避免在備份過程中，表被更新mysql>LOCK TABLES READ tbl_name;2、導(dǎo)出數(shù)據(jù)mysql>SELECT * INTO OUTFILE tbl_name.bak FROM tbl_name;3、解鎖表mysql>UNLOCK TABLES;相應(yīng)的恢復(fù)備份的數(shù)據(jù)的過程如下

6、：1、為表增加一個寫鎖定：mysql>LOCK TABLES tbl_name WRITE;2、恢復(fù)數(shù)據(jù)mysql>LOAD DATA INFILE tbl_name.bak ->REPLACE INTO TABLE tbl_name;如果，你指定一個LOW_PRIORITY關(guān)鍵字，就不必如上要對表鎖定，因為數(shù)據(jù)的導(dǎo)入將被推遲到?jīng)]有客戶讀表為止：mysql>LOAD DATA LOW_PRIORITY INFILE tbl_name ->REPLACE INTO TABLE tbl_name;3、解鎖表使用mysqlimport恢復(fù)數(shù)據(jù)如果你僅僅恢復(fù)數(shù)據(jù)，那么完全

7、沒有必要在客戶機中執(zhí)行SQL語句，因為你可以簡單的使用mysqlimport程序，它完全是與LOAD DATA 語句對應(yīng)的，由發(fā)送一個LOAD DATA INFILE命令到服務(wù)器來運作。執(zhí)行命令mysqlimport -help，仔細(xì)查看輸出，你可以從這里得到幫助。shell> mysqlimport options db_name filename .對于在命令行上命名的每個文本文件，mysqlimport剝?nèi)ノ募臄U展名并且使用它決定哪個表導(dǎo)入文件的內(nèi)容。例如，名為“patient.txt”、“patient.text”和“patient”將全部被導(dǎo)入名為patient的一個表中。

8、 常用的選項為：-C, -compress 如果客戶和服務(wù)器均支持壓縮，壓縮兩者之間的所有信息。-d, -delete 在導(dǎo)入文本文件前倒空表格。l, -lock-tables 在處理任何文本文件前為寫入所定所有的表。這保證所有的表在服務(wù)器上被同步。-low-priority，-local，-replace，-ignore分別對應(yīng)LOAD DATA語句的LOW_PRIORITY，LOCAL，REPLACE，IGNORE關(guān)鍵字。例如恢復(fù)數(shù)據(jù)庫db1中表tbl1的數(shù)據(jù)，保存數(shù)據(jù)的文件為tbl1.bak，假定你在服務(wù)器主機上：shell>mysqlimport -lock-tables -re

9、place db1 tbl1.bak這樣在恢復(fù)數(shù)據(jù)之前現(xiàn)對表鎖定，也可以利用-low-priority選項：shell>mysqlimport -low-priority -replace db1 tbl1.bak如果你為遠(yuǎn)程的服務(wù)器恢復(fù)數(shù)據(jù)，還可以這樣：shell>mysqlimport -C -lock-tables -replace db1 tbl1.bak當(dāng)然，解壓縮要消耗CPU時間。象其它客戶機一樣，你可能需要提供-u，-p選項以通過身分驗證，也可以在選項文件f中存儲這些參數(shù)，具體方法和其它客戶機一樣，這里就不詳述了。mysql->UNLOCAK TABLES; 用

10、mysqldump備份數(shù)據(jù)同mysqlimport一樣，也存在一個工具mysqldump備份數(shù)據(jù)，但是它比SQL語句多做的工作是可以在導(dǎo)出的文件中包括SQL語句，因此可以備份數(shù)據(jù)庫表的結(jié)構(gòu)，而且可以備份一個數(shù)據(jù)庫，甚至整個數(shù)據(jù)庫系統(tǒng)。mysqldump OPTIONS database tablesmysqldump OPTIONS -databases OPTIONS DB1 DB2 DB3.mysqldump OPTIONS -all-databases OPTIONS如果你不給定任何表，整個數(shù)據(jù)庫將被傾倒。 通過執(zhí)行mysqldump -help，你能得到你mysqldump的版本支持的

11、選項表。 1、備份數(shù)據(jù)庫的方法例如，假定你在服務(wù)器主機上備份數(shù)據(jù)庫db_nameshell> mydqldump db_name當(dāng)然，由于mysqldump缺省時把輸出定位到標(biāo)準(zhǔn)輸出，你需要重定向標(biāo)準(zhǔn)輸出。例如，把數(shù)據(jù)庫備份到bd_name.bak中：shell> mydqldump db_name>db_name.bak你可以備份多個數(shù)據(jù)庫，注意這種方法將不能指定數(shù)據(jù)表：shell> mydqldump -databases db1 db1>db.bak你也可以備份整個數(shù)據(jù)庫系統(tǒng)的拷貝，不過對于一個龐大的系統(tǒng)，這樣做沒有什么實際的價值：shell> myd

12、qldump -all-databases>db.bak雖然用mysqldump導(dǎo)出表的結(jié)構(gòu)很有用，但是恢復(fù)大量數(shù)據(jù)時，眾多SQL語句使恢復(fù)的效率降低。你可以通過使用-tab選項，分開數(shù)據(jù)和創(chuàng)建表的SQL語句。-T，-tab= 在選項指定的目錄里，創(chuàng)建用制表符（tab）分隔列值的數(shù)據(jù)文件和包含創(chuàng)建表結(jié)構(gòu)的SQL語句的文件，分別用擴展名.txt和.sql表示。該選項不能與-databases或-all-databases同時使用，并且mysqldump必須運行在服務(wù)器主機上。例如，假設(shè)數(shù)據(jù)庫db包括表tbl1，tbl2，你準(zhǔn)備備份它們到/var/mysqldbshell>mysqld

13、ump -tab=/var/mysqldb/ db其效果是在目錄/var/mysqldb中生成4個文件，分別是tbl1.txt、tbl1.sql、tbl2.txt和tbl2.sql。2、mysqldump實用程序時的身份驗證的問題 同其他客戶機一樣，你也必須提供一個MySQL數(shù)據(jù)庫帳號用來導(dǎo)出數(shù)據(jù)庫，如果你不是使用匿名用戶的話，可能需要手工提供參數(shù)或者使用選項文件：如果這樣：shell>mysql -u root pmypass db_name>db_name.sql或者這樣在選項文件中提供參數(shù)：mysqldumpuser=rootpassword=mypass然后執(zhí)行shell&

14、gt;mysqldump db_name>db_name.sql那么一切順利，不會有任何問題，但要注意命令歷史會泄漏密碼，或者不能讓任何除你之外的用戶能夠訪問選項文件，由于數(shù)據(jù)庫服務(wù)器也需要這個選項文件時，選項文件只能被啟動服務(wù)器的用戶（如，mysql）擁有和訪問，以免泄密。在Unix下你還有一個解決辦法，可以在自己的用戶目錄中提供個人選項文件（/f）,例如，/home/some_user/f，然后把上面的內(nèi)容加入文件中，注意防止泄密。在NT系統(tǒng)中，你可以簡單的讓c:f能被指定的用戶訪問。你可能要問，為什么這么麻煩呢，例如，這樣使用命令行：shell>mysql -u root p

15、 db_name>db_name.sql或者在選項文件中加入mysqldumpuser=rootpassword然后執(zhí)行命令行：shell>mysql db_name>db_name.sql你發(fā)現(xiàn)了什么？往常熟悉的Enter password:提示并沒有出現(xiàn)，因為標(biāo)準(zhǔn)輸出被重定向到文件db_name.sql中了，所以看不到往常的提示符，程序在等待你輸入密碼。在重定向的情況下，再使用交互模式，就會有問題。在上面的情況下，你還可以直接輸入密碼。然后在文件db_name.sql文件的第一行看到:Enter password:#.你可能說問題不大，但是mysqldump之所以把結(jié)果輸

16、出到標(biāo)準(zhǔn)輸出，是為了重定向到其它程序的標(biāo)準(zhǔn)輸入，這樣有利于編寫腳本。例如：用來自于一個數(shù)據(jù)庫的信息充實另外一個MySQL數(shù)據(jù)庫也是有用的： shell>mysqldump -opt database | mysql -host=remote-host -C database如果mysqldump仍運行在提示輸入密碼的交互模式下，該命令不會成功，但是如果mysql是否運行在提示輸入密碼的交互模式下，都是可以的。如果在選項文件中的client或者mysqldump任何一段中指定了password選項，且不提供密碼，即使，在另一段中有提供密碼的選項password=mypass,例如clien

17、tuser=rootpasswordmysqldumpuser=adminpassword=mypass那么mysqldump一定要你輸入admin用戶的密碼：mysql>mysqldump db_name即使是這樣使用命令行：mysql>mysqldump u root ppass1 db也是這樣，不過要如果-u指定的用戶的密碼。其它使用選項文件的客戶程序也是這樣3、有關(guān)生成SQL語句的優(yōu)化控制-add-locks 生成的SQL 語句中，在每個表數(shù)據(jù)恢復(fù)之前增加LOCK TABLES并且之后UNLOCK TABLE。(為了使得更快地插入到MySQL)。 -add-drop-tab

18、le 生成的SQL 語句中，在每個create語句之前增加一個drop table。 -e, -extended-insert 使用全新多行INSERT語法。（給出更緊縮并且更快的插入語句） 下面兩個選項能夠加快備份表的速度：-l, -lock-tables. 為開始導(dǎo)出數(shù)據(jù)前，讀鎖定所有涉及的表。-q, -quick 不緩沖查詢，直接傾倒至stdout。理論上，備份時你應(yīng)該指定上訴所有選項。這樣會使命令行過于復(fù)雜，作為代替，你可以簡單的指定一個-opt選項，它會使上述所有選項有效。例如，你將導(dǎo)出一個很大的數(shù)據(jù)庫：shell> mysqldump -opt db_name > db

19、_name.txt當(dāng)然，使用-tab選項時，由于不生成恢復(fù)數(shù)據(jù)的SQL語句，使用-opt時，只會加快數(shù)據(jù)導(dǎo)出。4、恢復(fù)mysqldump備份的數(shù)據(jù)由于備份文件是SQL語句的集合，所以需要在批處理模式下使用客戶機如果你使用mysqldump備份單個數(shù)據(jù)庫或表，即：shell>mysqldump -opt db_name > db_name.sql由于db_name.sql中不包括創(chuàng)建數(shù)據(jù)庫或者選取數(shù)據(jù)庫的語句，你需要指定數(shù)據(jù)庫shell>mysql db2 < db_name.sql如果，你使用-databases或者-all-databases選項，由于導(dǎo)出文件中已經(jīng)包

20、含創(chuàng)建和選用數(shù)據(jù)庫的語句，可以直接使用，不比指定數(shù)據(jù)庫，例如：shell>mysqldump -databases db_name > db_name.sqlshell>mysql 如果你使用-tab選項備份數(shù)據(jù)，數(shù)據(jù)恢復(fù)可能效率會高些例如，備份數(shù)據(jù)庫db_name后在恢復(fù)：shell>mysqldump -tab=/path/to/dir -opt test如果要恢復(fù)表的結(jié)構(gòu)，可以這樣：shell>mysql < /path/to/dir/tbl1.sql如果要恢復(fù)數(shù)據(jù)，可以這樣shell>mysqlimport -l db /path/to/dir/tbl1.txt如果是在Unix平臺下使用（推薦），就更方便了：shell>ls -l *.sql