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

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

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

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

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

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

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

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

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

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

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

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

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

14、CT 列名列表 INTO OUTFILE|DUMPFILE 文件名 輸出選項 FROM表名 其它SELECT子句,2.2 表備份,SELECT.INTO OUTFILE語句的主要作用是快速地把一個表轉儲到服務器機器上。如果想在服務器主機之外的部分客戶主機上創建結果文件，不能使用此語句，此時應該在客戶主機上使用如“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語句用于高速地從一個文本文件中讀取行，并裝入一個表中。也可以通過使用mysqlimport應用程序載入數據文件，它通過向服務器發送一個LOAD DATA INFILE語句實現此功能。,2.2 表備份,確定文件的字符集 載入文件時，My

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

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

18、NORE 有些輸入記錄把原有的記錄復制到唯一關鍵字值上。REPLACE和IGNORE關鍵字用于控制這些輸入記錄的操作。 如果指定了REPLACE，則輸入行會替換原有行（換句話說，與原有行一樣，對一個主索引或唯一索引具有相同值的行）。,2.2 表備份,如果指定IGNORE，則把原有行復制到唯一關鍵字值的輸入行被跳過。如果這兩個選項都不指定，則運行情況根據LOCAL關鍵詞是否被指定而定。不使用LOCAL時，當出現重復關鍵字值時，會發生錯誤，并且剩下的文本文件被忽略。使用LOCAL時，默認的運行情況和IGNORE被指定時的情況相同；這是因為在運行中間，服務器沒有辦法中止文件的傳輸。,2.2 表備份,

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

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

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

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

23、日志中可用的所有信息。 二進制日志包含了所有更新了數據或者已經潛在更新了數據（例如，沒有匹配任何行的一個DELETE）的所有語句。語句以“事件”的形式保存，它描述數據更改。,2.3 增量備份,mysqlbinlog 要想檢查二進制日志文件，應使用mysqlbinlog實用工具。Mysqlbinlog的調用方法如下： shell mysqlbinlog 選項 二進制文件名.,2.3 增量備份,通常情況，可以使用mysqlbinlog直接讀取二進制日志文件并將它們用于本地MySQL服務器。也可以使用-read-from-remote-server選項從遠程服務器讀取二進制日志。 當讀取遠程二進制日志時，可以通過連接參數選項來指示如何連接服務器，但它們經常被忽略掉，除非還指定了-read-from-remote-server選項。這些選項是-host、-password、-port、-protocol、-socket和-user。,2.3 增量備份,可以將mysq