What are the differences between redo log and binlog in MySQL?
- WBOYforward
- 2023-06-03 18:53:402083browse
Preface
There are six types of log files in MySQL, namely: redo log (redo log), rollback log (undo log), binary Log (binlog), error log (errorlog), slow query log (slow query log), general query log (general log), and relay log (relay log).
1. What is redo log?
redo log, also known as redo log file, is used to record changes in transaction operations. What is recorded is the value after data modification. It will be recorded regardless of whether the transaction is submitted or not. When an instance or media fails (media failure), the redo log file can come in handy. If the database is powered off, the InnoDB storage engine will use the redo log to restore to the moment before the power off to ensure the integrity of the data.
1.1 redo log file name
Each InnoDB storage engine has at least 1 redo log file group (group), and each file group has at least 2 redo log files, as default ib_logfile0 and ib_logfile1.
1.2 Impact on redo log parameters
innodb_log_file_size: Specify the size of each redo log, the default value is 48MB
-
innodb_log_files_in_group: Specify the number of redo log files in the log file group, the default is 2
innodb_log_group_home_dir: Specify the path where the log file group is located, the default value is ./, which refers to the data directory of mysql datadir
- ##innodb_mirrored_log_groups: Specifies the number of log mirror file groups. The default is 1. This function is an unimplemented function and was abandoned in version 5.6 and deleted in version 5.7.
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 How to set the redo log size? The size setting of the redo log file has a great impact on the performance of the InnoDB storage engine.
- The setting is too large
- The setting is too small
- How to set it?
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)As you can see from the above example, the MySQL database first performs an update operation, and the returned result shows that Changed is 0, which means that the operation did not cause changes to the database. But by showing binlog events in '...' we can see that it is indeed recorded in the binary log. If you want to record SELECT and SHOW operations, you can only use query log --general_log[={0|1}](1 is enabled)
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的参数
指定每个binlog文件的最大大小为max_binlog_size。默认值为1g，最大值1g，如果超过该值，则产生新的binlog文件，后缀名+1，并记录到.index文件。
binlog_cache_size：使用事务表存储引擎（如innodb存储引擎）时，所有未提交的binlog日志会被记录到一个缓存中去，等事务提交时再将缓存中的binlog写入到binlog文件中。The size of the cache is determined by binlog_cache_size and the default size is 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）。MySQL目前无法使用大于4GB的二进制日志位置，因此建议的最大值为4GB。
"expire_logs_days" denotes the automatic deletion of binlog files created N days ago.。默认值为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://cache.yisu.com/upload/information/20220215/112/979585.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是循环使用。
Fifth: Binlog can be used for data recovery, master-slave replication is established, and redo log can be used for data recovery after abnormal downtime or media failure.
The above is the detailed content of What are the differences between redo log and binlog in MySQL?. For more information, please follow other related articles on the PHP Chinese website!