MySQL備份和恢復(fù)策略（頁）

1、在數(shù)據(jù)庫表丟失或損壞的情況下，備份你的數(shù)據(jù)庫是很重要的。如果發(fā)生系統(tǒng)朋潰，你肯定 想能夠?qū)⒛愕谋肀M可能丟失最少的數(shù)據(jù)恢復(fù)到崩潰發(fā)生時的狀態(tài)。木文主要對my isam表做 備份恢復(fù)。備份策略一：直接拷貝數(shù)據(jù)庫文件（不推薦）備份策略二：使用mysqlhotcopy備份數(shù)據(jù)庫（完全備份，適合小型數(shù)據(jù)庫備份）備份策略三：使用mysqldump備份數(shù)據(jù)庫（完全+增量備份，適合屮型數(shù)據(jù)庫備份）備份策略四：使用主從復(fù)制機制（replication）（實現(xiàn)數(shù)據(jù)庫實時備份）腳木下載地址：點擊下載腳木備份策略一、直接拷貝數(shù)據(jù)庫文件直接拷貝數(shù)據(jù)文件最為直接、快速、方便，但缺點是基木上不能實現(xiàn)增量備份。為了保證數(shù) 據(jù)

2、的一致性，需要在備份文件前，執(zhí)行以下sql語句：flush tables with read lock;也就是把內(nèi)存中的數(shù)據(jù)都刷新到磁盤中，同時鎖定數(shù)據(jù)表，以保證拷貝過程中不會冇新的數(shù) 據(jù)寫入。這種方法備份出來的數(shù)據(jù)恢復(fù)也很簡單，肓接拷貝回原來的數(shù)據(jù)庫目錄下即可。備份策略二、使用mysqlhotcopy備份數(shù)據(jù)庫mysqlhotcopy 是一個 perl 程序，最初由 tim bunce 編寫。它使用 lock tables、 flush tables和cp或scp來快速備份數(shù)據(jù)庫。它是備份數(shù)據(jù)庫或單個表的最快的途徑, 但它只能運行在數(shù)據(jù)庫文件（包括數(shù)據(jù)表定義文件、數(shù)據(jù)文件、索引文件）所在的機

3、器上, 并fl. mysqlhotcopy只能用于備份my isam表。本備份策略適合于小型數(shù)據(jù)庫的備份，數(shù)據(jù)量不大，可以采用mysqlhotcopy程序每天進行 次完全備份。備份策略布置：（1）、安裝dbd-mysql perl模塊,支持mysqlhotcopy腳本連接到mysql數(shù)據(jù)庫。shcl1> tar -xzvf dbd-mysql-4. 005. tar. gzshel1> cd dbd-mysql-4. 005shel1> unset langshell> perl makefile- pl -mysql_config二/usi71ocal/mysql/b

4、in/mysql_config -testuser=root -testpassword二uscrpwdshell> makeshell> make testshel1> make instal1(2) 、設(shè)置crontab任務(wù)，每天執(zhí)行備份腳本shell> crontab -e0 3* * * /root/mysqlbackup/mysq1 backup, sh >/dev/nul1 2>&1每天凌晨3:00執(zhí)行備份腳本。mysqlbackup. sh 注釋：#!/bin/sh# name:mysqlbackup. sh# ps:mysql dat

5、abase backup, use mysqlhotcopy script.# write by:i. stone# last modify:2007-ll-15#定義變量，請根據(jù)具體情況修改#定義腳本所在目錄scriptsdir='pwd#數(shù)據(jù)庫的數(shù)據(jù)目錄datadir=/usr/local/mysql/data/#數(shù)據(jù)備份目錄tmpbackupdir=/tmp/tmpbackup/backupdir二/tmp/mysqlbackup/#用來備份數(shù)據(jù)庫的用戶名和密碼mysqluscrtootmysqlpwd=llllll#定義email地址email=altersomode. com

