MySQL MyISAM和InNodb備份與恢復(fù)技巧

1. 為什么要備份數(shù)據(jù)庫

對數(shù)據(jù)庫來說，最重要也最容易被忽視的就是備份。由于不可預(yù)測性，偶然的事件可能會(huì)導(dǎo)致非常慘重的損失。

數(shù)據(jù)越是重要，數(shù)據(jù)的變化越頻繁，備份越發(fā)需要經(jīng)常進(jìn)行。

備份周期根據(jù)不同業(yè)務(wù)的需要可以調(diào)整，但是不能忽視備份。

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

2. MyISAM 表備份/恢復(fù)策略

2.1. 文件熱備份

2.1.1. 拷貝文件

因?yàn)?a class="contentlabel" href="http://www.104case.com/news/listbylabel/label/MySQL">MySQL表保存為文件方式，很容易備份。要想保持備份的一致性，對相關(guān)表執(zhí)行LOCK TABLES操作，然后對表執(zhí)行FLUSH TABLES。

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

標(biāo)準(zhǔn)流程：鎖表-》刷新表到磁盤-》拷貝文件-》解鎖。

2.1.2. 使用sql 語句備份

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

對于BACKUP TABLE也如此，因?yàn)楦采w完整的文件會(huì)有安全風(fēng)險(xiǎn)。

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

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

2.1.3. 使用mysqlhotcopy 備份

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

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

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

備份給定數(shù)據(jù)庫中的匹配正則表達(dá)式的表：

shell> mysqlhotcopy db_name./regex/

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

shell> mysqlhotcopy db_name./~regex/

mysqlhotcopy支持下面的選項(xiàng)：

· ---help，-?

顯示幫助消息并退出。

· --allowold

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

· --checkpoint=db_name.tbl_name

在指定的db_name.tbl_name插入檢查點(diǎn)條目。

· ---debug

啟用調(diào)試輸出。

· --dryrun，-n

報(bào)告動(dòng)作而不執(zhí)行它們。

· --flushlog

所有表鎖定后刷新日志。

· --keepold

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

· -- method=command

復(fù)制方法(cp或scp)。

· --noindices

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

· --password=password，-p password

當(dāng)連接服務(wù)器時(shí)使用的密碼。請注意該選項(xiàng)的密碼值是不可選的，不象其它MySQL程序。

· --port=port_num，-P port_num

當(dāng)連接本地服務(wù)器時(shí)使用的TCP/IP端口號。

· --quiet，-q

除了出現(xiàn)錯(cuò)誤時(shí)保持沉默。

· --regexp=expr

復(fù)制所有數(shù)據(jù)庫名匹配給出的正則表達(dá)式的數(shù)據(jù)庫。

· --socket=path，-S path

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

· --suffix=str

所復(fù)制的數(shù)據(jù)庫名的后綴。

· --tmpdir=path

臨時(shí)目錄(代替/tmp)。

· --user=user_name，-u user_name

當(dāng)連接服務(wù)器時(shí)使用的MySQL用戶名。

mysqlhotcopy從選項(xiàng)文件讀取[client]和[mysqlhotcopy]選項(xiàng)組。

因?yàn)閙ysqlhotcopy一般是用來做完全備份，所以推薦使用—flushlog選項(xiàng)來產(chǎn)生增量更新日志。

2.1.4. 使用mysqldump 備份

可以備份表結(jié)構(gòu)和數(shù)據(jù)，可以同時(shí)支持MyISAM和InnoDB引擎數(shù)據(jù)庫。

mysqldump可以備份單個(gè)表、單個(gè)庫或所有庫。

Mysqldump 還可以只導(dǎo)出表結(jié)構(gòu)。

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

有3種方式來調(diào)用mysqldump：

shell> mysqldump [options] db_name [tables]

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

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

如果沒有指定任何表或使用了---database或--all--database選項(xiàng)，則轉(zhuǎn)儲(chǔ)整個(gè)數(shù)據(jù)庫。

要想獲得你的版本的mysqldump支持的選項(xiàng)，執(zhí)行mysqldump ---help。

如果運(yùn)行mysqldump沒有--quick或--opt選項(xiàng)，mysqldump在轉(zhuǎn)儲(chǔ)結(jié)果前將整個(gè)結(jié)果集裝入內(nèi)存。如果轉(zhuǎn)儲(chǔ)大數(shù)據(jù)庫可能會(huì)出現(xiàn)問題。該選項(xiàng)默認(rèn)啟用，但可以用--skip-opt禁用。

mysqldump支持下面的選項(xiàng)：

· ---help，-?

顯示幫助消息并退出。

· --add-drop--database

在每個(gè)CREATE DATABASE語句前添加DROP DATABASE語句。

· --add-drop-tables

在每個(gè)CREATE TABLE語句前添加DROP TABLE語句。

· --add-locking

用LOCK TABLES和UNLOCK TABLES語句引用每個(gè)表轉(zhuǎn)儲(chǔ)。重載轉(zhuǎn)儲(chǔ)文件時(shí)插入得更快。

· --all--database，-A

轉(zhuǎn)儲(chǔ)所有數(shù)據(jù)庫中的所有表。與使用---database選項(xiàng)相同，在命令行中命名所有數(shù)據(jù)庫。

· --allow-keywords

允許創(chuàng)建關(guān)鍵字列名。應(yīng)在每個(gè)列名前面加上表名前綴。

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

如果設(shè)置為 0，禁止轉(zhuǎn)儲(chǔ)文件中的其它信息，例如程序版本、服務(wù)器版本和主機(jī)。--skip—comments與---comments=0的結(jié)果相同。 默認(rèn)值為1，即包括額外信息。

· --compact

產(chǎn)生少量輸出。該選項(xiàng)禁用注釋并啟用--skip-add-drop-tables、--no-set-names、--skip-disable-keys和--skip-add-locking選項(xiàng)。