My-SQL數(shù)據(jù)庫(kù)管理與應(yīng)用第11章-MySQL備份與恢復(fù)

第11章MySQL備份與恢復(fù)課程目標(biāo)

存儲(chǔ)引擎和表類型數(shù)據(jù)庫(kù)的備份與恢復(fù)完全備份表備份增量備份概述盡管數(shù)據(jù)庫(kù)系統(tǒng)中采取了各種保護(hù)措施來(lái)保證數(shù)據(jù)庫(kù)的平安性和完整性，但是計(jì)算機(jī)系統(tǒng)中的硬件故障、軟件錯(cuò)誤、操作失誤及惡意破壞仍是不可防止的，因此數(shù)據(jù)庫(kù)管理系統(tǒng)必須具有把數(shù)據(jù)庫(kù)從錯(cuò)誤狀態(tài)恢復(fù)到某一的正確狀態(tài)〔亦稱為一致狀態(tài)或完整狀態(tài)〕的功能，這就是數(shù)據(jù)庫(kù)的恢復(fù)。概述恢復(fù)子系統(tǒng)是數(shù)據(jù)庫(kù)管理系統(tǒng)的一個(gè)重要組成局部，而且還相當(dāng)龐大，常常占整個(gè)系統(tǒng)代碼的百分之十以上。數(shù)據(jù)庫(kù)系統(tǒng)所采用的恢復(fù)技術(shù)是否行之有效，不僅對(duì)系統(tǒng)的可靠程度起著決定性作用，而且對(duì)系統(tǒng)的運(yùn)行效率也有很大音響，是衡量系統(tǒng)性能優(yōu)劣的重要指標(biāo)。1.存儲(chǔ)引擎和表類型MySQL在保存數(shù)據(jù)庫(kù)時(shí)有很多種存儲(chǔ)方式，不同的存儲(chǔ)方式執(zhí)行同一SQL語(yǔ)句的效率可能會(huì)相差很多?？紤]到在實(shí)際使用中，不同系統(tǒng)對(duì)數(shù)據(jù)庫(kù)的要求不盡相同，執(zhí)行的操作及每種操作執(zhí)行的頻率也都不一樣，為了進(jìn)一步提高數(shù)據(jù)庫(kù)的處理效率，MySQL將表類型劃分成很多種類，并允許對(duì)不同的數(shù)據(jù)庫(kù)選擇不同的存儲(chǔ)引擎。1.存儲(chǔ)引擎和表類型MySQL插件式存儲(chǔ)引擎的體系結(jié)構(gòu)

1.存儲(chǔ)引擎和表類型MySQL的存儲(chǔ)引擎包括以下類型：MyISAM管理非事務(wù)表。它提供高速存儲(chǔ)和檢索，以及全文搜索能力。MyISAM在所有MySQL配置里被支持，它是默認(rèn)的存儲(chǔ)引擎，除非配置MySQL默認(rèn)使用另外一個(gè)引擎。MEMORY存儲(chǔ)引擎提供“內(nèi)存中”表。MERGE存儲(chǔ)引擎允許集合將被處理同樣的MyISAM表作為一個(gè)單獨(dú)的表。就像MyISAM一樣，MEMORY和MERGE存儲(chǔ)引擎處理非事務(wù)表，這兩個(gè)引擎也都被默認(rèn)包含在MySQL中。1.存儲(chǔ)引擎和表類型InnoDB和BDB存儲(chǔ)引擎提供事務(wù)平安表。BDB被包含在為支持它的操作系統(tǒng)發(fā)布的MySQL-Max二進(jìn)制分發(fā)版里。InnoDB也默認(rèn)被包括在所有MySQL5.1二進(jìn)制分發(fā)版里，可以按照喜好通過(guò)配置MySQL來(lái)允許或禁止任一引擎。EXAMPLE存儲(chǔ)引擎是一個(gè)“存根”引擎，它不做什么?？梢杂眠@個(gè)引擎創(chuàng)立表，但沒(méi)有數(shù)據(jù)被存儲(chǔ)于其中或從其中檢索。這個(gè)引擎的目的是效勞。同樣，它的主要興趣是對(duì)開發(fā)者。1.存儲(chǔ)引擎和表類型NDBCluster是被MySQLCluster用來(lái)實(shí)現(xiàn)分割到多臺(tái)計(jì)算機(jī)上的表的存儲(chǔ)引擎。它在MySQL-Max5.1二進(jìn)制分發(fā)版里提供。這個(gè)存儲(chǔ)引擎只被Linux,Solaris,和MacOSX支持。ARCHIVE存儲(chǔ)引擎被用來(lái)無(wú)索引地，非常小地覆蓋存儲(chǔ)的大量數(shù)據(jù)。

