一、基本概念

binlog是一個(gè)二進(jìn)制格式的文件，用于記錄用戶對(duì)數(shù)據(jù)庫更新的SQL語句信息，例如更改數(shù)據(jù)庫表和更改內(nèi)容的SQL語句都會(huì)記錄到binlog里，但是不會(huì)記錄SELECT和SHOW這類操作。

1.1 特點(diǎn)

• binlog在MySQL的Server層實(shí)現(xiàn)(引擎共用)
• binlog為邏輯日志,記錄的是一條SQL語句的原始邏輯
  • binlog不限制大小,追加寫入,不會(huì)覆蓋以前的日志.
  • 默認(rèn)情況下，binlog日志是二進(jìn)制格式的，不能使用查看文本工具的命令（比如，cat，vi等）查看，而使用mysqlbinlog解析查看。

開啟Binlog日志有以下兩個(gè)最重要的使用場(chǎng)景:

• 主從復(fù)制：在主庫中開啟Binlog功能，這樣主庫就可以把Binlog傳遞給從庫，從庫拿到Binlog后實(shí)現(xiàn)數(shù)據(jù)恢復(fù)達(dá)到主從數(shù)據(jù)一致性。
• 數(shù)據(jù)恢復(fù)：通過mysqlbinlog工具來恢復(fù)數(shù)據(jù)。

二、日志的三種模式

2.1 ROW（row-based replication, RBR）

日志中會(huì)記錄每一行數(shù)據(jù)被修改的情況，然后在slave端對(duì)相同的數(shù)據(jù)進(jìn)行修改。

• 優(yōu)點(diǎn)：能清楚記錄每一個(gè)行數(shù)據(jù)的修改細(xì)節(jié)，能完全實(shí)現(xiàn)主從數(shù)據(jù)同步和數(shù)據(jù)的恢復(fù)。而且不會(huì)出現(xiàn)某些特定情況下存儲(chǔ)過程或function，以及trigger的調(diào)用和觸發(fā)器無法被正確復(fù)制的問題。
• 缺點(diǎn)：批量操作，會(huì)產(chǎn)生大量的日志，尤其是alter table會(huì)讓日志暴漲。

2.2 STATMENT（statement-based replication, SBR）

記錄每一條修改數(shù)據(jù)的SQL語句（批量修改時(shí)，記錄的不是單條SQL語句，而是批量修改的SQL語句事件）, slave在復(fù)制的時(shí)候SQL進(jìn)程會(huì)解析成和原來master端執(zhí)行過的相同的SQL再次執(zhí)行。簡(jiǎn)稱SQL語句復(fù)制。

• 優(yōu)點(diǎn)：日志量小，減少磁盤IO，提升存儲(chǔ)和恢復(fù)速度
• 缺點(diǎn)：在某些情況下會(huì)導(dǎo)致主從數(shù)據(jù)不一致，比如last_insert_id()、now()等函數(shù)。

2.3 MIXED（mixed-based replication, MBR）

以上兩種模式的混合使用，一般會(huì)使用STATEMENT模式保存binlog，對(duì)于STATEMENT模式無法復(fù)制的操作使用ROW模式保存binlog，MySQL會(huì)根據(jù)執(zhí)行的SQL語句選擇寫入模式。
企業(yè)場(chǎng)景如何選擇binlog的模式

1. 如果生產(chǎn)中使用MySQL的特殊功能相對(duì)少（存儲(chǔ)過程、觸發(fā)器、函數(shù)）。選擇默認(rèn)的語句模式，Statement。
2. 如果生產(chǎn)中使用MySQL的特殊功能較多的，可以選擇Mixed模式。
3. 如果生產(chǎn)中使用MySQL的特殊功能較多，又希望數(shù)據(jù)最大化一致，此時(shí)最好Row 模式；但是要注意，該模式的binlog日志量增長(zhǎng)非?？?

• 跳過語句模式：默認(rèn)情況下，MySQL復(fù)制使用的是語句模式（Statement mode），即主服務(wù)器上執(zhí)行的SQL語句會(huì)被發(fā)送到從服務(wù)器執(zhí)行。然而，對(duì)于某些存儲(chǔ)過程，MySQL可能無法正確復(fù)制執(zhí)行過程中的SQL語句，因?yàn)榇鎯?chǔ)過程可能包含了諸如非確定性函數(shù)、臨時(shí)表等語句，這些語句在從服務(wù)器上可能無法正確執(zhí)行，從而導(dǎo)致復(fù)制的不一致性。
• 函數(shù)依賴：存儲(chǔ)過程中可能會(huì)調(diào)用其他存儲(chǔ)過程或自定義函數(shù)，而這些函數(shù)的結(jié)果可能會(huì)因?yàn)橹鞣?wù)器和從服務(wù)器上的數(shù)據(jù)不一致而產(chǎn)生不同的結(jié)果，導(dǎo)致數(shù)據(jù)不一致。
• 依賴外部數(shù)據(jù)：存儲(chǔ)過程可能會(huì)依賴外部的數(shù)據(jù)或環(huán)境變量，而這些數(shù)據(jù)在主服務(wù)器和從服務(wù)器上可能不同，導(dǎo)致存儲(chǔ)過程的執(zhí)行結(jié)果不一致。
• 并發(fā)執(zhí)行問題：如果多個(gè)客戶端同時(shí)執(zhí)行存儲(chǔ)過程，可能會(huì)出現(xiàn)并發(fā)執(zhí)行的問題，從而導(dǎo)致數(shù)據(jù)不一致。

