mysql二進制日志的使用

mysql二進制日志也就是一些mysql命令操作的記錄

刪除二進制日志信息：

二進制日志會記錄大量的信息（其中包含一些無用的信息）。如果很長時間不清理二進制日志，將會浪費很多的磁盤空間。但是，刪除之后可能導致數據庫崩潰時無法進行恢復，所以若要刪除二進制日志首先將其和數據庫備份一份，其中也只能刪除備份前的二進制日志，新產生的日志信息不可刪(可以做即時點還原)。也不可在關閉mysql服務器之后直接刪除因為這樣可能會給數據庫帶來錯誤的。若非要刪除二進制日志需要做如下操作：導出備份數據庫和二進制日志文件進行壓縮歸檔存儲。刪除二進制文件的方法如下：

1、使用RESET MASTER語句可以刪除所有的二進制日志重新記錄

mysql> reset master;

mysql> show binary logs;

解析：首先不建議在生產環境下使用此操作；刪除所有的二進制日志后，Mysql將會重新創建新的二進制日志。新二進制日志的編號從000001開始。

2、根據文件或時間點來刪除二進制日志：

語法形式：

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

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

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

mysql> PURGEBINARY LOGS BEFORE '2013-10-19 10:26:36'; #使用時間來刪除二進制日志

★注意：my.cnf配置開啟二進制日志功能默認記錄所有數據庫，如果指定數據庫做二進制日志記錄則添加

log-bin = mysql-bin

binlog-do-db = happy

binlog-do-db = iwker_global

##二進制開啟狀態：

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

##使用show master status命令可以查看當前啟用二進制日志記錄的數據庫

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上的二進制日志

mysql> show binary logs;

★★★使用mysqlbinlog工具進行恢復

步驟：

1、分析定位關鍵的位置或者時間點

2、導入全庫備份的文件

3、注釋掉誤操作的命令或者避開誤操作的時間點進行導入

//二進制日志轉換成可讀的SQL文件

mysqlbinlog mysql-bin.000001 > mysql.sql    

//或者只導出某個指定數據庫的二進制日志

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

a.查詢二進制日志文件含有drop操作的記錄

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

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

b.根據時間來還原 --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.根據數據庫名來進行還原

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

d.基于位置恢復

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