第13章 備份和恢復(fù)

1第13章備份和恢復(fù)13.1備份和恢復(fù)概述13.2備份數(shù)據(jù)13.3恢復(fù)數(shù)據(jù)數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）213.1備份和恢復(fù)概述數(shù)據(jù)庫中的數(shù)據(jù)丟失或被破壞可能是由以下原因造成：（1）計算機硬件故障。由于使用不當或產(chǎn)品質(zhì)量等原因，計算機硬件可能會出現(xiàn)故障，不能使用。（2）軟件故障。由于軟件設(shè)計上的失誤或用戶使用的不當，軟件系統(tǒng)可能會誤操作數(shù)據(jù)引起數(shù)據(jù)破壞。（3）病毒。破壞性病毒會破壞系統(tǒng)軟件、硬件和數(shù)據(jù)。（4）誤操作。如用戶錯誤使用了例如DELETE、UPDATE等命令而引起數(shù)據(jù)丟失或破壞；錯誤使用DROPDATABASE或DROPTABLE的語句，會讓數(shù)據(jù)庫或數(shù)據(jù)表中數(shù)據(jù)被清除；又如DELETE*FROMtable_name語句，可以清空數(shù)據(jù)表。這樣的錯誤很容易發(fā)生。數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）3（5）自然災(zāi)害。如火災(zāi)、洪水或地震等，它們會造成極大的破壞，會毀壞計算機系統(tǒng)及其數(shù)據(jù)。（6）盜竊。一些重要數(shù)據(jù)可能會被盜竊。面對上述情況，數(shù)據(jù)庫系統(tǒng)提供了備份和恢復(fù)策略來保證數(shù)據(jù)庫中的數(shù)據(jù)的可靠性和完整性。數(shù)據(jù)庫備份是通過導(dǎo)出數(shù)據(jù)或復(fù)制表文件等方式制作數(shù)據(jù)庫的副本。數(shù)據(jù)庫恢復(fù)是當數(shù)據(jù)庫出現(xiàn)故障或受到破壞時，將數(shù)據(jù)庫備份加載到系統(tǒng)，從而使數(shù)據(jù)庫從錯誤狀態(tài)恢復(fù)到備份時的正確狀態(tài)。數(shù)據(jù)庫的恢復(fù)以備份為基礎(chǔ)，它是與備份相對應(yīng)的系統(tǒng)維護和管理工作。13.1備份和恢復(fù)概述數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）413.2備份數(shù)據(jù)13.2.1表數(shù)據(jù)導(dǎo)出使用SELECT...INTOOUTFILE語句可導(dǎo)出表數(shù)據(jù)的文本文件。并可使用LOADDATAINFILE語句恢復(fù)先前導(dǎo)出表的數(shù)據(jù)。但只能導(dǎo)出或?qū)氡淼臄?shù)據(jù)內(nèi)容，不包括表結(jié)構(gòu)。

語法格式：

SELECTcolumnistFROMtableWHEREconditionINTOOUTFILE‘filename’[OPTIONS]

其中.OPTIONS:FIELDSTERMINATEDBY'value'FIELDS[OPTIONALLY]ENCLOSEDBY'value'FIELDSESCAPEDBY'value'LINESSTARTINGBY'value'LINESTERMINATEDBY'value'數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）5說明：

（1）filename：指定導(dǎo)出文件名（2）在OPTIONS中可加入以下兩個自選的子句，它們的作用是決定數(shù)據(jù)行在文件中存放的格式：●

FIELDS子句：在FIELDS子句中有三個亞子句：TERMINATEDBY、[OPTIONALLY]ENCLOSEDBY和ESCAPEDBY。如果指定了FIELDS子句，則這三個亞子句中至少要指定一個。TERMINATEDBY用來指定字段值之間的符號，例如，“TERMINATEDBY','”指定了逗號作為兩個字段值之間的標志。ENCLOSEDBY子句用來指定包裹文件中字符值的符號，例如，“ENCLOSEDBY'"'”表示文件中字符值放在雙引號之間，若加上關(guān)鍵字OPTIONALLY表示所有的值都放在雙引號之間。13.2備份數(shù)據(jù)數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）6ESCAPEDBY子句用來指定轉(zhuǎn)義字符，例如，“ESCAPEDBY'*'”將“*”指定為轉(zhuǎn)義字符，取代“\”，如空格將表示為“*N”?！馤INES子句：在LINES子句中使用TERMINATEDBY指定一行結(jié)束的標志，如“LINESTERMINATEDBY'?'”表示一行以“?”作為結(jié)束標志。如果FIELDS和LINES子句都不指定，則默認聲明以下子句：

