mysql二進位日誌(bin-log)配置及相關操作

標籤：mysql二進位日誌(bin-log)配置及相關操作

1.開啟bin-log

(1)在my.con設定檔裡面

[mysqld] #選項添加

log-bin=mysql-bin #記錄檔名稱，未指定位置，預設資料檔案位置

重啟mysql服務

log_bin是產生的bin-log的檔案名稱，尾碼則是6位元字的編碼，從000001開始，按照上面的配置，產生的檔案則為： 

      mysql_bin.000001 

      mysql_bin.000002 

      ...... 

2.基本操作

(1)查看所有記錄檔:

mysql> show binary logs;   或show master logs;

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

| Log_name         | File_size |

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

| mysql-bin.000001 |       120 |

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

(2)查看正在寫入的記錄檔：

mysql> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000001 |      120 |              |                  |                   |

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

(3)查看當前binlog檔案內容：

mysql> show binlog events;  #可以格式化輸出 show binlog events\G; 或指定記錄檔查看show binlog events in ‘mysql-bin.000001‘;

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

| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                  |

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

| mysql-bin.000001 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.25-log, Binlog ver: 4 |

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

註：

Log_name:此條log存在哪個檔案中 

Pos:log在bin-log中的開始位置 

Event_type:log的類型資訊 

Server_id:可以查看配置中的server_id,表示log是哪個伺服器產生 

End_log_pos：log在bin-log中的結束位置 

Info:log的一些備忘資訊，可以直觀的看出進行了什麼操作 

(4)手動啟用新的記錄檔,一般備份完資料庫後執行

mysql> show master status; #

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000001 |      120 |              |                  |                   |

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

1 row in set (0.00 sec)

mysql> flush logs; #結束正在寫入記錄檔

Query OK, 0 rows affected (0.00 sec)

mysql> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000002 |      120 |              |                  |                   |

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

(5)刪除所有二進位日誌，並從新開始記錄 

mysql> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000002 |      120 |              |                  |                   |

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

1 row in set (0.00 sec)

mysql> reset master; #重新開始

Query OK, 0 rows affected (0.00 sec)

mysql> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000001 |      120 |              |                  |                   |

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

另外：

mysql> purge master logs to ‘mysql-bin.000002‘;  #是將‘mysql-bin.000002‘編號之前的所有日誌進行刪除

mysql> purge master logs before ‘yyyy-mm-dd hh:mm:ss‘ #是將在‘yyyy-mm-dd hh:mm:ss‘時間之前的所有日誌進行刪除

3.二進位記錄檔匯出

# mysqlbinlog --start-datetime="2015-07-02 11:25:56" --stop-datetime="2015-07-02 14:20:10" mysql-bin.000001 > /data/test01.log #按時間點匯出

# mysqlbinlog --start-position=203  --stop-position=203 mysql-bin.000001 > /data/test02.log #按事件位置匯出

4.恢複資料

強烈建議：做任何恢複之前都給資料庫做一個完整備份，建立庫進行恢複。

恢複

 bin-log是記錄著mysql所有事件的操作，可以通過bin-log做完整恢複，基於時間點的恢複，和基於位置的恢複

(1)完整恢複，先執行上次完整備份恢複，再執行自上次備份後產生的二進位記錄檔恢複

# mysql localhost mysql-bin.000001 | mysql -uroot -p

這樣資料庫就可以完全的恢複到崩潰前的完全狀態

(2)基於時間點的恢複，如果確認誤操作時間點為2015-06-04 10:00:00執行如下

# mysqlbinlog --stop-date=‘2015-06-04 9:59:59‘ mysql-bin.000001 | mysql -uroot -p

然後跳過誤操作的時間點，繼續執行後面的binlog

# mysqlbinlog --start-date=‘2015-06-04 10:01:00‘ mysql-bin.000001 | mysql -uroot -p

其中--stop-date=‘2015-06-04 9:59:59‘ 和 --start-date=‘2015-06-04 10:01:00‘ 

取兩時間點

# mysqlbinlog --start-datetime="2015-07-02 11:25:56" --stop-datetime="2015-07-02 14:20:10" mysql-bin.000001 | mysql -u root -p 

#註：其中的時間是你誤操作的時間，而且這個時間點還可能涉及到的不只是誤操作，也有可能有正確的操作也被跳過去了。那麼執行位置恢複

基於位置恢複，通過查看記錄檔資訊，確認6259-6362為誤操作點

# mysqlbinlog --stop-position=6259 mysql-bin.000001 | mysql -uroot -p #從1開始至6259的事件讀，不包括6259事件

# mysqlbinlog --start-position=6363 mysql-bin.000001 | mysql -uroot -p #從6259的事件開始讀

# 取兩事件點

mysqlbinlog --start-position=5786 --stop-position=6254 mysql-bin.000001 | mysql -uroot -p

本文出自 “Home_Tang” 部落格，請務必保留此出處http://yagetang.blog.51cto.com/1780698/1670236

mysql二進位日誌(bin-log)配置及相關操作