第11章MySQL備份與恢復課程目標

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

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

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

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

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

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