MySQL學習之二進位日誌

標籤：mysql   二進位日誌   

二進位日誌記錄了資料庫的所有改變，使得任何Slave都可以通過執行Master二進位日誌保持資料的一致。

二進位日誌僅包含可能改變資料庫的語句。那些尚沒有但是可能改變資料庫的語句也會記錄下來，注意那些可能帶來變化的語句，如DROP TABLE IFEXISTS CREATE IF NOT EXISTS，以及那些不匹配任何行的語句，select語句一般不會被記錄，因為它們不會對資料庫做任何改動。

伺服器上的事務通常不是一個接一個順序執行的，而是交錯的並存執行，為了防止兩個事務之間產生衝突導致不一致的結果，伺服器要確保事務的執行是順序化的。二進位日誌按Master上的提交順序記錄事務，雖然事務可能在Master上交錯執行，但每個事務在二進位日誌中的順序是不變的，取決於事務的提交（commit）時間。

二進位日誌的結構

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M00/4C/7E/wKioL1Q-bhvz628zAADCAobzMzM496.jpg" title="9B9]3U5EFA@_Q{YDQ{RY~A9.png" alt="wKioL1Q-bhvz628zAADCAobzMzM496.jpg" />

看，二進位日誌由若干個檔案組成，事件儲存在一系列binlog檔案中，檔案名稱類似於host-bin.000001，還有一個索引檔案通常檔案名稱為host-bin.index，索引檔案Tracing Service器使用的所以binlog檔案，索引檔案的每一行都包含了一個binlog檔案的完整檔案名稱，使用這些命令，如PURGE BINARY LOGS，RESTART MASTER FLUSH LOGS，添加或刪除binlog檔案，也導致索引檔案添加或刪除行。

資料安全

    比較常見的敏感資訊就是密碼，當執行改變資料庫中表的語句，並且它包含訪問這個表所必須的密碼的時候，包含密碼的事件就會被寫入二進位日誌。

例：

       update employee set pass= password(‘123456’) where email = ‘[email protected]’;

    如果複製是正確的，最好重寫這個語句，可以通過以下方法實現：計算和儲存雜湊密碼到使用者自訂變數，然後在運算式中使用它：

       set @password =password(‘123456’);

       update employee set pass= @password  where email = ‘[email protected]’;

    由於set語句沒有被複製，密碼將不會儲存在二進位日誌中，而僅在執行該語句的時候儲存在伺服器記憶體中。

清除binlog檔案

隨著時間的推移，伺服器會集聚binlog檔案，要使伺服器自動清理舊的binlog檔案，需設定expire-logs-days選項。這個選項可用來作為一個伺服器變數，達到你想保留binlog檔案的天數，如果希望在重啟後仍保持自動清除，必須添加該設定到my.cnf檔案。

使用PURGE BINARY LOGS 命令手工清除binlog檔案，有如下兩種格式：

PURGE BINARYLOGS BEFORE datetime

這個命令格式將清除在給定時間之前的所有binlog檔案。如果datetime在一個記錄檔的中間，那麼datetime所在的那個檔案之前的所有檔案將被清除。

實戰演練：

1、由於是通過日期時間刪除，我們先要知道需要刪除那個時間之前的二進位日誌。

mysqlbinlog  --force-if-open--base64-output=never mysql-bin.000003

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M02/4C/7E/wKioL1Q-byHyk5DoAAJv_OFARIo967.jpg" title="GMZVNBE@ZKLHB0IY{05E3]I.jpg" alt="wKioL1Q-byHyk5DoAAJv_OFARIo967.jpg" />

2、假如我們需要刪除2014/09/09之前的日誌，執行如下命令：

PURGE BINARY LOGS BEFORE 140909；

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M00/4C/7D/wKiom1Q-b2ahjwdVAAJM8b2znxw457.jpg" title="19ZVKKPC5I]65(}@B)P4[VC.jpg" alt="wKiom1Q-b2ahjwdVAAJM8b2znxw457.jpg" />

PURGE BINARY LOGS TO ‘filename’

這個命令格式將清除在給定檔案之前的所有檔案。也就是Show masterlogs 命令輸出的所有檔案中，在filename之前的檔案都將被刪除。

實戰演練：

PURGE BINARY LOGS TO ‘mysql-bin.000002‘;

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M02/4C/7E/wKioL1Q-b96x3XpCAAGBmvM3Ujg201.jpg" title="QQ20141015172403.png" alt="wKioL1Q-b96x3XpCAAGBmvM3Ujg201.jpg" />

mysqlbinlog 工具的使用

mysqlbinlog

       --short-form 只列印被執行的SQL語句資訊，忽略關於二進位日誌的事件注釋資訊。

       --force-if-open 如果binlog檔案正在被寫入，或因為伺服器崩潰，mysqlbinlog都將列印一條警告資訊說這個binlog檔案沒有被正確關閉，這個選項忽略列印警告資訊。

       --base64-output=never  阻止mysqlbinlog列印base64-encoded事件。

       start-position=bytepos 轉儲的第一個事件的位元組位置。如果幾個binlog是提供給mysqlbinlog的，這個位置將被解釋為在序號中第一個檔案的位置。

       stop-position=bytepos 最後列印的事件的位元組位置，如果給定了多個binlog檔案，該位置將是序列中最後一個檔案的位置。

       start-datetime=datetime 只列印那些有時間戳記或datetime後的事件。

       stop-datetime=datetime 只列印那些有時間戳記或datetime前的事件。

讀取遠程檔案執行個體：

       mysqlbinlog  --read-from-remote-server --host=192.168.18.202 --base64-output=never --user=repl_user --password --start-position=386 --stop-position=643 mysqld-bin.000001

我們接下來使用mysqlbinlog來讀取一個二進位記錄檔：

mysqlbinlog --force-if-open--base64-output=never /mylogbin/mysql-bin.000001 | cat -n

 

     1    /*!50530 [email protected]@SESSION.PSEUDO_SLAVE_MODE=1*/;

     2    /*!40019SET @@session.max_insert_delayed_threads=0*/;

     3    /*!50003SET @[email protected]@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

     4    DELIMITER/*!*/;

     5    #at 4

     6    #141015  9:03:53 server id 1  end_log_pos 245      Start: binlog v 4, server v 5.5.32-MariaDB-log created141015  9:03:53 at startup

     7    ROLLBACK/*!*/;

     8    #at 245

     9    #141015  9:09:21 server id 1  end_log_pos 428      Query     thread_id=1    exec_time=0   error_code=0

    10    use`test`/*!*/;

    11    SETTIMESTAMP=1413335361/*!*/;

    12    [email protected]@session.pseudo_thread_id=1/*!*/;

    13    [email protected]@session.foreign_key_checks=1, @@session.sql_auto_is_null=0,@@session.unique_checks=1, @@session.autocommit=1/*!*/;

    14    [email protected]@session.sql_mode=0/*!*/;

    15    [email protected]@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;

    16    /*!\Cutf8 *//*!*/;

    17    [email protected]@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;

    18    [email protected]@session.lc_time_names=0/*!*/;

    19    [email protected]@session.collation_database=DEFAULT/*!*/;

    20    createtable employee ( id int auto_increment,name char(64) not null,emailchar(64),password char(64),primary key (id) )

    21    /*!*/;

    22    #at 428

    23    #141015  9:12:27 server id 1  end_log_pos 496      Query     thread_id=1    exec_time=0   error_code=0

    24    SETTIMESTAMP=1413335547/*!*/;

    25    BEGIN

    26    /*!*/;

 

    1~7行相對應的部分，它們修複序言中設定的值，然後復原所以活動事務。

    每當資料庫更改時，第10行的use語句會被列印，當一個use語句出現時，他就是一個新事件的第一行。

    緊接在use語句下面一行是SET TIMESTAMP 這個語句提供自紀元時間開始以秒計算的事件開始執行時的時間戳記。

    12~19行包含常規設定，類似於第10行的use，只有第一個事件和當它們的值被改變時，它們才會被列印。

注釋詳解

# at 245

#141015  9:09:21 server id 1  end_log_pos 428    Query     thread_id=1

       exec_time=0   error_code=0

注釋的各部分意義如下：

at 245

事件開始的位元組位置，也就是該事件的第一個位元組；

141015  9:09:21

事件被寫入二進位日誌的時間；

Server id 1

產生改事件的伺服器的server id；

end_log_pos 428

緊接著該事件之後，下一個事件開始的位元組位置；

Query

事件類型，例如User_var，Intvar和Xid；

thread_id=1

執行該事件的線程ID；

exec_time=0

以秒為單位的查詢執行時間；

error_code=0

從語句執行結果得到的錯誤碼；

二進位日誌配置選項

expire-log-days=days

binlog需要保留的天數。當二進位日誌已經輪換或伺服器重啟時，比指定數值老的檔案將從檔案系統中刪除。這個選項預設是0，意味著binlog檔案永遠不會被刪除。

 

log-bin [ =basename ]

通過在my.cnf中添加log-bin選項來開啟二進位日誌，這個選項還給出了binlog檔案的基本名稱，也就是在圓點之前的部分檔案名稱。如果該選項沒有指定basename，則基本名預設為host-bin，強烈建議給log-bin選項賦值。否則當hostname改變時，binlog檔案將改名（除非pid-file被給定一個確切值）。

 

log-bin-index [ =filename ]

給出索引檔案的名稱，與log-bin選項情況類似，hostname將被用來組成索引檔案名稱，意味著如果hostname改變，複製將中斷，出於這個原因建議給這個選項賦值。

 

binlog-cache-size=bytes

事務緩衝，當事務緩衝的大小超過該值時，剩餘的資料將進入磁碟。這有可能造成效能問題，因此如果你使用許多大型事務，增大該選項可以提高效能。

注意，只分配非常大的緩衝區並不是好主意，因為這意味著伺服器的其他部分得到更少的記憶體，這可能導致效能下降。

 

max-binlog-cache-size=bytes

使用這個選項來限制在二進位日誌中的每個事務的大小。由於大型事務有可能阻塞二進位日誌很長時間，他們將導致其他線程為二進位日誌護航二造成重大效能問題。如果事務的大小超過bytes，該語句將出錯而被終止。

 

max-binlog-size=bytes

指定每個binlog檔案的大小，如事務或語句超過了max-binlog-size，二進位日誌將被輪換，該事務全部內容將被寫入新的檔案，因為事務永遠不會被分割到不同的binlog檔案。

 

sync-binlog=period (時段，周期)

指定多長時間通過fdatasync寫二進位日誌到磁碟一次，如果給定的數值為1，每次事務提交時調用fdatasync，而如果給定值為10，則每10個事務提交將調用一次fdatasync。數值為0表示永遠不會調用，而且伺服器相信作業系統會寫二進位日誌到磁碟，作為正常檔案處理的一部分。

 

read-only

防止任何用戶端進程（除了Slave進程和有SUPER許可權的使用者）補救伺服器上的任何資料。

本篇內容純手打，是筆者看書學到的知識，從中摘取了一些比較關鍵的內容和筆者的實驗過程記錄了下來。

本文出自 “壞人的部落格” 部落格，轉載請與作者聯絡！

MySQL學習之二進位日誌