FIELDSTERMINATEDBY'\t'ENCLOSEDBY''ESCAPEDBY'\\'LINESTERMINATEDBY'\n'

MySQL對使用SELECT...INTOOUTFILE語句和LOADDATAINFILE語句進行導(dǎo)出和導(dǎo)入的目錄有權(quán)限限制，需要對指定目錄進行操作，指定目錄為：C:/ProgramData/MySQL/MySQLServer8.0/Uploads/。13.2備份數(shù)據(jù)數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）7【例13.1】備份stusys數(shù)據(jù)庫中的course表中數(shù)據(jù)，要求字段值如果是字符就用雙引號標注，字段值之間用逗號隔開，每行以問號為結(jié)束標志。

mysql>SELECT*FROMcourse->INTOOUTFILE'C:/ProgramData/MySQL/MySQLServer8.0/Uploads/course.txt'->FIELDSTERMINATEDBY','->OPTIONALLYENCLOSEDBY'"'->LINESTERMINATEDBY'?';QueryOK,5rowsaffected(0.07sec)

導(dǎo)出成功后，course.txt文件內(nèi)容如圖13.1所示。圖13.1備份數(shù)據(jù)文件的內(nèi)容13.2備份數(shù)據(jù)數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）813.2.2使用mysqldump命令備份數(shù)據(jù)

MySQL提供了很多客戶端程序和實用工具，MySQL目錄下的bin子目錄存儲這些客戶端程序，mysqldump命令是其中之一。使用客戶端程序方法為：（1）單擊“開始”菜單，在“搜索程序和文件”框中輸入“cmd”命令，按“Enter”鍵，進入DOS窗口。（2）輸入“cdC:\ProgramFiles\MySQL\MySQLServer8.0\bin”命令，按“Enter”鍵，進入安裝MySQL的bin目錄。進入MySQL客戶端實用程序運行界面如圖13.2所示。圖13.2

MySQL客戶端實用程序運行界面13.2備份數(shù)據(jù)數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）9mysqldump命令可將數(shù)據(jù)庫的數(shù)據(jù)備份成一個文本文件，其工作原理為首先查出要備份的表的結(jié)構(gòu)，在文本文件中生成一個CREATE語句；然后將表中的記錄轉(zhuǎn)換成INSERT語句。以后在恢復(fù)數(shù)據(jù)時，將使用這些CREATE語句和INSERT語句。mysqldump命令可用于備份表、備份數(shù)據(jù)庫和備份整個數(shù)據(jù)庫系統(tǒng)，下面分別介紹。1.備份表使用mysqldump命令可備份一個數(shù)據(jù)庫的一個表或多個表。

語法格式：

mysqldump-uusername-pdbnametable1table2...>filename.sql

說明：●dbname：指定數(shù)據(jù)庫名稱。

13.2備份數(shù)據(jù)數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）10●

table1table2...：指定一個表或多個表的名稱?！?/p>

filename.sql：備份文件的名稱，文件名前可加上一個絕對路徑，通常備份成后綴名為sql的文件。

【例13.2】使用mysqldump備份stusys數(shù)據(jù)庫的course表到D盤mysqlbak目錄下。操作前先在Windows中創(chuàng)建目錄D:\mysqlbak

mysqldump-uroot-pstusyscourse>D:\mysqlbak\course.sql

使用mysqldump備份course表如圖13.3所示。圖13.3使用mysqldump備份course表13.2備份數(shù)據(jù)數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）11查看course.sql文本文件，其內(nèi)容包括創(chuàng)建course表的CREATE語句和插入數(shù)據(jù)的INSERT語句：

