MySQL 二進位日誌(Binary Log)

標籤：database   sql   dba   mysql   

    同大多數關係型資料庫一樣，記錄檔是MySQL資料庫的重要組成部分。MySQL有幾種不同的記錄檔，通常包括錯誤記錄檔檔案，二進位日誌，通用日誌，慢查詢日誌，等等。這些日誌可以協助我們定位mysqld內部發生的事件，資料庫效能故障，記錄資料的變更曆史，使用者恢複資料庫等等。二進位日誌，也叫binary log，是MySQL Server中最為重要的日誌之一，本文主要描述二進位日誌。

 

1、MySQL記錄檔系統的組成
   a、錯誤記錄檔：記錄啟動、運行或停止mysqld時出現的問題。
   b、通用日誌：記錄建立的用戶端串連和執行的語句。
   c、更新日誌：記錄更改資料的語句。該日誌在MySQL 5.1中已不再使用。
   d、二進位日誌：記錄所有更改資料的語句。還用於複製。
   e、慢查詢日誌：記錄所有執行時間超過long_query_time秒的所有查詢或不使用索引的查詢。
   f、Innodb日誌：innodb redo log
 
   預設情況下，所有日誌建立於mysqld資料目錄中。
   可以通過重新整理日誌，來強制mysqld來關閉和重新開啟記錄檔（或者在某些情況下切換到一個新的日誌）。
   當你執行一個FLUSH LOGS語句或執行mysqladmin flush-logs或mysqladmin refresh時，則日誌被老化。
   對於存在MySQL複製的情形下，從複製伺服器將維護更多記錄檔，被稱為接替日誌。

 

2、二進位日誌(Binary log)
  a、它包含的內容及作用如下：
    包含了所有更新了資料或者已經潛在更新了資料(比如沒有匹配任何行的一個DELETE)
    包含關於每個更新資料庫(DML)的語句的執行時間資訊
    不包含沒有修改任何資料的語句，如果需要啟用該選項，需要開啟通用日誌功能
    主要目的是儘可能的將資料庫恢複到資料庫故障點，因為二進位日誌包含備份後進行的所有更新
    用於在主複製伺服器上記錄所有將發送給從伺服器的語句
    啟用該選項資料庫效能降低1%，但保障資料庫完整性，對於重要資料庫值得以效能換完整。有些類似於oracle開啟歸檔模式。
  
  b、開啟二進位日誌的方法及屬性
    使用--log-bin[=file_name]選項或在設定檔中指定log-bin啟動時，mysqld寫入包含所有更新資料的SQL命令的記錄檔。
    對於未給出file_name值， 預設名為-bin後面所跟的主機名稱。
    在未指定絕對路徑的情形下，預設位置儲存在資料目錄下。
    每個二進位日誌名會添加一個數字副檔名用於日誌老化，因此不支援自訂的副檔名，會被mysql數字副檔名動態替換。
    若當前的日誌大小達到max_binlog_size，則自動建立新的二進位日誌。
    對於大的事務，二進位日誌會超過max_binlog_size設定的值。也即是事務僅僅寫入一個二進位日誌。
    由是可知，二進位記錄檔大小接近，其size不是完全相等，這點不同於oracle。
    二進位記錄檔會有一個對應二進位日誌索引檔案，該檔案包含所有的二進位日誌，其檔案名稱與二進位日誌相同，副檔名為.index
    二進位索引檔案通過--log-bin-index[=file_name]選項來指定
    RESET MASTER語句將刪除所有二進位記錄檔，這將影響到從庫。也可以用PURGE MASTER LOGS只刪除部分二進位檔案。
  

3、二進位日誌相關示範

