第8章

MySQL數(shù)據(jù)庫備份與恢復(fù)根本概念8.18.2常用的備份恢復(fù)方法日志文件8.38.1根本概念數(shù)據(jù)庫中的數(shù)據(jù)喪失或被破壞可能是由以下原因造成：〔1〕計(jì)算機(jī)硬件故障。由于使用不當(dāng)或產(chǎn)品質(zhì)量等原因，計(jì)算機(jī)硬件可能會(huì)出現(xiàn)故障，不能使用。如硬盤損壞會(huì)使得存儲(chǔ)于其上的數(shù)據(jù)喪失。〔2〕軟件故障。由于軟件設(shè)計(jì)上的失誤或用戶使用的不當(dāng)，軟件系統(tǒng)可能會(huì)誤操作數(shù)據(jù)引起數(shù)據(jù)破壞?！?〕病毒。破壞性病毒會(huì)破壞系統(tǒng)軟件、硬件和數(shù)據(jù)。〔4〕誤操作。如用戶誤使用了諸如DELETE、UPDATE等命令而引起數(shù)據(jù)喪失或破壞?！?〕自然災(zāi)害。如火災(zāi)、洪水或地震等，它們會(huì)造成極大的破壞，會(huì)毀壞計(jì)算機(jī)系統(tǒng)及其數(shù)據(jù)?！?〕盜竊。一些重要數(shù)據(jù)可能會(huì)遭竊。8.1根本概念因此，擁有能夠恢復(fù)的數(shù)據(jù)對(duì)于一個(gè)數(shù)據(jù)庫系統(tǒng)來說是非常重要的。MySQL有三種保證數(shù)據(jù)平安的方法?！?〕數(shù)據(jù)庫備份：通過導(dǎo)出數(shù)據(jù)或者表文件的拷貝來保護(hù)數(shù)據(jù)。〔2〕二進(jìn)制日志文件：保存更新數(shù)據(jù)的所有語句。〔3〕數(shù)據(jù)庫復(fù)制：MySQL內(nèi)部復(fù)制功能建立在兩個(gè)或兩個(gè)以上效勞器之間，通過設(shè)定它們之間的主從關(guān)系來實(shí)現(xiàn)的。其中一個(gè)作為主效勞器，其他的作為從效勞器。8.2常用的備份恢復(fù)方法說明：〔1〕這個(gè)語句的作用是將表中SELECT語句選中的行寫入到一個(gè)文件中。文件默認(rèn)在效勞器主機(jī)上創(chuàng)立，并且文件存在原文件將被覆蓋。〔2〕使用OUTFILE時(shí)，可以在export_options中參加以下兩個(gè)自選的子句，它們的作用是決定數(shù)據(jù)行在文件中存放的格式?！?〕FIELDS子句：在FIELDS子句中有三個(gè)亞子句：TERMINATEDBY、[OPTIONALLY]ENCLOSEDBY和ESCAPEDBY?！?〕LINES子句：在LINES子句中使用TERMINATEDBY指定一行結(jié)束的標(biāo)志，如“LINESTERMINATEDBY'?'〞表示一行以“?〞作為結(jié)束標(biāo)志。8.2常用的備份恢復(fù)方法2.導(dǎo)入表數(shù)據(jù)LOADDATA…INFILE格式：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á)式,...)]8.2常用的備份恢復(fù)方法說明：LOW_PRIORITY|CONCURRENT：假設(shè)指定LOW_PRIORITY，那么延遲語句的執(zhí)行。LOCAL：假設(shè)指定了LOCAL，那么文件會(huì)被客戶主機(jī)上的客戶端讀取，并被發(fā)送到效勞器。文件名.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í)修改表中列的值。8.2常用的備份恢復(fù)方法【例8.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'?';8.2常用的備份恢復(fù)方法導(dǎo)出成功后可以查看D盤file文件夾下的myfile1.txt文件，文件內(nèi)容如圖8.1所示。8.2常用的備份恢復(fù)方法文件備份完后可以將文件中的數(shù)據(jù)導(dǎo)入到course表中，使用以下命令：loaddatainfile'd:/file/myfile1.txt' intotablecourse fieldsterminatedby',' optionallyenclosedby'"' linesterminatedby'?';8.2.2使用客戶端工具：備份數(shù)據(jù)庫使用客戶端程序的方法如下。翻開命令行，進(jìn)入bin目錄：cdC:\ProgramFiles\MySQL\MySQLServer5.6\bin后面介紹的客戶端命令都在此處輸入，如圖8.2所示。8.2.2使用客戶端工具：備份數(shù)據(jù)庫1．使用mysqldump備份數(shù)據(jù)mysqldump客戶端也可用于備份數(shù)據(jù)，它比SQL語句多做的工作是可以在導(dǎo)出的文件中包含表結(jié)構(gòu)的SQL語句，因此可以備份數(shù)據(jù)庫表的結(jié)構(gòu)，而且可以備份一個(gè)數(shù)據(jù)庫，甚至整個(gè)數(shù)據(jù)庫系統(tǒng)?！?〕備份表命令格式：mysqldump[OPTIONS]db_name[tables]>filename同其他客戶端程序一樣，備份數(shù)據(jù)時(shí)需要使用一個(gè)用戶賬號(hào)連接到效勞器，這需要用戶手工提供參數(shù)或在選項(xiàng)文件中修改有關(guān)值。參數(shù)格式為：-h[hostname]-u[username]-p[password]【例8.2】使用mysqldump備份xs表和kc表。具體命令如下：mysqldump-hlocalhost-uroot-p19830925xscjxskc>twotables.sql8.2.2使用客戶端工具：備份數(shù)據(jù)庫〔2〕備份數(shù)據(jù)庫mysqldump程序還可以將一個(gè)或多個(gè)數(shù)據(jù)庫備份到一個(gè)文件中。命令格式：mysqldump[OPTIONS]--databases[OPTIONS]DB1[DB2DB3...]>filename【例8.3】備份xscj數(shù)據(jù)庫和test數(shù)據(jù)庫到D盤file文件夾下。命令如下：mysqldump-uroot-p19830925--databasesxscjtest>D:/file/data.sql8.2.2使用客戶端工具：備份數(shù)據(jù)庫MySQL還能備份整個(gè)數(shù)據(jù)庫系統(tǒng)，即系統(tǒng)中的所有數(shù)據(jù)庫?！纠?.4】備份MySQL效勞器上的所有數(shù)據(jù)庫。使用如下命令：mysqldump-uroot–p19830925--all-databases>all.sql【例8.5】將xscj數(shù)據(jù)庫中所有表的表結(jié)構(gòu)和數(shù)據(jù)都分別備份到D盤file文件夾下。命令如下：mysqldump-uroot-p19830925--tab=D:/file/xscj其效果是在file文件夾生成xscj數(shù)據(jù)庫中每個(gè)表所對(duì)應(yīng)的.sql文件和.txt文件。8.2.2使用客戶端工具：備份數(shù)據(jù)庫〔3〕恢復(fù)數(shù)據(jù)庫mysqldump程序備份的文件中存儲(chǔ)的是SQL語句的集合，用戶可以將這些語句復(fù)原到效勞器中以恢復(fù)一個(gè)損壞的數(shù)據(jù)庫?！纠?.6】假設(shè)xscj數(shù)據(jù)庫損壞，用備份文件將其恢復(fù)。備份xscj數(shù)據(jù)庫的命令為：mysqldump-uroot–p19830925xscj>xscj.sql恢復(fù)命令為：mysql-uroot–p19830925xscj<xscj.sql如果表的結(jié)構(gòu)損壞，也可以恢復(fù)，但是表中原有的數(shù)據(jù)將全部被清空。8.2.2使用客戶端工具：備份數(shù)據(jù)庫【例8.7】假設(shè)xs表結(jié)構(gòu)損壞，備份文件在D盤file目錄下，現(xiàn)將包含xs表結(jié)構(gòu)的.sql文件恢復(fù)到效勞器中。命令如下：mysql-uroot-p19830925xscj<D:/file/xs.sql如果只恢復(fù)表中的數(shù)據(jù)，就要使用mysqlimport客戶端。8.2.2使用客戶端工具：備份數(shù)據(jù)庫2．使用mysqlimport恢復(fù)數(shù)據(jù)mysqlimport客戶端可以用來恢復(fù)表中的數(shù)據(jù)，它提供了LOADDATAINFILE語句的一個(gè)命令行接口，發(fā)送一個(gè)LOADDATAINFILE命令到效勞器來運(yùn)作。它大多數(shù)選項(xiàng)直接對(duì)應(yīng)LOADDATAINFILE語句。mysqlimport命令格式為：mysqlimport[options]db_namefilename...對(duì)于在命令行上命名的每個(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–p19830925--low-priority--replacexscjxs.txt8.2.3直接復(fù)制根據(jù)本章前兩節(jié)的介紹，由于MySQL的數(shù)據(jù)庫和表是直接通過目錄和表文件實(shí)現(xiàn)的，因此可以通過直接復(fù)制文件的方法來備份數(shù)據(jù)庫。不過，直接復(fù)制文件不能夠移植到其他機(jī)器上，除非要復(fù)制的表使用MyISAM存儲(chǔ)格式。如果要把MyISAM類型的表直接復(fù)制到另一個(gè)效勞器使用，首先要求兩個(gè)效勞器必須使用相同的MySQL版本，而且硬件結(jié)構(gòu)必須相同或相似。8.3日志文件8.3.1啟用日志二進(jìn)制日志可以在啟動(dòng)效勞器的時(shí)候啟用，這需要修改C:\ProgramFiles\MySQL\MySQLServer5.6文件夾中的my-default.ini選項(xiàng)文件。翻開該文件，找到[mysqld]所在行，在該行后面加上以下格式的一行：log-bin[=filename]8.3.1啟用日志假設(shè)這里filename取名為bin_log。假設(shè)不指定目錄，那么在MySQL的data目錄下自動(dòng)創(chuàng)立二進(jìn)制日志文件。由于下面使用mysqlbinlog工具處理日志時(shí)，日志必須處于bin目錄下，所以日志的路徑就指定為bin目錄，添加的行改為以下一行：log-bin=C:/ProgramFiles/MySQL/MySQLServer5.6/bin/bin_log保存，重啟效勞器。重啟效勞器的方法可以是：先關(guān)閉效勞器，在如圖8.2所示窗口中輸入以下命令：netstopmysql再啟動(dòng)效勞器：netstartmysql此時(shí)，MySQL安裝目錄的bin目錄下多出兩個(gè)文件：bin_log.000001和bin_log.index。8.3.2用mysqlbinlog處理日志使用mysqlbinlog實(shí)用工具可以檢查二進(jìn)制日志文件，命令格式為：mysqlbinlog[options]log-files...例如，運(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]log-files…|mysql[options]8.3.2用mysqlbinlog處理日志【例8.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;此時(shí)創(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)，在圖8.2的命令窗口輸入以下命令：mysqldump-uroot–p19830925xscj<file.sql8.3.2用mysqlbinlog處理日志使用以下命令將數(shù)據(jù)庫恢復(fù)到星期二下午時(shí)的狀態(tài)：mysqlbinlogbin_log.000001|mysql-uroot–p19830925再使用以下命令即可將數(shù)據(jù)庫