一文弄懂MySQL中redo log與binlog的區別

2022-02-15 19:01:53

前言

MySQL中有六種紀錄檔檔案，分別是：重做紀錄檔（redo log）、回滾紀錄檔（undo log）、二進位制紀錄檔（binlog）、錯誤紀錄檔（errorlog）、慢查詢紀錄檔（slow query log）、一般查詢紀錄檔（general log），中繼紀錄檔（relay log）。本文將詳細介紹MySQL redo log與binlog區別，下面來一起看看詳細的介紹吧

1. 什麼是redo log？

redo log又稱重做紀錄檔檔案，用於記錄事務操作的變化，記錄的是資料修改之後的值，不管事務是否提交都會記錄下來。在範例和媒介失敗（media failure）時，redo log檔案就能派上用場，如資料庫掉電，InnoDB儲存引擎會使用redo log恢復到掉電前的時刻，以此來保證資料的完整性。

1.1 redo紀錄檔檔名

每個InnoDB儲存引擎至少有1個重做紀錄檔檔案組（group），每個檔案組至少有2個重做紀錄檔檔案，如預設的ib_logfile0和ib_logfile1。
[外連圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片儲存下來直接上傳(img-kHz7zVve-1584783729916)(https://s3.ap-northeast-1.wasabisys.com/img.it145.com/202202/wbDzJFmynxihwzby3d.png)]

1.2 影響redo log引數

innodb_log_file_size：指定每個redo紀錄檔大小，預設值48MB
innodb_log_files_in_group：指定紀錄檔檔案組中redo紀錄檔檔案數量，預設為2
innodb_log_group_home_dir：指定紀錄檔檔案組所在路勁，預設值./，指mysql的資料目錄datadir
innodb_mirrored_log_groups：指定紀錄檔映象檔案組的數量，預設為1，此功能屬於未實現的功能，在5.6版本中廢棄，在5.7版本中刪除了。

以下顯示了一個關於redo紀錄檔組的設定：

mysql> show variables like 'innodb%log%';
+----------------------------------+------------+
| Variable_name                    | Value      |
+----------------------------------+------------+
...     
| innodb_log_file_size             | 2147483648 |
| innodb_log_files_in_group        | 2          |
| innodb_log_group_home_dir        | ./         |
...
+----------------------------------+------------+
15 rows in set (0.00 sec)

1.3 redo log大小怎麼設定？

redo log檔案的大小設定對於InnoDB儲存引擎的效能有著非常大的影響。

設定的太大

設定很大以後減少了checkpoint，並且由於redo log是順序I/O，大大提高了I/O效能。但是如果資料庫意外出現了問題，比如意外宕機，那麼需要重放紀錄檔並且恢復已經提交的事務，如果紀錄檔很大，那麼將會導致恢復時間很長。甚至到我們不能接受的程度。

設定的太小

當一個紀錄檔檔案寫滿後，innodb會自動切換到另外一個紀錄檔檔案，而且會觸發資料庫的檢查點（checkpoint），這會導致innodb快取髒頁的小批次重新整理，會明顯降低innodb的效能。

[外連圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片儲存下來直接上傳(img-6gU4thAZ-1584783729918)(https://s3.ap-northeast-1.wasabisys.com/img.it145.com/202202/Y2m7CCA2ir0c4i5402.png)]

怎麼設定？

參考官方檔案’Optimizing InnoDB Redo Logging’章節

把重做紀錄檔檔案設定很大，甚至與緩衝池一樣大。當InnoDB將重做紀錄檔檔案寫滿時，它會觸發資料庫的檢查點，把緩衝池的髒資料寫入磁碟。小的重做紀錄檔檔案會導致許多不必要的磁碟寫入。雖然在以前版本中，大的重做紀錄檔檔案導致冗長的恢復時間，但現在恢復速度更快，可以放心地使用大型重做紀錄檔檔案。

考慮增加紀錄檔緩衝區的大小。大型紀錄檔緩衝區可以在事務提交之前執行大型事務，而無需將紀錄檔寫入磁碟。因此，如果您有更新，插入或刪除許多行的事務，則使紀錄檔緩衝區更大可以節省磁碟I/O. 使用innodb_log_buffer_size設定選項設定紀錄檔緩衝區大小。

設定innodb_log_write_ahead_size引數，表示redo log寫前的塊大小。InnoDB以512位元組一個block的方式對齊寫入ib_logfile檔案，但檔案系統一般以4096位元組為一個block單位。如果即將寫入的紀錄檔檔案塊不在OS Cache時，就需要將對應的4096位元組的block讀入記憶體，修改其中的512位元組，然後再把該block寫回磁碟。當當前寫入檔案的偏移量不能整除該值時，則補0，多寫一部分資料。這樣當寫入的資料是以磁碟block size對齊時，就可以直接write磁碟，而無需read-modify-write這三步了。

2. 什麼是binlog

binlog記錄了對MySQL資料庫執行更改的所有操作，但是不包括SELECT和SHOW這類操作，因為這類操作對資料本身並沒有修改。然後，若操作本身並沒有導致資料庫發生變化，那麼該操作也會寫入二進位制紀錄檔。例如：

root@localhost [(none)] 08:30:14>set binlog_format = 'STATEMENT';

root@localhost [(none)] 08:30:26>use test;
Database changed
root@localhost [test] 08:30:33>select * from account;
+----------+---------+
| acct_num | amount  |
+----------+---------+
|      138 |   14.98 |
|      141 | 1937.50 |
|       97 | -100.00 |
+----------+---------+
3 rows in set (0.00 sec)


root@localhost [test] 08:30:53>show master status;
+----------------------+----------+--------------+------------------+--------------------------------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                          |
+----------------------+----------+--------------+------------------+--------------------------------------------+
| my3306_binlog.000052 |      471 |              |                  | e4382832-949d-11e8-97ba-080027793430:1-205 |
+----------------------+----------+--------------+------------------+--------------------------------------------+


root@localhost [test] 08:31:04>update account set acct_num=139 where amount=14;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 0  Changed: 0  Warnings: 0

root@localhost [test] 08:31:35>show binlog events in 'my3306_binlog.000052';
+----------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------+
| Log_name             | Pos | Event_type     | Server_id | End_log_pos | Info                                                                |
+----------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------+
| my3306_binlog.000052 |   4 | Format_desc    |   1003306 |         123 | Server ver: 5.7.23-log, Binlog ver: 4                               |
| my3306_binlog.000052 | 123 | Previous_gtids |   1003306 |         194 | e4382832-949d-11e8-97ba-080027793430:1-204                          |
| my3306_binlog.000052 | 194 | Gtid           |   1003306 |         259 | SET @@SESSION.GTID_NEXT= 'e4382832-949d-11e8-97ba-080027793430:205' |
| my3306_binlog.000052 | 259 | Query          |   1003306 |         331 | BEGIN                                                               |
| my3306_binlog.000052 | 331 | Table_map      |   1003306 |         384 | table_id: 108 (test.account)                                        |
| my3306_binlog.000052 | 384 | Update_rows    |   1003306 |         440 | table_id: 108 flags: STMT_END_F                                     |
| my3306_binlog.000052 | 440 | Xid            |   1003306 |         471 | COMMIT /* xid=14 */                                                 |
| my3306_binlog.000052 | 471 | Gtid           |   1003306 |         536 | SET @@SESSION.GTID_NEXT= 'e4382832-949d-11e8-97ba-080027793430:206' |
| my3306_binlog.000052 | 536 | Query          |   1003306 |         615 | BEGIN                                                               |
| my3306_binlog.000052 | 615 | Query          |   1003306 |         736 | use `test`; update account set acct_num=139 where amount=14         |
| my3306_binlog.000052 | 736 | Query          |   1003306 |         816 | COMMIT                                                              |
+----------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------+
11 rows in set (0.01 sec)

從上述例子中可以看到，MySQL資料庫首先進行update操作，從返回的結果看到Changed為0，這意味著該操作並沒有導致資料庫的變化。但是通過show binlog events in '...'可以看出在二進位制紀錄檔中的確進行了記錄。

如果想記錄SELECT和SHOW操作，那隻能使用查詢紀錄檔--general_log[={0|1}](1為啟用)

2.1 binlog檔名

通過設定引數--log-bin[=name]可以啟動二進位制紀錄檔。如果不指定那麼，則預設binlog紀錄檔檔名為主機名，字尾名為binlog的序列號，預設路勁為資料目錄(datadir).你也可以指定絕對路徑，如：

# cat /etc/my.cnf|grep log-bin
log-bin = /data/mysql/mysql3306/logs/my3306_binlog

# cd /data/mysql/mysql3306/logs
# ls -l
total 60
-rw-r----- 1 mysql mysql   194 Aug 15 10:04 my3306_binlog.000045
-rw-r----- 1 mysql mysql  1552 Aug 16 10:01 my3306_binlog.000046
-rw-r----- 1 mysql mysql  2953 Aug 17 09:56 my3306_binlog.000047
-rw-r----- 1 mysql mysql  1239 Aug 20 10:29 my3306_binlog.000048
-rw-r----- 1 mysql mysql   217 Aug 20 10:29 my3306_binlog.000049
-rw-r----- 1 mysql mysql 19567 Aug 21 10:24 my3306_binlog.000050
-rw-r----- 1 mysql mysql   194 Aug 22 08:01 my3306_binlog.000051
-rw-r----- 1 mysql mysql   816 Aug 22 08:31 my3306_binlog.000052
-rw-r----- 1 mysql mysql   384 Aug 22 08:01 my3306_binlog.index

2.2 影響binlog的引數

max_binlog_size:指定單個binlog檔案最大值。預設值為1g，最大值1g，如果超過該值，則產生新的binlog檔案，字尾名+1，並記錄到.index檔案。
binlog_cache_size：使用事務表儲存引擎（如innodb儲存引擎）時，所有未提交的binlog紀錄檔會被記錄到一個快取中去，等事務提交時再將快取中的binlog寫入到binlog檔案中。快取的大小由binlog_cache_size決定，預設大小為32K。

binlog_cache_size是基於session的，也就是說，當一個執行緒開始一個事務時，MySQL會自動分配一個大小為binlog_cache_size的快取，因此該值的設定需要非常小心，不能設定過大。
當一個事務的記錄大於設定的binlog_cache_size時，MySQL會把快取中的紀錄檔寫入一個臨時檔案中，因此該值又不能設的太小。
那怎麼設定呢？

通過show global status命令檢視binlog_cache_use,binlog_cache_disk_use的狀態，以判斷當前binlog_cache_size設定是否合適。

通過show global status命令檢視binlog_cache_use,binlog_cache_disk_use的狀態，以判斷當前binlog_cache_size設定是否合適。

binlog_cache_use：記錄了使用快取寫binlog次數
binlog_cache_disk_use：記錄了使用臨時檔案寫binlog次數。

範例：

root@localhost [(none)] 09:55:48>show variables like 'binlog_cache_size';
+-------------------+---------+
| Variable_name     | Value   |
+-------------------+---------+
| binlog_cache_size | 32768   |
+-------------------+---------+
1 row in set (0.00 sec)

root@localhost [(none)] 09:53:26>show global status like 'binlog_cache%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Binlog_cache_disk_use | 0     |
| Binlog_cache_use      | 33553 |
+-----------------------+-------+
2 rows in set (0.00 sec)

使用快取次數為33553，臨時檔案使用次數為0。說明32KB的快取大小對於當前MySQL資料庫是夠用的。

max_binlog_cache_size:如果事務需要的記憶體超過很多位元組，則伺服器會生成多於“max_binlog_cache_size”位元組的儲存錯誤所需的並行事務。最小值為4096位元組，最大可能值為16EB（exabytes）。建議的最大值為4GB; 這是因為MySQL目前無法使用大於4GB的二進位制紀錄檔位置。
expire_logs_days：表示binlog檔案自動刪除N天前的檔案。預設值為0，表示不自動刪除，最大值99.要手動刪除binlog檔案，可以使用purge binary logs語句。例如：

PURGE { BINARY | MASTER } LOGS
   { TO 'log_name' | BEFORE datetime_expr }

PURGE BINARY LOGS TO 'mysql-bin.010';
PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';
PURGE BINARY LOGS BEFORE now() - interval 3 days;

binlog_rows_query_log_events：預設為不啟用，啟用binlog_rows_query_log_events時，可以在binary log中記錄原始的SQL語句

root@localhost [test] 08:07:52>show binlog events in 'my3306_binlog.000056';
+----------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------+
| Log_name             | Pos | Event_type     | Server_id | End_log_pos | Info                                                                |
+----------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------+
| my3306_binlog.000056 |   4 | Format_desc    |   1003306 |         123 | Server ver: 5.7.23-log, Binlog ver: 4                               |
| my3306_binlog.000056 | 123 | Previous_gtids |   1003306 |         194 | e4382832-949d-11e8-97ba-080027793430:1-206                          |
| my3306_binlog.000056 | 194 | Gtid           |   1003306 |         259 | SET @@SESSION.GTID_NEXT= 'e4382832-949d-11e8-97ba-080027793430:207' |
| my3306_binlog.000056 | 259 | Query          |   1003306 |         331 | BEGIN                                                               |
| my3306_binlog.000056 | 331 | Table_map      |   1003306 |         375 | table_id: 108 (test.t)                                              |
| my3306_binlog.000056 | 375 | Update_rows    |   1003306 |         421 | table_id: 108 flags: STMT_END_F                                     |
| my3306_binlog.000056 | 421 | Xid            |   1003306 |         452 | COMMIT /* xid=16 */                                                 |
| my3306_binlog.000056 | 452 | Gtid           |   1003306 |         517 | SET @@SESSION.GTID_NEXT= 'e4382832-949d-11e8-97ba-080027793430:208' |
| my3306_binlog.000056 | 517 | Query          |   1003306 |         589 | BEGIN                                                               |
| my3306_binlog.000056 | 589 | Table_map      |   1003306 |         633 | table_id: 108 (test.t)                                              |
| my3306_binlog.000056 | 633 | Write_rows     |   1003306 |         673 | table_id: 108 flags: STMT_END_F                                     |
| my3306_binlog.000056 | 673 | Xid            |   1003306 |         704 | COMMIT /* xid=18 */                                                 |
| my3306_binlog.000056 | 704 | Gtid           |   1003306 |         769 | SET @@SESSION.GTID_NEXT= 'e4382832-949d-11e8-97ba-080027793430:209' |
| my3306_binlog.000056 | 769 | Query          |   1003306 |         841 | BEGIN                                                               |
| my3306_binlog.000056 | 841 | Rows_query     |   1003306 |         887 | # insert into t select 3                                            |
| my3306_binlog.000056 | 887 | Table_map      |   1003306 |         931 | table_id: 108 (test.t)                                              |
| my3306_binlog.000056 | 931 | Write_rows     |   1003306 |         971 | table_id: 108 flags: STMT_END_F                                     |
| my3306_binlog.000056 | 971 | Xid            |   1003306 |        1002 | COMMIT /* xid=24 */                                                 |
+----------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------+
# insert into t select 3   就是開啟binlog_rows_query_log_events選項後，記錄的原始SQL語句。

sync_binlog：sync_binlog=[N]表示沒寫緩衝N次就同步到磁碟，如果將N設為1，即sync_binlog表示採用同步寫磁碟的方式來寫二進位制紀錄檔，在MySQL5.7.7後，預設為1。會對資料庫的IO系統帶來一定影響，但可以得到最大的高可用行。
binlog_checksum：該引數目的就是寫入binlog進行校驗，有兩個值[crc32|none],預設為crc32
binlog-do-db：表示需要寫入紀錄檔的資料庫，預設為空，表示同步所有庫
binlog-ignore-db：表示忽略寫入紀錄檔的資料庫，預設為空，表示同步所有庫
log-slave-update:表示從master端取得並執行的binlog，寫入自己的binlog檔案中，一般應用在master=>slave=>slave架構
binlog_format：記錄binlog的格式。[statement,row,mixed],在MySQL5.7.7之後，預設為row。

儲存引起對binlog格式的支援情況：

[外連圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片儲存下來直接上傳(img-drzPlzHa-1584783729919)(https://s3.ap-northeast-1.wasabisys.com/img.it145.com/202202/nFgK19H5keowykp305.png)]

2.3 檢視binlog

使用mysqlbinlog程式進行檢視，例如：

[root@mysqldb1 10:58:18 /data/mysql/mysql3306/logs]
# mysqlbinlog -v --base64-output=decode-rows my3306_binlog.000052|more



/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#180822  8:01:00 server id 1003306  end_log_pos 123 CRC32 0xcbe20047 	Start: binlog v 4, server v 5.7.23-log created 180822  8:01:00 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
# at 123
#180822  8:01:00 server id 1003306  end_log_pos 194 CRC32 0xb1bda518 	Previous-GTIDs
# e4382832-949d-11e8-97ba-080027793430:1-204
# at 194
#180822  8:10:59 server id 1003306  end_log_pos 259 CRC32 0xeced9ada 	GTID	last_committed=0	sequence_number=1	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'e4382832-949d-11e8-97ba-080027793430:205'/*!*/;
# at 259
#180822  8:10:59 server id 1003306  end_log_pos 331 CRC32 0x6da7802a 	Query	thread_id=2	exec_time=0	error_code=0
SET TIMESTAMP=1534896659/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
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=45/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 331
#180822  8:10:59 server id 1003306  end_log_pos 384 CRC32 0xf239dd79 	Table_map: `test`.`account` mapped to number 108
# at 384
#180822  8:10:59 server id 1003306  end_log_pos 440 CRC32 0xef6460fe 	Update_rows: table id 108 flags: STMT_END_F
### UPDATE `test`.`account`
### WHERE
###   @1=137
###   @2=14.98
### SET
###   @1=138
###   @2=14.98
# at 440
#180822  8:10:59 server id 1003306  end_log_pos 471 CRC32 0x360f05d0 	Xid = 14
COMMIT/*!*/;
# at 471
#180822  8:31:35 server id 1003306  end_log_pos 536 CRC32 0x662c8f17 	GTID	last_committed=1	sequence_number=2	rbr_only=no
SET @@SESSION.GTID_NEXT= 'e4382832-949d-11e8-97ba-080027793430:206'/*!*/;
# at 536
#180822  8:31:35 server id 1003306  end_log_pos 615 CRC32 0xa728a60a 	Query	thread_id=3	exec_time=0	error_code=0
SET TIMESTAMP=1534897895/*!*/;
BEGIN
/*!*/;
# at 615
#180822  8:31:35 server id 1003306  end_log_pos 736 CRC32 0x7513aa73 	Query	thread_id=3	exec_time=0	error_code=0
use `test`/*!*/;
SET TIMESTAMP=1534897895/*!*/;
update account set acct_num=139 where amount=14
/*!*/;
# at 736
#180822  8:31:35 server id 1003306  end_log_pos 816 CRC32 0x1cd7f41c 	Query	thread_id=3	exec_time=0	error_code=0
SET TIMESTAMP=1534897895/*!*/;
COMMIT
/*!*/;
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*/;

3. redo log與binlog的區別

第一:redo log是在InnoDB儲存引擎層產生，而binlog是MySQL資料庫的上層產生的，並且二進位制紀錄檔不僅僅針對INNODB儲存引擎，MySQL資料庫中的任何儲存引擎對於資料庫的更改都會產生二進位制紀錄檔。

第二：兩種紀錄檔記錄的內容形式不同。MySQL的binlog是邏輯紀錄檔，其記錄是對應的SQL語句。而innodb儲存引擎層面的重做紀錄檔是物理紀錄檔。

第三：兩種紀錄檔與記錄寫入磁碟的時間點不同，二進位制紀錄檔只在事務提交完成後進行一次寫入。而innodb儲存引擎的重做紀錄檔在事務進行中不斷地被寫入，並紀錄檔不是隨事務提交的順序進行寫入的。

二進位制紀錄檔僅在事務提交時記錄，並且對於每一個事務，僅在事務提交時記錄，並且對於每一個事務，僅包含對應事務的一個紀錄檔。而對於innodb儲存引擎的重做紀錄檔，由於其記錄是物理操作紀錄檔，因此每個事務對應多個紀錄檔條目，並且事務的重做紀錄檔寫入是並行的，並非在事務提交時寫入，其在檔案中記錄的順序並非是事務開始的順序。

第四：binlog不是迴圈使用，在寫滿或者重啟之後，會生成新的binlog檔案，redo log是迴圈使用。

第五：binlog可以作為恢復資料使用，主從複製搭建，redo log作為異常宕機或者媒介故障後的資料恢復使用。