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

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

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

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

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

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

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

2.1. 文件熱備份

2.1.1. 拷貝文件

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

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

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

2.1.2. 使用sql 語句備份

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

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

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

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

2.1.3. 使用mysqlhotcopy 備份

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

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

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

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

shell> mysqlhotcopy db_name./regex/

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

shell> mysqlhotcopy db_name./~regex/

mysqlhotcopy支持下面的選項：

· ---help，-?

顯示幫助消息并退出。

· --allowold

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

· --checkpoint=db_name.tbl_name

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

· ---debug

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

· --dryrun，-n

報告動作而不執(zhí)行它們。

· --flushlog

所有表鎖定后刷新日志。

· --keepold

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

· -- method=command

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

· --noindices

備份中不包括全部索引文件。這樣使備份更小、更快?？梢栽谝院笥胢yisamchk -rq重新構(gòu)建索引。

· --password=password，-p password

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

· --port=port_num，-P port_num

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

· --quiet，-q

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

· --regexp=expr

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

· --socket=path，-S path

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

· --suffix=str

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

· --tmpdir=path

臨時目錄(代替/tmp)。

· --user=user_name，-u user_name

當連接服務(wù)器時使用的MySQL用戶名。

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

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

2.1.4. 使用mysqldump 備份

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

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

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選項，則轉(zhuǎn)儲整個數(shù)據(jù)庫。

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

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

mysqldump支持下面的選項：

· ---help，-?

顯示幫助消息并退出。

· --add-drop--database

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

· --add-drop-tables

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

· --add-locking

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

· --all--database，-A

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

· --allow-keywords

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

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

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

· --compact

產(chǎn)生少量輸出。該選項禁用注釋并啟用--skip-add-drop-tables、--no-set-names、--skip-disable-keys和--skip-add-locking選項。[!--empirenews.page--]

· --compatible=name

產(chǎn)生與其它數(shù)據(jù)庫系統(tǒng)或舊的MySQL服務(wù)器更兼容的輸出。值可以為ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options或者no_field_options。要使用幾個值，用逗號將它們隔開。這些值與設(shè)置服務(wù)器SQL模式的相應(yīng)選項有相同的含義。

該選項不能保證同其它服務(wù)器之間的兼容性。它只啟用那些目前能夠使轉(zhuǎn)儲輸出更兼容的SQL模式值。例如，--compatible=oracle 不映射Oracle類型或使用Oracle注釋語法的數(shù)據(jù)類型。

· --complete-insert，-c

使用包括列名的完整的INSERT語句。

· --compress，-C

壓縮在客戶端和服務(wù)器之間發(fā)送的所有信息(如果二者均支持壓縮)。

· --create-option

在CREATE TABLE語句中包括所有MySQL表選項。

· ---database，-B

轉(zhuǎn)儲幾個數(shù)據(jù)庫。通常情況，mysqldump將命令行中的第1個名字參量看作數(shù)據(jù)庫名，后面的名看作表名。使用該選項，它將所有名字參量看作數(shù)據(jù)庫名。CREATE DATABASE IF NOT EXISTS db_name和USE db_name語句包含在每個新數(shù)據(jù)庫前的輸出中。

· ---debug[=debug_options]，-# [debug_options]

寫調(diào)試日志。debug_options字符串通常為'd:t:o,file_name'。

· --default-character-set=charset

使用charsetas默認字符集。如果沒有指定，mysqldump使用utf8。

· --delayed-insert

使用INSERT DELAYED語句插入行。

· --delete-master-logs

在主復(fù)制服務(wù)器上，完成轉(zhuǎn)儲操作后刪除二進制日志。該選項自動啟用--master-data。

· --disable-keys，-K

對于每個表，用;和;語句引用INSERT語句。這樣可以更快地裝載轉(zhuǎn)儲文件，因為在插入所有行后創(chuàng)建索引。該選項只適合MyISAM表。

· --extended-insert，-e

使用包括幾個VALUES列表的多行INSERT語法。這樣使轉(zhuǎn)儲文件更小，重載文件時可以加速插入。

· --fields-terminated-by=...，--fields-enclosed-by=...，--fields-optionally-enclosed-by=...，--fields-escaped-by=...，--行-terminated-by=...

