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

1、MySQL備份與恢復(fù),課程目標(biāo),存儲引擎和表類型 數(shù)據(jù)庫的備份與恢復(fù) 完全備份 表備份 增量備份,概述,盡管數(shù)據(jù)庫系統(tǒng)中采取了各種保護(hù)措施來保證數(shù)據(jù)庫的安全性和完整性，但是計(jì)算機(jī)系統(tǒng)中的硬件故障、軟件錯(cuò)誤、操作失誤及惡意破壞仍是不可避免的，因此數(shù)據(jù)庫管理系統(tǒng)必須具有把數(shù)據(jù)庫從錯(cuò)誤狀態(tài)恢復(fù)到某一已知的正確狀態(tài)（亦稱為一致狀態(tài)或完整狀態(tài)）的功能，這就是數(shù)據(jù)庫的恢復(fù)。,概述,恢復(fù)子系統(tǒng)是數(shù)據(jù)庫管理系統(tǒng)的一個(gè)重要組成部分，而且還相當(dāng)龐大，常常占整個(gè)系統(tǒng)代碼的百分之十以上。數(shù)據(jù)庫系統(tǒng)所采用的恢復(fù)技術(shù)是否行之有效，不僅對系統(tǒng)的可靠程度起著決定性作用，而且對系統(tǒng)的運(yùn)行效率也有很大音響，是衡量系統(tǒng)性能優(yōu)劣的重

2、要指標(biāo)。,1. 存儲引擎和表類型,MySQL在保存數(shù)據(jù)庫時(shí)有很多種存儲方式，不同的存儲方式執(zhí)行同一SQL語句的效率可能會(huì)相差很多。考慮到在實(shí)際使用中，不同系統(tǒng)對數(shù)據(jù)庫的要求不盡相同，執(zhí)行的操作及每種操作執(zhí)行的頻率也都不一樣，為了進(jìn)一步提高數(shù)據(jù)庫的處理效率，MySQL將表類型劃分成很多種類，并允許對不同的數(shù)據(jù)庫選擇不同的存儲引擎。,1. 存儲引擎和表類型,MySQL插件式存儲引擎的體系結(jié)構(gòu),1. 存儲引擎和表類型,MySQL的存儲引擎包括以下類型： MyISAM管理非事務(wù)表。它提供高速存儲和檢索，以及全文搜索能力。MyISAM在所有MySQL配置里被支持，它是默認(rèn)的存儲引擎，除非配置MySQL默

3、認(rèn)使用另外一個(gè)引擎。 MEMORY存儲引擎提供“內(nèi)存中”表。MERGE存儲引擎允許集合將被處理同樣的MyISAM表作為一個(gè)單獨(dú)的表。就像MyISAM一樣，MEMORY和MERGE存儲引擎處理非事務(wù)表，這兩個(gè)引擎也都被默認(rèn)包含在MySQL中。,1. 存儲引擎和表類型,InnoDB和BDB存儲引擎提供事務(wù)安全表。BDB被包含在為支持它的操作系統(tǒng)發(fā)布的MySQL-Max二進(jìn)制分發(fā)版里。InnoDB也默認(rèn)被包括在所有MySQL 5.1二進(jìn)制分發(fā)版里，可以按照喜好通過配置MySQL來允許或禁止任一引擎。 EXAMPLE存儲引擎是一個(gè)“存根”引擎，它不做什么?？梢杂眠@個(gè)引擎創(chuàng)建表，但沒有數(shù)據(jù)被存儲于其中或

4、從其中檢索。這個(gè)引擎的目的是服務(wù)。同樣，它的主要興趣是對開發(fā)者。,1. 存儲引擎和表類型,NDB Cluster是被MySQL Cluster用來實(shí)現(xiàn)分割到多臺計(jì)算機(jī)上的表的存儲引擎。它在MySQL-Max 5.1二進(jìn)制分發(fā)版里提供。這個(gè)存儲引擎只被Linux, Solaris, 和Mac OS X 支持。 ARCHIVE存儲引擎被用來無索引地，非常小地覆蓋存儲的大量數(shù)據(jù)。,1. 存儲引擎和表類型,CSV存儲引擎把數(shù)據(jù)以逗號分隔的格式存儲在文本文件中。 BLACKHOLE存儲引擎接受但不存儲數(shù)據(jù)，并且檢索總是返回一個(gè)空集。 FEDERATED存儲引擎把數(shù)據(jù)存在遠(yuǎn)程數(shù)據(jù)庫中。在MySQL 5.1