6、#如果臨時備份目錄存在，清空它，如果不存在則創(chuàng)建它if -e $tmpbackupdir ; thenrm -rf stmpbackupdiraelsemkdir $tmpbackupdirfi#如果備份目錄不存在則創(chuàng)建它if ! -e $backupdir ;thenmkdir $backupdirfi# 清空 mysqlbackup. logif -s mysqlbackup. log ; thencat /dev/null >mysqlbackup. logfi#得到數(shù)據(jù)庫備份列表，在此可以過濾不想備份的數(shù)據(jù)庫for databases in find $datadir -type

7、 d | sed e /zs/usr/l ocal /mysql /data/z， | sed -e "s/test/"、 ; doif $databases =; thencontinueelse#備份數(shù)據(jù)庫/usr/local/mysql/bin/mysqlhotcopy -user=$mysqluser -password=$mysqlpwd -q $databases $tmpbackupdirdatetime二'date +%ym. %d %h:%m:%s'echo z/$datetime database: $databases backupsu

8、ccess!z，»mysqlbackup. logfidone#壓縮備份文件date二 date tcd $tmpbackupdirtar czf $backupdir/mysql-$date. tar. gz . /#發(fā)送郵件通知if -s mysqlbackup. log ; thencat mysqlbackup. log | mail -s "mysql backup" $email fi#使用smbclientmv. sh腳木上傳數(shù)據(jù)庫備份到備份服務(wù)器# $scriptsdir/smbclientmv. shsmbclientmv. sh 注釋#!/bi

9、n/sh# name:smbclientmv. sh# ps:move the data to backup server.# write by:i.stonen last modify:2007-ll-15n#定義變量#備份服務(wù)器名backupscrvcr:=zzbackupscrvcrnamcz，#共享文件夾名backupsharesharename"7#備份服務(wù)器的訪問用戶名和密碼backupuser=，smbuserz，backuppw=/zsmbpasswordz，#定義備份目錄backupdir=/tmp/mysqlbackup date= date t# move th

10、e data to backupserver smbclient /$backupserver/$backupshare $backuppw -do -w workgroup -u $backupuser -c "put $backupdir/mysql-$date. tar. gz mysql-$datc. tar. gz# delete temp filesrm -f $backupdir/mysql-$date. tar. gz(3) 、恢復(fù)數(shù)據(jù)庫到備份時的狀態(tài)mysqlhotcopy備份出來的是整個數(shù)據(jù)庫口錄，使用時可以直接拷貝到mysqld 指定的datadir (在這里是

11、/usr/local/mysql/data/) g錄下即可,同時要注意 權(quán)限的問題，如下例：shel1> cp -rf db_name /usr/1ocal/mysql/data/shell> chown -r mysql:mysql /usr/1 ocal/mysql/delta/ (將 db_ncime 目錄的 展主改成mysqld運彳亍用戶)本套備份策略只能恢復(fù)數(shù)據(jù)庫到最后一次備份時的狀態(tài)，要想在崩潰時丟失的數(shù) 據(jù)盡量少應(yīng)該更頻繁的進行備份，要想恢復(fù)數(shù)據(jù)到崩潰時的狀態(tài)請使用主從復(fù)制 機制(replication)。備份策略三、使用mysqldump備份數(shù)據(jù)庫mysqldump

12、是采用sql級別的備份機制，它將數(shù)據(jù)表導(dǎo)成sql腳本文件，在不同 的mysql版本之間升級時相對比較合適，這也是最常用的備份方法。mysqldump比肓接拷 貝要慢些。關(guān)于mysqldump的更詳細解釋見最后的附錄。對于中等級別業(yè)務(wù)量的系統(tǒng)來說，備份策略可以這么定：第一次完全備份，每天一次增量備 份，每周再做一次完全備份，如此一直重復(fù)。而對于重要的r繁忙的系統(tǒng)來說，則可能盂要 每天一次全量備份，每小時一次增量備份，其至更頻繁。為了不影響線上業(yè)務(wù)，實現(xiàn)在線備 份，并且能增最備份，最好的辦法就是采用主從復(fù)制機制(replication),在slave機器上 做備份。備份策略布置：(1) 、創(chuàng)建備份

