MySQL MyISAM和InNodb備份與恢復技巧

1. 為什么要備份數據庫

對數據庫來說，最重要也最容易被忽視的就是備份。由于不可預測性，偶然的事件可能會導致非常慘重的損失。

數據越是重要，數據的變化越頻繁，備份越發需要經常進行。

備份周期根據不同業務的需要可以調整，但是不能忽視備份。

備份時最好也備份my.cnf或my.ini，這樣可以保存你以前的配置參數。

2. MyISAM 表備份/恢復策略

2.1. 文件熱備份

2.1.1. 拷貝文件

因為MySQL表保存為文件方式，很容易備份。要想保持備份的一致性，對相關表執行LOCK TABLES操作，然后對表執行FLUSH TABLES。

你只需要讀鎖定;這樣當你復制數據庫目錄中的文件時，允許其它客戶繼續查詢表。需要FLUSH TABLES語句來確保開始備份前將所有激活的索引頁寫入硬盤。

標準流程：鎖表-》刷新表到磁盤-》拷貝文件-》解鎖。

2.1.2. 使用sql 語句備份

如果你想要進行SQL級別的表備份，你可以使用SELECT INTO ...OUTFILE或BACKUP TABLE。對于SELECT INTO ...OUTFILE， 輸出的文件不能先存在。

對于BACKUP TABLE也如此，因為覆蓋完整的文件會有安全風險。

這兩種備份方法，如果輸出文件有重名的話，最好把重名文件移除。

BACKUP TABLE備份時注意輸出目錄的權限，改方法只是備份MYD和frm文件，不備份索引。

2.1.3. 使用mysqlhotcopy 備份

mysqlhotcopy 是一個 Perl腳本，最初由Tim Bunce編寫并提供。它使用LOCK TABLES、FLUSH TABLES和cp或scp來快速備份數據庫。它是備份數據庫或單個表的最快的途徑，但它只能運行在數據庫目錄所在的機器上。mysqlhotcopy 只用于備份MyISAM。它運行在Unix和NetWare中。

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

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

備份給定數據庫中的匹配正則表達式的表：

shell> mysqlhotcopy db_name./regex/

加上發音符(‘~’)前綴，表名的正則表達式可以被否定：

shell> mysqlhotcopy db_name./~regex/

mysqlhotcopy支持下面的選項：

· ---help，-?

顯示幫助消息并退出。

· --allowold

如果目標存在不放棄(加上一個_old后綴重新命名它)。

· --checkpoint=db_name.tbl_name

在指定的db_name.tbl_name插入檢查點條目。

· ---debug

啟用調試輸出。

· --dryrun，-n

報告動作而不執行它們。

· --flushlog

所有表鎖定后刷新日志。

· --keepold

完成后不刪除以前(重新命名的)的目標。

· -- method=command

復制方法(cp或scp)。

· --noindices

備份中不包括全部索引文件。這樣使備份更小、更快。可以在以后用myisamchk -rq重新構建索引。

· --password=password，-p password

當連接服務器時使用的密碼。請注意該選項的密碼值是不可選的，不象其它MySQL程序。

· --port=port_num，-P port_num

當連接本地服務器時使用的TCP/IP端口號。

· --quiet，-q

除了出現錯誤時保持沉默。

· --regexp=expr

復制所有數據庫名匹配給出的正則表達式的數據庫。

· --socket=path，-S path

用于連接的Unix套接字文件。

· --suffix=str

所復制的數據庫名的后綴。

· --tmpdir=path

臨時目錄(代替/tmp)。

· --user=user_name，-u user_name

當連接服務器時使用的MySQL用戶名。

mysqlhotcopy從選項文件讀取[client]和[mysqlhotcopy]選項組。

因為mysqlhotcopy一般是用來做完全備份，所以推薦使用—flushlog選項來產生增量更新日志。

2.1.4. 使用mysqldump 備份

可以備份表結構和數據，可以同時支持MyISAM和InnoDB引擎數據庫。

mysqldump可以備份單個表、單個庫或所有庫。

Mysqldump 還可以只導出表結構。

mysqldump是邏輯備份，輸出的是sql語句文件，還可以輸出其他數據庫兼容的格式。

有3種方式來調用mysqldump：

shell> mysqldump [options] db_name [tables]

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

shell> mysqldump [options] --all--database

如果沒有指定任何表或使用了---database或--all--database選項，則轉儲整個數據庫。

要想獲得你的版本的mysqldump支持的選項，執行mysqldump ---help。

如果運行mysqldump沒有--quick或--opt選項，mysqldump在轉儲結果前將整個結果集裝入內存。如果轉儲大數據庫可能會出現問題。該選項默認啟用，但可以用--skip-opt禁用。

mysqldump支持下面的選項：

· ---help，-?

顯示幫助消息并退出。

· --add-drop--database

在每個CREATE DATABASE語句前添加DROP DATABASE語句。

· --add-drop-tables

在每個CREATE TABLE語句前添加DROP TABLE語句。

· --add-locking

用LOCK TABLES和UNLOCK TABLES語句引用每個表轉儲。重載轉儲文件時插入得更快。

· --all--database，-A

轉儲所有數據庫中的所有表。與使用---database選項相同，在命令行中命名所有數據庫。

· --allow-keywords

允許創建關鍵字列名。應在每個列名前面加上表名前綴。

· ---comments[={0|1}]

如果設置為 0，禁止轉儲文件中的其它信息，例如程序版本、服務器版本和主機。--skip—comments與---comments=0的結果相同。 默認值為1，即包括額外信息。

· --compact

產生少量輸出。該選項禁用注釋并啟用--skip-add-drop-tables、--no-set-names、--skip-disable-keys和--skip-add-locking選項。