怎樣理解mysql binlog

今天就跟大家聊聊有關怎樣理解mysql binlog，可能很多人都不太了解，為了讓大家更加了解，小編給大家總結了以下內容，希望大家根據這篇文章可以有所收獲。

Mysql binlog

1、mysql binlog的類型：

mysql的binlog按照生成方式，可以分為三種，分別是：

1）基于記錄的復制RBR(Row Based Replication) 或Row：

優點：binlog中可以不記錄執行的sql語句的上下文相關的信息，僅需要記錄那一條記錄被修改成什么了。所以rowlevel的日志內容會非常清楚的記錄下每一行數據修改的細節。而且不會出現某些特定情況下的存儲過程，或function，以及trigger的調用和觸發無法被正確復制的問題。

缺點：所有的執行的語句當記錄到日志中的時候，都將以每行記錄的修改來記錄，這樣可能會產生大量的日志內容,比如一條update語句，修改多條記錄，則binlog中每一條修改都會有記錄，這樣造成binlog日志量會很大，特別是當執行alter table之類的語句的時候，由于表結構修改，每條記錄都發生改變，那么該表每一條記錄都會記錄到日志中。

2）基于語句的復制，簡稱SBR(Statement Based Replication) 或Statement：

相比row能提高性能，減少日志量。但是這個是取決于應用的SQL情況，正常同一條記錄修改或者插入row格式所產生的日志量會小于Statement產生的日志量，但是考慮到如果帶條件的update操作，以及整表刪除，alter表等操作，ROW格式會產生大量日志，因此在考慮是否使用ROW格式日志時應該根據應用的實際情況，考慮其所產生的日志量會增加多少，以及帶來的IO性能問題。

優點：可以對任何語句都能正確工作，不需要記錄每一行的變化，減少了binlog日志量，節約了IO，提高性能，一些語句的效率更高。例如，一個更新GB的數據的查詢僅需要幾十個字節的二進制日志。

缺點：就是二進制日志可能會很大，而且不直觀，所以，你不能使用mysqlbinlog來查看二進制日志。而且由于記錄的只是執行語句，為了這些語句能在slave上正確運行，因此還必須記錄每條語句在執行的時候的一些相關信息，以保證所有語句能在slave得到和在master端執行時候相同的結果。另外mysql 的復制,像一些特定函數功能，slave可與master上要保持一致會有很多相關問題(如sleep()函數， last_insert_id()，以及user-defined functions(udf)會出現問題)。此外，存儲過程和觸發器也是一個問題。另外一個問題就是基于語句的復制必須是串行化的。這要求大量特殊的代碼，配置，例如InnoDB的next-key鎖等。并不是所有的存儲引擎都支持基于語句的復制。

使用以下函數的語句也無法被復制：

* LOAD_FILE()

* UUID()

* USER()

* FOUND_ROWS()

* SYSDATE() (除非啟動時啟用了 --sysdate-is-now 選項)

同時在INSERT ...SELECT 會產生比 RBR 更多的行級鎖

3）混合方式MBR(Mixed Based Replication)：

由于兩種方式不能對所有情況都能很好的處理，所以，MySQL 5.1以上支持在基于語句的復制和基于記錄的復制之前動態交換。可以通過設置session變量binlog_format來進行控制。

2、Binlog日志格式選擇

Mysql默認是使用Statement日志格式，推薦使用MIXED.

由于一些特殊使用，可以考慮使用ROW，如自己通過binlog日志來同步數據的修改，這樣會節省很多相關操作。對于binlog數據處理會變得非常輕松,相對mixed，解析也會很輕松(當然前提是增加的日志量所帶來的IO開銷在容忍的范圍內即可)。

mysql對于日志格式的選定原則:如果是采用 INSERT，UPDATE，DELETE 等直接操作表的情況，則日志格式根據 binlog_format 的設定而記錄,如果是采用 GRANT，REVOKE，SET PASSWORD 等管理語句來做的話，那么無論如何都采用Statement模式記錄

3、Binlog相關參數

如以下：

binlog_format = MIXED   //binlog日志格式，可以選擇為mixed，statement，row

log_bin =目錄/mysql-bin.log    //binlog日志名