--MySQLdump10.13Distrib8.0.18,forWin64(x86_64)----Host:localhostDatabase:stusys----------------------------------------------------------Serverversion 8.0.18

/*!40101SET@OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT*/;/*!40101SET@OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS*/;/*!40101SET@OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION*/;/*!50503SETNAMESutf8mb4*/;/*!40103SET@OLD_TIME_ZONE=@@TIME_ZONE*/;13.2備份數(shù)據(jù)數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）12/*!40103SETTIME_ZONE='+00:00'*/;/*!40014SET@OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS,UNIQUE_CHECKS=0*/;/*!40014SET@OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS,FOREIGN_KEY_CHECKS=0*/;/*!40101SET@OLD_SQL_MODE=@@SQL_MODE,SQL_MODE='NO_AUTO_VALUE_ON_ZERO'*/;/*!40111SET@OLD_SQL_NOTES=@@SQL_NOTES,SQL_NOTES=0*/;

----Tablestructurefortable`course`--

DROPTABLEIFEXISTS`course`;/*!40101SET@saved_cs_client=@@character_set_client*/;/*!50503SETcharacter_set_client=utf8mb4*/;13.2備份數(shù)據(jù)數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）13CREATETABLE`course`(`cno`char(4)NOTNULL,`cname`char(16)NOTNULL,`credit`tinyint(4)DEFAULTNULL,PRIMARYKEY(`cno`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ci;/*!40101SETcharacter_set_client=@saved_cs_client*/;

----Dumpingdatafortable`course`--

LOCKTABLES`course`WRITE;/*!40000ALTERTABLE`course`DISABLEKEYS*/;INSERTINTO`course`VALUES('1004','數(shù)據(jù)庫系統(tǒng)',4),('1017','操作系統(tǒng)',3),('1201','英語',4),('4002','數(shù)字電路',3),('8001','高等數(shù)學(xué)',4);13.2備份數(shù)據(jù)數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）14/*!40000ALTERTABLE`course`ENABLEKEYS*/;UNLOCKTABLES;/*!40103SETTIME_ZONE=@OLD_TIME_ZONE*/;

/*!40101SETSQL_MODE=@OLD_SQL_MODE*/;/*!40014SETFOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS*/;/*!40014SETUNIQUE_CHECKS=@OLD_UNIQUE_CHECKS*/;/*!40101SETCHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT*/;/*!40101SETCHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS*/;/*!40101SETCOLLATION_CONNECTION=@OLD_COLLATION_CONNECTION*/;/*!40111SETSQL_NOTES=@OLD_SQL_NOTES*/;

--Dumpcompletedon2020-05-2821:21:0413.2備份數(shù)據(jù)數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）152.備份數(shù)據(jù)庫使用mysqldump命令可備份一個數(shù)據(jù)庫或多個數(shù)據(jù)庫。（1）備份一個數(shù)據(jù)庫

語法格式：

mysqldump-uusername-pdbname>filename.sql

說明：●dbname：指定數(shù)據(jù)庫名稱?！?/p>

filename.sql：備份文件的名稱，文件名前可加上一個絕對路徑，通常備份成后綴名為sql的文件。13.2備份數(shù)據(jù)數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）16【例13.3】備份stusys數(shù)據(jù)庫到D盤mysqlbak目錄下。

mysqldump-uroot-pstusys>D:\mysqlbak\stusys.sql

使用mysqldump備份stusys數(shù)據(jù)庫如圖13.4所示。圖13.4使用mysqldump備份stusys數(shù)據(jù)庫（2）備份多個數(shù)據(jù)庫

語法格式：

mysqldump-uusername-p–database[dbname,[dbname...]]>filename.sql13.2備份數(shù)據(jù)數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）17說明：

●

dbname：指定數(shù)據(jù)庫名稱?！?/p>

filename.sql：備份文件的名稱，文件名前可加上一個絕對路徑，通常備份成后綴名為sql的文件。

3.備份整個數(shù)據(jù)庫系統(tǒng)使用mysqldump命令可備份整個數(shù)據(jù)庫系統(tǒng)。

語法格式：

mysqldump-uusername-p–all-database>filename.sql

說明：

●

–all-database：指定整個數(shù)據(jù)庫系統(tǒng)?！?/p>

filename.sql：備份文件的名稱，文件名前可加上一個絕對路徑，通常備份成后綴名為sql的文件。13.2備份數(shù)據(jù)數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）18【例13.4】備份MySQL服務(wù)器上的所有數(shù)據(jù)庫到D盤mysqlbak目錄下。

mysqldump-uroot-p–all-database>D:\mysqlbak\alldata.sql

使用mysqldump備份MySQL服務(wù)器上的所有數(shù)據(jù)庫如圖13.5所示。圖13.5使用mysqldump備份MySQL服務(wù)器上的所有數(shù)據(jù)庫13.2備份數(shù)據(jù)數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）1913.3恢復(fù)數(shù)據(jù)13.3.1表數(shù)據(jù)導(dǎo)入

表數(shù)據(jù)導(dǎo)入可使用LOADDATAINFILE語句。

語法格式：

LOADDATA[LOCAL]INFILEfilenameINTOTABLE‘tablename’[OPTIONS][IGNOREnumberLINES]

其中.OPTIONS:FIELDSTERMINATEDBY'value'FIELDS[OPTIONALLY]ENCLOSEDBY'value'FIELDSESCAPEDBY'value'LINESSTARTINGBY'value'LINESTERMINATEDBY'value'

數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）20說明：

（1）filename：待導(dǎo)入的數(shù)據(jù)備份文件名。（2）tablename：指定需要導(dǎo)入數(shù)據(jù)的表名。（3）在OPTIONS中可加入以下兩個自選的子句，它們的作用是決定數(shù)據(jù)行在文件中存放的格式：●FIELDS子句：在FIELDS子句中有三個亞子句：TERMINATEDBY、[OPTIONALLY]ENCLOSEDBY和ESCAPEDBY。如果指定了FIELDS子句，則這三個亞子句中至少要指定一個。TERMINATEDBY用來指定字段值之間的符號，例如，“TERMINATEDBY','”指定了逗號作為兩個字段值之間的標志。ENCLOSEDBY子句用來指定包裹文件中字符值的符號，例如，“ENCLOSEDBY'"'”表示文件中字符值放在雙引號之間，若加上關(guān)鍵字OPTIONALLY表示所有的值都放在雙引號之間。13.3恢復(fù)數(shù)據(jù)數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）21ESCAPEDBY子句用來指定轉(zhuǎn)義字符，例如，“ESCAPEDBY'*'”將“*”指定為轉(zhuǎn)義字符，取代“\”，如空格將表示為“*N”?！馤INES子句：在LINES子句中使用TERMINATEDBY指定一行結(jié)束的標志，如“LINESTERMINATEDBY'?'”表示一行以“?”作為結(jié)束標志。

【例13.5】刪除stusys數(shù)據(jù)庫中的course表中數(shù)據(jù)后，使用LOADDATAINFILE語句將例13.1備份文件course.txt導(dǎo)入到空表course中。刪除stusys數(shù)據(jù)庫中course表中數(shù)據(jù)。

mysql>DELETEFROMcourse;QueryOK,5rowsaffected(0.09sec)

查詢course表中數(shù)據(jù)，course表為空表。

13.3恢復(fù)數(shù)據(jù)數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）22mysql>SELECT*FROMcourse;Emptyset(0.02sec)

將上例備份后的數(shù)據(jù)導(dǎo)入到空表course中。

mysql>LOADDATAINFILE'C:/ProgramData/MySQL/MySQLServer8.0/Uploads/course.txt'->INTOTABLEcourse->FIELDSTERMINATEDBY','->OPTIONALLYENCLOSEDBY'"'->LINESTERMINATEDBY'?';QueryOK,5rowsaffected(0.10sec)Records:5Deleted:0Skipped:0Warnings:0

查詢course表中數(shù)據(jù)。

mysql>SELECT*