13、目錄shell> mkdir /tmp/mysqlbackupshell> mkdir /tmp/mysqlbackup/daily(2) 、卅用二進制口志采用binlog的方法相對來說更靈活，省心省力，而且還可以支持增最備份。啟用binlog時必須要重啟mysqldo首先，關(guān)閉mysqld,打開/etc/f,加入以下幾 行：mysqldlog-bin然后啟動mysqld就可以了。運行過程中會產(chǎn)生hostname-bin.oooool以及hostname-bin.index,前面的文件是mysqld記錄所有対數(shù)據(jù)的更新操作，后面的文件則 是所冇binlog的索引，都不能輕易刪除。關(guān)

14、于binlog的更詳細信息請查看手冊。(3) 、配置ssh密鑰登錄,用于將mysql備份傳送到備份服務(wù)器(如果備份服務(wù)器為windows, 請?zhí)^此部)。1) 、在mysql所在服務(wù)器(192.168.0.20)生成ssh密鑰rootlab # ssh-keygen -t rsagenerating public/private rsa key pair.enter file in which to save the key (/root/.ssh/id_rsa): /直接回乍enter passphrase (empty for no passphrase):/jfi接回午，不使用密碼ent

15、er same passphrase again:/直接冋千，不使用密碼your identification has been saved in /root/.ssh/id_rsa.your public key has been saved in /root/.ssh/id_rsa.pub.the key fingerprint is:c2:96:9f:2d:5a:8e:0&42:43:35:2f:85:5e:72:f8:lc rootlab2) 、在備份服務(wù)器(192.168. 0. 200)上創(chuàng)建目錄，修改權(quán)限，并傳送公鑰。root®lab # ssh 192.168

16、.0.200 "inkdir .ssh;chmod 0700 .ssh"the authenticity of host '19268.0.200 (192.168.0.200)' can't be established.rsa key fingerprint is 37:57:55:cl:32:fl:dd:bb:lb:8a:13:6f:89:fb:b&9d.are you sure you want to continue connecting (yes/no)? yeswarning: permanently added '19

17、2.168.0.200' (rs a) to the list of known hosts.root® 192.168.0.200's password:/輸入備份服務(wù)器的 root 密碼rootlab # scp .ssh/id_rsa.pub 192.168.0.200:.ssh/authorized_keys2root 192.16&().2()0's password:id_rsa.pub100% 2180.2kb/s 00:003) 、測試ssh登錄rootlab # ssh 192.168.0.200/測試 ssh 登錄last logi

18、n: fri nov 16 10:34:02 2007 from 192.168.0.20root©lib #(4) 、設(shè)置crontab任務(wù)，每天執(zhí)行備份腳本shel1> crontab -e#每個星期日凌晨3:00執(zhí)行完全備份腳本0 3*0 /root/mysqlbackup/mysqlfulibackup. sh >/dev/null 2>&1#周一到周六凌晨3:00做增雖備份0 3* 1-6 /root/mysqlbackup/mysq1 dai 1ybackup. sh >/dev/nul1 2>&1 mysqlfuliback

19、up. sh 注釋:#!/bin/sh# name:msqlfulibackup. sh# ps:mysql database full backup.# write by:i. stone # last modify:2007-ll-17 # use mysqldunip -help get more detail.n#定義變量，請根據(jù)具體情況修改#定義腳本目錄 scriptsdir='pwd#定義數(shù)據(jù)庫目錄 mysqldir=/usr/local/mysql#定義用于備份數(shù)據(jù)庫的用戶名和密碼user二rootuserpwd=llllll#定義備份目錄databackupdir=/t

20、mp/mysqlbackup#定義郵件正文文件emaiifile=$databackupdir/emai1. txt#定義郵件地址email=aitersomode com#定義備份日志文件logfile=$databackupdir/mysqlbackup. logdate='date -iecho "” > $emailfileecho $（date +y-%m-%d %h:%m:%s） » $ema訂file cd $databackupdi r#定義備份文件名dumpfi1e=mysq1_$date. sqlgzdumpfi1e=mysql_$date

