MySQL中redo log与binlog的区别有哪些

魁首哥

作者

MySQL中redo log与binlog的区别有哪些

这篇文章主要介绍“MySQL中redolog与binlog的区别有哪些”的相关知识，小编通过实际案例向大家展示操作过程，操作方法简单快捷，实用性强，希望这篇“MySQL中redolog与binlog的区别有哪些”文章能帮助大家解决问题。

前言

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。

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>showvariableslike'innodb%log%';+----------------------------------+------------+|Variable_name|Value|+----------------------------------+------------+...|innodb_log_file_size|2147483648||innodb_log_files_in_group|2||innodb_log_group_home_dir|./|...+----------------------------------+------------+15rowsinset(0.00sec)

1.3 redo log大小怎么设置？

redo log文件的大小设置对于InnoDB存储引擎的性能有着非常大的影响。

设置的太大

设置很大以后减少了checkpoint，并且由于redo log是顺序I/O，大大提高了I/O性能。但是如果数据库意外出现了问题，比如意外宕机，那么需要重放日志并且恢复已经提交的事务，如果日志很大，那么将会导致恢复时间很长。甚至到我们不能接受的程度。

设置的太小

当一个日志文件写满后，innodb会自动切换到另外一个日志文件，而且会触发数据库的检查点（checkpoint），这会导致innodb缓存脏页的小批量刷新，会明显降低innodb的性能。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6gU4thAZ-1584783729918)(https://mip.qiaqa.com/uploads/article/2022/02/15/6587.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>setbinlog_format='STATEMENT';root@localhost[(none)]08:30:26>usetest;Databasechangedroot@localhost[test]08:30:33>select*fromaccount;+----------+---------+|acct_num|amount|+----------+---------+|138|14.98||141|1937.50||97|-100.00|+----------+---------+3rowsinset(0.00sec)root@localhost[test]08:30:53>showmasterstatus;+----------------------+----------+--------------+------------------+--------------------------------------------+|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>updateaccountsetacct_num=139whereamount=14;QueryOK,0rowsaffected(0.01sec)Rowsmatched:0Changed:0Warnings:0root@localhost[test]08:31:35>showbinlogeventsin'my3306_binlog.000052';+----------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------+|Log_name|Pos|Event_type|Server_id|End_log_pos|Info|+----------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------+|my3306_binlog.000052|4|Format_desc|1003306|123|Serverver:5.7.23-log,Binlogver: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:108flags: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`;updateaccountsetacct_num=139whereamount=14||my3306_binlog.000052|736|Query|1003306|816|COMMIT|+----------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------+11rowsinset(0.01sec)

从上述例子中可以看到，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|greplog-binlog-bin=/data/mysql/mysql3306/logs/my3306_binlog#cd/data/mysql/mysql3306/logs#ls-ltotal60-rw-r-----1mysqlmysql194Aug1510:04my3306_binlog.000045-rw-r-----1mysqlmysql1552Aug1610:01my3306_binlog.000046-rw-r-----1mysqlmysql2953Aug1709:56my3306_binlog.000047-rw-r-----1mysqlmysql1239Aug2010:29my3306_binlog.000048-rw-r-----1mysqlmysql217Aug2010:29my3306_binlog.000049-rw-r-----1mysqlmysql19567Aug2110:24my3306_binlog.000050-rw-r-----1mysqlmysql194Aug2208:01my3306_binlog.000051-rw-r-----1mysqlmysql816Aug2208:31my3306_binlog.000052-rw-r-----1mysqlmysql384Aug2208:01my3306_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设置是否合适。

通过showglobalstatus命令查看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>showvariableslike'binlog_cache_size';+-------------------+---------+|Variable_name|Value|+-------------------+---------+|binlog_cache_size|32768|+-------------------+---------+1rowinset(0.00sec)root@localhost[(none)]09:53:26>showglobalstatuslike'binlog_cache%';+-----------------------+-------+|Variable_name|Value|+-----------------------+-------+|Binlog_cache_disk_use|0||Binlog_cache_use|33553|+-----------------------+-------+2rowsinset(0.00sec)使用缓存次数为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'|BEFOREdatetime_expr}PURGEBINARYLOGSTO'mysql-bin.010';PURGEBINARYLOGSBEFORE'2008-04-0222:46:26';PURGEBINARYLOGSBEFOREnow()-interval3days;

binlog_rows_query_log_events：默认为不启用，启用binlog_rows_query_log_events时，可以在binary log中记录原始的SQL语句

root@localhost[test]08:07:52>showbinlogeventsin'my3306_binlog.000056';+----------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------+|Log_name|Pos|Event_type|Server_id|End_log_pos|Info|+----------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------+|my3306_binlog.000056|4|Format_desc|1003306|123|Serverver:5.7.23-log,Binlogver: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:108flags: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:108flags: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|#insertintotselect3||my3306_binlog.000056|887|Table_map|1003306|931|table_id:108(test.t)||my3306_binlog.000056|931|Write_rows|1003306|971|table_id:108flags:STMT_END_F||my3306_binlog.000056|971|Xid|1003306|1002|COMMIT/*xid=24*/|+----------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------+#insertintotselect3就是开启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://mip.qiaqa.com/uploads/article/2022/02/15/6588.png)]

2.3 查看binlog

使用mysqlbinlog程序进行查看，例如：

[root@mysqldb110:58:18/data/mysql/mysql3306/logs]#mysqlbinlog-v--base64-output=decode-rowsmy3306_binlog.000052|more/*!50530SET@@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!50003SET@OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER/*!*/;#at4#1808228:01:00serverid1003306end_log_pos123CRC320xcbe20047Start:binlogv4,serverv5.7.23-logcreated1808228:01:00atstartup#Warning:thisbinlogiseitherinuseorwasnotclosedproperly.ROLLBACK/*!*/;#at123#1808228:01:00serverid1003306end_log_pos194CRC320xb1bda518Previous-GTIDs#e4382832-949d-11e8-97ba-080027793430:1-204#at194#1808228:10:59serverid1003306end_log_pos259CRC320xeced9adaGTIDlast_committed=0sequence_number=1rbr_only=yes/*!50718SETTRANSACTIONISOLATIONLEVELREADCOMMITTED*//*!*/;SET@@SESSION.GTID_NEXT='e4382832-949d-11e8-97ba-080027793430:205'/*!*/;#at259#1808228:10:59serverid1003306end_log_pos331CRC320x6da7802aQuerythread_id=2exec_time=0error_code=0SETTIMESTAMP=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/*!*/;/*!\Cutf8*//*!*/;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/*!*/;#at331#1808228:10:59serverid1003306end_log_pos384CRC320xf239dd79Table_map:`test`.`account`mappedtonumber108#at384#1808228:10:59serverid1003306end_log_pos440CRC320xef6460feUpdate_rows:tableid108flags:STMT_END_F###UPDATE`test`.`account`###WHERE###@1=137###@2=14.98###SET###@1=138###@2=14.98#at440#1808228:10:59serverid1003306end_log_pos471CRC320x360f05d0Xid=14COMMIT/*!*/;#at471#1808228:31:35serverid1003306end_log_pos536CRC320x662c8f17GTIDlast_committed=1sequence_number=2rbr_only=noSET@@SESSION.GTID_NEXT='e4382832-949d-11e8-97ba-080027793430:206'/*!*/;#at536#1808228:31:35serverid1003306end_log_pos615CRC320xa728a60aQuerythread_id=3exec_time=0error_code=0SETTIMESTAMP=1534897895/*!*/;BEGIN/*!*/;#at615#1808228:31:35serverid1003306end_log_pos736CRC320x7513aa73Querythread_id=3exec_time=0error_code=0use`test`/*!*/;SETTIMESTAMP=1534897895/*!*/;updateaccountsetacct_num=139whereamount=14/*!*/;#at736#1808228:31:35serverid1003306end_log_pos816CRC320x1cd7f41cQuerythread_id=3exec_time=0error_code=0SETTIMESTAMP=1534897895/*!*/;COMMIT/*!*/;SET@@SESSION.GTID_NEXT='AUTOMATIC'/*addedbymysqlbinlog*//*!*/;DELIMITER;#Endoflogfile/*!50003SETCOMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530SET@@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作为异常宕机或者介质故障后的数据恢复使用。

关于“MySQL中redolog与binlog的区别有哪些”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识，可以关注恰卡编程网行业资讯频道，小编每天都会为大家更新不同的知识点。

阅读全文

发布于 2022-02-15 20:39:31