ZhangZhihui's Blog  
MySQL - binlog

What's the difference between binlog and redolog?

 

In MySQL, the binlog (binary log) and redolog (redo log) are often confused because they both record data changes. However, they exist at different layers of the database architecture and serve fundamentally different purposes.

The simplest way to think about it is: Redolog is the "emergency backup" for the storage engine to survive a crash, while binlog is the "history book" used to sync other servers or recover to a specific point in time.

Key Differences at a Glance

Feature Binlog (Binary Log) Redolog (Redo Log)
Layer MySQL Server Layer (Available for all engines) InnoDB Storage Engine Layer (InnoDB only)
Log Type Logical Log: Records the "logic" of the change (e.g., "Update row 10 to X"). Physical Log: Records changes to specific physical data pages.
Purpose Replication and Point-in-Time Recovery. Crash Recovery: Ensuring data isn't lost if the power cuts out.
File Management Append-only: New files are created as old ones fill up. Circular: Overwrites the oldest data once the file is full.
Persistence Permanent (until manually purged). Temporary (data is deleted once flushed to the disk).

1. The Redolog (The "Short-term Memory")

The redolog is specific to the InnoDB storage engine. When you update data, MySQL doesn't immediately write it to the actual data files on disk (which is slow). Instead, it writes the change to the redolog and updates the data in memory (Buffer Pool).

  • Crash Safety: If the server crashes, the data in memory is lost. Upon restart, InnoDB reads the redolog to "redo" any transactions that were committed but hadn't made it to the final disk storage yet.

  • Circular Structure: It has a fixed size. Once it reaches the end of the file, it "wraps around" to the beginning, overwriting old data that has already been safely synced to the main data files.

2. The Binlog (The "Archival History")

The binlog belongs to the MySQL Server itself, meaning it works regardless of whether you use InnoDB, MyISAM, or other engines. It records all DDL (structure changes) and DML (data changes) events.

  • Replication: This is the magic behind Master-Slave setups. The master writes to the binlog, and the slave reads those events to stay in sync.

  • Point-in-Time Recovery: If you accidentally delete a table at 2:00 PM, you can restore a backup from last night and "replay" the binlog up until 1:59 PM to recover everything.

3. How they work together: Two-Phase Commit

To ensure these two logs don't disagree (e.g., a transaction is in the redolog but missing from the binlog), MySQL uses a Two-Phase Commit (2PC).

  1. Prepare Phase: InnoDB writes the change to the redolog and marks it as "Prepare."

  2. Commit Phase: The server writes the transaction to the binlog.

  3. Finalize: InnoDB changes the redolog status from "Prepare" to "Commit."

Would you like me to show you how to check the status and size of these logs on your running MySQL instance?

 

SHOW VARIABLES LIKE 'log_bin%';

1

 

SHOW BINARY LOGS;

1

 

PS C:\Users\ZhangZhihui> docker exec -it zmysql bash

bash-5.1# find / -name mysqlbinlog 2>/dev/null
/usr/libexec/mysqlsh/mysqlbinlog

 