這些選項結(jié)合-T選項使用，與LOAD DATA INFILE的相應(yīng)子句有相同的含義。

· --first-slave，-x

不贊成使用，現(xiàn)在重新命名為--lock-all-tables。

· --flush-logs，-F

開始轉(zhuǎn)儲前刷新MySQL服務(wù)器日志文件。該選項要求RELOAD權(quán)限。請注意如果結(jié)合--all--database(或-A)選項使用該選項，根據(jù)每個轉(zhuǎn)儲的數(shù)據(jù)庫刷新日志。例外情況是當使用--lock-all-tables或--master-data的時候：在這種情況下，日志只刷新一次，在所有 表被鎖定后刷新。如果你想要同時轉(zhuǎn)儲和刷新日志，應(yīng)使用--flush-logs連同--lock-all-tables或--master-data。

· --force，-f

在表轉(zhuǎn)儲過程中，即使出現(xiàn)SQL錯誤也繼續(xù)。

· --host=host_name，-h host_name

從給定主機的MySQL服務(wù)器轉(zhuǎn)儲數(shù)據(jù)。默認主機是localhost。

· --hex-blob

使用十六進制符號轉(zhuǎn)儲二進制字符串列(例如，'abc' 變?yōu)?x616263)。影響到的列有BINARY、VARBINARY、BLOB。

· --lock-all-tables，-x

所有數(shù)據(jù)庫中的所有表加鎖。在整體轉(zhuǎn)儲過程中通過全局讀鎖定來實現(xiàn)。該選項自動關(guān)閉--single-transaction和--lock-tables。

· --lock-tables，-l

開始轉(zhuǎn)儲前鎖定所有表。用READ LOCAL鎖定表以允許并行插入MyISAM表。對于事務(wù)表例如InnoDB和BDB，--single-transaction是一個更好的選項，因為它不根本需要鎖定表。

請注意當轉(zhuǎn)儲多個數(shù)據(jù)庫時，--lock-tables分別為每個數(shù)據(jù)庫鎖定表。因此，該選項不能保證轉(zhuǎn)儲文件中的表在數(shù)據(jù)庫之間的邏輯一致性。不同數(shù)據(jù)庫表的轉(zhuǎn)儲狀態(tài)可以完全不同。

· --master-data[=value]

該選項將二進制日志的位置和文件名寫入到輸出中。該選項要求有RELOAD權(quán)限，并且必須啟用二進制日志。如果該選項值等于1，位置和文件名被寫入CHANGE MASTER語句形式的轉(zhuǎn)儲輸出，如果你使用該SQL轉(zhuǎn)儲主服務(wù)器以設(shè)置從服務(wù)器，從服務(wù)器從主服務(wù)器二進制日志的正確位置開始。如果選項值等于2，CHANGE MASTER語句被寫成SQL注釋。如果value被省略，這是默認動作。