21、. sql. tar. gz#使用mysqldump備份數(shù)據(jù)庫，請根據(jù)具體情況設(shè)置參數(shù)$mysqldir/bin/mysqldump -u$user -p$userpwd -opt -default-character-set二utf8 -extended-insert=false -triggers -r -hex-blob -all-databases -flush-logs -delete-master-logs -delete-mastert ogs -x > $dumpfilc#壓縮備份文件if $?- 0 ; thentar czf $gzdumpfile $dumpfile

22、 >> $emaiifile 2>&1echo "backupfi1ename:$gzdumpfile >> $cmailfileecho "'database backup success!，z >> $emailfilerm -f $dumpfile# delete deiily backup files.cd $databackupdir/dailyrm -f *# delete old backup files (mtime>2).$scriptsdir/rmbackup. sh#如果不需要將備份傳送到

23、備份服務(wù)器或備份服務(wù)器為windows,請將標(biāo)綠的行 注釋掉# move backup files to backup server.#適合linux (mysql服務(wù)器)到linux (備份服務(wù)器)$scriptsdir/rsyncbackup. sh» $ema訂f訂e$ema訂f訂eif ( !$? ); thenecho "move backup files to backup server success!elseecho "move backup files to backup server fail!" >> fielseech

24、o "'database backup fail!" >> $emai lfi lefi#寫日志文件echo "$logfilecat $emailfile >> $logfile#發(fā)送郵件通知cat $emailf訂c | mail -s "mysql backup" $emailmysqldailybackup. sh 注釋:#!/bin/sh# name: mysqldail)backup. sh# ps:mysql database daily backup.# write by:i stonen las

25、t modify:2007-ll-17#定義變量，請根據(jù)具體情況修改#定義數(shù)據(jù)庫目錄和數(shù)據(jù)目錄 scriptsdir='pwdmysqldir=/usr/local/mysqldatadi r二$mysqldi r/data#定義用于備份數(shù)據(jù)庫的用戶名和密碼user=rootuserpwd=llllll#定義備份口錄，每日備份文件備份到$databackupdir/daily databackupdi r=/tmp/mysqlbackupdail ybackupd i r=$databackup) ir/dail#定義郵件正文文件emailfile=$databackupdir/ema

26、il, txt#定義郵件地址email=altersomode. com#定義日志文件logfile=$databackupdir/mysqlbackup log#得到數(shù)據(jù)庫所在主機的主機名hostname' uname -n'#echo > $cmailfilcecho $ (date +%y-%m-%d %h:%m:%s) >> $emailfile#刷新日志，使數(shù)據(jù)庫使用新的二進制日志文件$mysqldir/bin/mysqladmin u$user -p$userpwd flush-logscd $datadir#得到二進制日志列表f訂elist=&#

27、39;cat $hostname-bin. indexicounter=0for file in $filelistdoicounter='expr $icounter + 1donenextnum=0ifile=0for file in $filelistdobinlogname='basename $filenextnum二 expr $nextmum + 1#跳過最后一個二進制fl志(數(shù)據(jù)庫當(dāng)前使用的二進制fl志文件) if $ncxtnum = $icounter ; thenecho "skip lastest!" > /dev/nullel

28、sedest=$dailybackupdir/$binlogname#跳過已經(jīng)備份的二進制口志文件if -e $dest ; thenecho "skip exist $binlogname!> /dev/nullelse#備份fl志文件到備份目錄cp $binlognamc $dailybackupdirif $?= 0 ; then ifile='expr $ifile + 1 echo $binlogname backup success!z，>> $emailfile fififidoneif $if訂e 二二 0 ;thenecho "n

