MySQL教程（第4版） 課件 第8章 數(shù)據(jù)庫(kù)備份與恢復(fù)

第8章

數(shù)據(jù)庫(kù)備份與恢復(fù)——SQL語(yǔ)句導(dǎo)出或?qū)霐?shù)據(jù)01表記錄導(dǎo)出表記錄導(dǎo)出下列語(yǔ)句可把表記錄導(dǎo)出到一個(gè)文件中。SELECT*INTOOUTFILE'文件名'導(dǎo)出選項(xiàng)其中，導(dǎo)出選項(xiàng)：[FIELDS [TERMINATEDBY'符號(hào)'] [[OPTIONALLY]ENCLOSEDBY'轉(zhuǎn)義字符'] [ESCAPEDBY'轉(zhuǎn)義字符']][LINESTERMINATEDBY'字符串']表記錄導(dǎo)出說(shuō)明：（1）導(dǎo)出文件默認(rèn)在服務(wù)器主機(jī)上創(chuàng)建，并且文件名不能是已經(jīng)存在的，否則可能將原文件覆蓋，需要在文件名前加上具體的路徑。（2）FIELDS子句就是表列子句，指定導(dǎo)出文件中數(shù)據(jù)存放的格式。（3）LINES子句TERMINATEDBY指定一行結(jié)束的標(biāo)志。如果FIELDS和LINES子句都不指定，則默認(rèn)聲明以下子句：FIELDSTERMINATEDBY'\t'ENCLOSEDBY''ESCAPEDBY'\\'LINESTERMINATEDBY'\n'FIELDS子句與LINES子句的語(yǔ)法完全相同，兩種子句都是可選的，但如果在LOADDATA后同時(shí)指定了兩種，則FIELDS子句必須位于LINES子句之前。（4）如果使用DUMPFILE而非OUTFILE：SELECT*INTODUMPFILE'文件名'導(dǎo)出選項(xiàng)表記錄導(dǎo)出【例】導(dǎo)出xscj數(shù)據(jù)庫(kù)kc表的所有記錄到E:\mysql5\data目錄kc.txt文件中，要求列值如果是字符就用雙引號(hào)標(biāo)注，列值之間用逗號(hào)隔開(kāi)，每行以“\r\n”為結(jié)束標(biāo)志。SETGLOBALlocal_infile=ON; USExscj;SELECT*FROMkc INTOOUTFILE'E:\mysql5\\data\\kc.txt' FIELDS TERMINATEDBY',' ENCLOSEDBY'\"' ESCAPEDBY'\'' LINESTERMINATEDBY'\r\n'; 說(shuō)明：（1）將local_infile這個(gè)系統(tǒng)全局環(huán)境設(shè)置為ON。（2）如果數(shù)據(jù)庫(kù)（表）的字符集與操作系統(tǒng)字符集不同，導(dǎo)出的txt文件會(huì)顯示亂碼。（3）如果導(dǎo)出文件不能成功，先停止MySQL，查看系統(tǒng)配置文件my.ini?？梢孕薷腫mysqld]段secure_file_priv=E:\mysql5\data，重新啟動(dòng)MySQL。02導(dǎo)入文本數(shù)據(jù)導(dǎo)入文本數(shù)據(jù)MySQL的LOADDATA語(yǔ)句能以非常快的速度將文本文件中的數(shù)據(jù)導(dǎo)入到表中：LOADDATA[LOCAL]INFILE文件名INTOTABLE表名

FIELDS... LINES... 說(shuō)明一：（1）“文件名”用單引號(hào)括起來(lái)，文件以普通文本文件（.txt）格式保存。（2）FIELDS聲明一系列字段子句，同導(dǎo)出語(yǔ)句。（3）LINES聲明一系列行子句，同導(dǎo)出語(yǔ)句。說(shuō)明二：對(duì)從文本文件中導(dǎo)入數(shù)據(jù)的處理遵循如下規(guī)則：（1）對(duì)于數(shù)值類型數(shù)據(jù)，引用字符加不加都可以。例如，學(xué)時(shí)、學(xué)分。（2）文本文件中的行可省略一個(gè)或多個(gè)字段的數(shù)據(jù)內(nèi)容，僅以分隔字符占位，被省略的字段將默認(rèn)按照其表中列類型的隱式默認(rèn)值填寫(xiě)數(shù)據(jù)。（3）對(duì)于blob類型的數(shù)據(jù)，按文本文件中實(shí)際給出的字符（不論有無(wú)引用字符）分配空間，一個(gè)字符對(duì)應(yīng)一個(gè)字節(jié)的存儲(chǔ)空間。導(dǎo)入文本數(shù)據(jù)【例】讀取E:\mysql5\data目錄文件kc.txt，將文件內(nèi)容加載到課程拷貝表kc_copy中。USExscj;DROPTABLEIFEXISTSkc_copy;CREATETABLEkc_copyLIKEkc;SETGLOBALlocal_infile=ON; LOADDATALOCALINFILE'E:\mysql5\\data\\kc.txt'INTOTABLEkc_copy FIELDS TERMINATEDBY',' ENCLOSEDBY'\"' ESCAPEDBY'\'' LINES TERMINATEDBY'\r\n'; 第8章