1.存儲(chǔ)引擎和表類型CSV存儲(chǔ)引擎把數(shù)據(jù)以逗號(hào)分隔的格式存儲(chǔ)在文本文件中。BLACKHOLE存儲(chǔ)引擎接受但不存儲(chǔ)數(shù)據(jù)，并且檢索總是返回一個(gè)空集。FEDERATED存儲(chǔ)引擎把數(shù)據(jù)存在遠(yuǎn)程數(shù)據(jù)庫(kù)中。在MySQL5.1中，它只和MySQL一起工作，使用MySQLCClientAPI。在未來(lái)的分發(fā)版中，我們想要讓它使用其它驅(qū)動(dòng)器或客戶端連接方法連接到另外的數(shù)據(jù)源。

1.存儲(chǔ)引擎和表類型當(dāng)創(chuàng)立一個(gè)新表的時(shí)候，可以通過(guò)使用ENGINE或TYPE選項(xiàng)到來(lái)指定要?jiǎng)?chuàng)立什么類型的表：CREATETABLEt(iINT)ENGINE=INNODB;1.存儲(chǔ)引擎和表類型如果省略掉ENGINE或TYPE選項(xiàng)，默認(rèn)是使用MyISAM引擎。要想修改默認(rèn)的引擎可以用--default-storage-engine或--default-table-type效勞器啟動(dòng)選項(xiàng)來(lái)進(jìn)行設(shè)置，或者通過(guò)設(shè)置storage_engine或table_type系統(tǒng)變量來(lái)改變。1.存儲(chǔ)引擎和表類型1.1MyISAMMyISAM是默認(rèn)存儲(chǔ)引擎。它是在早期MySQL的ISAM引擎根底上，經(jīng)過(guò)一定的擴(kuò)展而產(chǎn)生的?！睲ySQL5.1不支持ISAM〕。MyISAM類型的表在磁盤上存儲(chǔ)成三個(gè)文件。1.存儲(chǔ)引擎和表類型這三個(gè)文件的名稱相同，為該表的表名，僅擴(kuò)展名不同，各文件作用如下：.frm文件存儲(chǔ)表定義。.MYD(MYData)文件存儲(chǔ)表中數(shù)據(jù)。.MYI(MYIndex)文件存儲(chǔ)表上建立的索引。

1.存儲(chǔ)引擎和表類型1.2InnoDBInnoDB給MySQL提供了具有提交，回滾和崩潰恢復(fù)能力的事務(wù)平安〔ACID兼容〕存儲(chǔ)引擎。InnoDB鎖定在行級(jí)，這一特點(diǎn)可大大提高多用戶部署時(shí)的性能。在SQL查詢中，可以自由地將InnoDB類型的表與其它MySQL的表的類型混合起來(lái)，甚至在同一個(gè)查詢中也可以混合。1.存儲(chǔ)引擎和表類型InnoDB是為處理巨大數(shù)據(jù)量時(shí)的最大性能設(shè)計(jì)。它的CPU效率可能是任何其它基于磁盤的關(guān)系數(shù)據(jù)庫(kù)引擎所不能匹敵的。InnoDB常常用于支持需要高性能的大型數(shù)據(jù)庫(kù)站點(diǎn)。著名的Internet新聞?wù)军c(diǎn)S運(yùn)行在InnoDB上。Mytrix,Inc.在InnoDB上存儲(chǔ)超過(guò)1TB的數(shù)據(jù)，還有一些其它站點(diǎn)在InnoDB上處理平均每秒800次插入/更新的負(fù)荷。