29、o binlog backup!" >> $emaiifileelseecho "backup $ifile file（s）.>> $emailfileecho "backup mysql binlog 0k! >> $emailfile#如果不需要將備份傳送到備份服務(wù)器或備份服務(wù)器為windows,請將標(biāo)綠的行 注釋掉# move backup files to backup server#適合linux （mysql服務(wù)器）到linux （備份服務(wù)器）$scriptsdir/rsyncbackup.shif $?- 0 ;

30、thenecho "move backup fi les to backup server success!z，>> $emai ifile elseecho "move backup files to backup server fail!" >> $emailfilefifi#發(fā)送郵件通知cat $cmailfilc | mail -s "mysql backup" $cmail#寫日志文件echo »$logfilecat $emaiifile >> $logfile rsyncbackup.

31、sh 注釋：#!/bin/sh# name:rsyncbackup. sh# ps:move backup files to backup server.# write by:i stone# last modify:2007-ll-17#請根據(jù)具體情況修改，注意最后有#定義數(shù)據(jù)庫備份目錄databackupdir=/tmp/mysq1 backup/#定義備份服務(wù)器上存放備份數(shù)據(jù)的目錄 backupserverdir=/root/mysqlbackup/#定義備份服務(wù)器backupserver二192. 168. 0. 200#同步備份文件到備份服務(wù)器rsync -a -delete $da

32、tabackupdir -e ssh sbackupserver: sbackupserverdir > /dev/null 2>&1rmbackup. sh 注釋:#!/bin/sh# name:rmbackup sh# ps:delete old backup.# write by:i.stone# last modify:2007-11-15#定義備份目錄 databackupdir=/tmp/mysqlbackup#刪除mtime>2的日志備份文件find $databackupdir -name mysqlgzz，-type f -mtime +2 -exe

33、c rm ; > /dev/null 2>&1（5）、恢復(fù)數(shù)據(jù)庫到備份時的狀態(tài)用mysqldump備份出來的文件是一個可以直接倒入的sql腳本,直接用mysql客戶端導(dǎo) 入就可以了。/usr/local/mysql/bin/mysql -uroot -puserpwd db_name < db_name.sql對于任何可適用的更新li志，將它們作為mysql的輸入：% is -t -r - i hostname-bin* i xargs mysq i bin log i mysql -uuser -puserpwdis命令生成更新口志文件的一個單列列表，根據(jù)服務(wù)器產(chǎn)生

34、它們的次序排序（注意：如果你 修改任何一個文件，你將改變排序次序，這將導(dǎo)致更新h志以錯謀的次序被運用。）木套備份策略只能恢復(fù)數(shù)據(jù)庫到最示一次備份時的狀態(tài)，要想在崩潰時丟失的數(shù)據(jù)盡量少應(yīng) 該更頻繁的進行備份，要想恢復(fù)數(shù)據(jù)到崩潰時的狀態(tài)請使用主從復(fù)制機制（replication） o 如果使用本套備份腳木，將口志文件和數(shù)據(jù)文件放到不同的磁盤上是一個不錯的主義，這樣 不僅可以提高數(shù)據(jù)寫入速度，還能使數(shù)據(jù)更安全。備份策略四、使用主從復(fù)制機制(replication)mysql支持單向、異步復(fù)制，復(fù)制過程屮一個服務(wù)器充當(dāng)主服務(wù)器，而-個或多 個其它服務(wù)器充當(dāng)從服務(wù)器。主服務(wù)器將更新寫入二進制f1志文件，

35、并維護f1 志文件的一個索引以跟蹤口志循環(huán)。當(dāng)一個從服務(wù)器連接到主服務(wù)器時，它通知 主服務(wù)器從服務(wù)器在h志屮讀取的最后一次成功更新的位置。從服務(wù)器接收從 那時起發(fā)生的任何更新，然后封鎖并等待主服務(wù)器通知下一次更新。為什么使用主從復(fù)制？1、主服務(wù)器/從服務(wù)器設(shè)置增加了健壯性。主服務(wù)器出現(xiàn)問題時，你可以切換到 從服務(wù)器作為備份。2、通過在主服務(wù)器和從服務(wù)器之間切分處理客戶查詢的負荷，可以得到更好的 客戶響應(yīng)吋間。但是不要同吋在主從服務(wù)器上進行更新，這樣可能引起沖突。3、使用復(fù)制的另一個好處是可以使用一個從服務(wù)器執(zhí)行備份，而不會干擾主服 務(wù)器。在備份過程屮主服務(wù)器可以繼續(xù)處理更新。mysql使用3個

