mysql二進位日誌的使用

標籤：Database Backup   伺服器   master   mysql   二進位   

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

刪除二進位日誌資訊：

二進位日誌會記錄大量的資訊（其中包含一些無用的資訊）。如果很長時間不清理二進位日誌，將會浪費很多的磁碟空間。但是，刪除之後可能導致資料庫崩潰時無法進行恢複，所以若要刪除二進位日誌首先將其和Database Backup一份，其中也只能刪除備份前的二進位日誌，新產生的日誌資訊不可刪(可以做即時點還原)。也不可在關閉mysql伺服器之後直接刪除因為這樣可能會給資料庫帶來錯誤的。若非要刪除二進位日誌需要做如下操作：匯出備份資料庫和二進位記錄檔進行壓縮Archive Storage。刪除二進位檔案的方法如下：

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

mysql二進位日誌的使用