數(shù)據(jù)庫(kù)備份與恢復(fù)——數(shù)據(jù)庫(kù)備份與恢復(fù)數(shù)據(jù)庫(kù)備份與恢復(fù)MySQL提供三種數(shù)據(jù)庫(kù)備份類型：（1）備份文件：通過(guò)SQL語(yǔ)句或使用客戶端工具導(dǎo)出數(shù)據(jù)或表文件的拷貝來(lái)備份數(shù)據(jù)庫(kù)。（2）日志文件：通過(guò)保存更新數(shù)據(jù)的所有語(yǔ)句來(lái)備份數(shù)據(jù)庫(kù)。（3）主從復(fù)制：此功能建立在兩個(gè)或以上的服務(wù)器之間，通過(guò)設(shè)定它們的主從關(guān)系來(lái)實(shí)現(xiàn)。01Windows命令行窗口備份和恢復(fù)mysqldump備份數(shù)據(jù)庫(kù)及表mysql恢復(fù)數(shù)據(jù)庫(kù)及表Windows命令行窗口備份和恢復(fù)1.mysqldump備份數(shù)據(jù)庫(kù)及表mysqldump是MySQL內(nèi)置的工具，允許用戶將數(shù)據(jù)庫(kù)指定不同的選項(xiàng)備份到文件、服務(wù)器，甚至是壓縮gzip文件中。它使用靈活、快速。mysqldump備份包括下列三種寫(xiě)法：mysqldump[選項(xiàng)]數(shù)據(jù)庫(kù)[表]>文件名mysqldump[選項(xiàng)]--databases[選項(xiàng)]數(shù)據(jù)庫(kù)...>文件名mysqldump[選項(xiàng)]--all-databases[選項(xiàng)]>文件名說(shuō)明：（1）常用選項(xiàng)-u：后面的用戶需要具有相應(yīng)權(quán)限才能備份。-p：后面跟密碼不能有空格；也可不跟密碼，在命令回車后再輸入密碼，系統(tǒng)顯示“*”。-h：主機(jī)名。如果是本地服務(wù)器，可以省略-h參數(shù)。--databases：備份數(shù)據(jù)庫(kù)。--all-databases：備份所有數(shù)據(jù)庫(kù)。--tables：備份表。--where：記錄篩選條件。-d：只備份數(shù)據(jù)。-help：得到mysqldump選項(xiàng)表及幫助信息。（2）>文件名>：導(dǎo)出符。表示導(dǎo)出到文件名指定的文件中。文件名：可指定存放文件的目錄（例如：E:\mysql5\data），目錄必須已經(jīng)創(chuàng)建，如有同名文件將覆蓋。Windows命令行窗口備份和恢復(fù)【例】備份數(shù)據(jù)庫(kù)。在Windows命令行窗口，進(jìn)入MySQL安裝目錄下的bin子目錄：cdC:\ProgramFiles\MySQL\MySQLServer5.7\bin（1）備份xscj數(shù)據(jù)庫(kù)（包括所有對(duì)象和數(shù)據(jù)）：mysqldump-uroot-p--databasesxscj>E:\MYSQL5\data\xscj.sql命令執(zhí)行后，輸入登錄MySQL實(shí)例密碼。數(shù)據(jù)庫(kù)可以同時(shí)寫(xiě)多個(gè)，用空格分開(kāi)。（2）備份OPPO主機(jī)test數(shù)據(jù)庫(kù)（包括所有對(duì)象和數(shù)據(jù)）：mysqldump-uroot-p123456mm-hOPPO--databasestest>E:\MYSQL5\data\oppo_test.sql（3）備份xscj數(shù)據(jù)庫(kù)xs、kc和cj表：mysqldump-uroot-p123456--databasesxscj--tablesxskccj>E:\mysql5\data\xs_kc_cj.sql直接寫(xiě)“123456”本機(jī)密碼。（4）備份xscj數(shù)據(jù)庫(kù)所有表結(jié)構(gòu)：mysqldump-hlocalhost-uroot-p-dxscj>E:\mysql5\data\xscj_stru.sql（5）備份所有的數(shù)據(jù)庫(kù)表結(jié)構(gòu)：mysqldump-hlocalhost-uroot-p-d--all-databases>E:\mysql5\data\all_stru.sqlWindows命令行窗口備份和恢復(fù)2.mysql恢復(fù)數(shù)據(jù)庫(kù)及表mysqldump備份的文件中存儲(chǔ)的是SQL語(yǔ)句的集合，用戶可以將這些語(yǔ)句還原到服務(wù)器上以恢復(fù)一個(gè)損壞的數(shù)據(jù)庫(kù)。此恢復(fù)命令只能恢復(fù)數(shù)據(jù)庫(kù)中被損壞的表及數(shù)據(jù)，如果刪除的是整個(gè)數(shù)據(jù)庫(kù)，將無(wú)法恢復(fù)，除非再建一個(gè)同名的數(shù)據(jù)庫(kù)，然后往其中恢復(fù)表和數(shù)據(jù)?！纠肯葌浞荼镜豻est數(shù)據(jù)庫(kù)，再用備份文件將其恢復(fù)。（1）備份本地test數(shù)據(jù)庫(kù)后刪除。mysqldump-uroot-p--databasestest>E:\MYSQL5\data\test.sql刪除test數(shù)據(jù)庫(kù)中的表。（2）恢復(fù)test數(shù)據(jù)庫(kù)mysql-uroot-p123456test<E:\mysql5\data\test.sql（3）查看test數(shù)據(jù)庫(kù)中的對(duì)象02使用日志文件備份和恢復(fù)啟用日志處理日志使用日志文件備份和恢復(fù)1.啟用日志二進(jìn)制日志可以在啟動(dòng)服務(wù)器的時(shí)候啟用，這需要修改MySQ安裝文件夾中的my.ini配置文件。log_bin[=文件名]若不指定目錄，則在MySQL的data目錄下自動(dòng)創(chuàng)建二進(jìn)制日志文件。由于下面使用mysqlbinlog工具處理日志時(shí)，日志必須處于bin目錄下，所以日志的路徑就指定為bin目錄路徑。（1）修改my.ini配置文件需要先停止MySQL服務(wù)：netstopMySQL服務(wù)名如顯示拒絕，改用Windows“計(jì)算機(jī)管理”工具停止MySQL運(yùn)行。（2）修改my.ini配置文件，例如：log_bin=C:\ProgramFiles\MySQL\MySQLServer5.7\bin\logfile保存，重啟服務(wù)：netstartMySQL服務(wù)名使用日志文件備份和恢復(fù)2.處理日志從Windows命令行窗口進(jìn)入MySQL安裝目錄\bin下。（1）使用mysqlbinlog實(shí)用工具可以查看和處理二進(jìn)制日志文件mysqlbinlog[選項(xiàng)]日志文件...例如，運(yùn)行以下命令可以查看logfile.000001的內(nèi)容：mysqlbinloglogfile.000001由于二進(jìn)制數(shù)據(jù)非常龐大，無(wú)法在屏幕上延伸，可以保存到文本文件中：mysqlbinlog日志文件.000001>E:\mysql5\logfile_000001.txt（2）使用日志恢復(fù)數(shù)據(jù)mysqlbinlog[選項(xiàng)]日志文件...|mysql[用戶選項(xiàng)]（3）清理日志flushlogs;使用日志文件備份和恢復(fù)【例】使用日志文件備份和恢復(fù)xscj數(shù)據(jù)庫(kù)。1）

