mysql二進(jìn)制日志的使用

mysql二進(jìn)制日志也就是一些mysql命令操作的記錄

刪除二進(jìn)制日志信息：

二進(jìn)制日志會(huì)記錄大量的信息（其中包含一些無(wú)用的信息）。如果很長(zhǎng)時(shí)間不清理二進(jìn)制日志，將會(huì)浪費(fèi)很多的磁盤空間。但是，刪除之后可能導(dǎo)致數(shù)據(jù)庫(kù)崩潰時(shí)無(wú)法進(jìn)行恢復(fù)，所以若要?jiǎng)h除二進(jìn)制日志首先將其和數(shù)據(jù)庫(kù)備份一份，其中也只能刪除備份前的二進(jìn)制日志，新產(chǎn)生的日志信息不可刪(可以做即時(shí)點(diǎn)還原)。也不可在關(guān)閉mysql服務(wù)器之后直接刪除因?yàn)檫@樣可能會(huì)給數(shù)據(jù)庫(kù)帶來(lái)錯(cuò)誤的。若非要?jiǎng)h除二進(jìn)制日志需要做如下操作：導(dǎo)出備份數(shù)據(jù)庫(kù)和二進(jìn)制日志文件進(jìn)行壓縮歸檔存儲(chǔ)。刪除二進(jìn)制文件的方法如下：

1、使用RESET MASTER語(yǔ)句可以刪除所有的二進(jìn)制日志重新記錄

mysql> reset master;

mysql> show binary logs;

解析：首先不建議在生產(chǎn)環(huán)境下使用此操作；刪除所有的二進(jìn)制日志后，Mysql將會(huì)重新創(chuàng)建新的二進(jìn)制日志。新二進(jìn)制日志的編號(hào)從000001開(kāi)始。

2、根據(jù)文件或時(shí)間點(diǎn)來(lái)刪除二進(jìn)制日志：

語(yǔ)法形式：

mysql> PURGE { BINARY | MASTER } LOGS {TO 'log_name' | BEFORE datetime_expr }

其中TO'log_name'表示把這個(gè)文件之前的其他文件都刪除掉，也可使用BEFORE datetime_expr指定把哪個(gè)時(shí)間之前的二進(jìn)制文件刪除了。

mysql> PURGE BINARY LOGS TO 'mysql-bin.000007';  #把mysql-bin.000007文件之前的其他文件都刪除掉（000001--000006）

mysql> PURGEBINARY LOGS BEFORE '2013-10-19 10:26:36'; #使用時(shí)間來(lái)刪除二進(jìn)制日志

★注意：my.cnf配置開(kāi)啟二進(jìn)制日志功能默認(rèn)記錄所有數(shù)據(jù)庫(kù)，如果指定數(shù)據(jù)庫(kù)做二進(jìn)制日志記錄則添加

log-bin = mysql-bin

binlog-do-db = happy

binlog-do-db = iwker_global

##二進(jìn)制開(kāi)啟狀態(tài)：

mysql> show global variables like "%log_bin%";

##使用show master status命令可以查看當(dāng)前啟用二進(jìn)制日志記錄的數(shù)據(jù)庫(kù)

mysql> show master status;

+------------------+----------+-----------------------------------------------+------------------+-------------------+

| File             | Position | Binlog_Do_DB                                  | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+-----------------------------------------------+------------------+-------------------+

| mysql-bin.000002 |      120 | iwker_global,iwker_inside,iwker_ucenter |                  |                   |

+------------------+----------+-----------------------------------------------+------------------+-------------------+

##查看MySQL Server上的二進(jìn)制日志

mysql> show binary logs;

★★★使用mysqlbinlog工具進(jìn)行恢復(fù)

步驟：

1、分析定位關(guān)鍵的位置或者時(shí)間點(diǎn)

2、導(dǎo)入全庫(kù)備份的文件

3、注釋掉誤操作的命令或者避開(kāi)誤操作的時(shí)間點(diǎn)進(jìn)行導(dǎo)入

//二進(jìn)制日志轉(zhuǎn)換成可讀的SQL文件

mysqlbinlog mysql-bin.000001 > mysql.sql    

//或者只導(dǎo)出某個(gè)指定數(shù)據(jù)庫(kù)的二進(jìn)制日志

mysqlbinlog -d happy mysql-bin.000001 > happy_bin.sql

a.查詢二進(jìn)制日志文件含有drop操作的記錄

mysqlbinlog binlog.0000003 |less    //輸出包括在binlog.000003中包含的所有語(yǔ)句，以及其它信息例如每個(gè)語(yǔ)句花費(fèi)的時(shí)間、客戶發(fā)出的線程ID、發(fā)出線程時(shí)的時(shí)間戳等等。

mysqlbinlog mysql-bin.000002 |grep -i -C 2 "drop table" -c

b.根據(jù)時(shí)間來(lái)還原 --start-datetime,--stop-datetime

mysqlbinlog --start-datetime="2010-09-29 18:00:00" --stop-datetime="2010-09-29 23:00:00" /var/lib/mysql/mysql-bin.000002 |mysql -u root -p

mysqlbinlog --stop-datetime='2016-07-21 14:40:10' /var/log/mysql-bin.000001 | mysql -uroot -p

c.根據(jù)數(shù)據(jù)庫(kù)名來(lái)進(jìn)行還原

mysqlbinlog -d iwker_inside  /var/lib/mysql/mysql-bin.000002

d.基于位置恢復(fù)

mysqlbinlog --start-position=370 --stop-position=440  /var/lib/mysql/mysql-bin.000002 |mysql -u root -p