MySQL redo log 与 binlog 的区别

什么是redo log
什么是binlog
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。
MySQL redo log 与 binlog 的区别

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的性能。
怎么设置？
参考官方文档'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设置是否合适。

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时，会在binlog日志中记录原始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 */                                                 |

+----------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------+

#黄颜色标记的就是，开启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格式的支持情况：

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作为异常宕机或者介质故障后的数据恢复使用。

MySQL redo log 与 binlog 的区别的更多相关文章

一生挚友redo log、binlog《死磕MySQL系列二》
系列文章原来一条select语句在MySQL是这样执行的<死磕MySQL系列一> 一生挚友redo log.binlog<死磕MySQL系列二> 前言咔咔闲谈上期根据 ...
数据库中的两个最重要的日志redo log和binlog
mysql整体来看其实只有两部分,一部分是server层,一部分是引擎层. 1.redo log(重做日志):当有一条记录需要更新的时候,InnoDB 引擎就会先把记录写入redo log里面,并更新 ...
MySQL日志系统：redo log与binlog
日志系统主要有redo log(重做日志)和binlog(归档日志).redo log是InnoDB存储引擎层的日志,binlog是MySQL Server层记录的日志, 两者都是记录了某些操作的日志 ...
MySQL 日志系统之 redo log 和 binlog
之前我们了解了一条查询语句的执行流程,并介绍了执行过程中涉及的处理模块.一条查询语句的执行过程一般是经过连接器.分析器.优化器.执行器等功能模块,最后到达存储引擎. 那么,一条 SQL 更新语句的执行 ...
MySQL更新数据时，日志（redo log、binlog）执行流程
1:背景项目需要做Es和数据库的同步,而手动在代码中进行数据同步又是Es的一些不必要的数据同步操作和业务逻辑耦合,所以使用的了读取mysql的binlog日志的方式进行同步Es的数据. 问题1:根据 ...
msyql redo log和binlog
更新语句执行流程下面是这个表的创建语句,这个表有一个主键 ID 和一个整型字段 c: create table T(ID int primary key, c int); 如果要将 ID=2 这一行 ...
redo log 与 binlog
redo log 与 binlog redo log redo log (重做日志)是处于存储引擎层的,是InnoDB引擎特有的 redo log 存储的是物理日志 --- 即,"在某个 ...
MySQL redo log及recover过程浅析
写在前面:作者水平有限,欢迎不吝赐教,一切以最新源码为准. InnoDB redo log 首先介绍下Innodb redo log是什么,为什么需要记录redo log,以及redo log的作用都 ...
zz MySQL redo log及recover过程浅析
原作地址:http://www.cnblogs.com/liuhao/p/3714012.html 写在前面:作者水平有限,欢迎不吝赐教,一切以最新源码为准. InnoDB redo log 首先介绍 ...

随机推荐

nth-of-type在选择class的时候需要注意的一个小问题
查了下w3和MDN的手册,没发现有这个说明,写篇随笔记下. 1..class:nth-of-type(n)在选择class的时候,如果在class前面插入x个同类型标签,n需要加上x <!DOC ...
反人类的MyEclipse之-调整JavaScript代码-花括号换行显示
然后将除最后一项外所有的都勾选为Next Line
阿里云主机(aliyun-Linux) x64安装Redis详解
转载于:http://www.itxuexiwang.com/a/shujukujishu/redis/2016/0216/97.html?1455870336 如何在Linux上安装Redis呢, ...
fgets和fputs函数
1 函数输入下面两个函数提供每次输入一行的功能. #include <stdio.h> char *fgets( char *restrict buf, int n, FILE *res ...
CSS3中样式顺序
.box{ /*1*/ background: yellow; /*2*/ background: radial-gradient(ellise, yellow, red); } 就以上样式1和2的顺 ...
table的边框线的设置
http://hi.baidu.com/weisuotang/item/a1d98ec298c0aa49a8ba9447 http://www.cnblogs.com/xinlei/archive/2 ...
chrome 浏览器帐号登录不来，如何解决自己的书签
装系统前把该目录下 C:\Users\Administrator\AppData\Local\Google\Chrome\User Data\Default 的 Bookmarks 复制出 ...
性能测试工具Locust的使用
一.写在前面官网:https://www.locust.io/ 官方使用文档:https://docs.locust.io/en/latest/ 大并发量测试时,建议在linux系统下进行. 二.L ...
关于分页器border重叠问题
.paging li { cursor: pointer; display: inline-block; float: left; box-sizing: border-box; margin-lef ...
debugger
今天爬取cfda时遇到的困难,一旦开启了调试,就debugger pause, ???还有这种操作一顿google,百度,解决了这个问题,点一下Deactivate breakPoints,然后点一 ...