--master-data選項啟用--lock-all-tables，除非還指定--single-transaction(在這種情況下，只在剛開始轉(zhuǎn)儲時短時間獲得全局讀鎖定。又見--single-transaction。在任何一種情況下，日志相關(guān)動作發(fā)生在轉(zhuǎn)儲時。該選項自動關(guān)閉--lock-tables。

· --no-create-db，-n

該選項禁用CREATE DATABASE db_name語句，如果給出---database或--all--database選項，則包含到輸出中。

· --no-create-info，-t

不寫重新創(chuàng)建每個轉(zhuǎn)儲表的CREATE TABLE語句。

· --no-data，-d

不寫表的任何行信息。如果你只想轉(zhuǎn)儲表的結(jié)構(gòu)這很有用。

· --opt

該選項是速記;等同于指定 --add-drop-tables--add-locking --create-option --disable-keys--extended-insert --lock-tables --quick --set-charset。它可以給出很快的轉(zhuǎn)儲操作并產(chǎn)生一個可以很快裝入MySQL服務(wù)器的轉(zhuǎn)儲文件。該選項默認開啟，但可以用--skip-opt禁用。要想只禁用確信用-opt啟用的選項，使用--skip形式;例如，--skip-add-drop-tables或--skip-quick。

· --password[=password]，-p[password]

連接服務(wù)器時使用的密碼。如果你使用短選項形式(-p)，不能在選項和密碼之間有一個空格。如果在命令行中，忽略了--password或-p選項后面的 密碼值，將提示你輸入一個。

· --port=port_num，-P port_num

用于連接的TCP/IP端口號。

· --protocol={TCP | SOCKET | PIPE | MEMORY}

使用的連接協(xié)議。

· --quick，-q[!--empirenews.page--]

該選項用于轉(zhuǎn)儲大的表。它強制mysqldump從服務(wù)器一次一行地檢索表中的行而不是檢索所有行并在輸出前將它緩存到內(nèi)存中。

· --quote-names，-Q

用‘`’字符引用數(shù)據(jù)庫、表和列名。如果服務(wù)器SQL模式包括ANSI_QUOTES選項，用‘"’字符引用名。默認啟用該選項?？梢杂?-skip-quote-names禁用，但該選項應(yīng)跟在其它選項后面，例如可以啟用--quote-names的--compatible。

· --result-file=file，-r file

將輸出轉(zhuǎn)向給定的文件。該選項應(yīng)用在Windows中，因為它禁止將新行‘/n’字符轉(zhuǎn)換為‘/r/n’回車、返回/新行序列。

· --routines，-R

在轉(zhuǎn)儲的數(shù)據(jù)庫中轉(zhuǎn)儲存儲程序(函數(shù)和程序)。使用---routines產(chǎn)生的輸出包含CREATE PROCEDURE和CREATE FUNCTION語句以重新創(chuàng)建子程序。但是，這些語句不包括屬性，例如子程序定義者或創(chuàng)建和修改時間戳。這說明當重載子程序時，對它們進行創(chuàng)建時定義者應(yīng)設(shè)置為重載用戶，時間戳等于重載時間。

如果你需要創(chuàng)建的子程序使用原來的定義者和時間戳屬性，不使用--routines。相反，使用一個具有mysql數(shù)據(jù)庫相應(yīng)權(quán)限的MySQL賬戶直接轉(zhuǎn)儲和重載mysql.proc表的內(nèi)容。

該選項在MySQL 5.1.2中添加進來。在此之前，存儲程序不轉(zhuǎn)儲。

· --set-charset

將SET NAMES default_character_set加到輸出中。該選項默認啟用。要想禁用SET NAMES語句，使用--skip-set-charset。

· --single-transaction

該選項從服務(wù)器轉(zhuǎn)儲數(shù)據(jù)之前發(fā)出一個BEGIN SQL語句。它只適用于事務(wù)表，例如InnoDB和BDB，因為然后它將在發(fā)出BEGIN而沒有阻塞任何應(yīng)用程序時轉(zhuǎn)儲一致的數(shù)據(jù)庫狀態(tài)。

當使用該選項時，應(yīng)記住只有InnoDB表能以一致的狀態(tài)被轉(zhuǎn)儲。例如，使用該選項時任何轉(zhuǎn)儲的MyISAM或HEAP表仍然可以更改狀態(tài)。

--single-transaction選項和--lock-tables選項是互斥的，因為LOCK TABLES會使任何掛起的事務(wù)隱含提交。

要想轉(zhuǎn)儲大的表，應(yīng)結(jié)合--quick使用該選項。

· --socket=path，-S path

當連接localhost(為默認主機)時使用的套接字文件。

· --skip--comments

參見---comments選項的描述。

· --tab=path，-T path

產(chǎn)生tab分割的數(shù)據(jù)文件。對于每個轉(zhuǎn)儲的表，mysqldump創(chuàng)建一個包含創(chuàng)建表的CREATE TABLE語句的tbl_name.sql文件，和一個包含其數(shù)據(jù)的tbl_name.txt文件。選項值為寫入文件的目錄。

默認情況，.txt數(shù)據(jù)文件的格式是在列值和每行后面的新行之間使用tab字符?？梢允褂?-fields-xxx和--行--xxx選項明顯指定格式。

注釋：該選項只適用于mysqldump與mysqld服務(wù)器在同一臺機器上運行時。你必須具有FILE權(quán)限，并且服務(wù)器必須有在你指定的目錄中有寫文件的許可。

· --tables

覆蓋---database或-B選項。選項后面的所有參量被看作表名。

· --triggers

為每個轉(zhuǎn)儲的表轉(zhuǎn)儲觸發(fā)器。該選項默認啟用;用--skip-triggers禁用它。

· --tz-utc

在轉(zhuǎn)儲文件中加入SET TIME_ZONE='+00:00'以便TIMESTAMP列可以在具有不同時區(qū)的服務(wù)器之間轉(zhuǎn)儲和重載。(不使用該選項，TIMESTAMP列在具有本地時區(qū)的源服務(wù)器和目的服務(wù)器之間轉(zhuǎn)儲和重載)。--tz-utc也可以保護由于夏令時帶來的更改。--tz-utc默認啟用。要想禁用它，使用--skip-tz-utc。該選項在MySQL 5.1.2中加入。

· --user=user_name，-u user_name

連接服務(wù)器時使用的MySQL用戶名。

· --verbose，-v

冗長模式。打印出程序操作的詳細信息。

· --version，-V

顯示版本信息并退出。

· --where='where-condition', -w 'where-condition'

只轉(zhuǎn)儲給定的WHERE條件選擇的記錄。請注意如果條件包含命令解釋符專用空格或字符，一定要將條件引用起來。

2.2. 文件冷備份

當mysql服務(wù)器停止時，通過復(fù)制所有表文件(*.frm、*.MYD和*.MYI文件)來備份MyISAM數(shù)據(jù)庫。

2.3. MyISAM 表恢復(fù)

1、 如果是通過mysqldump備份的，就執(zhí)行：mysql –u root < 備份文件名。

2、 如果通過mysqlhotcopy或文件冷/熱拷貝來備份的，停止mysql服務(wù)，使用備份文件來覆蓋現(xiàn)有文件。

3、 如果是采用BACKUP TABLE備份的，使用restore table來恢復(fù)。因為backup table不會備份索引文件，恢復(fù)表注意重建索引。

4、 如果是采用SELECT INTO ...OUTFILE備份的，使用load data恢復(fù)數(shù)據(jù)，也可以使用mysqlimport命令來代替。

3. 日志備份

3.1. 日志備份的好處及如何啟用

日志備份可以支持MyISAM和InnoDB，這跟有些備份工具只支持MyISAM不一樣。

另外，日志可以做增量備份，這是其他方法無法做到的。

啟動日志備份：用--log-bin[=file_name]選項來啟動mysql服務(wù)。

3.2. 增量備份

先執(zhí)行FLUSH LOGS刷新日志，同步日志緩存到磁盤，關(guān)閉當前的日志并產(chǎn)生新的日志文件。

拷貝上一次完整備份或增量備份后的一個或一些日志文件到一個安全的地方。

這樣就制作了一個增量備份。

3.3. 日志恢復(fù)

日志恢復(fù)可以選擇恢復(fù)某個數(shù)據(jù)庫、某些操作點或時間范圍，非常靈活。

通過mysqlbinlog工具可以恢復(fù)二進制日志，可以一次恢復(fù)多個日志文件，命令如下：

shell> mysqlbinlog [options] hostname-bin.[0-9]* | mysql

mysqlbinlog命令選項

· ---help，-?

顯示幫助消息并退出。

· ---database=db_name，-d db_name

只列出該數(shù)據(jù)庫的條目(只用本地日志)。

· --force-read，-f

使用該選項，如果mysqlbinlog讀它不能識別的二進制日志事件，它會打印警告，忽略該事件并繼續(xù)。沒有該選項，如果mysqlbinlog讀到此類事件則停止。

· --hexdump，-H

[!--empirenews.page--]

在注釋中顯示日志的十六進制轉(zhuǎn)儲。該輸出可以幫助復(fù)制過程中的調(diào)試。在MySQL 5.1.2中添加了該選項。

· --host=host_name，-h host_name

獲取給定主機上的MySQL服務(wù)器的二進制日志。

· --local-load=path，-l pat

為指定目錄中的LOAD DATA INFILE預(yù)處理本地臨時文件。

· --offset=N，-o N

跳過前N個條目。

· --password[=password]，-p[password]

當連接服務(wù)器時使用的密碼。如果使用短選項形式(-p)，選項和 密碼之間不能有空格。如果在命令行中--password或-p選項后面沒有 密碼值，則提示輸入一個密碼。

· --port=port_num，-P port_num

用于連接遠程服務(wù)器的TCP/IP端口號。

· --position=N，-j N

不贊成使用，應(yīng)使用--start-position。

· --protocol={TCP | SOCKET | PIPE | -position

使用的連接協(xié)議。

· --read-from-remote-server，-R

從MySQL服務(wù)器讀二進制日志。如果未給出該選項，任何連接參數(shù)選項將被忽略。這些選項是--host、--password、--port、--protocol、--socket和--user。

· --result-file=name, -r name

將輸出指向給定的文件。

· --short-form，-s

只顯示日志中包含的語句，不顯示其它信息。

· --socket=path，-S path

用于連接的套接字文件。

· --start-datetime=datetime

從二進制日志中第1個日期時間等于或晚于datetime參量的事件開始讀取。datetime值相對于運行mysqlbinlog的機器上的本地時區(qū)。該值格式應(yīng)符合DATETIME或TIMESTAMP數(shù)據(jù)類型。例如：

shell> mysqlbinlog --start-datetime="2004-12-25 11:25:56" binlog.000003

該選項可以幫助點對點恢復(fù)。

· --stop-datetime=datetime

從二進制日志中第1個日期時間等于或晚于datetime參量的事件起停止讀。關(guān)于datetime值的描述參見--start-datetime選項。該選項可以幫助及時恢復(fù)。

· --start-position=N

從二進制日志中第1個位置等于N參量時的事件開始讀。

· --stop-position=N

從二進制日志中第1個位置等于和大于N參量時的事件起停止讀。

· --to-last-logs，-t

在MySQL服務(wù)器中請求的二進制日志的結(jié)尾處不停止，而是繼續(xù)打印直到最后一個二進制日志的結(jié)尾。如果將輸出發(fā)送給同一臺MySQL服務(wù)器，會導(dǎo)致無限循環(huán)。該選項要求--read-from-remote-server。

· --disable-logs-bin，-D

禁用二進制日志。如果使用--to-last-logs選項將輸出發(fā)送給同一臺MySQL服務(wù)器，可以避免無限循環(huán)。該選項在崩潰恢復(fù)時也很有用，可以避免復(fù)制已經(jīng)記錄的語句。注釋：該選項要求有SUPER權(quán)限。

· --user=user_name，-u user_name

連接遠程服務(wù)器時使用的MySQL用戶名。

· --version，-V

顯示版本信息并退出。

還可以使用--var_name=value選項設(shè)置下面的變量：

· open_files_limit

指定要保留的打開的文件描述符的數(shù)量。

4. InnoDB 表備份/恢復(fù)策略

4.1. 使用商業(yè)的在線熱備份工具

InnoDB Hotbackup 是一個在線備份工具，你可以用它來在InnoDB數(shù)據(jù)庫運行之時備份你的InnoDB數(shù)據(jù)庫。InnoDB 熱備份工具 不要求你關(guān)閉數(shù)據(jù)庫，并且它不設(shè)置任何鎖定或干擾你正常的數(shù)據(jù)庫處理。InnoDB 熱備份工具 是非免費(商業(yè)的)附加軟件，它每年的證書費用是每臺MySQL服務(wù)器運行的計算機390歐元。

具體使用方法可以參考相關(guān)文檔，我也沒有使用過這個工具。

4.2. 使用mysqldump 熱備份

Mysqldump提供對InnoDB非物理的在線邏輯熱備份。

使用方法與備份MyISAM時一樣。

4.3. 使用select into 熱備份單個/多個表

可以使用select into備份一個或多個表，用法與MyISAM表相同。

4.4. 二進制冷備份

如果你可以關(guān)閉你的MySQL服務(wù)器，你可以生成一個包含InnoDB用來管理它的表的所有文件的二進制備份。使用如下步驟：

1. 關(guān)閉MySQL服務(wù)器，確信它是無錯誤關(guān)閉。

2. 復(fù)制你所有數(shù)據(jù)文件(ibdata文件和.ibd文件)到一個安全的地方。

3. 復(fù)制你所有ib_logfile文件到一個安全的地方。

4. 復(fù)制my.cnf配置文件或文件到一個安全的地方。

5. 為你InnoDB表復(fù)制.frm文件到一個安全的地方。

4.5. InnoDB 表恢復(fù)

1、 先嘗試使用InnoDB的日志自動恢復(fù)功能，方法是重啟mysql服務(wù)。

2、 在一些情況下，明顯地數(shù)據(jù)庫損壞是因為操作系統(tǒng)損壞它自己的文件緩存，磁盤上的數(shù)據(jù)可能完好，最好是首先重啟計算機。它可以消除那些顯得是數(shù)據(jù)庫頁損壞的錯誤。

如果不行，則采用下面的方法進行恢復(fù)。

3、 如果是mysqldump做的完全備份，先恢復(fù)完全備份，然后再恢復(fù)完全備份后的增量日志備份。

4、 如果是采用select into備份表的話，則采用load data或mysqlimport恢復(fù)。

5、 如果是采用二進制冷備份做的完全備份，則先停止mysql服務(wù)，覆蓋備份的二進制文件，然后執(zhí)行上次完全備份后的增量日志備份。

6、 注意：InnoDB二進制文件沒有MyISAM那么好，必須在相同的浮點數(shù)的cpu機器上移植。

5. MyISAM/InnoDB 備份/恢復(fù)策略

因為在一個數(shù)據(jù)庫中有可能要同時使用MyISAM和InnoDB兩種引擎，因此統(tǒng)一考慮他們的備份/恢復(fù)策略。

1、 完整備份采用mysqldump。

2、 增量備份采用bin-log日志。

3、 單表備份采用select into。

4、

6. 定期維護表

雖然MySQL提供了多種備份/恢復(fù)手段，但是定期維護表大大可以降低表毀壞的可能性，并且可以提高查詢性能。

1、對于InnoDB引擎，你可以使用innodb_tablespace_monitor來檢查表空間文件內(nèi)文件空間管理的完整性。

2、對于MyISAM引擎，可以通過sql語句或myisamchk 工具來維護表 。[!--empirenews.page--]

在許多情況下，你會發(fā)現(xiàn)使用SQL語句實現(xiàn)MyISAM表的維護比執(zhí)行myisamchk操作要容易地多：

· 要想檢查或維護MyISAM表，使用CHECK TABLE或REPAIR TABLE。

· 要想優(yōu)化MyISAM表，使用OPTIMIZE TABLE。

· 要想分析MyISAM表，使用ANALYZE TABLE。

這些語句比myisamchk有利的地方是服務(wù)器可以做任何工作。使用myisamchk，你必須確保服務(wù)器在同一時間不使用表。否則，myisamchk和服務(wù)器之間會出現(xiàn)不期望的相互干涉。

一般建議在停止mysql服務(wù)時執(zhí)行myisamchk，如果是在線執(zhí)行則最好先flush tables(把所有更新寫入磁盤)。

7. 備份策略摘要

1、一定用--log-bin或甚至--log-bin=log_name選項運行MySQL服務(wù)器，其中日志文件名位于某個安全媒介上，不同于數(shù)據(jù)目錄所在驅(qū)動器。如果你有這樣的安全媒介，最好進行硬盤負載均衡(這樣能夠提高性能)。

2、定期進行完全備份，使用mysqldump命令進行在線非塊備份。

在負載比較低的時候進行，并且建議采用--single-transaction參數(shù)來保證事務(wù)數(shù)據(jù)的一致性，同時不影響其他用戶的正常讀寫。

3、完整備份時采用FLUSH LOGS，便于產(chǎn)生增量備份日志。

4、用FLUSH LOGS或mysqladmin flush-logs 刷新日志進行定期增量備份。

5、定期維護表，這樣既可以提高性能，并且可以減少數(shù)據(jù)丟失和出錯的可能性。

6、關(guān)鍵表可以另外再做備份，根據(jù)需要