2.數(shù)據(jù)庫(kù)備份在MySQL中備份數(shù)據(jù)庫(kù)的方法很多，可分為以下幾種：完全備份：將數(shù)據(jù)庫(kù)中的數(shù)據(jù)及所有對(duì)象全部進(jìn)行備份。表備份：僅將一張或多張表中的數(shù)據(jù)進(jìn)行備份。增量備份：在某一次完全備份的根底，只備份其后數(shù)據(jù)的變化。2.數(shù)據(jù)庫(kù)備份2.1完全備份完全備份最簡(jiǎn)單也最快速的方法是拷貝數(shù)據(jù)庫(kù)文件夾，當(dāng)然在拷貝時(shí)對(duì)MySQL會(huì)有些要求。也可以使用mysqldump程序或mysqlhotcopy腳本對(duì)數(shù)據(jù)庫(kù)進(jìn)行SQL語(yǔ)句級(jí)別的備份，它們速度要稍微慢一些，不過(guò)通用性更強(qiáng)。2.1完全備份使用拷貝文件夾的方式備份因?yàn)镸ySQL表保存為文件方式，所以理論上來(lái)說(shuō)可以通過(guò)拷貝文件的方式對(duì)數(shù)據(jù)庫(kù)進(jìn)行備份。不過(guò)，使用直接備份方法時(shí)，必須要保證表在拷貝期間不被使用。如果不能滿足這一條件，拷貝的文件就可能存在數(shù)據(jù)的不一致性，拷貝操作也就失敗了。2.1完全備份最可靠的方法是在拷貝以前關(guān)閉MySQL效勞，拷貝完成后再重新啟動(dòng)效勞。但有些應(yīng)用會(huì)要求24小時(shí)不間斷效勞，此時(shí)關(guān)閉效勞器的方法就不很適宜，不過(guò)MySQL提供了表的鎖定和解鎖的相關(guān)操作，以確保在拷貝文件期間該文件不會(huì)被修改。2.1完全備份使用鎖定機(jī)制備份表的根本步驟為：使用LOCKTABLES命令鎖定某一表或多個(gè)表；拷貝對(duì)應(yīng)的文件；使用UNLOCKTABLES解鎖已拷貝完的表。2.1完全備份MysqldumpMysqldump可以在MySQL安裝目錄中找到，該程序用于轉(zhuǎn)儲(chǔ)數(shù)據(jù)庫(kù)或搜集數(shù)據(jù)庫(kù)進(jìn)行備份或?qū)?shù)據(jù)轉(zhuǎn)移到另一個(gè)支持SQL語(yǔ)句的效勞器(不一定是一個(gè)MySQL效勞器)。備份后產(chǎn)生的備份文件是一個(gè)文本文件，文件內(nèi)容為創(chuàng)立表和往表中插入數(shù)據(jù)的SQL語(yǔ)句。2.1完全備份有3種方式來(lái)調(diào)用mysqldump程序：1．備份一個(gè)數(shù)據(jù)庫(kù)或一張表，語(yǔ)法如下：shell>mysqldump[選項(xiàng)]數(shù)據(jù)庫(kù)名[表名]2．同時(shí)備份多個(gè)數(shù)據(jù)庫(kù)，語(yǔ)法如下：shell>mysqldump[選項(xiàng)]---database數(shù)據(jù)庫(kù)1[數(shù)據(jù)庫(kù)2數(shù)據(jù)庫(kù)3...]3．備份效勞器上所有的數(shù)據(jù)庫(kù)，語(yǔ)法如下：shell>mysqldump[選項(xiàng)]--all--database2.1完全備份mysqldump最常用于備份一個(gè)完整的數(shù)據(jù)庫(kù)，備份一個(gè)數(shù)據(jù)庫(kù)的根本語(yǔ)法如下：shell>mysqldump--opt數(shù)據(jù)庫(kù)名>備份文件名.sql可以使用以下語(yǔ)法將備份文件讀回到效勞器：shell>mysql數(shù)據(jù)庫(kù)名<備份文件名.sql或者為：shell>mysql-e"備份文件路徑/備份文件名.sql"數(shù)據(jù)庫(kù)名2.1完全備份mysqldump也可用于從一個(gè)MySQL效勞器向另一個(gè)效勞器復(fù)制數(shù)據(jù)時(shí)裝載數(shù)據(jù)庫(kù)，其語(yǔ)法如下：shell>mysqldump--opt數(shù)據(jù)庫(kù)名|mysql--host=其它效勞器主機(jī)名-C數(shù)據(jù)庫(kù)名一次備份多個(gè)數(shù)據(jù)庫(kù)的語(yǔ)法如下：shell>mysqldump---database數(shù)據(jù)庫(kù)名1[數(shù)據(jù)庫(kù)名2...]>備份文件名.sql使用--all--database選項(xiàng)可以一次備份效勞器上所有的數(shù)據(jù)庫(kù)：shell>mysqldump--all-databases>備份文件名.sql2.1完全備份mysqlhotcopy使用mysqlhotcopy進(jìn)行備份是備份數(shù)據(jù)庫(kù)或單個(gè)表的最快的途徑。mysqlhotcopy是一個(gè)Perl腳本，最初由TimBunce編寫并提供。它使用LOCKTABLES、FLUSHTABLES和cp或scp來(lái)快速備份數(shù)據(jù)庫(kù)。2.1完全備份Mysqlhotcopy對(duì)使用場(chǎng)合有一定限制，它只能運(yùn)行在數(shù)據(jù)庫(kù)目錄所在的機(jī)器上，并且只能用于備份MyISAM數(shù)據(jù)庫(kù)，最后，它只能運(yùn)行在Unix和NetWare中。Mysqlhotcopy使用的根本語(yǔ)法如下：shell>mysqlhotcopy數(shù)據(jù)庫(kù)名[/路徑/備份目錄](méi)shell>mysqlhotcopy數(shù)據(jù)庫(kù)名1，數(shù)據(jù)庫(kù)名2...[/路徑/備份目錄](méi)2.2表備份如果只想對(duì)數(shù)據(jù)庫(kù)中的某些表進(jìn)行備份，可以使用SELECTINTO...OUTFILE或BACKUPTABLE語(yǔ)句。2.2表備份SELECTINTO...OUTFILE此語(yǔ)法是MySQL對(duì)SELECT語(yǔ)句的擴(kuò)展應(yīng)用，其用法如下：SELECT列名列表INTOOUTFILE|DUMPFILE'文件名'輸出選項(xiàng)FROM表名［其它SELECT子句］2.2表備份SELECT...INTOOUTFILE語(yǔ)句的主要作用是快速地把一個(gè)表轉(zhuǎn)儲(chǔ)到效勞器機(jī)器上。如果想在效勞器主機(jī)之外的局部客戶主機(jī)上創(chuàng)立結(jié)果文件，不能使用此語(yǔ)句，此時(shí)應(yīng)該在客戶主機(jī)上使用如“mysql–e"SELECT...">file_name”的命令，來(lái)生成文件。2.2表備份LOADDATA[LOW_PRIORITY|CONCURRENT][LOCAL]INFILE'文件名'[REPLACE|IGNORE]INTOTABLE表名[FIELDS[TERMINATEDBY'字符串'][[OPTIONALLY]ENCLOSEDBY'字符'][ESCAPEDBY'字符']][LINES[STARTINGBY'字符串'][TERMINATEDBY'字符串']][IGNOREnumberLINES][(col_name_or_user_var,...)][SETcol_name=expr,...]]2.2表備份LOADDATAINFILE語(yǔ)句用于高速地從一個(gè)文本文件中讀取行，并裝入一個(gè)表中。也可以通過(guò)使用mysqlimport應(yīng)用程序載入數(shù)據(jù)文件，它通過(guò)向效勞器發(fā)送一個(gè)LOADDATAINFILE語(yǔ)句實(shí)現(xiàn)此功能。2.2表備份確定文件的字符集載入文件時(shí)，MySQL使用character_set_database系統(tǒng)變量所指示的字符集解釋文件中的數(shù)據(jù)。SETNAMES和character_set_client的設(shè)置不會(huì)影響對(duì)輸入的解釋。LOCAL如果指定了LOCAL，那么文件會(huì)被客戶主機(jī)上的客戶端讀取，并被發(fā)送到效勞器。文件會(huì)被給予一個(gè)完整的路徑名稱，以指定確切的位置。如果給定的是一個(gè)相對(duì)的路徑名稱，那么此名稱會(huì)被理解為相對(duì)于啟動(dòng)客戶端時(shí)所在的目錄。2.2表備份LOW_PRIORITY如果使用LOW_PRIORITY，那么LOADDATA語(yǔ)句的執(zhí)行被延遲，直到?jīng)]有其它的客戶端從表中讀取為止。CONCURRENT如果一個(gè)MyISAM表滿足同時(shí)插入的條件〔即該表在中間有空閑塊〕，并且對(duì)這個(gè)MyISAM表指定了CONCURRENT，那么當(dāng)LOADDATA正在執(zhí)行時(shí)，其它線程會(huì)從表中重新獲取數(shù)據(jù)。不過(guò)，使用本選項(xiàng)也會(huì)略微影響LOADDATA的性能，即使沒(méi)有其它線程在同時(shí)使用本表格。2.2表備份如何定位文件當(dāng)在效勞器主機(jī)上為文件定位時(shí)，效勞器使用以下規(guī)那么：如果給定了一個(gè)絕對(duì)的路徑名稱，那么效勞器使用此路徑名稱。如果給定了帶有一個(gè)或多個(gè)引導(dǎo)組件的相對(duì)路徑名稱，那么效勞器會(huì)搜索相對(duì)于效勞器數(shù)據(jù)目錄的文件。如果給定了一個(gè)不帶引導(dǎo)組件的文件名稱，那么效勞器會(huì)在默認(rèn)數(shù)據(jù)庫(kù)的數(shù)據(jù)庫(kù)目錄中尋找文件。2.2表備份REPLACE和IGNORE有些輸入記錄把原有的記錄復(fù)制到唯一關(guān)鍵字值上。REPLACE和IGNORE關(guān)鍵字用于控制這些輸入記錄的操作。如果指定了REPLACE，那么輸入行會(huì)替換原有行〔換句話說(shuō)，與原有行一樣，對(duì)一個(gè)主索引或唯一索引具有相同值的行〕。2.2表備份如果指定IGNORE，那么把原有行復(fù)制到唯一關(guān)鍵字值的輸入行被跳過(guò)。如果這兩個(gè)選項(xiàng)都不指定，那么運(yùn)行情況根據(jù)LOCAL關(guān)鍵詞是否被指定而定。不使用LOCAL時(shí)，當(dāng)出現(xiàn)重復(fù)關(guān)鍵字值時(shí)，會(huì)發(fā)生錯(cuò)誤，并且剩下的文本文件被忽略。使用LOCAL時(shí)，默認(rèn)的運(yùn)行情況和IGNORE被指定時(shí)的情況相同；這是因?yàn)樵谶\(yùn)行中間，效勞器沒(méi)有方法中止文件的傳輸。2.2表備份FIELDS和LINESFIELDS和LINES子句的語(yǔ)法對(duì)于兩個(gè)語(yǔ)句是一樣的。兩個(gè)子句都是自選的，但是如果兩個(gè)都被指定了，F(xiàn)IELDS必須位于LINES的前面。2.2表備份IGNOREIGNORE選項(xiàng)用于在文件的開始處忽略指定數(shù)量的行。例如，可以使用IGNORE1LINES來(lái)跳過(guò)一個(gè)包含列名稱的起始標(biāo)題行：mysql>LOADDATAINFILE'/tmp/test.txt‘->INTOTABLEtestIGNORE1LINES;2.2表備份BACKUPTABLEBACKUPTABLE表名1[,表名2]...TO'/文件路徑/文件名'本語(yǔ)句提供在線備份能力，但MySQL不推薦使用這種方法，如果可能的話，應(yīng)盡量使用mysqlhotcopy原本替代本語(yǔ)句。2.2表備份BACKUPTABLE用于在刷新了所有對(duì)磁盤的緩沖變更后，把恢復(fù)表所需的最少數(shù)目的表文件拷貝到備份目錄中。本語(yǔ)句只對(duì)MyISAM表起作用。它可以拷貝.frm定義文件和.MYD數(shù)據(jù)文件。.MYI索引文件可以從這兩個(gè)文件中重建。本目錄應(yīng)被指定為一個(gè)完整的路徑名。2.2表備份該語(yǔ)句的返回一張表，表中每列的列名及其含義如下：Table：表名稱。Op：進(jìn)行備份。Msg_type：狀態(tài)、錯(cuò)誤、信息或警告之一。Msg_text：消息。2.3增量備份增量備份是在某一次完全備份的根底，只備份其后數(shù)據(jù)的變化。如果mysqld在運(yùn)行那么停止，然后用--log-bin[=file_name]選項(xiàng)來(lái)啟動(dòng)。二進(jìn)制日志文件中提供了執(zhí)行mysqldump之后對(duì)數(shù)據(jù)庫(kù)的更改進(jìn)行復(fù)制所需要的信息。2.3增量備份MySQL支持增量備份:需要用--log-bin選項(xiàng)來(lái)啟動(dòng)效勞器以便啟用二進(jìn)制日志。當(dāng)想要進(jìn)行增量備份時(shí)(包含上一次完全備份或增量備份之后的所有更改)，應(yīng)使用FLUSHLOGS回滾二進(jìn)制日志。然后，需要將從最后的完全或增量備份的某個(gè)時(shí)刻到最后某個(gè)點(diǎn)的所有二進(jìn)制日志復(fù)制到備份位置。這些二進(jìn)制日志為增量備份；恢復(fù)時(shí)，按照下面的解釋應(yīng)用。下次進(jìn)行完全備份時(shí)，還應(yīng)使用FLUSHLOGS或mysqlhotcopy--flushlogs回滾二進(jìn)制日志。2.3增量備份如果必須恢復(fù)MyISAM表，先使用REPAIRTABLE或myisamchk-r來(lái)恢復(fù)。99.9%的情況下該方法可以工作。如果myisamchk失敗，試試下面的方法。請(qǐng)注意只有用--log-bin選項(xiàng)啟動(dòng)了MySQL從而啟用二進(jìn)制日志它才工作?；謴?fù)原mysqldump備份，或二進(jìn)制備份。執(zhí)行下面的命令重新更新二進(jìn)制日志：shell>mysqlbinloghostname-bin.[0-9]*|mysql2.3增量備份二進(jìn)制日志二進(jìn)制日志以一種更有效的格式，并且是事務(wù)平安的方式包含更新日志中可用的所有信息。二進(jìn)制日志包含了所有更新了數(shù)據(jù)或者已經(jīng)潛在更新了數(shù)據(jù)〔例如，沒(méi)有匹配任何行的一個(gè)DELETE〕的所有語(yǔ)句。語(yǔ)句以“事件”的形式保存，它描述數(shù)據(jù)更改。2.3增量備份mysqlbinlog要想檢查二進(jìn)制日志文件，應(yīng)使用mysqlbinlog實(shí)用工具。Mysqlbinlog的調(diào)用方法如下：shell>mysqlbinlog[選項(xiàng)]二進(jìn)制文件名...2.3增量備份通常情況，可以使用mysqlbinlog直接讀取二進(jìn)制日志文件并將它們用于本地MySQL效勞器。也可以使用--read-from-remote-server選項(xiàng)從遠(yuǎn)程效勞器讀取二進(jìn)制日志。當(dāng)讀取遠(yuǎn)程二進(jìn)制日志時(shí)，可以通過(guò)連接參數(shù)選項(xiàng)來(lái)指示如何連接效勞器，但它們經(jīng)常被忽略掉，除非還指定了--read-from-remote-server選項(xiàng)。這些選項(xiàng)是--host、--password、--port、--protocol、--socket