三、 Binlog寫入機(jī)制

3.1 binlog文件結(jié)構(gòu)

• MySQL的binlog文件中記錄的是對(duì)數(shù)據(jù)庫的各種修改操作，用來表示修改操作的數(shù)據(jù)結(jié)構(gòu)是Log event。不同的修改操作對(duì)應(yīng)的不同的log event。
  • 比較常用的log event有：Query event、Row event、Xid event等。binlog文件的內(nèi)容就是各種Log event的集合。

3.2 binlog落盤策略

binlog 的寫入順序: binlog cache (write) -> OS cache ?-> (fsync) disk.

• write表示: 寫入文件系統(tǒng)緩存,fsync表示持久化到磁盤的時(shí)機(jī)
• binlog刷數(shù)據(jù)到磁盤由參數(shù)sync_binlog進(jìn)行配置
  • sync_binlog=0 的時(shí)候，表示每次提交事務(wù)都只 write,不 fsync;
  • sync_binlog=1 的時(shí)候，表示每次提交事務(wù)都會(huì)執(zhí)行 fsync;
  • sync_binlog=N(N>1) 的時(shí)候，表示每次提交事務(wù)都 write，但累積 N 個(gè)事務(wù)后才 fsync。

mysql> show variables like '%sync_binlog%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog   | 1     |
+---------------+-------+
1 row in set (0.00 sec)

注意: 不建議將這個(gè)參數(shù)設(shè)成 0，比較常見的是將其設(shè)置為 100~1000 中的某個(gè)數(shù)值。如果設(shè)置成0，主動(dòng)重啟丟失的數(shù)據(jù)不可控制。設(shè)置成1，效率低下，設(shè)置成N(N>1)，則主機(jī)重啟，造成最多N個(gè)事務(wù)的binlog日志丟失，但是性能高，丟失數(shù)據(jù)量可控。

3.3 binlog 寫入流程

1. 根據(jù)記錄模式和操作觸發(fā)event事件生成log event.
2. 事務(wù)執(zhí)行過程中，先把日志(log event) 寫到binlog cache，事務(wù)提交的時(shí)候，再把binlog cache寫到binlog文件中。
3. binlog cache，系統(tǒng)為每個(gè)線程分配了一片binlog cache內(nèi)存 (每個(gè)線程都有自己的binlog cache，共用一份binlog文件) .
4. 事務(wù)提交的時(shí)候，執(zhí)行器把binlog cache里完整的事務(wù)寫入binlog中。并清空binlog cache.

3.4 redo log 和 binlog的區(qū)別

CrashSafe指MySQL服務(wù)器宕機(jī)重啟后，能夠保證：

• 所有已經(jīng)提交的事務(wù)的數(shù)據(jù)仍然存在。
• 所有沒有提交的事務(wù)的數(shù)據(jù)自動(dòng)回滾。

四、Binlog命令操作

4.1 使用sql命令查看binlog文件

啟用Binlog

vim /etc/my.cnf

--增加下面幾個(gè)參數(shù)
binlog-format=ROW
log-bin=mysqlbinlog
server-id=1

-- 重啟mysql
systemctl restart mysqld

• binlog-format: 文件模式
• log-bin: mysql會(huì)根據(jù)這個(gè)配置自動(dòng)設(shè)置log_bin為on狀態(tài)，自動(dòng)設(shè)置log_bin_index文件為你指定的文件名后跟.index
• server-id=1: ?隨機(jī)指定一個(gè)不能和其他集群中機(jī)器重名的字符串，如果只有一臺(tái)機(jī)器，那就可以隨便指定了

啟動(dòng)成功之后，我們可以登陸查看我們的配置是否起作用

mysql> show variables like '%log_bin%';
+---------------------------------+----------------------------------+
| Variable_name                   | Value                            |
+---------------------------------+----------------------------------+
| log_bin                         | ON                               |
| log_bin_basename                | /var/lib/mysql/mysqlbinlog       |
| log_bin_index                   | /var/lib/mysql/mysqlbinlog.index |
| log_bin_trust_function_creators | OFF                              |
| log_bin_use_v1_row_events       | OFF                              |
| sql_log_bin                     | ON                               |
+---------------------------------+----------------------------------+