5、中，它只和MySQL一起工作，使用MySQL C Client API。在未來的分發(fā)版中，我們想要讓它使用其它驅(qū)動(dòng)器或客戶端連接方法連接到另外的數(shù)據(jù)源。,1. 存儲引擎和表類型,當(dāng)創(chuàng)建一個(gè)新表的時(shí)候，可以通過使用ENGINE 或TYPE 選項(xiàng)到來指定要?jiǎng)?chuàng)建什么類型的表： CREATE TABLE t (i INT) ENGINE = INNODB;,1. 存儲引擎和表類型,如果省略掉ENGINE或TYPE選項(xiàng)，默認(rèn)是使用MyISAM引擎。要想修改默認(rèn)的引擎可以用-default-storage-engine或-default-table-type服務(wù)器啟動(dòng)選項(xiàng)來進(jìn)行設(shè)置，或者通過設(shè)置stora

6、ge_engine或table_type系統(tǒng)變量來改變。,1. 存儲引擎和表類型,1.1 MyISAM MyISAM是默認(rèn)存儲引擎。它是在早期MySQL的ISAM引擎基礎(chǔ)上，經(jīng)過一定的擴(kuò)展而產(chǎn)生的。（MySQL 5.1不支持ISAM）。 MyISAM類型的表在磁盤上存儲成三個(gè)文件。,1. 存儲引擎和表類型,這三個(gè)文件的名稱相同，為該表的表名，僅擴(kuò)展名不同，各文件作用如下： .frm文件存儲表定義。 .MYD (MYData)文件存儲表中數(shù)據(jù)。 .MYI (MYIndex)文件存儲表上建立的索引。,1. 存儲引擎和表類型,1.2 InnoDB InnoDB給MySQL提供了具有提交，回滾和崩潰恢

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

8、次插入/更新的負(fù)荷。,2.數(shù)據(jù)庫備份,在MySQL中備份數(shù)據(jù)庫的方法很多，可分為以下幾種： 完全備份：將數(shù)據(jù)庫中的數(shù)據(jù)及所有對象全部進(jìn)行備份。 表備份：僅將一張或多張表中的數(shù)據(jù)進(jìn)行備份。 增量備份：在某一次完全備份的基礎(chǔ)，只備份其后數(shù)據(jù)的變化。,2.數(shù)據(jù)庫備份,2.1 完全備份 完全備份最簡單也最快速的方法是拷貝數(shù)據(jù)庫文件夾，當(dāng)然在拷貝時(shí)對MySQL會(huì)有些要求。也可以使用mysqldump程序或mysqlhotcopy腳本對數(shù)據(jù)庫進(jìn)行SQL語句級別的備份，它們速度要稍微慢一些，不過通用性更強(qiáng)。,2.1 完全備份,使用拷貝文件夾的方式備份 因?yàn)镸ySQL表保存為文件方式，所以理論上來說可以通過拷

9、貝文件的方式對數(shù)據(jù)庫進(jìn)行備份。不過，使用直接備份方法時(shí)，必須要保證表在拷貝期間不被使用。如果不能滿足這一條件，拷貝的文件就可能存在數(shù)據(jù)的不一致性，拷貝操作也就失敗了。,2.1 完全備份,最可靠的方法是在拷貝以前關(guān)閉MySQL服務(wù)，拷貝完成后再重新啟動(dòng)服務(wù)。但有些應(yīng)用會(huì)要求24小時(shí)不間斷服務(wù)，此時(shí)關(guān)閉服務(wù)器的方法就不很合適，不過MySQL提供了表的鎖定和解鎖的相關(guān)操作，以確保在拷貝文件期間該文件不會(huì)被修改。,2.1 完全備份,使用鎖定機(jī)制備份表的基本步驟為： 使用LOCK TABLES命令鎖定某一表或多個(gè)表； 拷貝對應(yīng)的文件； 使用UNLOCK TABLES解鎖已拷貝完的表。,2.1 完全備份,

