它是邏輯備份,優(yōu)點(diǎn)可以備份各種存儲(chǔ)引擎1.備份所有的數(shù)據(jù)庫(kù)#mysqldump-uroot-p--all-database>all.sql2.備份指定的數(shù)據(jù)庫(kù)#mysqldump-uroot-ptest>test.sql3.備份指定數(shù)據(jù)庫(kù)中的表#mysqldump-uroot-ptests>test_s.sql備份完全恢復(fù)實(shí)例(1)上午9點(diǎn)備份數(shù)據(jù)庫(kù)#mysqldump-uroot-p-l-Fstudent>student.dmp-l給所有表加讀鎖-F生成一個(gè)新的日志文件此時(shí)s表數(shù)據(jù)如下:mysql>select*froms;+------+-------+------+-----------+|sno|sname|sex|address|+------+-------+------+-----------+|0901|Jim|1|shanghai||0902|helun|2|beijing||0903|sam|1|sichuan||0904|keke|1|xizang||0905|gugu|1|suzhou||0906|tang|2|guangdong|+------+-------+------+-----------+6rowsinset(0.00sec)備份完畢等到了student.dmp文件,還有mysql-bin.000012(2)9點(diǎn)半備份完畢,然后插入新的數(shù)據(jù)mysql>insertintosvalues('0907','liu','1','jiangxi');QueryOK,1rowaffected(0.00sec)mysql>insertintosvalues('0908','wang','2','wuxi');QueryOK,1rowaffected(0.00sec)(3)10點(diǎn),數(shù)據(jù)庫(kù)突然故障,數(shù)據(jù)無(wú)法訪(fǎng)問(wèn).需要恢復(fù)備份:#mysql-uroot-pstudent<student.dmp恢復(fù)后的數(shù)據(jù):mysql>select*froms;+------+-------+------+-----------+|sno|sname|sex|address|+------+-------+------+-----------+|0901|Jim|1|shanghai||0902|helun|2|beijing||0903|sam|1|sichuan||0904|keke|1|xizang||0905|gugu|1|suzhou||0906|tang|2|guangdong|+------+-------+------+-----------+6rowsinset(0.00sec)(4)使用mysqlbinlog恢復(fù)mysqldump備份以來(lái)的BINLOG#mysqlbinlogmysql-bin.000012|mysql-uroot-pstudent查詢(xún)完全恢復(fù)后的數(shù)據(jù):mysql>select*froms;+------+-------+------+-----------+|sno|sname|sex|address|+------+-------+------+-----------+|0901|Jim|1|shanghai||0902|helun|2|beijing||0903|sam|1|sichuan||0904|keke|1|xizang||0905|gugu|1|suzhou||0906|tang|2|guangdong||0907|liu|1|jiangxi||0908|wang|2|wuxi|+------+-------+------+-----------+8rowsinset(0.00sec)恢復(fù)完成!基于時(shí)間點(diǎn)的恢復(fù)(不完全恢復(fù))由于誤操作,比如刪除了一張表,使用完全恢復(fù)是沒(méi)有用的,我們需要的是恢復(fù)到誤操作之前的狀態(tài),然后跳過(guò)誤操作語(yǔ)句,再恢復(fù)后面執(zhí)行的語(yǔ)句,完成恢復(fù);例:(1)上午10點(diǎn)發(fā)生誤操作,可以用如下語(yǔ)句備份和BINLOG將數(shù)據(jù)恢復(fù)到故障前#mysqlbinlog--stop-date="2010-10-319:59:59"/usr/local/mysql/var/mysql-bin.000013|mysql-uroot-p(2)跳過(guò)故障時(shí)間點(diǎn),繼續(xù)執(zhí)行后面的BINLOG,完成恢復(fù)#mysqlbinlog--start-date="2010-10-3110:01:00"/usr/local/mysql/var/mysql-bin.000013|mysql-uroot-p基于位置恢復(fù)(不完全恢復(fù))和基于時(shí)間點(diǎn)恢復(fù)類(lèi)是,但是更加精確.因?yàn)橥粫r(shí)間點(diǎn)可能有多條SQL語(yǔ)句執(zhí)行;例:#mysqlbinlog--start-date="2010-10-319:55:00"--stop-date="2010-10-3110:05:00"/usr/local/mysql/var/mysql-bin.000013>/tmp/mysql_restore.sql該命令將在/tmp/目錄下創(chuàng)建小的文件,編輯它找到錯(cuò)誤語(yǔ)句前后的位置號(hào),例如前后位置號(hào)分別是368312和368315(2)恢復(fù)了以前的備份文件后,輸入#mysqlbinlog--stop-position="368312"/usr/local/mysql/var/mysql-bin.000013|mysql-uroot-p#mysqlbinlog--start-position="368315"/usr/local/mysql/var/mysql-bin.000013|mysql-uroot-p