bash-5.1# /usr/libexec/mysqlsh/mysqlbinlog -v /var/lib/mysql/binlog.000003
# The proper term is pseudo_replica_mode, but we use this compatibility alias
# to make the statement usable on server versions 8.0.24 and older.
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#251222  2:07:53 server id 1  end_log_pos 127 CRC32 0x3f81d23f  Start: binlog v 4, server v 9.4.0 created 251222  2:07:53 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
+adIaQ8BAAAAewAAAH8AAAABAAQAOS40LjAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAD5p0hpEwANAAgAAAAABAAEAAAAYwAEGggAAAAAAAACAAAACgoKKioAEjQA
CigAAAE/0oE/
'/*!*/;
# at 127
#251222  2:07:53 server id 1  end_log_pos 158 CRC32 0x0200e96b  Previous-GTIDs
# [empty]
# at 158
#251222  2:38:23 server id 1  end_log_pos 237 CRC32 0x0caaac85  Anonymous_GTID  last_committed=0        sequence_number=1       rbr_only=no     original_committed_timestamp=1766371103606912 immediate_commit_timestamp=1766371103606912     transaction_length=290
# original_commit_timestamp=1766371103606912 (2025-12-22 02:38:23.606912 UTC)
# immediate_commit_timestamp=1766371103606912 (2025-12-22 02:38:23.606912 UTC)
/*!80001 SET @@session.original_commit_timestamp=1766371103606912*//*!*/;
/*!80014 SET @@session.original_server_version=90400*//*!*/;
/*!80014 SET @@session.immediate_server_version=90400*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 237
#251222  2:38:23 server id 1  end_log_pos 448 CRC32 0x2bca3419  Query   thread_id=13    exec_time=0     error_code=0    Xid = 61
use `z_mysql_db`/*!*/;
SET TIMESTAMP=1766371103/*!*/;
SET @@session.pseudo_thread_id=13/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
/* ApplicationName=DBeaver 25.3.1 - SQLEditor <Script-2.sql> */ CREATE TABLE ztest_1(id int, name varchar(100))
/*!*/;
# at 448
#251222  3:20:36 server id 1  end_log_pos 527 CRC32 0x3064c9b7  Anonymous_GTID  last_committed=1        sequence_number=2       rbr_only=yes    original_committed_timestamp=1766373636742144 immediate_commit_timestamp=1766373636742144     transaction_length=303
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1766373636742144 (2025-12-22 03:20:36.742144 UTC)
# immediate_commit_timestamp=1766373636742144 (2025-12-22 03:20:36.742144 UTC)
/*!80001 SET @@session.original_commit_timestamp=1766373636742144*//*!*/;
/*!80014 SET @@session.original_server_version=90400*//*!*/;
/*!80014 SET @@session.immediate_server_version=90400*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 527
#251222  3:20:36 server id 1  end_log_pos 608 CRC32 0xc0c88dd9  Query   thread_id=13    exec_time=0     error_code=0
SET TIMESTAMP=1766373636/*!*/;
BEGIN
/*!*/;
# at 608
#251222  3:20:36 server id 1  end_log_pos 675 CRC32 0xa01280b7  Table_map: `z_mysql_db`.`ztest_1` mapped to number 97
# has_generated_invisible_primary_key=0
# at 675
#251222  3:20:36 server id 1  end_log_pos 720 CRC32 0x437cd88b  Write_rows: table id 97 flags: STMT_END_F

BINLOG '
BLlIaRMBAAAAQwAAAKMCAAAAAGEAAAAAAAEACnpfbXlzcWxfZGIAB3p0ZXN0XzEAAgMPApABAwEB
AAID/P8At4ASoA==
BLlIaR4BAAAALQAAANACAAAAAGEAAAAAAAEAAgAC/wABAAAAAwBhYWGL2HxD
'/*!*/;
### INSERT INTO `z_mysql_db`.`ztest_1`
### SET
###   @1=1
###   @2='aaa'
# at 720
#251222  3:20:36 server id 1  end_log_pos 751 CRC32 0xedf461a4  Xid = 74
COMMIT/*!*/;
# at 751
#251222  3:29:20 server id 1  end_log_pos 830 CRC32 0xbf48572d  Anonymous_GTID  last_committed=2        sequence_number=3       rbr_only=yes    original_committed_timestamp=1766374160678769 immediate_commit_timestamp=1766374160678769     transaction_length=303
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1766374160678769 (2025-12-22 03:29:20.678769 UTC)
# immediate_commit_timestamp=1766374160678769 (2025-12-22 03:29:20.678769 UTC)
/*!80001 SET @@session.original_commit_timestamp=1766374160678769*//*!*/;
/*!80014 SET @@session.original_server_version=90400*//*!*/;
/*!80014 SET @@session.immediate_server_version=90400*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 830
#251222  3:29:20 server id 1  end_log_pos 911 CRC32 0x2c030e5c  Query   thread_id=13    exec_time=0     error_code=0
SET TIMESTAMP=1766374160/*!*/;
BEGIN
/*!*/;
# at 911
#251222  3:29:20 server id 1  end_log_pos 978 CRC32 0x44277c51  Table_map: `z_mysql_db`.`ztest_1` mapped to number 97
# has_generated_invisible_primary_key=0
# at 978
#251222  3:29:20 server id 1  end_log_pos 1023 CRC32 0x157c1979         Delete_rows: table id 97 flags: STMT_END_F

BINLOG '
ELtIaRMBAAAAQwAAANIDAAAAAGEAAAAAAAEACnpfbXlzcWxfZGIAB3p0ZXN0XzEAAgMPApABAwEB
AAID/P8AUXwnRA==
ELtIaSABAAAALQAAAP8DAAAAAGEAAAAAAAEAAgAC/wABAAAAAwBhYWF5GXwV
'/*!*/;
### DELETE FROM `z_mysql_db`.`ztest_1`
### WHERE
###   @1=1
###   @2='aaa'
# at 1023
#251222  3:29:20 server id 1  end_log_pos 1054 CRC32 0x99b881ce         Xid = 77
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*/;

 