10、Mysqldump Mysqldump可以在MySQL安裝目錄中找到，該程序用于轉(zhuǎn)儲數(shù)據(jù)庫或搜集數(shù)據(jù)庫進(jìn)行備份或?qū)?shù)據(jù)轉(zhuǎn)移到另一個(gè)支持SQL語句的服務(wù)器(不一定是一個(gè)MySQL服務(wù)器)。備份后產(chǎn)生的備份文件是一個(gè)文本文件，文件內(nèi)容為創(chuàng)建表和往表中插入數(shù)據(jù)的SQL語句。,2.1 完全備份,有3種方式來調(diào)用mysqldump程序： 1備份一個(gè)數(shù)據(jù)庫或一張表，語法如下： shell mysqldump 選項(xiàng) 數(shù)據(jù)庫名表名 2同時(shí)備份多個(gè)數(shù)據(jù)庫，語法如下： shell mysqldump 選項(xiàng) -database 數(shù)據(jù)庫1 數(shù)據(jù)庫2 數(shù)據(jù)庫3. 3備份服務(wù)器上所有的數(shù)據(jù)庫，語法如下： shell my

11、sqldump 選項(xiàng) -all-database,2.1 完全備份,mysqldump最常用于備份一個(gè)完整的數(shù)據(jù)庫，備份一個(gè)數(shù)據(jù)庫的基本語法如下： shell mysqldump -opt 數(shù)據(jù)庫名 備份文件名.sql 可以使用以下語法將備份文件讀回到服務(wù)器： shell mysql 數(shù)據(jù)庫名 mysql -e 備份文件路徑/備份文件名.sql 數(shù)據(jù)庫名,2.1 完全備份,mysqldump也可用于從一個(gè)MySQL服務(wù)器向另一個(gè)服務(wù)器復(fù)制數(shù)據(jù)時(shí)裝載數(shù)據(jù)庫，其語法如下： shell mysqldump -opt 數(shù)據(jù)庫名 | mysql -host=其它服務(wù)器主機(jī)名 -C 數(shù)據(jù)庫名 一次備份多個(gè)

12、數(shù)據(jù)庫的語法如下： shell mysqldump -database 數(shù)據(jù)庫名1 數(shù)據(jù)庫名2 . 備份文件名.sql 使用-all-database選項(xiàng)可以一次備份服務(wù)器上所有的數(shù)據(jù)庫： shell mysqldump -all-databases 備份文件名.sql,2.1 完全備份,mysqlhotcopy 使用mysqlhotcopy進(jìn)行備份是備份數(shù)據(jù)庫或單個(gè)表的最快的途徑。mysqlhotcopy是一個(gè)Perl腳本，最初由Tim Bunce編寫并提供。它使用LOCK TABLES、FLUSH TABLES和cp或scp來快速備份數(shù)據(jù)庫。,2.1 完全備份,Mysqlhotcopy對使

13、用場合有一定限制，它只能運(yùn)行在數(shù)據(jù)庫目錄所在的機(jī)器上，并且只能用于備份MyISAM數(shù)據(jù)庫，最后，它只能運(yùn)行在Unix和NetWare中。 Mysqlhotcopy使用的基本語法如下： shell mysqlhotcopy 數(shù)據(jù)庫名 /路徑/備份目錄 shell mysqlhotcopy 數(shù)據(jù)庫名1，數(shù)據(jù)庫名2 . /路徑/備份目錄,2.2 表備份,如果只想對數(shù)據(jù)庫中的某些表進(jìn)行備份，可以使用 SELECT INTO .OUTFILE 或BACKUP TABLE語句。,2.2 表備份,SELECT INTO .OUTFILE 此語法是MySQL對SELECT語句的擴(kuò)展應(yīng)用，其用法如下： SELE

