第7章 MySQL數(shù)據(jù)庫備份與恢復(fù)

第7章MySQL數(shù)據(jù)庫備份與恢復(fù)——常用的備份方法MySQL教程（第2版）01使用SQL語句導(dǎo)出或?qū)氡頂?shù)據(jù)1.導(dǎo)出表數(shù)據(jù)2.導(dǎo)入表數(shù)據(jù)使用SQL語句導(dǎo)出或?qū)氡頂?shù)據(jù)1．導(dǎo)出表數(shù)據(jù)導(dǎo)出表數(shù)據(jù)的語法格式如下：SELECT*INTOOUTFILE'文件名1'[FIELDS[TERMINATEDBY'string'][[OPTIONALLY]ENCLOSEDBY'char'][ESCAPEDBY'char']][LINESTERMINATEDBY'string']|DUMPFILE'文件名2'使用SQL語句導(dǎo)出或?qū)氡頂?shù)據(jù)使用OUTFILE時(shí)，可以加入兩個(gè)自選的子句，它們的作用是決定數(shù)據(jù)行在文件中存放的格式。（1）FIELDS子句：需要指定下列3個(gè)中的至少一個(gè)。①TERMINATEDBY：指定列值之間的分隔符，例如，“TERMINATEDBY','”表示指定逗號(hào)作為兩個(gè)列值之間的分隔符。②ENCLOSEDBY：指定標(biāo)示文件中字符值的符號(hào)，例如，“ENCLOSEDBY'"'”表示文件中字符值放在雙引號(hào)之間，若加上關(guān)鍵字OPTIONALLY表示所有的值都放在雙引號(hào)之間。③ESCAPEDBY：指定轉(zhuǎn)義字符，例如，使用“ESCAPEDBY'*'”將“*”指定為轉(zhuǎn)義字符，取代“\”，如空格將表示為“*N”。（2）LINES子句：使用TERMINATEDBY指定一行結(jié)束的標(biāo)志，如“LINESTERMINATEDBY'?'”表示一行以“?”作為結(jié)束標(biāo)志。如果FIELDS子句和LINES子句都沒有指定，則默認(rèn)聲明以下子句：FIELDSTERMINATEDBY'\t'ENCLOSEDBY''ESCAPEDBY'\\'LINESTERMINATEDBY'\n'使用SQL語句導(dǎo)出或?qū)氡頂?shù)據(jù)2．導(dǎo)入表數(shù)據(jù)導(dǎo)入表數(shù)據(jù)的語法格式如下：LOADDATA[LOW_PRIORITY|CONCURRENT][LOCAL]INFILE'文件名.txt’ [REPLACE|IGNORE] INTOTABLE表名 [FIELDS [TERMINATEDBY'string’] [[OPTIONALLY]ENCLOSEDBY'char’] [ESCAPEDBY'char’] ] [LINES [STARTINGBY'string’] [TERMINATEDBY'string’] ] [IGNOREnumberLINES] [(列名或用戶變量,...)] [SET列名=表達(dá)式,...]]使用SQL語句：導(dǎo)出或?qū)氡頂?shù)據(jù)說明：LOW_PRIORITY|CONCURRENT：若指定LOW_PRIORITY，則延遲語句的執(zhí)行。LOCAL：文件會(huì)被客戶端讀取，并被發(fā)送到服務(wù)器。文件名.txt：該文件中保存了待存入數(shù)據(jù)庫的數(shù)據(jù)行，它由SELECTINTO…OUTFILE命令導(dǎo)出產(chǎn)生。表名：該表在數(shù)據(jù)庫中必須存在，表結(jié)構(gòu)必須與導(dǎo)入文件的數(shù)據(jù)行一致。REPLACE|IGNORE：如果指定了REPLACE，則當(dāng)文件中出現(xiàn)與原有行相同的唯一關(guān)鍵字值時(shí)，輸入行會(huì)替換原有行。FIELDS子句：和SELECT..INTOOUTFILE語句類似。LINES子句：TERMINATEDBY指定一行結(jié)束的標(biāo)志。IGNOREnumberLINES：這個(gè)選項(xiàng)可以用于忽略文件的前幾行。列名或用戶變量：如果需要載入一個(gè)表的部分列或文件中字段值順序與表中列的順序不同，就必須指定一個(gè)列清單。SET子句：SET子句可以在導(dǎo)入數(shù)據(jù)時(shí)修改表中列的值。使用SQL語句：導(dǎo)出或?qū)氡頂?shù)據(jù)【例7.1】備份xscj數(shù)據(jù)庫的kc表中數(shù)據(jù)到D盤file目錄中，要求列值如果是字符就用雙引號(hào)標(biāo)示，列值之間用逗號(hào)隔開，每行以“？”為結(jié)束標(biāo)志。最后將備份后的數(shù)據(jù)導(dǎo)入一個(gè)和kc表結(jié)構(gòu)一樣的空表course中。首先導(dǎo)出數(shù)據(jù)（操作前先創(chuàng)建D:\file目錄）：USExscj;SELECT*fromkc INTOoutfile'd:/file/myfile1.txt’ FIELDSTERMINATEDBY',’ OPTIONALLYENCLOSEDBY'“’ LINESTERMINATEDBY'?';導(dǎo)出成功后可以查看D盤file文件夾下的myfile1.txt文件，文件內(nèi)容如圖。文件備份完后可以將文件中的數(shù)據(jù)導(dǎo)入到course表中，使用以下命令：LOADDATAINFILE'd:/file/myfile1.txt’ INTOTABLEcourse FIELDSTERMINATEDBY',’ OPTIONALLYENCLOSEDBY'“’ LINESTERMINATEDBY'?';02使用客戶端程序備份數(shù)據(jù)庫1.使用mysqldump程序備份數(shù)據(jù)2.使用mysqlimport程序恢復(fù)數(shù)據(jù)使用客戶端程序備份數(shù)據(jù)庫使用客戶端程序的方法如下。打開命令行，進(jìn)入bin目錄：cdE:\MySQL57\bin后面介紹的客戶端命令都在此處輸入，如圖。使用客戶端程序備份數(shù)據(jù)庫1．使用mysqldump程序備份數(shù)據(jù)（1）備份表。備份表的命令格式如下：mysqldump[OPTIONS]數(shù)據(jù)庫名[表名…]>備份的文件名同其他客戶端程序一樣，備份數(shù)據(jù)時(shí)需要使用一個(gè)用戶賬號(hào)連接到服務(wù)器，這需要用戶手動(dòng)提供參數(shù)或在選項(xiàng)文件中修改有關(guān)值。參數(shù)格式為：-h[主機(jī)名]-u[用戶名]-p[密碼]其中，-p選項(xiàng)和密碼之間不能有空格?！纠?.2】使用mysqldump程序備份xs表和kc表。mysqldump-hlocalhost-uroot-pnjnu123456xscjxskc>twotables.sql使用客戶端程序備份數(shù)據(jù)庫（2）備份數(shù)據(jù)庫。使用mysqldump程序還可以將一個(gè)或多個(gè)數(shù)據(jù)庫備份到一個(gè)文件中。備份數(shù)據(jù)庫的命令格式如下：mysqldump[OPTIONS]--databases[OPTIONS]數(shù)據(jù)庫名...]>filename【例7.3】備份xscj數(shù)據(jù)庫和mytest數(shù)據(jù)庫到D:/file目錄下。mysqldump-uroot-pnjnu123456--databasesxscjmytest>D:/file/data.sql【例7.4】備份MySQL服務(wù)器上的所有數(shù)據(jù)庫。使用如下命令：mysqldump-uroot–pnjnu123456--all-databases>all.sql【例7.5】將xscj數(shù)據(jù)庫中所有表的表結(jié)構(gòu)和數(shù)據(jù)都分別備份到D:/file目錄下。mysqldump-uroot-pnjnu123456--tab=D:/file/xscj其效果是在D:/file目錄下生成xscj數(shù)據(jù)庫中每個(gè)表所對應(yīng)的.sql文件和.txt文件。使用客戶端程序備份數(shù)據(jù)庫（3）恢復(fù)數(shù)據(jù)庫使用mysqldump程序備份的文件中存儲(chǔ)的是SQL語句的集合，用戶可以將這些語句還原到服務(wù)器中以恢復(fù)一個(gè)損壞的數(shù)據(jù)庫。【例7.6】假設(shè)xscj數(shù)據(jù)庫損壞，用備份文件將其恢復(fù)。備份xscj數(shù)據(jù)庫的命令：mysqldump-uroot–pnjnu123456xscj>xscj.sql恢復(fù)命令：mysql-uroot–pnjnu123456xscj<xscj.sql【例7.7】假設(shè)xs表結(jié)構(gòu)損壞，備份文件在D盤file目錄下，現(xiàn)將包含xs表結(jié)構(gòu)的.sql文件恢復(fù)到服務(wù)器中。mysql-uroot-pnjnu123456xscj<D:/file/xs.sql使用客戶端程序備份數(shù)據(jù)庫2．使用mysqlimport程序恢復(fù)數(shù)據(jù)Mysqlimport程序可以用來恢復(fù)表中的數(shù)據(jù)，它提供了LOADDATAINFILE語句的一個(gè)命令行接口，通過發(fā)送一條LOADDATAINFILE命令到服務(wù)器來運(yùn)作。它的大多數(shù)選項(xiàng)直接對應(yīng)LOADDATAINFILE語句，其命令格式如下：mysqlimport[options]db_namefilename...對于在命令行上命名的每個(gè)文本文件，mysqlimport程序剝?nèi)ノ募臄U(kuò)展名，并使用文件名決定向哪個(gè)表導(dǎo)入文件的內(nèi)容。例如，“patient.txt”“patient.sql”“patient”都會(huì)被導(dǎo)入名為patient的表中。所以備份的文件名應(yīng)根據(jù)需要恢復(fù)的表命名?！纠?.8】恢復(fù)xscj數(shù)據(jù)庫中表xs的數(shù)據(jù)，保存數(shù)據(jù)的文件為xs.txt，命令如下：mysqlimport-uroot–pnjnu123456--low-priority--replacexscjxs.txtmysqlimport也需要提供-u、-p選項(xiàng)來連接服務(wù)器。03直接復(fù)制直接復(fù)制由于MySQL的數(shù)據(jù)庫和表是直接通過目錄和表文件實(shí)現(xiàn)的，因此可以通過直接復(fù)制文件的方法來備份數(shù)據(jù)庫。不過，直接復(fù)制文件不能夠移植到其他機(jī)器上，除非要復(fù)制的表使用MyISAM存儲(chǔ)格式。如果要把MyISAM類型的表直接復(fù)制到另一個(gè)服務(wù)器使用，首先要求兩個(gè)服務(wù)器必須使用相同的MySQL版本，而且硬件結(jié)構(gòu)必須相同或相似。在復(fù)制之前要保證數(shù)據(jù)表不被使用，保證復(fù)制完整性的最好方法是關(guān)閉服務(wù)器，復(fù)制數(shù)據(jù)庫下的所有表文件（*.frm、*.MYD和*.MYI文件），然后重啟服務(wù)器。第7章MySQL數(shù)據(jù)庫備份與恢復(fù)——日志文件01啟用日志啟用日志二進(jìn)制日志可以在啟動(dòng)服務(wù)器的時(shí)候啟用，這需要修改MySQL安裝目錄中的my-default.ini選項(xiàng)文件。打開該文件，找到[mysqld]所在行，在該行后面加上以下格式的一行：log-bin[=filename]假設(shè)這里filename為bin_log。若不指定目錄，則在MySQL的data目錄下自動(dòng)創(chuàng)建二進(jìn)制格式日志文件。由于使用mysqlbinlog工具處理日志時(shí)，日志必須處于bin目錄下，因此日志的路徑就被指定為bin目錄，添加的行改為以下一行：log-bin=E:/MySQL57/bin/bin_log保存，重啟服務(wù)器。要重啟服務(wù)器，可以先關(guān)閉服務(wù)器，在命令窗口中輸入以下命令：netstopmysql再啟動(dòng)服務(wù)器：netstartmysqlMySQL安裝目錄的bin目錄下多出兩個(gè)文件：bin_log.000001和bin_log.index。02用mysqlbinlog工具處理日志用mysqlbinlog工具處理日志使用mysqlbinlog實(shí)用工具可以檢查二進(jìn)制日志文件，命令格式為：mysqlbinlog[options]日志文件名...例如，運(yùn)行以下命令可以查看bin_log.000001的內(nèi)容：mysqlbinlogbin_log.000001由于二進(jìn)制數(shù)據(jù)可能非常龐大，無法在屏幕上延伸，可以保存到文本文件中：mysqlbinlogbin_log.000001>D:/file/lbin-log000001.txt使用日志恢復(fù)數(shù)據(jù)的命令格式如下：mysqlbinlog[options]日志文件名...|mysql[options]【例7.9】假設(shè)用戶在星期一下午1點(diǎn)使用mysqldump工具進(jìn)行數(shù)據(jù)庫xscj的完全備份，備份文件為file.sql。從星期一下午1點(diǎn)用戶開始啟用日志，bin_log.000001文件保存了從星期一下午1點(diǎn)到星期二下午1點(diǎn)的所有更改，在星期二下午1點(diǎn)運(yùn)行一條SQL語句：flushlogs;創(chuàng)建了bin_log.000002文件，在星期三下午1點(diǎn)時(shí)數(shù)據(jù)庫崩潰?，F(xiàn)要將數(shù)據(jù)庫恢復(fù)到星期三下午1點(diǎn)時(shí)的狀態(tài)。首先將數(shù)據(jù)庫恢復(fù)到星期一下午1點(diǎn)時(shí)的狀態(tài)，在命令窗口輸入以下命令：mysqldump-uroot–p19830925xscj<file.sql用mysqlbinlog工具處理日志使用以下命令將數(shù)據(jù)庫恢復(fù)到星期二下午時(shí)的狀態(tài)：mysqlbinlogbin_log.000001|mysql-uroot–p198