expire_logs_days = 7                //binlog過期清理時間

max_binlog_size = 100m                    //binlog每個日志文件大小

binlog-do-db = 需要備份的數據庫名，如果備份多個數據庫，重復設置這個選項即可

binlog-ignore-db = 不需要備份的數據庫苦命，如果備份多個數據庫，重復設置這個選項即可

4、binlog相關文件

mysql-bin.index：

用于跟蹤磁盤上存在哪些二進制日志文件。MySQL用它來定位二進制日志文件。

mysql-relay-bin.index：

該文件的功能與mysql-bin.index類似，但是它是針對中繼日志，而不是二進制日志。

master.info：

保存master的相關信息。不要刪除它，否則，slave重啟后不能連接master。

relay-log.info：

包含slave中當前二進制日志和中繼日志的信息。

5、binlog日志內容解析

1)在mysql命令界面中查看時：

如果是statement模式：

mysql> show binlog events in 'mysql-bin.000021'\G;

截取部分查詢結果：

*************************** 20. row ***************************

Log_name: mysql-bin.000021  -----------------------> 查詢的binlog日志文件名

Pos: 11197 ------------------------------------------------------------> pos起始點:

Event_type: Query ----------------------------------------------> 事件類型：Query

Server_id: 1 -------------------------------------------> 標識是由哪臺服務器執行的

End_log_pos: 11308 ------------------> pos結束點:11308(即：下行的pos起始點)

Info: use `zyyshop`; INSERT INTO `team2` VALUES (0,345,'asdf8er5')

---> 執行的sql語句

*************************** 21. row ***************************

Log_name: mysql-bin.000021

Pos: 11308 -----------------------------> pos起始點:11308(即：上行的pos結束點)

Event_type: Query

Server_id: 1

End_log_pos: 11417

Info: use `zyyshop`; /*!40000 ALTER TABLE `team2` ENABLE KEYS */

*************************** 22. row ***************************

Log_name: mysql-bin.000021

如果是row模式：

mysql> show binlog events in 'mysql-bin.000005'\G;

截取部分查詢結果：

*************************** 2. row ***************************

Log_name: mysql-bin.000005

Pos: 120

Event_type: Query

Server_id: 3

End_log_pos: 191

Info: BEGIN

*************************** 3. row ***************************

Log_name: mysql-bin.000005

Pos: 191

Event_type: Table_map

Server_id: 3

End_log_pos: 234

Info: table_id: 87 (lxm.t)    ---> 這里看不到執行的sql語句，只能看到表名

*************************** 4. row ***************************

Log_name: mysql-bin.000005

Pos: 234

Event_type: Update_rows

Server_id: 3

End_log_pos: 280

Info: table_id: 87 flags: STMT_END_F

*************************** 5. row ***************************

Log_name: mysql-bin.000005

Pos: 280

Event_type: Xid

Server_id: 3

End_log_pos: 311

Info: COMMIT /* xid=249 */

*************************** 6. row ***************************

Log_name: mysql-bin.000005

2)用mysqlbinlog工具查看時：

如果是statement模式：

# /usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql-bin.000013

  下面截取一個片段分析：

.............................................................................................................

# at 552

#131128 17:50:46 server id 1  end_log_pos 665   Query   thread_id=11    exec_time=0     error_code=0        ---->執行時間:17:50:46；pos點:665

  SET TIMESTAMP=1385632246/*!*/;

  update zyyshop.stu set name='李四' where id=4        ---->執行的SQL

  /*!*/;

# at 665

#131128 17:50:46 server id 1  end_log_pos 692   Xid = 1454 ---->執行時間:17:50:46；pos點:692

.............................................................................................................

注: server id 1         數據庫主機的服務號；

    end_log_pos 665  pos點

    thread_id=11      線程號

如果是row模式：

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

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

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#161022 15:59:31 server id 3  end_log_pos 120 CRC32 0x45d9e7a2  Start: binlog v 4, server v 5.6.24-log created 161022 15:59:31

BINLOG '

YxwLWA8DAAAAdAAAAHgAAAAAAAQANS42LjI0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAaLn

2UU=

'/*!*/;

# at 120