14、CT 列名列表 INTO OUTFILE|DUMPFILE 文件名 輸出選項(xiàng) FROM表名 其它SELECT子句,2.2 表備份,SELECT.INTO OUTFILE語句的主要作用是快速地把一個(gè)表轉(zhuǎn)儲到服務(wù)器機(jī)器上。如果想在服務(wù)器主機(jī)之外的部分客戶主機(jī)上創(chuàng)建結(jié)果文件，不能使用此語句，此時(shí)應(yīng)該在客戶主機(jī)上使用如“mysql e SELECT . file_name”的命令，來生成文件。,2.2 表備份,LOAD DATA LOW_PRIORITY | CONCURRENT LOCAL INFILE 文件名 REPLACE | IGNORE INTO TABLE 表名 FIELDS TERMIN

15、ATED BY 字符串 OPTIONALLY ENCLOSED BY 字符 ESCAPED BY 字符 LINES STARTING BY 字符串 TERMINATED BY 字符串 IGNORE number LINES (col_name_or_user_var,.) SET col_name = expr,.,2.2 表備份,LOAD DATA INFILE語句用于高速地從一個(gè)文本文件中讀取行，并裝入一個(gè)表中。也可以通過使用mysqlimport應(yīng)用程序載入數(shù)據(jù)文件，它通過向服務(wù)器發(fā)送一個(gè)LOAD DATA INFILE語句實(shí)現(xiàn)此功能。,2.2 表備份,確定文件的字符集 載入文件時(shí)，My

16、SQL使用character_set_database系統(tǒng)變量所指示的字符集解釋文件中的數(shù)據(jù)。SET NAMES和character_set_client的設(shè)置不會(huì)影響對輸入的解釋。 LOCAL 如果指定了LOCAL，則文件會(huì)被客戶主機(jī)上的客戶端讀取，并被發(fā)送到服務(wù)器。文件會(huì)被給予一個(gè)完整的路徑名稱，以指定確切的位置。如果給定的是一個(gè)相對的路徑名稱，則此名稱會(huì)被理解為相對于啟動(dòng)客戶端時(shí)所在的目錄。,2.2 表備份,LOW_PRIORITY 如果使用LOW_PRIORITY，則LOAD DATA語句的執(zhí)行被延遲，直到?jīng)]有其它的客戶端從表中讀取為止。 CONCURRENT 如果一個(gè)MyISAM表滿

17、足同時(shí)插入的條件（即該表在中間有空閑塊），并且對這個(gè)MyISAM表指定了CONCURRENT，則當(dāng)LOAD DATA正在執(zhí)行時(shí)，其它線程會(huì)從表中重新獲取數(shù)據(jù)。不過，使用本選項(xiàng)也會(huì)略微影響LOAD DATA的性能，即使沒有其它線程在同時(shí)使用本表格。,2.2 表備份,如何定位文件 當(dāng)在服務(wù)器主機(jī)上為文件定位時(shí)，服務(wù)器使用以下規(guī)則： 如果給定了一個(gè)絕對的路徑名稱，則服務(wù)器使用此路徑名稱。 如果給定了帶有一個(gè)或多個(gè)引導(dǎo)組件的相對路徑名稱，則服務(wù)器會(huì)搜索相對于服務(wù)器數(shù)據(jù)目錄的文件。 如果給定了一個(gè)不帶引導(dǎo)組件的文件名稱，則服務(wù)器會(huì)在默認(rèn)數(shù)據(jù)庫的數(shù)據(jù)庫目錄中尋找文件。,2.2 表備份,REPLACE和IG

18、NORE 有些輸入記錄把原有的記錄復(fù)制到唯一關(guān)鍵字值上。REPLACE和IGNORE關(guān)鍵字用于控制這些輸入記錄的操作。 如果指定了REPLACE，則輸入行會(huì)替換原有行（換句話說，與原有行一樣，對一個(gè)主索引或唯一索引具有相同值的行）。,2.2 表備份,如果指定IGNORE，則把原有行復(fù)制到唯一關(guān)鍵字值的輸入行被跳過。如果這兩個(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)行中間，服務(wù)器沒有辦法中止文件的傳輸。,2.2 表備份,

