共計 9893 個字符，預計需要花費 25 分鐘才能閱讀完成。
今天就跟大家聊聊有關怎樣理解 mysql binlog，可能很多人都不太了解，為了讓大家更加了解，丸趣 TV 小編給大家總結了以下內容，希望大家根據這篇文章可以有所收獲。
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
截取部分查詢結果：
*************************** 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
截取部分查詢結果：
*************************** 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
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 有進一步的了解嗎？如果還想了解更多知識或者相關內容，請關注丸趣 TV 行業資訊頻道，感謝大家的支持。