#161022 16:00:57 server id 3  end_log_pos 191 CRC32 0x37e11f27  Query   thread_id=1     exec_time=0     error_code=0

SET TIMESTAMP=1477123257/*!*/;

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=1073741824/*!*/;

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=33/*!*/;

SET @@session.lc_time_names=0/*!*/;

SET @@session.collation_database=DEFAULT/*!*/;

BEGIN

/*!*/;

# at 191

#161022 16:00:57 server id 3  end_log_pos 234 CRC32 0xe2ba303b  Table_map: `lxm`.`t` mapped to number 87

# at 234

#161022 16:00:57 server id 3  end_log_pos 280 CRC32 0xdae765d4  Update_rows: table id 87 flags: STMT_END_F

.............................................................................................................

是看不到明文的DML語句。

6、常用binlog日志操作命令

@查看所有binlog日志列表

mysql> show master logs;或者show binary logs;

@查看master狀態，即最后(最新)一個binlog日志的編號名稱，及其最后一個操作事件pos結束點(Position)值

mysql> show master status;

@刷新log日志，自此刻開始產生一個新編號的binlog日志文件

mysql> flush logs;

注：每當mysqld服務重啟時，會自動執行此命令，刷新binlog日志；在mysqldump備份數據時加 -F 選項也會刷新binlog日志；

@重置(清空)所有binlog日志

mysql> reset master;

7、如何查看binlog日志的內容

1)在mysql命令界面中查看：

mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

這條語句可以將指定的binlog日志文件，分成有效事件行的方式返回，并可使用limit指定pos點的起始偏移，查詢條數；

選項解析：

IN 'log_name'   指定要查詢的binlog文件名(不指定就是第一個binlog文件)

FROM pos   指定從哪個pos起始點開始查起(不指定就是從整個文件首個pos點開始算)

LIMIT [offset,]   偏移量(不指定就是0)

row_count       查詢總條數(不指定就是所有行)

示例：

A.查詢第一個(最早)的binlog日志：

    mysql> show binlog events\G;

B.指定查詢 mysql-bin.000021 這個文件：

    mysql> show binlog events in 'mysql-bin.000021'\G;

C.指定查詢 mysql-bin.000021 這個文件，從pos點:8224開始查起：

    mysql> show binlog events in 'mysql-bin.000021' from 8224\G;

D.指定查詢 mysql-bin.000021 這個文件，從pos點:8224開始查起，查詢10條

    mysql> show binlog events in 'mysql-bin.000021' from 8224 limit 10\G;

E.指定查詢 mysql-bin.000021 這個文件，從pos點:8224開始查起，偏移2行，查詢10條

    mysql> show binlog events in 'mysql-bin.000021' from 8224 limit 2,10\G;

2) 使用mysqlbinlog：

binlog是二進制文件，普通文件查看器cat、more、vi等都無法打開，必須使用自帶的 mysqlbinlog 命令查看。binlog日志與數據庫文件在同目錄中。

在MySQL5.5以下版本使用mysqlbinlog命令時如果報錯，就加上 “--no-defaults”選項。

a)如果是ROW模式的二進制日志文件，為了查看mysql具體執行了什么樣的sql語句，需要使用-v（--verbose）選項，該選項會將行事件重構成被注釋掉的偽SQL語句，如果想看到更詳細的信息可以將該選項給兩次如-vv，這樣可以包含一些數據類型和元信息的注釋內容。例如：

mysqlbinlog -v mysql-bin.000001

mysqlbinlog -vv mysql-bin.000001

b)mysqlbinlog和可以通過--read-from-remote-server選項從遠程服務器讀取二進制日志文件，這時需要一些而外的連接參數，如--host，--password ，--port，--user，--socket，--protocol等，這些參數僅在指定了--read-from-remote-server后有效。

c)無論是本地二進制日志文件還是遠程服務器上的二進制日志文件，無論是行模式、語句模式還是混合模式的二進制日志文件，被mysqlbinlog工具解析后都可直接應用與MySQL Server進行基于時間點、位置或數據庫的恢復。

常見參數有:

1)   --database=db_name, -d db_name

