MySql二進(jìn)制日志的應(yīng)用

bin-log日志記錄了所有的DDL和DML的語(yǔ)句，但不包括查詢(xún)的語(yǔ)句，語(yǔ)句以事件的方式保存，描述了數(shù)據(jù)的更改過(guò)程，此日志對(duì)發(fā)生災(zāi)難時(shí)數(shù)據(jù)恢復(fù)起到了極為重要的作用。

開(kāi)啟
mysql默認(rèn)是沒(méi)有開(kāi)發(fā)bin-log日志，首先我們需要開(kāi)啟bin-log日志，在my.cnf中修改



指定了bin-log日志的路徑，開(kāi)啟日志后需要myssqladmin flush log才生效,重啟后我們發(fā)現(xiàn)在剛才設(shè)定的路徑新增了log文件，這就是我們需要的二進(jìn)制日志



由于日志是以二進(jìn)制方式存儲(chǔ)的，不能直接讀取，需要使用mysql自帶的mysqlbinlog工具來(lái)進(jìn)行查看
語(yǔ)法如下：

#mysqlbinlog mysql-bin.000002


現(xiàn)在我們嘗試向test1表插入數(shù)據(jù)



然后使用mysqlbinlog工具進(jìn)行日志查看

#mysqlbinlog mysql-bin.000002-d test




清理

如果每天都會(huì)生成大量的二進(jìn)制日志，這些日志長(zhǎng)時(shí)間不清理的話(huà)，將會(huì)對(duì)磁盤(pán)空間帶來(lái)很大的浪費(fèi)，所以定期清理日志是DBA維護(hù)mysql的一個(gè)重要工作

1)RESET MASTER
在上面查看日志存放的文件夾中，二進(jìn)制日志命名的格式是以mysql-bin.*，*代表日志的序號(hào),序號(hào)是遞增的,其中還有mysql-bin.index是日志的索引文件,記錄了日志的最大序號(hào)
我們執(zhí)行RESET MASTER命名刪除全部日志



查看刪除后的日志



可以看到,以前的日志全部被清空,新的日志從00001開(kāi)始

2)PURGE MASTER LOGS TO&PURGE MASTER LOGS BEFORE
執(zhí)行PURGE MASTER LOGS TO 'mysql-bin.******'命令，是將'******'編號(hào)之前的所有日志進(jìn)行刪除
執(zhí)行PURGE MASTER LOGS BEFORE 'yyyy-mm-dd hh:mm:ss'命令，是將在'yyyy-mm-dd hh:mm:ss'時(shí)間之前的所有日志進(jìn)行刪除

3)-EXPIRE_LOGS_DAYS
此參數(shù)是設(shè)置日志的過(guò)期天數(shù)，過(guò)期的日志將會(huì)被自動(dòng)刪除，這有利于減少我們管理日志的工作量，需要修改my.cnf



這里我們?cè)O(shè)定保存日志為3天，3天之后過(guò)期的日志將被自動(dòng)刪除

恢復(fù)

bin-log是記錄著mysql所有事件的操作，當(dāng)mysql發(fā)生災(zāi)難性錯(cuò)誤時(shí)，可以通過(guò)bin-log做完整恢復(fù)，基于時(shí)間點(diǎn)的恢復(fù)，和基于位置的恢復(fù)

完整恢復(fù)，假定我們每天凌晨2點(diǎn)都會(huì)使用mysqldump備份數(shù)據(jù)庫(kù)，但在第二天早上9點(diǎn)由于數(shù)據(jù)庫(kù)出現(xiàn)了故障，數(shù)據(jù)無(wú)法訪問(wèn)，需要恢復(fù)數(shù)據(jù)，先使用昨天凌晨備份的文件進(jìn)行恢復(fù)到凌晨2點(diǎn)的狀態(tài)，在使用mysqlbinlog恢復(fù)自mysqldump備份以來(lái)的binlog
mysql localhost mysql-bin.000001 | mysql -uroot -p
這樣數(shù)據(jù)庫(kù)就可以完全的恢復(fù)到崩潰前的完全狀態(tài)

基于時(shí)間點(diǎn)的恢復(fù)，由于誤操作，比如說(shuō)刪除了一張表，這時(shí)使用上面講的完全恢復(fù)是沒(méi)有用的，因?yàn)槿罩纠锩孢�存在誤操作的語(yǔ)句，，我們需要的是恢復(fù)到誤操作前的狀態(tài)，然后跳過(guò)誤操作的語(yǔ)句，再恢復(fù)后面操作的語(yǔ)句，假定我們刪除了一張表的誤操作發(fā)生在10:00這個(gè)時(shí)間點(diǎn)，我們可以使用下面的語(yǔ)句用備份和binlog將數(shù)據(jù)恢復(fù)到故障前

mysqlbinlog --stop-date='2010-09-04 9:59:59' /var/log/mysql-bin.000001 | mysql -uroot -p


然后跳過(guò)誤操作的時(shí)間點(diǎn)，繼續(xù)執(zhí)行后面的binlog

mysqlbinlog --start-date='2010-09-04 10:01:00' /var/log/mysql-bin.000001 | mysql -uroot -p


其中--stop-date='2010-09-04 9:59:59' 和 --start-date='2010-09-04 10:01:00' 其中的時(shí)間是你誤操作的時(shí)間點(diǎn)，當(dāng)然了，這個(gè)時(shí)間點(diǎn)你需要你自己計(jì)算的，而且這個(gè)時(shí)間點(diǎn)還可以涉及到的不只是誤操作，還可以有正確的操作也被跳過(guò)去了。

基于位置恢復(fù)，由于上面提到的，使用基于時(shí)間點(diǎn)的恢復(fù)可能出現(xiàn)，在一個(gè)時(shí)間點(diǎn)里面可能存在誤操作和其他正確的操作，所以我們需要一種更為精確的恢復(fù)方式
使用mysqlbinlog查看二進(jìn)制，可看到



其中drop tables test1這個(gè)誤操作的end_log_pos為8879917，幾下這個(gè)id,得出它前后操作的id分別為8879916,8879918
我們將進(jìn)行位置恢復(fù)操作

mysqlbinlog --stop-position='8879916' /var/log/mysql-bin.000001 | mysql -uroot -p

mysqlbinlog --start-position='8879918' /var/log/mysql-bin.000001 | mysql -uroot -p


第一行是恢復(fù)到停止位置位置的所以事務(wù)，第二性是恢復(fù)從給定的起始位置知道二進(jìn)制日志結(jié)束所有事物。