MySQL不同儲存引擎的資料備份與恢復

資料備份的目的很直接也很簡單，就是為了避免因不可預測、偶然的事件而導致的慘重損失，所以資料越重要、變化越頻繁，就越要進行資料備份。在之前的幾篇文章中，我們以MySQL為例對資料備份進行了粗略的解讀，本文我們依然以MySQL為例，講講面對不同的儲存引擎如何做資料備份與恢復。

為了應對不同的資料處理，MySQL提供了十幾種不同的儲存引擎，不過，我們沒有必要一一去了解，因為熟悉使用MySQL的人都知道，比較常用的儲存引擎有兩個，分別是MyISAM和InnoDB。

• MyISAM是MySQL的ISAM擴充套件格式和預設的資料庫引擎，不支援事務、也不支援外來鍵，但其優勢在於訪問速度快，對事務完整性沒有要求，以select，insert為主的應用基本上可以用這個引擎來建立表。常用於高讀取的應用場景資料庫，支援三種不同型別的儲存結構：靜態型、動態型、壓縮型。

• InnoDB提供了具有提交、回滾和崩潰恢復能力的事務安全，支援自動增長列，支援外來鍵約束。對比MyISAM引擎，InnoDB寫的處理效率會差一些，並且會佔用更多的磁碟空間以保留資料和索引。 

瞭解了MySQL常用的兩種儲存引擎之後，我們就來看看在這兩種引擎中如何進行資料備份和恢復。

MyISAM資料備份

因為MyISAM是儲存成檔案的形式，所以在備份時有多種方法可以使用，並且大多數虛擬主機提供商和INTERNET平臺提供商只允許使用MyISAM格式，掌握MyISAM資料備份就格外重要了。

方法1：檔案拷貝

為了保持資料備份的一致性，我們可以對相關表執行LOCK TABLES操作，對錶執行FLUSH TABLES。當然，你只需要限制寫操作，這樣能夠保證在複製資料時，其它操作仍然可以查詢表，而FLUSH TABLES是用來確保開始備份前將所有啟用的索引頁寫入硬碟。

標準流程：鎖表、重新整理表到磁碟、拷貝檔案、解鎖。

方法2：SQL語句備份

SELECT INTO ...OUTFILE或BACKUP TABLE都可以進行SQL級別的表備份，需要注意的是這兩種方法如果有重名檔案，最好是先移除重名檔案。另外，BACKUP TABLE備份需要注意輸出目錄的許可權，改方法只是備份MYD和frm檔案，不備份索引。

方法3： mysqlhotcopy  備份

mysqlhotcopy 是一個 Perl指令碼，使用LOCK TABLES、FLUSH TABLES和cp或scp來快速備份資料庫，但其只能執行在資料庫目錄所在的機器上，且只用於備份MyISAM。

shell> mysqlhotcopy db_name [/path/to/new_directory ]

shell> mysqlhotcopy db_name_1 ... db_name_n /path/to/new_directory

方法4： mysqldump  備份

Mysqldump既可以備份表結構和資料，也可以備份單個表、單個庫或者所有庫，輸出是SQL語句檔案或者是其它資料庫相容的格式。在之前的文章中，我們較詳細的介紹了Mysqldump，本文就不再贅述了。

shell> mysqldump [options] db_name [tables]

shell> mysqldump [options] ---database DB1 [DB2 DB3...]

shell> mysqldump [options] --all—database

方法5：冷備份

冷備份的方法就很簡單粗暴了，在MySQL伺服器停止服務時，複製所有表檔案。

MyISAM資料備份恢復

不同的備份方式有相應的恢復方法：

• 如果是mysqldump備份，恢復方法是mysql –u root < 備份檔名；

• 如果是mysqlhotcopy或檔案冷/熱拷貝備份，恢復方法是停止MySQL服務，並用備份檔案覆蓋現有檔案；

• 如果是BACKUP TABLE備份，使用restore table來恢復；

• 如果是SELECT INTO ...OUTFILE備份，使用load data恢復資料或mysqlimport命令。

InnoDB資料備份

MyISAM不支援事務和外來鍵，這使得MySQL使用者往往會面臨一些挑戰，所以，理所當然的當InnoDB支援事務和外來鍵時，即使它的速度較慢，也還是獲得了MySQL使用者的青睞。

方法1：mysqldump

是不是看著很眼熟，沒錯兒，上面MyISAM資料備份的方法其中之一就是它。Mysqldump也可以對InnoDB提供非物理的線上邏輯熱備份，使用方法和MyISAM類似。

方法2：copy file

InnoDB底層儲存的時候會將資料和元資訊存在ibdata*, *.ibd, *.frm, *.ib_logfile*等檔案中，所以備份了這些檔案就相當於備份了InnoDB資料。

方法3：select into

與MyISAM用法一樣。

方法4：商業工具

InnoDB資料備份有很多商業工具可以使用，例如InnoDB Hotbackup，這是一個線上備份工具，即可以在InnoDB資料庫執行時備份InnoDB資料庫； ibbackup，將線上的my.cnf所指向的的資料內容備份到my.backup.cnf指向的資料目錄。

InnoDB資料備份恢復

在使用特定的恢復方法之前，InnoDB資料備份恢復還有兩個通用的方法，分別是InnoDB的日誌自動恢復功能，即重啟mysql服務和“萬能大法”——重啟計算機。

• 如果是mysqldump完全備份，先恢復完全備份，然後再恢復完全備份後的增量日誌備份。

• 如果是select into備份表，則採用load data或mysqlimport恢復。

• 如果是copy file，那麼就停止MySQL服務，備份檔案覆蓋當前檔案，並執行上次完全備份後的增量日誌備份。

【本文整理自多篇網路文章】 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31137683/viewspace-2213906/，如需轉載，請註明出處，否則將追究法律責任。