a、啟用二進位日誌--當前環境[email protected][(none)]> show variables like ‘%version%‘;+-------------------------+------------------------------+| Variable_name           | Value                        |+-------------------------+------------------------------+| innodb_version          | 5.5.39                       || protocol_version        | 10                           || slave_type_conversions  |                              || version                 | 5.5.39                       || version_comment         | MySQL Community Server (GPL) || version_compile_machine | x86_64                       || version_compile_os      | Linux                        |+-------------------------+------------------------------+[email protected][(none)]> show variables like ‘%log_bin%‘;+---------------------------------+-------+| Variable_name                   | Value |+---------------------------------+-------+| log_bin                         | OFF   | --該參數用於設定是否啟用二進位日誌| log_bin_trust_function_creators | OFF   || sql_log_bin                     | ON    |+---------------------------------+-------+--以下為binary log相關參數[email protected][(none)]> show variables like ‘%binlog%‘;+-----------------------------------------+----------------------+| Variable_name                           | Value                |+-----------------------------------------+----------------------+| binlog_cache_size                       | 32768                || binlog_direct_non_transactional_updates | OFF                  || binlog_format                           | STATEMENT            || binlog_stmt_cache_size                  | 32768                || innodb_locks_unsafe_for_binlog          | OFF                  || max_binlog_cache_size                   | 18446744073709547520 || max_binlog_size                         | 1073741824           || max_binlog_stmt_cache_size              | 18446744073709547520 || sync_binlog                             | 0                    |+-----------------------------------------+----------------------+--當前mysql伺服器資料檔案的預設位置[email protected][(none)]> show variables like ‘%datadir%‘;+---------------+-----------------+| Variable_name | Value           |+---------------+-----------------+| datadir       | /var/lib/mysql/ |+---------------+-----------------+--停止mysql伺服器suse11b:~ # service mysql stopShutting down MySQL....                                               done--編輯my.cnf來設定binary log日誌位置(注，配置二進位日誌路徑及檔案名稱後，系統變數log_bin被自動置為on)suse11b:~ # vi /etc/my.cnfsuse11b:~ # grep -v ^# /etc/my.cnf[mysqld]log-error=/tmp/suse11b.errlog_bin=/var/lib/mysql/binarylog/binlogsuse11b:~ # mkdir -p /var/lib/mysql/binarylogsuse11b:~ # chown -R mysql:mysql /var/lib/mysql/binarylogsuse11b:~ # /etc/init.d/mysql startStarting MySQL..                                                      donesuse11b:~ # ls -hltr /var/lib/mysql/binarylog/*-rw-rw---- 1 mysql mysql  39 Oct  3 13:41 /var/lib/mysql/binarylog/binlog.index  #索引檔案-rw-rw---- 1 mysql mysql 107 Oct  3 13:41 /var/lib/mysql/binarylog/binlog.000001 #記錄檔b、切換日誌suse11b:~ # mysql -uroot -pxxx[email protected][(none)]> flush logs;Query OK, 0 rows affected (0.04 sec)[email protected][(none)]> system ls -hltr /var/lib/mysql/binarylog/*-rw-rw---- 1 mysql mysql  78 Oct  3 13:43 /var/lib/mysql/binarylog/binlog.index-rw-rw---- 1 mysql mysql 107 Oct  3 13:43 /var/lib/mysql/binarylog/binlog.000002  #切換後產生了000002-rw-rw---- 1 mysql mysql 147 Oct  3 13:43 /var/lib/mysql/binarylog/binlog.000001[email protected][(none)]> system mysqladmin flush-logs    #使用mysqladmin命令列工具flush-logs方式切換日誌[email protected][(none)]> system ls -hltr /var/lib/mysql/binarylog/*-rw-rw---- 1 mysql mysql 147 Oct  3 13:43 /var/lib/mysql/binarylog/binlog.000001-rw-rw---- 1 mysql mysql 117 Oct  3 13:45 /var/lib/mysql/binarylog/binlog.index-rw-rw---- 1 mysql mysql 107 Oct  3 13:45 /var/lib/mysql/binarylog/binlog.000003  #切換後產生了000003-rw-rw---- 1 mysql mysql 147 Oct  3 13:45 /var/lib/mysql/binarylog/binlog.000002[email protected][(none)]> system mysqladmin refresh       #使用mysqladmin命令列工具refresh方式切換日誌[email protected][(none)]> system ls -hltr /var/lib/mysql/binarylog/*-rw-rw---- 1 mysql mysql 147 Oct  3 13:43 /var/lib/mysql/binarylog/binlog.000001-rw-rw---- 1 mysql mysql 147 Oct  3 13:45 /var/lib/mysql/binarylog/binlog.000002-rw-rw---- 1 mysql mysql 156 Oct  3 13:46 /var/lib/mysql/binarylog/binlog.index-rw-rw---- 1 mysql mysql 107 Oct  3 13:46 /var/lib/mysql/binarylog/binlog.000004  #切換後產生了000004-rw-rw---- 1 mysql mysql 147 Oct  3 13:46 /var/lib/mysql/binarylog/binlog.000003 c、類比產生二進位日誌及查看內容[email protected][(none)]> create database tempdb;Query OK, 1 row affected (0.00 sec)[email protected][(none)]> use tempdbDatabase changed[email protected][tempdb]> create table tb1(id smallint,val varchar(10));Query OK, 0 rows affected (0.00 sec)[email protected][tempdb]> insert into tb1 values(1,‘jack‘);Query OK, 1 row affected (0.01 sec)[email protected][tempdb]> system strings /var/lib/mysql/binarylog/binlog.000004bin?8.T5.5.39-logz=.Ttempdbcreate database tempdbtempdbcreate table tb1(id smallint,val varchar(10))tempdbBEGINtempdbinsert into tb1 values(1,‘jack‘)[email protected][tempdb]> system more /var/lib/mysql/binarylog/binlog.index/var/lib/mysql/binarylog/binlog.000001/var/lib/mysql/binarylog/binlog.000002/var/lib/mysql/binarylog/binlog.000003/var/lib/mysql/binarylog/binlog.000004--使用命令列工具mysqlbinlog直接提取二進位日誌的內容[email protected][tempdb]> system mysqlbinlog /var/lib/mysql/binarylog/binlog.000004/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!40019 SET @@session.max_insert_delayed_threads=0*/;/*!50003 SET @[email protected]@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER /*!*/;# at 4#141003 13:46:39 server id 1  end_log_pos 107   Start: binlog v 4, server v 5.5.39-log created 141003 13:46:39# Warning: this binlog is either in use or was not closed properly.BINLOG ‘PzguVA8BAAAAZwAAAGsAAAABAAQANS41LjM5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==‘/*!*/;# at 107#141003 14:08:58 server id 1  end_log_pos 194   Query   thread_id=1     exec_time=0     error_code=0SET TIMESTAMP=1412316538/*!*/;SET @@session.pseudo_thread_id=1/*!*/;SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;SET @@session.sql_mode=0/*!*/;SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;/*!\C utf8 *//*!*/;SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;SET @@session.lc_time_names=0/*!*/;SET @@session.collation_database=DEFAULT/*!*/;create database tempdb/*!*/;# at 194#141003 14:09:36 server id 1  end_log_pos 304   Query   thread_id=1     exec_time=0     error_code=0use `tempdb`/*!*/;SET TIMESTAMP=1412316576/*!*/;create table tb1(id smallint,val varchar(10))/*!*/;# at 304#141003 14:09:56 server id 1  end_log_pos 374   Query   thread_id=1     exec_time=0     error_code=0SET TIMESTAMP=1412316596/*!*/;BEGIN/*!*/;# at 374#141003 14:09:56 server id 1  end_log_pos 471   Query   thread_id=1     exec_time=0     error_code=0SET TIMESTAMP=1412316596/*!*/;insert into tb1 values(1,‘jack‘)/*!*/;# at 471#141003 14:09:56 server id 1  end_log_pos 498   Xid = 25COMMIT/*!*/;DELIMITER ;# End of log fileROLLBACK /* added by mysqlbinlog */;/*!50003 SET [email protected]_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;--從以上的內容可以看出二進位日誌記錄了所有操作的DML語句及其開銷，以及一些系統內容變數的資訊。d、管理二進位日誌--對於二進位日誌，應儘可能儲存在安全的位置，與資料分開儲存  --使用show binary logs擷取二進位日誌相關資訊[email protected][(none)]> help show binary logs;Name: ‘SHOW BINARY LOGS‘Description:Syntax:SHOW BINARY LOGSSHOW MASTER LOGSLists the binary log files on the server. This statement is used aspart of the procedure described in [HELP PURGE BINARY LOGS], that showshow to determine which logs can be purged.[email protected][tempdb]> show binary logs;+---------------+-----------+| Log_name      | File_size |+---------------+-----------+| binlog.000001 |       147 || binlog.000002 |       147 || binlog.000003 |       147 || binlog.000004 |       498 |+---------------+-----------+show binlog events用於在二進位日誌中顯示事件。如果未指定‘log_name‘，則顯示第一個二進位日誌。[email protected][(none)]> help show binlog events;  --擷取協助資訊Name: ‘SHOW BINLOG EVENTS‘Description:Syntax:SHOW BINLOG EVENTS   [IN ‘log_name‘] [FROM pos] [LIMIT [offset,] row_count]Shows the events in the binary log. If you do not specify ‘log_name‘,the first binary log is displayed.[email protected][(none)]> show binlog events;+---------------+-----+-------------+-----------+-------------+---------------------------------------+| Log_name      | Pos | Event_type  | Server_id | End_log_pos | Info                                  |+---------------+-----+-------------+-----------+-------------+---------------------------------------+| binlog.000001 |   4 | Format_desc |         1 |         107 | Server ver: 5.5.39-log, Binlog ver: 4 || binlog.000001 | 107 | Rotate      |         1 |         147 | binlog.000002;pos=4                   |+---------------+-----+-------------+-----------+-------------+---------------------------------------+[email protected][(none)]> show binlog events in ‘binlog.000005‘;  --binlog.000005不存在，所以報錯ERROR 1220 (HY000): Error when executing command SHOW BINLOG EVENTS: Could not find target log--下面的這個查詢中，前面執行的DML在這裡均可以看到[email protected][tempdb]> show binlog events in ‘binlog.000004‘;+---------------+-----+-------------+-----------+-------------+-------------------------------------------------------------+| Log_name      | Pos | Event_type  | Server_id | End_log_pos | Info                                                        |+---------------+-----+-------------+-----------+-------------+-------------------------------------------------------------+| binlog.000004 |   4 | Format_desc |         1 |         107 | Server ver: 5.5.39-log, Binlog ver: 4                       || binlog.000004 | 107 | Query       |         1 |         194 | create database tempdb                                      || binlog.000004 | 194 | Query       |         1 |         304 | use `tempdb`; create table tb1(id smallint,val varchar(10)) || binlog.000004 | 304 | Query       |         1 |         374 | BEGIN                                                       || binlog.000004 | 374 | Query       |         1 |         471 | use `tempdb`; insert into tb1 values(1,‘jack‘)              || binlog.000004 | 471 | Xid         |         1 |         498 | COMMIT /* xid=25 */                                         |+---------------+-----+-------------+-----------+-------------+-------------------------------------------------------------+[email protected][tempdb]> show binlog events in ‘binlog.000004‘ from 374;+---------------+-----+------------+-----------+-------------+------------------------------------------------+| Log_name      | Pos | Event_type | Server_id | End_log_pos | Info                                           |+---------------+-----+------------+-----------+-------------+------------------------------------------------+| binlog.000004 | 374 | Query      |         1 |         471 | use `tempdb`; insert into tb1 values(1,‘jack‘) || binlog.000004 | 471 | Xid        |         1 |         498 | COMMIT /* xid=25 */                            |+---------------+-----+------------+-----------+-------------+------------------------------------------------+[email protected][tempdb]> show binlog events in ‘binlog.000004‘ from 374 limit 1;+---------------+-----+------------+-----------+-------------+------------------------------------------------+| Log_name      | Pos | Event_type | Server_id | End_log_pos | Info                                           |+---------------+-----+------------+-----------+-------------+------------------------------------------------+| binlog.000004 | 374 | Query      |         1 |         471 | use `tempdb`; insert into tb1 values(1,‘jack‘) |+---------------+-----+------------+-----------+-------------+------------------------------------------------+d、刪除曆史日誌--使用purge手動刪除指定日誌--使用expire-log-days刪除失效日誌，設定變數expire_logs_days，刪除超出這個變數保留期之前的所有日誌被刪除--自動日誌刪除通常發生在伺服器啟動以及日誌flush--reset master方式[email protected][(none)]> help purge;Name: ‘PURGE BINARY LOGS‘Description:Syntax:PURGE { BINARY | MASTER } LOGS    { TO ‘log_name‘ | BEFORE datetime_expr }Examples:PURGE BINARY LOGS TO ‘mysql-bin.010‘;PURGE BINARY LOGS BEFORE ‘2008-04-02 22:46:26‘;[email protected][tempdb]> purge binary logs to ‘binlog.000003‘;Query OK, 0 rows affected (0.12 sec)[email protected][tempdb]> show binary logs;+---------------+-----------+| Log_name      | File_size |+---------------+-----------+| binlog.000003 |       147 || binlog.000004 |       498 |+---------------+-----------+[email protected][tempdb]> system ls -hltr /var/lib/mysql/binarylog/*-rw-rw---- 1 mysql mysql 147 Oct  3 13:46 /var/lib/mysql/binarylog/binlog.000003-rw-rw---- 1 mysql mysql 498 Oct  3 14:09 /var/lib/mysql/binarylog/binlog.000004-rw-rw---- 1 mysql mysql  78 Oct  3 14:23 /var/lib/mysql/binarylog/binlog.index--使用before子句purge日誌，binlog.000003被刪除[email protected][tempdb]> purge binary logs before ‘2014-10-03 14:09:56‘;Query OK, 0 rows affected (0.02 sec)[email protected][tempdb]> show binary logs;+---------------+-----------+| Log_name      | File_size |+---------------+-----------+| binlog.000004 |       498 |+---------------+-----------+--Author: Leshami--Blog  : http://blog.csdn.net/leshami--重設所有日誌--reset master將刪除在索引檔案中列出所有的記錄檔並重設索引檔案，最後產生一個新的binlog檔案。--該操作之前先備份binlog至其它位置以備以後需要。[email protected][tempdb]> help reset master;Name: ‘RESET MASTER‘Description:Syntax:RESET MASTERDeletes all binary log files listed in the index file, resets thebinary log index file to be empty, and creates a new binary log file.This statement is intended to be used only when the master is startedfor the first time.[email protected][tempdb]> reset master;Query OK, 0 rows affected (0.13 sec)[email protected][tempdb]> show binary logs;+---------------+-----------+| Log_name      | File_size |+---------------+-----------+| binlog.000001 |       107 |  --reset之後，從000001開始產生全新空日誌+---------------+-----------+--expire_log系統變數控制二進位日誌自動刪除的天數。預設值為0,表示“沒有自動刪除”。啟動時和二進位日誌迴圈時可能刪除。[email protected][tempdb]> show variables like ‘expire_log%‘;+------------------+-------+| Variable_name    | Value |+------------------+-------+| expire_logs_days | 0     |+------------------+-------+[email protected][tempdb]> set expire_logs_days=7;   --提示次系統變數為全域變數ERROR 1229 (HY000): Variable ‘expire_logs_days‘ is a GLOBAL variable and should be set with SET GLOBAL[email protected][tempdb]> set global expire_logs_days=7;  --設定Query OK, 0 rows affected (0.01 sec)[email protected][tempdb]> select @@expire_logs_days;+--------------------+| @@expire_logs_days |+--------------------+|                  7 |+--------------------+

MySQL 二進位日誌(Binary Log)