36、線程來執(zhí)行復(fù)制功能(其中1個在主服務(wù)器上，另兩個在從服務(wù) 器上。當(dāng)發(fā)出start slave ii寸，從服務(wù)器創(chuàng)建一個i/o線程，以連接主服務(wù)器并 讓主服務(wù)器發(fā)送二進制口志o主服務(wù)器創(chuàng)建一個線程將二進制口志中的內(nèi)容發(fā)送 到從服務(wù)器。從服務(wù)器i/o線程讀取主服務(wù)器binlog dump線程發(fā)送的內(nèi)容并將 該數(shù)據(jù)拷貝到從服務(wù)器數(shù)據(jù)目錄屮的本地文件中，銀屮繼日志。第3個線程是 sql線程，從服務(wù)器使用此線程讀取中繼日志并執(zhí)行日志中包含的更新。show processlist語句可以查詢在主服務(wù)器上和從服務(wù)器上發(fā)生的關(guān)于復(fù)制的信息。默認中繼口志使用力ost_namc-rclay-bin. /“7形式的

37、文件名，其中host_namc 是從服務(wù)器主機名，皿皿皿是序列號。用連續(xù)序列號來創(chuàng)建連續(xù)屮繼h志文件， 從000001開始。從服務(wù)器跟蹤中繼日志索引文件來識別目前正使用的中繼日志。 默認屮繼日志索引文件名為host_name-relay-bin. indexq在默認情況，這些文 件在從服務(wù)器的數(shù)據(jù)廿錄中被創(chuàng)建。中繼fi志與二進制fi志的格式相同，并且可 以用mysqlbinlog讀取。當(dāng)sql線程執(zhí)行完中繼口志中的所有事件后，中繼日志 將會被自動刪除。從服務(wù)器在數(shù)據(jù)目錄中另外創(chuàng)建兩個狀態(tài)文件-master, info和 relay-log. info。狀態(tài)文件保存在硬盤上，從服務(wù)器關(guān)閉時不會丟

38、失。下次從服 務(wù)器啟動時，讀取這些文件以確定它已經(jīng)從主服務(wù)器讀取了多少二進制日志，以 及處理自己的中繼日志的程度。設(shè)置主從復(fù)制:1、確保在主服務(wù)器和從服務(wù)器上安裝的mysql版木和同，并且最好是mysql的 最新穩(wěn)定版本。2、在主服務(wù)器上為復(fù)制設(shè)置一個連接賬戶。該賬戶必須授予replication slave 權(quán)限。如果賬戶僅用于復(fù)制（推薦這樣做），則不需要再授予任何其它權(quán)限。mysql> grant replication slave on *-> to 'replication'©'% y ourdomai n. com， identified

39、 by 'slavepass'3、執(zhí)行flush tables with read lock語句清空所有表和塊寫入語句：mysql> flush tables with read lock；保持mysql客戶端程序不要退出。開啟另一個終端對主服務(wù)器數(shù)據(jù)口錄做快照。shell> cd /usr/local/mysql/shell> tar -cvf /tmp/mysql-snapshottar /data如果從服務(wù)器的用戶賬戶與主服務(wù)器的不同，你可能不想復(fù)制mysql數(shù)據(jù)庫。在 這種情況下，應(yīng)從歸檔中排除該數(shù)據(jù)庫。你也不需要在歸檔中包括任何日志文件 或者 mas ter. info 或 relay-log info 文何二。當(dāng)flush tables with read lock所置讀鎖定冇效時（即mysql客戶端程序不退 出），讀取主服務(wù)器上當(dāng)前的二進制日志名和偏移量值：mysql > show master status