淺析MySQL二進制日志
一般情況下,二進制日志更多的用於數據庫的同步,因為二進制日志記錄了數據庫的所有改變,可以使得SLAVE都可以執行同樣的更新,其實二進制日志可以對數據庫作一個寫入回放,所以也可以用於統計或者即時恢復等其它的目的。
二進制日志僅僅包含可能改變數據庫的語句,估計都很容易理解這個,但是那些還沒有改變且有可能改變數據庫的語句也會記錄下來,比如drop table if exists或者是帶有WHERE條件的UPDATE和DELETE語句。
www.2cto.com
一,二進制日志結構
二進制日志是一系列二進制日志事件(又稱為binlog事件),其實就是很多文件【包括系列日志文件和一個日志索引文件】共同組成二進制日志,這裡每個日志文件稱為binlog文件,每個日志文件由很多個日志事件組成,每個日志文件都是以Format_description事件開頭並且以日志輪換事件Rotate作為文件結束,如:
[sql]
mysql> show binlog events in 'master-bin.000003';
+-------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-------------------+-----+-------------+-----------+-------------+---------------------------------------+
| master-bin.000003 | 4 | Format_desc | 1 | 106 | Server ver: 5.1.34-log, Binlog ver: 4 |
| master-bin.000003 | 106 | Rotate | 1 | 150 | master-bin.000004;pos=4 |
+-------------------+-----+-------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec)
www.2cto.com
Format_description事件包含寫日志文件的服務器信息以及日志文件格式,而Rotate事件包含下一個日志文件的文件名及其開始讀取的位置。
除了這兩個事件以外,日志文件中的其他事件都被分成一個組一個組的形式,在事務存儲引擎中,每個組會對應一個事務,而其它有可能是一個語句,總之,日志文件中的事件要麼是單個語句,要麼是由多條語句組成的事務。
事件類型是有很多種,就是上面的Event_type在實際使用時,會有多個取值,但可以歸納為每個日志事件由三個部分組成:
通用頭。這部分信息就是所有事件都具備的信息,包含一些基本的信息,比如事件類型以及事件的大小,以上面為例可以從Pos和End_log_pos計算出這條語句的大小。
提交頭。這部分信息和特定的事件類型有關。
事件體。這部分信息存儲事件的主要數據,因事件類型不同而不同,例如,事件是Query的時候,存儲查詢語句。如下:
[sql]
| master-bin.000004 | 180 | Query | 1 | 297 | use `db_info`; insert into i_node(name,value) values("sql",@value)
www.2cto.com
二,記錄語句
傳統的MySQL采用基於語句的復制,將實際執行的語句及某些和執行相關的信息一起寫入二進制日志,然後在從庫上重新執行這些語句。由於二進制日志是多個線程往裡寫入數據,避免兩個線程同時更新對於同步來說是很重要的,為此,在事件寫入二進制日志之前,需要獲得一個互斥鎖,然後在事件寫完後釋放該鎖。下面討論一下哪些數據會被寫入二進制日志
2.1 數據操作語言
這通常就是DELETE,INSERT,UPDATE語句。在執行這些語句時,通常是執行語句擁有寫鎖期間寫二進制日志,然後在日志寫操作完成之後釋放鎖,這樣保證二進制日志和語句導致的更新信息是一致的。
2.2 數據定義語言
如一些CREATE TABLE 和ALTER TABLE之類的語句。
2.3 查詢語句
查詢語句的類型是Query事件,這也是最常見的事件,用來存儲主庫上執行的語句,其實除了實際執行的語句外,這個事件還要包含一些附加的信息。如在寫入一行數據中含有AUTO_INCREMENT的字段,我們執行一下寫入,然後可以看到日志事件中多了哪些事件:
執行下面語句:
[sql]
insert into i_node(name,value) values("sql","copy");
可以得到多了兩條日志事件 www.2cto.com
[sql]
| master-bin.000004 | 451 | Intvar | 1 | 479 | INSERT_ID=12 |
| master-bin.000004 | 479 | Query | 1 | 596 | use `db_info`; insert into i_node(name,value) values("sql","copy") |
+-------------------+-----+-------------+-----------+-------------+-----------------------------------------------------------------------------+
其實除此以外,還有其它的一些上下文信息會給當前的執行帶來結果的影響,這些都是MySQL執行時需要知道的隱式信息。如:
當前數據庫。可以看到我執行insert時,並沒有執行use db_info這條語句,但是也被日志事件記錄下來。因為我在最初執行了,後面MYSQL都采用當前的數據庫來執行語句。
用戶自定義變量的值。如我執行下面兩條語句之後
[sql]
mysql> set @value = 'copy-on-write';
Query OK, 0 rows affected (0.00 sec)
mysql> insert into i_node(name,value) values("sql",@value);
Query OK, 1 row affected (0.00 sec)
可以看到此時的日志事件如下:
[sql]
| master-bin.000004 | 596 | Intvar | 1 | 624 | INSERT_ID=13 |
| master-bin.000004 | 624 | User var | 1 | 675 | @`value`=_latin1 0x636F70792D6F6E2D7772697465 COLLATE latin1_swedish_ci |
| master-bin.000004 | 675 | Query | 1 | 792 | use `db_info`; insert into i_node(name,value) values("sql",@value) |
多了一個變量的賦值操作,類型是User var www.2cto.com
RAND()函數的種子。在執行隨機數時,不會記錄其隨機數,會記錄其種子數。
當前時間。
AUTO_INCREMENT字段的插入值,這個是一個上下文,因為它與前面的行有關。
LAST_INSERT_ID函數。
線程ID,調用CORRENT_ID函數。