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

第10章數(shù)據(jù)庫(kù)備份與恢復(fù)本章介紹數(shù)據(jù)庫(kù)備份與恢復(fù)的基本概念以及MySQL提供的數(shù)據(jù)備份與數(shù)據(jù)恢復(fù)方法。數(shù)據(jù)庫(kù)系統(tǒng)原理及應(yīng)用10.1基本概念10.2邏輯備份與恢復(fù)10.4NavicatPremium操作主要內(nèi)容10.3表的導(dǎo)出與導(dǎo)入基本概念10.110.1基本概念數(shù)據(jù)庫(kù)故障的種類：1）操作系統(tǒng)崩潰；2）電源故障；3）文件系統(tǒng)崩潰；4）存儲(chǔ)介質(zhì)故障；5）服務(wù)器癱瘓；6）用戶誤操作；7）病毒破壞；8）自然災(zāi)害備份是指定期或不定期地對(duì)數(shù)據(jù)庫(kù)數(shù)據(jù)進(jìn)行復(fù)制?？梢詮?fù)制到本地機(jī)器上，也可以復(fù)制到其它介質(zhì)上。是保證系統(tǒng)安全的一項(xiàng)重要措施。備份就是為了最大限度地降低災(zāi)難性數(shù)據(jù)丟失的風(fēng)險(xiǎn)，從數(shù)據(jù)庫(kù)中定期保存用戶對(duì)數(shù)據(jù)庫(kù)所做的修改，用以將數(shù)據(jù)庫(kù)從錯(cuò)誤狀態(tài)下恢復(fù)到某一正確狀態(tài)的副本。使用數(shù)據(jù)管理管理軟件，比如NavicatPremium就提供了一套功能強(qiáng)大的、安全的數(shù)據(jù)備份和恢復(fù)工具，數(shù)據(jù)庫(kù)的備份和恢復(fù)可以在系統(tǒng)發(fā)生錯(cuò)誤的時(shí)候，搶救恢復(fù)以前的數(shù)據(jù)。10.1.1備份類型按數(shù)據(jù)庫(kù)備份的方法劃分：邏輯備份邏輯備份是指將數(shù)據(jù)庫(kù)中的數(shù)據(jù)按照預(yù)定義的邏輯格式，生成一組CREATEDATABASE、CREATETABLE等定義數(shù)據(jù)庫(kù)結(jié)構(gòu)的語(yǔ)句和INSERT、定界文本文件等定義數(shù)據(jù)庫(kù)內(nèi)容的語(yǔ)句。物理備份：物理備份是指?jìng)浞輹r(shí)直接復(fù)制數(shù)據(jù)庫(kù)的數(shù)據(jù)文件。根據(jù)備份時(shí)數(shù)據(jù)服務(wù)是否在線可以把物理備份分為冷備份、熱備份和溫備份。冷備份：指在關(guān)閉MySQL服務(wù)器、停用數(shù)據(jù)庫(kù)的讀寫操作下所做的備份。熱備份：指在停用數(shù)據(jù)庫(kù)的寫入操作，但不停用讀操作的情況下進(jìn)行備份。溫備份：指在不停用數(shù)據(jù)庫(kù)所提供的數(shù)據(jù)服務(wù)的讀寫操作下所做的備份。10.1.1備份類型根據(jù)備份的數(shù)據(jù)集的范圍可以劃分全量備份：又稱為完全備份、完整備份、全備，指對(duì)數(shù)據(jù)庫(kù)中的全部信息進(jìn)行備份，包括數(shù)據(jù)庫(kù)的數(shù)據(jù)文件、日志文件、數(shù)據(jù)庫(kù)對(duì)象以及其它相關(guān)信息。特點(diǎn)：消耗時(shí)間和資源增量備份：備份從最近的一次備份之后對(duì)數(shù)據(jù)所作的更新。優(yōu)點(diǎn)：速度快，使備份一個(gè)數(shù)據(jù)庫(kù)需要的時(shí)間最小，因?yàn)樗獋浞莸臄?shù)據(jù)量比完全備份小得多。通過(guò)增加差異備份的備份次數(shù)，可以降低丟失數(shù)據(jù)的風(fēng)險(xiǎn)。在MySQL中，第一次增量備份是基于全量備份的，之后的增量備份是基于最近一次的備份（可能是全量備份，也可能是增量備份）10.1.1備份類型事務(wù)日志備份：事務(wù)日志備份是對(duì)數(shù)據(jù)庫(kù)發(fā)生的事務(wù)進(jìn)行備份，它可以在相應(yīng)的數(shù)據(jù)庫(kù)備份的基礎(chǔ)上，盡可能的恢復(fù)最新的數(shù)據(jù)庫(kù)記錄。由于它僅對(duì)數(shù)據(jù)庫(kù)事務(wù)日志進(jìn)行備份，所以其需要的磁盤空間和備份時(shí)間都比數(shù)據(jù)庫(kù)備份少得多。執(zhí)行事務(wù)日志備份主要有兩個(gè)原因：1）是要在一個(gè)安全的介質(zhì)上存儲(chǔ)自上次事務(wù)日志備份或數(shù)據(jù)庫(kù)備份以來(lái)修改的數(shù)據(jù)；2）是要合適地關(guān)閉事務(wù)日志到它的活動(dòng)部分的開始。10.1.2備份和恢復(fù)策略完整備份包括對(duì)數(shù)據(jù)和日志的備份特點(diǎn):適合數(shù)據(jù)庫(kù)數(shù)據(jù)量不是很大,數(shù)據(jù)更改不頻繁比如在每天0:00進(jìn)行一次完整備份的策略，如圖所示10.1.2備份和恢復(fù)策略完整備份加日志備份用戶不允許丟失太多數(shù)據(jù)，而且又不希望經(jīng)常進(jìn)行完整備份，這時(shí)可以在完整備份中間加入若干次日志備份。

