MySQL MyISAM和InNodb備份與恢復(fù)技巧
加入技術(shù)交流群
1. 為什么要備份數(shù)據(jù)庫(kù)
本文引用地址：http://2s4d.com/article/201609/304118.htm對(duì)數(shù)據(jù)庫(kù)來(lái)說(shuō)，最重要也最容易被忽視的就是備份。由于不可預(yù)測(cè)性，偶然的事件可能會(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://2s4d.com/news/listbylabel/label/MySQL">MySQL表保存為文件方式，很容易備份。要想保持備份的一致性，對(duì)相關(guān)表執(zhí)行LOCK TABLES操作，然后對(duì)表執(zhí)行FLUSH TABLES。
你只需要讀鎖定;這樣當(dāng)你復(fù)制數(shù)據(jù)庫(kù)目錄中的文件時(shí)，允許其它客戶繼續(xù)查詢表。需要FLUSH TABLES語(yǔ)句來(lái)確保開(kāi)始備份前將所有激活的索引頁(yè)寫(xiě)入硬盤(pán)。
標(biāo)準(zhǔn)流程：鎖表-》刷新表到磁盤(pán)-》拷貝文件-》解鎖。
2.1.2. 使用sql 語(yǔ)句備份
如果你想要進(jìn)行SQL級(jí)別的表備份，你可以使用SELECT INTO ...OUTFILE或BACKUP TABLE。對(duì)于SELECT INTO ...OUTFILE， 輸出的文件不能先存在。
對(duì)于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編寫(xiě)并提供。它使用LOCK TABLES、FLUSH TABLES和cp或scp來(lái)快速備份數(shù)據(jù)庫(kù)。它是備份數(shù)據(jù)庫(kù)或單個(gè)表的最快的途徑，但它只能運(yùn)行在數(shù)據(jù)庫(kù)目錄所在的機(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ù)庫(kù)中的匹配正則表達(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
備份中不包括全部索引文件。這樣使備份更小、更快?？梢栽谝院笥胢yisamchk -rq重新構(gòu)建索引。
· --password=password，-p password
當(dāng)連接服務(wù)器時(shí)使用的密碼。請(qǐng)注意該選項(xiàng)的密碼值是不可選的，不象其它MySQL程序。
· --port=port_num，-P port_num
當(dāng)連接本地服務(wù)器時(shí)使用的TCP/IP端口號(hào)。
· --quiet，-q
除了出現(xiàn)錯(cuò)誤時(shí)保持沉默。
· --regexp=expr
復(fù)制所有數(shù)據(jù)庫(kù)名匹配給出的正則表達(dá)式的數(shù)據(jù)庫(kù)。
· --socket=path，-S path
用于連接的Unix套接字文件。
· --suffix=str
所復(fù)制的數(shù)據(jù)庫(kù)名的后綴。
· --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一般是用來(lái)做完全備份，所以推薦使用—flushlog選項(xiàng)來(lái)產(chǎn)生增量更新日志。
2.1.4. 使用mysqldump 備份
可以備份表結(jié)構(gòu)和數(shù)據(jù)，可以同時(shí)支持MyISAM和InnoDB引擎數(shù)據(jù)庫(kù)。
mysqldump可以備份單個(gè)表、單個(gè)庫(kù)或所有庫(kù)。
Mysqldump 還可以只導(dǎo)出表結(jié)構(gòu)。
mysqldump是邏輯備份，輸出的是sql語(yǔ)句文件，還可以輸出其他數(shù)據(jù)庫(kù)兼容的格式。
有3種方式來(lái)調(diào)用mysqldump：
shell> mysqldump [options] db_name [tables]
shell> mysqldump [options] ---database DB1 [DB2 DB3...]
shell> mysqldump [options] --all--database
如果沒(méi)有指定任何表或使用了---database或--all--database選項(xiàng)，則轉(zhuǎn)儲(chǔ)整個(gè)數(shù)據(jù)庫(kù)。
要想獲得你的版本的mysqldump支持的選項(xiàng)，執(zhí)行mysqldump ---help。
如果運(yùn)行mysqldump沒(méi)有--quick或--opt選項(xiàng)，mysqldump在轉(zhuǎn)儲(chǔ)結(jié)果前將整個(gè)結(jié)果集裝入內(nèi)存。如果轉(zhuǎn)儲(chǔ)大數(shù)據(jù)庫(kù)可能會(huì)出現(xiàn)問(wèn)題。該選項(xiàng)默認(rèn)啟用，但可以用--skip-opt禁用。
mysqldump支持下面的選項(xiàng)：
· ---help，-?
顯示幫助消息并退出。
· --add-drop--database
在每個(gè)CREATE DATABASE語(yǔ)句前添加DROP DATABASE語(yǔ)句。
· --add-drop-tables
在每個(gè)CREATE TABLE語(yǔ)句前添加DROP TABLE語(yǔ)句。
· --add-locking
用LOCK TABLES和UNLOCK TABLES語(yǔ)句引用每個(gè)表轉(zhuǎn)儲(chǔ)。重載轉(zhuǎn)儲(chǔ)文件時(shí)插入得更快。
· --all--database，-A
轉(zhuǎn)儲(chǔ)所有數(shù)據(jù)庫(kù)中的所有表。與使用---database選項(xiàng)相同，在命令行中命名所有數(shù)據(jù)庫(kù)。
· --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)。
評(píng)論