19、FIELDS和LINES FIELDS和LINES子句的語法對于兩個(gè)語句是一樣的。兩個(gè)子句都是自選的，但是如果兩個(gè)都被指定了，F(xiàn)IELDS必須位于LINES的前面。,2.2 表備份,IGNORE IGNORE選項(xiàng)用于在文件的開始處忽略指定數(shù)量的行。例如，可以使用IGNORE 1 LINES來跳過一個(gè)包含列名稱的起始標(biāo)題行： mysql LOAD DATA INFILE /tmp/test.txt - INTO TABLE test IGNORE 1 LINES;,2.2 表備份,BACKUP TABLE BACKUP TABLE 表名1 ,表名2 . TO /文件路徑/文件名 本語句提供在線備

20、份能力，但MySQL不推薦使用這種方法，如果可能的話，應(yīng)盡量使用mysqlhotcopy原本替代本語句。,2.2 表備份,BACKUP TABLE用于在刷新了所有對磁盤的緩沖變更后，把恢復(fù)表所需的最少數(shù)目的表文件拷貝到備份目錄中。本語句只對MyISAM表起作用。它可以拷貝.frm定義文件和.MYD數(shù)據(jù)文件。.MYI索引文件可以從這兩個(gè)文件中重建。本目錄應(yīng)被指定為一個(gè)完整的路徑名。,2.2 表備份,該語句的返回一張表，表中每列的列名及其含義如下： Table：表名稱。 Op：進(jìn)行備份。 Msg_type：狀態(tài)、錯(cuò)誤、信息或警告之一。 Msg_text：消息。,2.3 增量備份,增量備份是在某一次

21、完全備份的基礎(chǔ)，只備份其后數(shù)據(jù)的變化。 如果mysqld在運(yùn)行則停止，然后用-log-bin=file_name選項(xiàng)來啟動(dòng)。二進(jìn)制日志文件中提供了執(zhí)行mysqldump之后對數(shù)據(jù)庫的更改進(jìn)行復(fù)制所需要的信息。,2.3 增量備份,MySQL支持增量備份:需要用-log-bin選項(xiàng)來啟動(dòng)服務(wù)器以便啟用二進(jìn)制日志。當(dāng)想要進(jìn)行增量備份時(shí)(包含上一次完全備份或增量備份之后的所有更改)，應(yīng)使用FLUSH LOGS回滾二進(jìn)制日志。然后，需要將從最后的完全或增量備份的某個(gè)時(shí)刻到最后某個(gè)點(diǎn)的所有二進(jìn)制日志復(fù)制到備份位置。這些二進(jìn)制日志為增量備份；恢復(fù)時(shí)，按照下面的解釋應(yīng)用。下次進(jìn)行完全備份時(shí)，還應(yīng)使用FLUSH

22、 LOGS或mysqlhotcopy -flushlogs回滾二進(jìn)制日志。,2.3 增量備份,如果必須恢復(fù)MyISAM表，先使用REPAIR TABLE或myisamchk -r來恢復(fù)。99.9%的情況下該方法可以工作。如果myisamchk失敗，試試下面的方法。請注意只有用-log-bin選項(xiàng)啟動(dòng)了MySQL從而啟用二進(jìn)制日志它才工作。 恢復(fù)原mysqldump備份，或二進(jìn)制備份。 執(zhí)行下面的命令重新更新二進(jìn)制日志： shell mysqlbinlog hostname-bin.0-9* | mysql,2.3 增量備份,二進(jìn)制日志 二進(jìn)制日志以一種更有效的格式，并且是事務(wù)安全的方式包含更新

23、日志中可用的所有信息。 二進(jìn)制日志包含了所有更新了數(shù)據(jù)或者已經(jīng)潛在更新了數(shù)據(jù)（例如，沒有匹配任何行的一個(gè)DELETE）的所有語句。語句以“事件”的形式保存，它描述數(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服務(wù)器。也可以使用-read-from-remote-server選項(xiàng)從遠(yuǎn)程服務(wù)器讀取二進(jìn)制日志。 當(dāng)讀取遠(yuǎn)程二進(jìn)制日志時(shí)，可以通過連接參數(shù)選項(xiàng)來指示如何連接服務(wù)器，但它們經(jīng)常被忽略掉，除非還指定了-read-from-remote-server選項(xiàng)。這些選項(xiàng)是-host、-password、-port、-protocol、-socket和-user。,2.3 增量備份,可以將mysq