比如如圖所示，每天0:00進(jìn)行一次完整備份，再間隔幾小時(shí)進(jìn)行一次日志備份10.1.2備份和恢復(fù)策略完整備份加增量備份再加日志備份當(dāng)用戶希望將進(jìn)行完整備份的時(shí)間間隔再加大，如果還采用完整備份加日志備份的方法恢復(fù)起來(lái)比較耗時(shí)。在此種情況下可取完整備份加增量備份再加日志備份的策略比如每周的周日進(jìn)行一次完整備份。這種策略的優(yōu)勢(shì)是備份和恢復(fù)的速度都比較快，而且當(dāng)系統(tǒng)出現(xiàn)故障時(shí)，丟失的數(shù)據(jù)也相對(duì)較少。10.1.2備份和恢復(fù)策略恢復(fù)數(shù)據(jù)庫(kù)時(shí)的順序：一般先恢復(fù)最近的完全備份然后按增量備份的先后順序恢復(fù)從最近的完全備份之后的所有增量備份。最后恢復(fù)日志備份邏輯備份與恢復(fù)10.210.2邏輯備份與恢復(fù)MySQL自帶的mysqldump工具、多線程備份工具mydumper，以及SELECT...INTOOUTFILE語(yǔ)句都可以實(shí)現(xiàn)邏輯備份。要恢復(fù)邏輯備份，可以使用MySQL客戶端處理SQL格式的備份文件、與mydumper配套的myloader工具，或者使用LOADDATAINFILE語(yǔ)句加載帶分隔符的文本格式的備份文件。10.2.1用mysqldump工具進(jìn)行邏輯備份mysqldump是MySQL自帶的數(shù)據(jù)庫(kù)邏輯備份工具,適用于所有的存儲(chǔ)引擎,支持溫備份，對(duì)于InnoDB存儲(chǔ)引擎支持熱備份。使用mysqldump可以備份一個(gè)數(shù)據(jù)庫(kù)，也可以備份多個(gè)數(shù)據(jù)庫(kù)或者備份所有數(shù)據(jù)庫(kù)。生成的備份文件可以是SQL格式的文件，也可以是帶分隔符的定界文本格式的文件，還可以是XML格式的文件。通常情況下，mysqldump是備份成后綴名為.sql的SQL格式的文件。mysqldump需要使用shell腳本。根據(jù)要備份的數(shù)據(jù)庫(kù)對(duì)象的不同，mysqldump語(yǔ)句有以下三種格式：（1）備份一個(gè)數(shù)據(jù)庫(kù)（2）備份多個(gè)數(shù)據(jù)庫(kù)（3）備份所有數(shù)據(jù)庫(kù)10.2.1用mysqldump工具進(jìn)行邏輯備份（1）備份一個(gè)數(shù)據(jù)庫(kù)語(yǔ)句的基本語(yǔ)法格式為：shell>mysqldump-u[uname]-p[pass]dbtb1tb2>dump.sql參數(shù)：（1）-u[uname]-p[pass]：連接MySQL的用戶名和密碼。在shell腳本中輸入密碼時(shí)屏幕上不顯示任何信息，不會(huì)出現(xiàn)“*”或其他符號(hào)，只要密碼輸入正確就可以了。（2）db：需要備份的表所在的數(shù)據(jù)庫(kù)的名稱。（3）tb1tb2：需要備份的表的名稱。沒有該參數(shù)時(shí)將備份整個(gè)數(shù)據(jù)庫(kù)。（4）dump.sql：SQL格式的備份文件的名稱。文件名前可加上絕對(duì)路徑。10.2.1用mysqldump工具進(jìn)行邏輯備份[例10-1]：以root用戶身份備份校園卡管理數(shù)據(jù)庫(kù)cardmanagement中的salebill表，備份文件名保存在指定目錄(此處以筆者的電腦桌面文件夾示例，讀者根據(jù)實(shí)際情況設(shè)定目錄)，備份文件名稱為salebill.sql。打開終端(Terminal)，輸入如下語(yǔ)句：mysqldump-uroot-pcardmanagementsalebill>'/Users/zqf/Desktop/salebill.sql'10.2.1用mysqldump工具進(jìn)行邏輯備份2）備份多個(gè)數(shù)據(jù)庫(kù)mysqldump語(yǔ)句的--databases選項(xiàng)用于備份多個(gè)數(shù)據(jù)庫(kù)，其基本的語(yǔ)法格式為：shell>mysqldump--databasesdb1db2db3>dump.sql參數(shù)：--databasesdb1db2db3：--databases后的所有名稱都被視為數(shù)據(jù)庫(kù)名稱。只備份一個(gè)數(shù)據(jù)庫(kù)的情況下，可以省略--databases選項(xiàng)。省略--databases的情況下，備份文件中不包含CREATEDATABASE和USE語(yǔ)句。使用--databases選項(xiàng)時(shí)，mysqldump在每個(gè)數(shù)據(jù)庫(kù)的備份文件之前寫入CREATEDATABASE和USE語(yǔ)句，以確保在重新加載備份文件時(shí)，如果不存在數(shù)據(jù)庫(kù)，將先創(chuàng)建數(shù)據(jù)庫(kù)并將其設(shè)置為默認(rèn)數(shù)據(jù)庫(kù)，以便將數(shù)據(jù)庫(kù)內(nèi)容加載到備份時(shí)的同名數(shù)據(jù)庫(kù)中。如果使用--add-drop-database選項(xiàng)，mysqldump還在每個(gè)CREATEDATABASE語(yǔ)句之前寫入DROPDATABASE語(yǔ)句，在加載備份文件重新創(chuàng)建數(shù)據(jù)庫(kù)之前強(qiáng)制刪除數(shù)據(jù)庫(kù)。10.2.1用mysqldump工具進(jìn)行邏輯備份[例10-2]：使用root用戶備份cardmanagement數(shù)據(jù)庫(kù)和mysql數(shù)據(jù)庫(kù)，備份文件名為backup.sql。打開終端，輸入如下mysqldump語(yǔ)句：mysqldump-uroot-p--databasescardmanagementmysql>'/Users/zqf/Desktop/backup.sql'10.2.1用mysqldump工具進(jìn)行邏輯備份3）備份所有數(shù)據(jù)庫(kù)mysqldump語(yǔ)句的--all-databases選項(xiàng)用于備份所有數(shù)據(jù)庫(kù)，其基本的語(yǔ)法格式為：shell>mysqldump[--all-databases]>dump.sql參數(shù)：[--all-databases]：省略該選項(xiàng)的情況下，備份文件中不包含CREATEDATABASE和USE語(yǔ)句。例10-3：使用root用戶備份所有數(shù)據(jù)庫(kù)，備份文件名為all.sql。mysqldump-u-root-p--all-databases>'/Users/zqf/Desktop/all.sql'10.2.2加載SQL格式的備份文件恢復(fù)數(shù)據(jù)庫(kù)1）用mysql語(yǔ)句恢復(fù)數(shù)據(jù)庫(kù)使用mysql語(yǔ)句直接在終端加載備份文件，其基本的語(yǔ)法格式為：shell>mysql-u[uname]-p[pass]<dump.sql[例10-4]：恢復(fù)cardmanagement數(shù)據(jù)庫(kù)中的salebill表。在終端輸入命令：mysql-uroot-pcardmanagement</Users/zqf/Desktop/salebill.sql按照提示輸入連接數(shù)據(jù)庫(kù)的密碼，輸入密碼后將完成數(shù)據(jù)恢復(fù)。10.2.2加載SQL格式的備份文件恢復(fù)數(shù)據(jù)庫(kù)2）用source語(yǔ)句恢復(fù)數(shù)據(jù)庫(kù)在MySQL內(nèi)部使用source語(yǔ)句加載備份文件，其基本的操作步驟是：（1）打開終端后，先連接MySQL?；镜恼Z(yǔ)法格式為：mysql-u[uname]-p[pass]（2）如果要恢復(fù)的數(shù)據(jù)庫(kù)不存在，需要先創(chuàng)建同名數(shù)據(jù)庫(kù)，并切換為當(dāng)前數(shù)據(jù)庫(kù)。基本的語(yǔ)法格式為：CREATEDATABASEdb;USEdb;（3）使用source語(yǔ)句加載備份文件?；镜恼Z(yǔ)法格式為：sourcedump.sql10.2.2加載SQL格式的備份文件恢復(fù)數(shù)據(jù)庫(kù)[例10-5]：恢復(fù)cardmanagement數(shù)據(jù)庫(kù)中的salebill表。（1）打開終端，按照提示輸入連接數(shù)據(jù)庫(kù)的密碼，進(jìn)入MySQL環(huán)境:

mysql-uroot-p（2）在MySQL環(huán)境下，把cardmanagement數(shù)據(jù)庫(kù)切換為當(dāng)前數(shù)據(jù)庫(kù)：

usecardmanagement（3）恢復(fù)salebill表：

source/Users/zqf/Desktop/salebill.sql[例10-6]：重新加載backup.sql，恢復(fù)cardmanagement數(shù)據(jù)庫(kù)和mysql數(shù)據(jù)庫(kù)。shell>mysql-uroot-p<backup.sql或者在MySQL環(huán)境下，執(zhí)行sourcebackup.sql表的導(dǎo)出與導(dǎo)入10.310.3.1用SELECT......INTOOUTFILE語(yǔ)句導(dǎo)出文本文件SELECT......INTOOUTFILE語(yǔ)句可用于將表的內(nèi)容導(dǎo)出為一個(gè)文本文件轉(zhuǎn)儲(chǔ)到服務(wù)器上，并且導(dǎo)出文件不能已存在。其基本的語(yǔ)法格式為：SELECTselect_expr[,select_expr]...INTOOUTFILE'file_name'[FIELDSTERMINATEDBY'string'][FIELDSENCLOSEDBY'char'][FIELDSESCAPEDBY'char'][LINESSTARTINGBY'string'][LINESTERMINATEDBY'string']10.3.1用SELECT......INTOOUTFILE語(yǔ)句導(dǎo)出文本文件參數(shù)：（1）SELECT子句：查詢需要備份的數(shù)據(jù)。（2）file_name：存放輸出數(shù)據(jù)的文件名。（3）FIELDSTERMINATEDBY'string'：設(shè)置字段之間的分隔符，可以為單個(gè)或多個(gè)字符。默認(rèn)值是“\t”。（4）FIELDSENCLOSEDBY'char'：設(shè)置字符來(lái)括住字段的值，只能為單個(gè)字符。默認(rèn)情況下不使用任何符號(hào)。（5）FIELDSESCAPEDBY'char'：設(shè)置轉(zhuǎn)義字符，只能為單個(gè)字符。默認(rèn)值為“\”。（6）LINESSTARTINGBY'string'：設(shè)置每行數(shù)據(jù)開頭的字符，可以為單個(gè)或多個(gè)字符。默認(rèn)情況下不使用任何字符。（7）LINESTERMINATEDBY'string'：設(shè)置每行數(shù)據(jù)結(jié)尾的字符，可以為單個(gè)或多個(gè)字符。默認(rèn)值是“\n”。（8）FIELDS和LINES兩個(gè)子句都是自選的，但是如果兩個(gè)子句都被指定了，F(xiàn)IELDS必須位于LINES的前面。10.3.1用SELECT......INTOOUTFILE語(yǔ)句導(dǎo)出文本文件[例10-7]：把student表的數(shù)據(jù)備份到桌面的student.txt文件，字段之間以逗號(hào)分隔，每個(gè)字段用雙引號(hào)括住。SELECT*FROMstudentINTOOUTFILE'/usr/local/mysql/data/student.txt'FIELDSTERMINATEDBY","ENCLOSEDBY'"';該語(yǔ)句的執(zhí)行結(jié)果如圖所示。10.3.2用LOADDATAINFILE語(yǔ)句將文本文件導(dǎo)入數(shù)據(jù)表LOADDATAINFILE語(yǔ)句可用于將SELECT......INTOOUTFILE語(yǔ)句導(dǎo)出的文件再導(dǎo)入到數(shù)據(jù)庫(kù)中，實(shí)現(xiàn)數(shù)據(jù)庫(kù)的恢復(fù)。其基本的語(yǔ)法格式為：LOADDATAINFILE'file_name'INTOTABLEtbl_name[FIELDSTERMINATEDBY'string'][FIELDSENCLOSEDBY'char'][FIELDSESCAPEDBY'char'][LINESSTARTINGBY'string'][LINESTERMINATEDBY'string']10.3.2用LOADDATAINFILE語(yǔ)句將文本文件導(dǎo)入數(shù)據(jù)表[例10-8]：使用DELETE語(yǔ)句刪除student表中的全部數(shù)據(jù)，模擬表中的數(shù)據(jù)被破壞，再使用備份文件student.txt文件恢復(fù)student表。DELETEFROMstudent;LOADDATAINFILE'/usr/local/mysql/data/student.txt'INTOTABLEstudentFIELDSTERMINATEDBY','ENCLOSEDBY'"';NavicatPremium操作10.410.4.1備份與恢復(fù)數(shù)據(jù)庫(kù)1）備份數(shù)據(jù)庫(kù)“備份”——“新建備份”。在對(duì)話框的常規(guī)屬性選項(xiàng)卡中查看主機(jī)、模式的信息。如有需要，可為備份文件輸入一個(gè)注釋。10.4.1備份與恢復(fù)數(shù)據(jù)庫(kù)在對(duì)象選擇選項(xiàng)卡中選擇需要備份的表、視圖、函數(shù)、事件等數(shù)據(jù)庫(kù)對(duì)象。系統(tǒng)只備份已勾選的數(shù)據(jù)庫(kù)對(duì)象?！案呒?jí)”選項(xiàng)卡的選項(xiàng)會(huì)根據(jù)連接的服務(wù)器類型而有所不同。1）備份數(shù)據(jù)庫(kù)10.4.1備份與恢復(fù)數(shù)據(jù)庫(kù)點(diǎn)擊“開始”按鈕，“信息日志”選項(xiàng)卡會(huì)顯示備份過(guò)程。備份完成后，可以看到生成的備份文件。1）備份數(shù)據(jù)庫(kù)10.4.1備份與恢復(fù)數(shù)據(jù)庫(kù)2）恢復(fù)數(shù)據(jù)庫(kù)在主窗口中，打開一個(gè)數(shù)據(jù)庫(kù)或模式，刪除數(shù)據(jù)庫(kù)或刪除數(shù)據(jù)庫(kù)中的某些數(shù)據(jù)庫(kù)對(duì)象，模擬數(shù)據(jù)庫(kù)已損壞。如果數(shù)據(jù)庫(kù)被刪除，在恢復(fù)前需要重建同名數(shù)據(jù)庫(kù)。選中需要恢復(fù)的數(shù)據(jù)庫(kù)下，點(diǎn)擊“備份”并選擇一個(gè)已有的備份文件。10.4.1備份與恢復(fù)數(shù)據(jù)庫(kù)在“常規(guī)”選項(xiàng)卡中查看備份文件基本信息。2）恢復(fù)數(shù)據(jù)庫(kù)在“對(duì)象選擇”選項(xiàng)卡選擇要恢復(fù)的數(shù)據(jù)庫(kù)對(duì)象。10.4.1備份與恢復(fù)數(shù)據(jù)庫(kù)在“高級(jí)”選項(xiàng)卡進(jìn)行恢復(fù)設(shè)置，并點(diǎn)擊“開始”。2）恢復(fù)數(shù)據(jù)庫(kù)在“信息日志”選項(xiàng)卡中可以看到恢復(fù)過(guò)程，然后點(diǎn)擊“關(guān)閉”即可。10.4.2導(dǎo)出與導(dǎo)入數(shù)據(jù)在主界面選擇要導(dǎo)出的數(shù)據(jù)對(duì)象，點(diǎn)擊右鍵，在彈出式菜單中選擇“導(dǎo)出向?qū)А?，或者在?duì)象控鍵欄中選擇“導(dǎo)出向?qū)А薄?）數(shù)據(jù)導(dǎo)出在“選擇文件格式”選項(xiàng)卡中為目標(biāo)文件選擇一種導(dǎo)出格式。10.4.2導(dǎo)出與導(dǎo)入數(shù)據(jù)在“選擇保存路徑”選項(xiàng)卡中可以設(shè)置保存路徑和導(dǎo)出文件名。1）數(shù)據(jù)導(dǎo)出在“選擇表字段”選項(xiàng)卡中可以選擇要導(dǎo)出的字段。10.4.2導(dǎo)出與導(dǎo)入數(shù)據(jù)在“選擇附加的選項(xiàng)”選項(xiàng)卡中進(jìn)行導(dǎo)出設(shè)置。選項(xiàng)會(huì)根據(jù)在第一步選擇的文件格式而有所不同。1）數(shù)據(jù)導(dǎo)出在“開始導(dǎo)出”選項(xiàng)卡中點(diǎn)擊