該參數使mysqlbinlog僅從本地二進制日志中輸出指定的db_name被use命令選作默認數據庫時產生的日志事件。行為類似于mysqld的--binlog-do-db命令。若該參數指定了多次那么只有最后一次指定的內容有效。參數具體的影響依賴于二進制日志格式，只有在使用行模式的日志格式時該參數才能保證一致性。基于語句或混合模式的二進制日志格式中因為可能存在跨庫的更新導致--database參數表現不同的行為，從而不能保證數據一致性。例如：

mysqlbinlog  mysql-bin.000001  -d testDB | mysql -uusername -p

2)   --force-read, -f

使用了該參數后mysqlbinlog工具在讀取到不能識別的日志事件時會打印出warning，忽略事件并繼續執行，沒有此參數的情況下mysqlbinlog會停止。

mysqlbinlog  mysql-bin.000001  -d testDB -f | mysql -uusername -p

3)   --no-defaults

阻止mysqlbinlog工具從任何配置文件讀取參數，.mylogin.cnf除外（以便于安全的保存密碼）

mysqlbinlog mysql-bin.000001 -d testDB -f --no-defaults| mysql -uusername -p

4) --start-datetime=datetime和--stop-datetime=datetime

這兩個參數用于指定恢復開始時間點和結束時間點，可以一起或單獨給出，也可與--start-position，--stop-position混用。

mysqlbinlog  mysql-bin.000001  -d testDB -f --no-defaults --start-datetime=datetime --stop-position=NNNNNN | mysql -uusername -p

5) --start-position=N, -j N和--stop-position=N

上邊一組參數用于指定恢復開始位置和結束位置，可以一起或單獨給出也可與--start-datetime，--stop-datetime混用

mysqlbinlog  mysql-bin.000001  -d testDB -f --no-defaults --start-position=NNNNNN --stop-datetime=datetime | mysql -uusername -p

d)如果需要還原的二進制日志文件不止一個，安全的方式是多個二進制文件同時執行。

mysqlbinlog  mysql-bin.000001  mysql-bin.000002 mysql-bin.000003 --start-position=NNNNNN --stop-datetime=datetime | mysql -uusername -p

或

mysqlbinlog  mysql-bin.00000[1-3] --start-position=NNNNNN --stop-datetime=datetime | mysql -uusername -p

當多個二進制日志文件同時執行時，--start-position和--stop-position分別只應用于第一個列出的二進制日志文件和最后一個列出的二進制日志文件

     當然也可以先將多個二進制日志文件的輸出導到同一個.sql文件最后在執行該.sql文件（適用于日志量不多的情況）。

8、binlog的應用：

可以用binlog來恢復誤操作的數據。

案例：

1)全備份

mysqldump -uroot -p123456 -lF --log-error=/root/myDump.err -B zyyshop > /root/BAK.zyyshop.sql

備份時使用-F選項，意味著備份工作剛開始時就會刷新log日志，產生新的binlog日志來記錄備份之后的數據庫的“增刪改”操作。

2)備份之后，業務對數據庫進行了大量的增刪改查操作。然后數據庫有張表被誤刪除了。此刻立即查看最后一個binlog日志，記錄下關鍵的pos點，即是在哪個點上的操作導致了數據庫的破壞。然后flush logs，讓mysql重新開始新的binlog日志記錄文件。從理論上講，此時舊的binlog日志是不會被繼續寫入了。此時，備份舊的binlog日志。

3)讀取舊的binlog日志，分析問題。

方式一，用mysqlbinlog命令來讀取binlog日志：

     mysqlbinlog  /usr/local/mysql/data/mysql-bin.000023

方式二，在mysql服務器中查看：

     mysql> show binlog events in 'mysql-bin.000023';

在輸出中找到誤刪除表的確切pos點。

4)首先用全備份進行恢復：

mysql -uroot -p123456 -v < /root/BAK.zyyshop.sql;

5)從binlog日志中恢復數據：

mysqlbinlog mysql-bin.0000xx | mysql -u用戶名 -p密碼數據庫名

所謂恢復，就是讓mysql將保存在binlog日志中指定段落區間的sql語句逐個重新執行一次而已。

看完上述內容，你們對怎樣理解mysql binlog有進一步的了解嗎？如果還想了解更多知識或者相關內容，請關注億速云行業資訊頻道，感謝大家的支持。