Created a new database named 'test_db' and a table 'ztest_2' in it.

bash-5.1# /usr/libexec/mysqlsh/mysqlbinlog -v /var/lib/mysql/binlog.000003
# The proper term is pseudo_replica_mode, but we use this compatibility alias
# to make the statement usable on server versions 8.0.24 and older.
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#251222  2:07:53 server id 1  end_log_pos 127 CRC32 0x3f81d23f  Start: binlog v 4, server v 9.4.0 created 251222  2:07:53 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
+adIaQ8BAAAAewAAAH8AAAABAAQAOS40LjAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAD5p0hpEwANAAgAAAAABAAEAAAAYwAEGggAAAAAAAACAAAACgoKKioAEjQA
CigAAAE/0oE/
'/*!*/;
# at 127
#251222  2:07:53 server id 1  end_log_pos 158 CRC32 0x0200e96b  Previous-GTIDs
# [empty]
# at 158
#251222  2:38:23 server id 1  end_log_pos 237 CRC32 0x0caaac85  Anonymous_GTID  last_committed=0        sequence_number=1       rbr_only=no     original_committed_timestamp=1766371103606912 immediate_commit_timestamp=1766371103606912     transaction_length=290
# original_commit_timestamp=1766371103606912 (2025-12-22 02:38:23.606912 UTC)
# immediate_commit_timestamp=1766371103606912 (2025-12-22 02:38:23.606912 UTC)
/*!80001 SET @@session.original_commit_timestamp=1766371103606912*//*!*/;
/*!80014 SET @@session.original_server_version=90400*//*!*/;
/*!80014 SET @@session.immediate_server_version=90400*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 237
#251222  2:38:23 server id 1  end_log_pos 448 CRC32 0x2bca3419  Query   thread_id=13    exec_time=0     error_code=0    Xid = 61
use `z_mysql_db`/*!*/;
SET TIMESTAMP=1766371103/*!*/;
SET @@session.pseudo_thread_id=13/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
/* ApplicationName=DBeaver 25.3.1 - SQLEditor <Script-2.sql> */ CREATE TABLE ztest_1(id int, name varchar(100))
/*!*/;
# at 448
#251222  3:20:36 server id 1  end_log_pos 527 CRC32 0x3064c9b7  Anonymous_GTID  last_committed=1        sequence_number=2       rbr_only=yes    original_committed_timestamp=1766373636742144 immediate_commit_timestamp=1766373636742144     transaction_length=303
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1766373636742144 (2025-12-22 03:20:36.742144 UTC)
# immediate_commit_timestamp=1766373636742144 (2025-12-22 03:20:36.742144 UTC)
/*!80001 SET @@session.original_commit_timestamp=1766373636742144*//*!*/;
/*!80014 SET @@session.original_server_version=90400*//*!*/;
/*!80014 SET @@session.immediate_server_version=90400*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 527
#251222  3:20:36 server id 1  end_log_pos 608 CRC32 0xc0c88dd9  Query   thread_id=13    exec_time=0     error_code=0
SET TIMESTAMP=1766373636/*!*/;
BEGIN
/*!*/;
# at 608
#251222  3:20:36 server id 1  end_log_pos 675 CRC32 0xa01280b7  Table_map: `z_mysql_db`.`ztest_1` mapped to number 97
# has_generated_invisible_primary_key=0
# at 675
#251222  3:20:36 server id 1  end_log_pos 720 CRC32 0x437cd88b  Write_rows: table id 97 flags: STMT_END_F