備份xscj數(shù)據(jù)庫(kù)（1）假設(shè)用戶在星期一下午1點(diǎn)使用mysqldump工具進(jìn)行數(shù)據(jù)庫(kù)xscj的完全備份，備份文件為xscj.sql。mysqldump-uroot-p--databasesxscj>E:\MYSQL5\data\xscj.sql（2）從星期一下午1點(diǎn)開(kāi)始用戶啟用日志，logfile.000001文件保存了從星期一下午1點(diǎn)到星期二下午1點(diǎn)的所有更改。（3）在星期二下午1點(diǎn)運(yùn)行一條SQL語(yǔ)句：flushlogs;此時(shí)創(chuàng)建了logfile.000002文件。使用日志文件備份和恢復(fù)2）恢復(fù)xscj數(shù)據(jù)庫(kù)（1）將數(shù)據(jù)庫(kù)恢復(fù)到星期一下午1點(diǎn)狀態(tài)：mysql-uroot-p123456xscj<E:\MYSQL5\data\xscj.sql（2）將數(shù)據(jù)庫(kù)恢復(fù)到星期二下午1時(shí)的狀態(tài)：mysqlbinloglogfile.000001|mysql-uroot–p123456（3）將數(shù)據(jù)庫(kù)恢復(fù)到星期三下午1點(diǎn)時(shí)的狀態(tài)：mysqlbinloglogfile.000002|mysql-uroot–p123456（4）清除所有的日志文件：由于日志文件要占用很大的硬盤(pán)資源，備份數(shù)據(jù)庫(kù)后，要及時(shí)將沒(méi)用的日志文件清除掉。下列SQL語(yǔ)句用于清除所有的日志文件：resetmaster;如果要?jiǎng)h除部分日志文件，可以使用PURGEMASTERLOGS語(yǔ)句：PURGE{MASTER|BINARY}LOGSTO'文件名'或PURGE{MASTER|BINARY}LOGSBEFORE'日期'第8章

數(shù)據(jù)庫(kù)備份與恢復(fù)——直接復(fù)制文件直接復(fù)制文件如果要把MyISAM類型的表直接復(fù)制到另一個(gè)服務(wù)器使用，首先要求兩個(gè)服務(wù)器必須使用相同的MySQL版本，而且硬件結(jié)構(gòu)必須相同或相似。在復(fù)制之前要保證數(shù)據(jù)表不被使用。保證復(fù)制完整性的最好方法是關(guān)閉MySQL服務(wù)，復(fù)制數(shù)據(jù)庫(kù)目錄下的所有表文件（*.frm、*.myd和*.myi文件），然后重啟服務(wù)。文件