mysql數據庫的二進制日志文件對于我們來講并不陌生了,我們數據庫一般會開啟二進制日志了,這樣可以保證數據丟失之后利用它來恢復數據了,下面一起來了解一下二進制日志一些格式.
MySQL二制進日志用于記錄數據庫的變更記錄,這里從結構上討論一下日志的格式,每個日志都包含4個字節的magic number 和event的描述包.
日志有前四個字節是magic number:oxfe ox62 0×69 0x6e = 0xfe ‘b”i”n’ 轉成整數:1852400382 用處就是讀4個字節對比不是這個數,說明就不是二進制日志,就不用處理了.
log_event.sh中可以查到:
/* 4 bytes which all binlogs should begin with */
#define BINLOG_MAGIC "/xfe/x62/x69/x6e"
每個event的header大概如下:
-每個event中包含：event的類型,什么時間,由哪版本的MySQL產生的.
-從header頭能找到該event的大小及一些變更信息.
第一個event稱為:format descriptor event(Event描述結構:FDE) 用于說明日志的格式,其它event就是依賴于描述結構不同,用不同的結構記錄數據,最后一個event是:日志切換事件(log-rotation event)用于指點定下個日志的文件名.
每個event的結構大概如下:
- +===================+
- |event header |
- +===================+
- |event data |
- +===================+
現在大多使用的V4結構,從MySQL 5.0起,具體如下:
- +=====================================+
- |event |timestamp 0:4 |
- |header+––––––––––––––+
- | |type_code 4:1 |
- | +––––––––––––––+
- | |server_id 5:4 |
- | +––––––––––––––+
- | |event_length 9:4 | --Vevb.com
- | +––––––––––––––+
- | |next_position 13:4 |
- | +––––––––––––––+
- | |flags 17:2 |
- | +––––––––––––––+
- | |extra_headers 19:x-19|
- +=====================================+
- |event |fixed part x:y |
- |data +––––––––––––––+
- | |variable part |
- +=====================================+
第一個event是FDE結構沒有extra_headers部分,所以固定為19個字節.
FDE的event_data中定長部分為:
2字節的的日志格式版本,從MySQL 5.0后都是4
50字節 用于記錄MySQL的版本號 如:5.6.16-64.2-rel64.2-log 不夠50字節用0×00填充
4字節 日志產生的時間
1字節 header長度,一般是19,如果大于19,則下面的event都有extra_header字段
對于FDE變長部分一般為空
其它Event計算:
header length = x byte
data length = (event_lenth -x )byte
數據區里定長部分長度:
fixed_part=ybyte
variable_part=(event_length-(x+y))byte
補充:二進制操作
開啟mysql二進制日志,編輯my.cnf,添加.
log-bin=/var/log/mysql/mysql-bin.log
開啟日志后需要myssqladmin flush logs才能生效,需要注意的是log-bin指定擴展名是無效的,當mysql創建二進制日志文件時,首先創建一個以“mysql_log_bin”為名稱，以“.index”為后綴的文件,再創建一個以“mysql_log_bin”為名稱，以 “.000001”為后綴的文件,當mysql服務重新啟動一次以“.000001”為后綴的文件會增加一個,并且后綴名加1遞增,如果日志長度超過了 max_binlog_size的上限(默認是1G)也會創建一個新的日志文件；使用flush logs(mysql命令符)或者執行mysqladmin –u –p flush-logs(windows命令提示符)也會創建一個新的日志文件.
查看:由于日志是以二進制方式存儲的,不能直接讀取,需要使用mysql自帶的mysqlbinlog工具來進行查看.
mysqlbinlog mysql-bin.000002 -d test
mysqlbinlog有一些選項可以使用,簡單說明常用選項:
-d,--database=name :指定數據庫名稱,只列出指定數據庫的操作.
-D, --disable-log-bin :執行恢復的時候,禁止二進制日志.可以防止同一臺MySQL加上-t時進入死循環
-o,--offset=n:忽略掉日志前n行命令
-r,--result-file=name:將輸出日志到指定文件
-R, --read-from-remote-server:從一個MySQL服務器上讀取二進制
-s,--short-form:顯示簡單格式,省略一些信息
-S, --socket=name:socket文件連接path.
-t,--to-last-log:和-R一起使用,在二進制日志結束的時候并不會停止,而是在MySQL服務器最后生成的binlog結束,如果輸出和輸入都在一臺MySQL上可能會導致死循環.
--set-charset=char-name:在輸出文本格式的時候,在第一行加上set names char-name.
--start-datetime=# --stop-datetime=#:指定輸出起始日期的日志.
--start-position=# --stop-position=#:指定起始日志的位置.
清理:
刪除全部二進制日志:reset master
刪除部分日志:
PURGE MASTER LOGS TO & PURGE MASTER LOGS BEFORE
PURGE MASTER LOGS TO 'mysql-bin.******'命令，是將'******'編號之前的所有日志進行刪除
PURGE MASTER LOGS BEFORE 'yyyy-mm-dd hh:mm:ss'命令,是將在'yyyy-mm-dd hh:mm:ss'時間之前的所有日志進行刪除.
設置日志過期時間:
修改my.cnf:expire_log_day=5
這里設置保存5天的日志，超過5天的日志會被自動刪除
恢復:
完全恢復:mysqlbinlog mysql-bin.00001|mysql -uroot -p
基于時間點的恢復:
如果誤刪了一張表,使用完全恢復是沒有用的,因為日志里同樣也保留著刪除的sql語句,所以我們需要恢復到誤操作前的狀態,然后跳過誤操作的語句.
假如我在20:00誤刪了一張表,可以使用以下語句恢復:
mysqlbinlog --stop-date='2012-06-05 19:59:59' /var/log/mysql-bin.000001 | mysql -uroot -p
跳過誤刪除的時間點,再執行:
mysqlbinlog --start-date='2012-06-05 20:01:00' /var/log/mysql-bin.000001 | mysql -uroot -p
基于位置點的恢復:
基于位置點的恢復可以得到更為精確的數據。
binlog
如上圖，drop table test這條語句的起始位置是889107，終止位置是889189，那么我們可以使用于以下語句進行恢復:
- mysqlbinlog --stop-position='889107' /var/lib/mysql/mysql-bin.000001|mysql -uroot -p
- mysqlbinlog --start-position='889189' /var/lib/mysql/mysql-bin.000001|mysql -uroot -p
有時有可能因為系統版本的問題,以上方法行不通,可以將二進制導出到一個sql文件中,再直接根據sql語句進行恢復.
mysqlbinlog mysqlbinlog.000001 >log.sql
新聞熱點
疑難解答