• log_bin: 是否開啟binlog日志
• log_bin_basename: 基礎(chǔ)文件名
• log_bin_index : binlog文件的索引文件，管理所有binlog文件
• sql_log_bin: 表示當(dāng)前會(huì)話是否記錄 bin log，默認(rèn)值on(當(dāng)sql_log_bin關(guān)閉后，主庫服務(wù)器上的改動(dòng)不記錄bin log，不會(huì)復(fù)制到從庫)。

查看binlog文件列表

  mysql> show binary logs;
+--------------------+-----------+
  | Log_name           | File_size |
  +--------------------+-----------+
  | mysqlbinlog.000001 |       177 |
  | mysqlbinlog.000002 |       177 |
  | mysqlbinlog.000003 |       154 |
  +--------------------+-----------+

查看正在寫入的binlog文件

  show master status;

查看binlog文件信息

使用show binlog events命令查詢到的每一行數(shù)據(jù)就是一個(gè)binlog管理事件

mysql> show binlog events;
+--------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name           | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+--------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysqlbinlog.000001 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.30-log, Binlog ver: 4 |
| mysqlbinlog.000001 | 123 | Previous_gtids |         1 |         154 |                                       |
| mysqlbinlog.000001 | 154 | Stop           |         1 |         177 |                                       |
+--------------------+-----+----------------+-----------+-------------+-----------

參數(shù)說明

• Log_name：當(dāng)前事件所在的binlog文件名稱；
• Pos：當(dāng)前事件的開始位置，每個(gè)事件都占用固定的字節(jié)大小，結(jié)束位置(End_log_position)減去Pos，就是這個(gè)事件占用的字節(jié)數(shù)。

第一個(gè)事件位置并不是從0開始，而是從4。Mysql通過文件中的前4個(gè)字節(jié)，來判斷這是不是一個(gè)binlog文件。這種方式很常見，很多格式文件，如pdf、doc、jpg等，都會(huì)通常前幾個(gè)特定字符判斷是否是合法文件。

• Event_type：表示事件的類型；
• Server_id：表示產(chǎn)生這個(gè)事件的mysql server_id，通過設(shè)置my.cnf中的server-id選項(xiàng)進(jìn)行配置；
• End_log_position：下一個(gè)事件的開始位置；
• Info：當(dāng)前事件的描述信息。

查看指定binlog文件內(nèi)容

mysql> show binlog events in 'mysqlbinlog.000001'\G;
*************************** 1. row ***************************
   Log_name: mysqlbinlog.000001
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 123
       Info: Server ver: 5.7.30-log, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: mysqlbinlog.000001
        Pos: 123
 Event_type: Previous_gtids
  Server_id: 1
End_log_pos: 154
       Info: 
*************************** 3. row ***************************
   Log_name: mysqlbinlog.000001
        Pos: 154
 Event_type: Stop
  Server_id: 1
End_log_pos: 177
     Info: 
3 rows in set (0.00 sec)

使用mysqlbinlog命令查看binlog文件

• mysql給我們提供了一個(gè)用于查看binlog日志的工具，叫做mysqlbinlog

[root@localhost mysql]# mysqlbinlog mysqlbinlog.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
  /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
  # at 4
  #220125  6:40:35 server id 1  end_log_pos 123 CRC32 0x1e570724  Start: binlog v 4, server v 5.7.30-log created 220125  6:40:35 at startup
  ROLLBACK/*!*/;
BINLOG '
  4wvwYQ8BAAAAdwAAAHsAAAAAAAQANS43LjMwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADjC/BhEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
  ASQHVx4=
  '/*!*/;
  # at 123
  #220125  6:40:35 server id 1  end_log_pos 154 CRC32 0x43fa19f1  Previous-GTIDs
  # [empty]
  # at 154
  #220125  6:41:17 server id 1  end_log_pos 177 CRC32 0x205de899  Stop
  SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
  DELIMITER ;
  # End of log file
  /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
  /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

輸出指定position位置的binlog日志

 mysqlbinlog --start-position='154' --stop-position='177'

輸出指定position位置的binlog日志到指定文件中

mysqlbinlog --start-position='154' --stop-position='177' mysqlbinlog.000001 > test.log

輸出指定position位置的binlog日志到壓縮文件中

mysqlbinlog --start-position="指定開始位置" --stop-position="指定結(jié)束位置" binlog文件|gzip > 壓縮文件名

輸出指定開始時(shí)間的binlog日志文章來源地址http://www.zghlxwxcb.cn/news/detail-856830.html

mysqlbinlog --start-datetime="yyyy-MM-dd HH:mm:ss" binlog文件

到了這里，關(guān)于【MySQL 數(shù)據(jù)寶典】【磁盤結(jié)構(gòu)】- 006 bin log 二進(jìn)制日志的基本模式和參數(shù)設(shè)置的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！