BINLOG '
BLlIaRMBAAAAQwAAAKMCAAAAAGEAAAAAAAEACnpfbXlzcWxfZGIAB3p0ZXN0XzEAAgMPApABAwEB
AAID/P8At4ASoA==
BLlIaR4BAAAALQAAANACAAAAAGEAAAAAAAEAAgAC/wABAAAAAwBhYWGL2HxD
'/*!*/;
### INSERT INTO `z_mysql_db`.`ztest_1`
### SET
###   @1=1
###   @2='aaa'
# at 720
#251222  3:20:36 server id 1  end_log_pos 751 CRC32 0xedf461a4  Xid = 74
COMMIT/*!*/;
# at 751
#251222  3:29:20 server id 1  end_log_pos 830 CRC32 0xbf48572d  Anonymous_GTID  last_committed=2        sequence_number=3       rbr_only=yes    original_committed_timestamp=1766374160678769 immediate_commit_timestamp=1766374160678769     transaction_length=303
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1766374160678769 (2025-12-22 03:29:20.678769 UTC)
# immediate_commit_timestamp=1766374160678769 (2025-12-22 03:29:20.678769 UTC)
/*!80001 SET @@session.original_commit_timestamp=1766374160678769*//*!*/;
/*!80014 SET @@session.original_server_version=90400*//*!*/;
/*!80014 SET @@session.immediate_server_version=90400*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 830
#251222  3:29:20 server id 1  end_log_pos 911 CRC32 0x2c030e5c  Query   thread_id=13    exec_time=0     error_code=0
SET TIMESTAMP=1766374160/*!*/;
BEGIN
/*!*/;
# at 911
#251222  3:29:20 server id 1  end_log_pos 978 CRC32 0x44277c51  Table_map: `z_mysql_db`.`ztest_1` mapped to number 97
# has_generated_invisible_primary_key=0
# at 978
#251222  3:29:20 server id 1  end_log_pos 1023 CRC32 0x157c1979         Delete_rows: table id 97 flags: STMT_END_F

BINLOG '
ELtIaRMBAAAAQwAAANIDAAAAAGEAAAAAAAEACnpfbXlzcWxfZGIAB3p0ZXN0XzEAAgMPApABAwEB
AAID/P8AUXwnRA==
ELtIaSABAAAALQAAAP8DAAAAAGEAAAAAAAEAAgAC/wABAAAAAwBhYWF5GXwV
'/*!*/;
### DELETE FROM `z_mysql_db`.`ztest_1`
### WHERE
###   @1=1
###   @2='aaa'
# at 1023
#251222  3:29:20 server id 1  end_log_pos 1054 CRC32 0x99b881ce         Xid = 77
COMMIT/*!*/;
# at 1054
#251222  3:52:11 server id 1  end_log_pos 1133 CRC32 0xc7ce51fd         Anonymous_GTID  last_committed=3        sequence_number=4       rbr_only=no     original_committed_timestamp=1766375531195211 immediate_commit_timestamp=1766375531195211     transaction_length=305
# original_commit_timestamp=1766375531195211 (2025-12-22 03:52:11.195211 UTC)
# immediate_commit_timestamp=1766375531195211 (2025-12-22 03:52:11.195211 UTC)
/*!80001 SET @@session.original_commit_timestamp=1766375531195211*//*!*/;
/*!80014 SET @@session.original_server_version=90400*//*!*/;
/*!80014 SET @@session.immediate_server_version=90400*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1133
#251222  3:52:11 server id 1  end_log_pos 1359 CRC32 0xb31b3402         Query   thread_id=11    exec_time=0     error_code=0    Xid = 85
SET TIMESTAMP=1766375531/*!*/;
/*!80016 SET @@session.default_table_encryption=0*//*!*/;
/* ApplicationName=DBeaver 25.3.1 - Main */ CREATE SCHEMA `test_db`
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_0900_ai_ci
/*!*/;
# at 1359
#251222  3:55:12 server id 1  end_log_pos 1438 CRC32 0xde6abf7e         Anonymous_GTID  last_committed=4        sequence_number=5       rbr_only=no     original_committed_timestamp=1766375712879511 immediate_commit_timestamp=1766375712879511     transaction_length=285
# original_commit_timestamp=1766375712879511 (2025-12-22 03:55:12.879511 UTC)
# immediate_commit_timestamp=1766375712879511 (2025-12-22 03:55:12.879511 UTC)
/*!80001 SET @@session.original_commit_timestamp=1766375712879511*//*!*/;
/*!80014 SET @@session.original_server_version=90400*//*!*/;
/*!80014 SET @@session.immediate_server_version=90400*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1438
#251222  3:55:12 server id 1  end_log_pos 1644 CRC32 0xb1e86d8d         Query   thread_id=16    exec_time=0     error_code=0    Xid = 110
use `test_db`/*!*/;
SET TIMESTAMP=1766375712/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
/* ApplicationName=DBeaver 25.3.1 - SQLEditor <Script-2.sql> */ CREATE TABLE ztest_2(name VARCHAR(100), age INT)
/*!*/;
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*/;

 

1

So multiple databases share the same binlog file.

 

SHOW variables LIKE 'innodb_redo%'

1

 

posted on 2025-12-22 11:26  ZhangZhihuiAAA  阅读(1)  评论(0)    收藏  举报
 
博客园  ©  2004-2025
浙公网安备 33010602011771号 浙ICP备2021040463号-3