MySQL redo log 与 binlog 的区别

1. 什么是redo log?

redo log又称重做日志文件,用于记录事务操作的变化,记录的是数据修改之后的值,不管事务是否提交都会记录下来。在实例和介质失败(media failure)时,redo log文件就能派上用场,如数据库掉电,InnoDB存储引擎会使用redo log恢复到掉电前的时刻,以此来保证数据的完整性。

1.1 redo日志文件名

每个InnoDB存储引擎至少有1个重做日志文件组(group),每个文件组至少有2个重做日志文件,如默认的ib_logfile0ib_logfile1

1.2 影响redo log参数

  1. - `innodb_log_file_size`:指定每个redo日志大小,默认值48MB
  2. - `innodb_log_files_in_group`:指定日志文件组中redo日志文件数量,默认为2
  3. - `innodb_log_group_home_dir`:指定日志文件组所在路劲,默认值`./`,指mysql的数据目录`datadir`
  4. - `innodb_mirrored_log_groups`:指定日志镜像文件组的数量,默认为1,此功能属于未实现的功能,在5.6版本中废弃,在5.7版本中删除了。

以下显示了一个关于redo日志组的配置:

  1. mysql> show variables like 'innodb%log%';
  2. +----------------------------------+------------+
  3. | Variable_name | Value |
  4. +----------------------------------+------------+
  5. | innodb_log_file_size | 2147483648 |
  6. | innodb_log_files_in_group | 2 |
  7. | innodb_log_group_home_dir | ./ |
  8. +----------------------------------+------------+
  9. 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这类操作,因为这类操作对数据本身并没有修改。然后,若操作本身并没有导致数据库发生变化,那么该操作也会写入二进制日志。例如:

  1. root@localhost [(none)] 08:30:14>set binlog_format = 'STATEMENT';
  2. root@localhost [(none)] 08:30:26>use test;
  3. Database changed
  4. root@localhost [test] 08:30:33>select * from account;
  5. +----------+---------+
  6. | acct_num | amount |
  7. +----------+---------+
  8. | 138 | 14.98 |
  9. | 141 | 1937.50 |
  10. | 97 | -100.00 |
  11. +----------+---------+
  12. 3 rows in set (0.00 sec)
  13. root@localhost [test] 08:30:53>show master status;
  14. +----------------------+----------+--------------+------------------+--------------------------------------------+
  15. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
  16. +----------------------+----------+--------------+------------------+--------------------------------------------+
  17. | my3306_binlog.000052 | 471 | | | e4382832-949d-11e8-97ba-080027793430:1-205 |
  18. +----------------------+----------+--------------+------------------+--------------------------------------------+
  19. root@localhost [test] 08:31:04>update account set acct_num=139 where amount=14;
  20. Query OK, 0 rows affected (0.01 sec)
  21. Rows matched: 0 Changed: 0 Warnings: 0
  22. root@localhost [test] 08:31:35>show binlog events in 'my3306_binlog.000052';
  23. +----------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------+
  24. | Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
  25. +----------------------+-----+----------------+-----------+-------------+-----------------------------------------------------+
  26. | my3306_binlog.000052 | 4 | Format_desc | 1003306 | 123 | Server ver: 5.7.23-log, Binlog ver: 4 |
  27. | my3306_binlog.000052 | 123 | Previous_gtids | 1003306 | 194 | e4382832-949d-11e8-97ba-080027793430:1-204 |
  28. | my3306_binlog.000052 | 194 | Gtid | 1003306 | 259 | SET @@SESSION.GTID_NEXT= 'e4382832-949d-11e8-97ba-080027793430:205' |
  29. | my3306_binlog.000052 | 259 | Query | 1003306 | 331 | BEGIN |
  30. | my3306_binlog.000052 | 331 | Table_map | 1003306 | 384 | table_id: 108 (test.account) |
  31. | my3306_binlog.000052 | 384 | Update_rows | 1003306 | 440 | table_id: 108 flags: STMT_END_F |
  32. | my3306_binlog.000052 | 440 | Xid | 1003306 | 471 | COMMIT /* xid=14 */ |
  33. | my3306_binlog.000052 | 471 | Gtid | 1003306 | 536 | SET @@SESSION.GTID_NEXT= 'e4382832-949d-11e8-97ba-080027793430:206' |
  34. | my3306_binlog.000052 | 536 | Query | 1003306 | 615 | BEGIN |
  35. | my3306_binlog.000052 | 615 | Query | 1003306 | 736 | use `test`; update account set acct_num=139 where amount=14 |
  36. | my3306_binlog.000052 | 736 | Query | 1003306 | 816 | COMMIT |
  37. +----------------------+-----+----------------+-----------+-------------+------------------------------------------------------+
  38. 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).你也可以指定绝对路径,如:

  1. # cat /etc/my.cnf|grep log-bin
  2. log-bin = /data/mysql/mysql3306/logs/my3306_binlog
  3. # cd /data/mysql/mysql3306/logs
  4. # ls -l
  5. total 60
  6. -rw-r----- 1 mysql mysql 194 Aug 15 10:04 my3306_binlog.000045
  7. -rw-r----- 1 mysql mysql 1552 Aug 16 10:01 my3306_binlog.000046
  8. -rw-r----- 1 mysql mysql 2953 Aug 17 09:56 my3306_binlog.000047
  9. -rw-r----- 1 mysql mysql 1239 Aug 20 10:29 my3306_binlog.000048
  10. -rw-r----- 1 mysql mysql 217 Aug 20 10:29 my3306_binlog.000049
  11. -rw-r----- 1 mysql mysql 19567 Aug 21 10:24 my3306_binlog.000050
  12. -rw-r----- 1 mysql mysql 194 Aug 22 08:01 my3306_binlog.000051
  13. -rw-r----- 1 mysql mysql 816 Aug 22 08:31 my3306_binlog.000052
  14. -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会把缓存中的日志写入一个临时文件中,因此该值又不能设的太小。

    那怎么设置呢?

  1. 通过show global status命令查看binlog_cache_use,binlog_cache_disk_use的状态,以判断当前binlog_cache_size设置是否合适。
  2. binlog_cache_use:记录了使用缓存写binlog次数
  3. binlog_cache_disk_use:记录了使用临时文件写binlog次数。
  4. 示例:
  5. root@localhost [(none)] 09:55:48>show variables like 'binlog_cache_size';
  6. +-------------------+---------+
  7. | Variable_name | Value |
  8. +-------------------+---------+
  9. | binlog_cache_size | 32768 |
  10. +-------------------+---------+
  11. 1 row in set (0.00 sec)
  12. root@localhost [(none)] 09:53:26>show global status like 'binlog_cache%';
  13. +-----------------------+-------+
  14. | Variable_name | Value |
  15. +-----------------------+-------+
  16. | Binlog_cache_disk_use | 0 |
  17. | Binlog_cache_use | 33553 |
  18. +-----------------------+-------+
  19. 2 rows in set (0.00 sec)
  20. 使用缓存次数为33553,临时文件使用次数为0。说明32KB的缓存大小对于当前MySQL数据库是够用的。
  21. - **max_binlog_cache_size**:如果事务需要的内存超过很多字节,则服务器会生成多于“max_binlog_cache_size”字节的存储错误所需的并发事务。 最小值为4096字节,最大可能值为16EBexabytes)。 **建议的最大值为4GB**; 这是因为MySQL目前无法使用大于4GB的二进制日志位置。
  22. - **expire_logs_days**:表示binlog文件自动删除N天前的文件。默认值为0,表示不自动删除,最大值99.要手动删除binlog文件,可以使用`purge binary logs`语句。例如:
  23. PURGE { BINARY | MASTER } LOGS
  24. { TO 'log_name' | BEFORE datetime_expr }
  25. PURGE BINARY LOGS TO 'mysql-bin.010';
  26. PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';
  27. PURGE BINARY LOGS BEFORE now() - interval 3 days;
  28. - binlog_rows_query_log_events
  29. :默认为不启用,启用binlog_rows_query_log_events时,会在binlog日志中记录原始SQL语句。
  30. root@localhost [test] 08:07:52>show binlog events in 'my3306_binlog.000056';
  31. +----------------------+-----+----------------+-----------+-------------+---------------------------------------------------+
  32. | Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
  33. +----------------------+-----+----------------+-----------+-------------+---------------------------------------------------+
  34. | my3306_binlog.000056 | 4 | Format_desc | 1003306 | 123 | Server ver: 5.7.23-log, Binlog ver: 4 |
  35. | my3306_binlog.000056 | 123 | Previous_gtids | 1003306 | 194 | e4382832-949d-11e8-97ba-080027793430:1-206 |
  36. | my3306_binlog.000056 | 194 | Gtid | 1003306 | 259 | SET @@SESSION.GTID_NEXT= 'e4382832-949d-11e8-97ba-080027793430:207' |
  37. | my3306_binlog.000056 | 259 | Query | 1003306 | 331 | BEGIN |
  38. | my3306_binlog.000056 | 331 | Table_map | 1003306 | 375 | table_id: 108 (test.t) |
  39. | my3306_binlog.000056 | 375 | Update_rows | 1003306 | 421 | table_id: 108 flags: STMT_END_F |
  40. | my3306_binlog.000056 | 421 | Xid | 1003306 | 452 | COMMIT /* xid=16 */ |
  41. | my3306_binlog.000056 | 452 | Gtid | 1003306 | 517 | SET @@SESSION.GTID_NEXT= 'e4382832-949d-11e8-97ba-080027793430:208' |
  42. | my3306_binlog.000056 | 517 | Query | 1003306 | 589 | BEGIN |
  43. | my3306_binlog.000056 | 589 | Table_map | 1003306 | 633 | table_id: 108 (test.t) |
  44. | my3306_binlog.000056 | 633 | Write_rows | 1003306 | 673 | table_id: 108 flags: STMT_END_F |
  45. | my3306_binlog.000056 | 673 | Xid | 1003306 | 704 | COMMIT /* xid=18 */ |
  46. | my3306_binlog.000056 | 704 | Gtid | 1003306 | 769 | SET @@SESSION.GTID_NEXT= 'e4382832-949d-11e8-97ba-080027793430:209' |
  47. | my3306_binlog.000056 | 769 | Query | 1003306 | 841 | BEGIN |
  48. | my3306_binlog.000056 | 841 | Rows_query | 1003306 | 887 | # insert into t select 3 |
  49. | my3306_binlog.000056 | 887 | Table_map | 1003306 | 931 | table_id: 108 (test.t) |
  50. | my3306_binlog.000056 | 931 | Write_rows | 1003306 | 971 | table_id: 108 flags: STMT_END_F |
  51. | my3306_binlog.000056 | 971 | Xid | 1003306 | 1002 | COMMIT /* xid=24 */ |
  52. +----------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------+
  53. #黄颜色标记的就是,开启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程序进行查看,例如:

  1. [root@mysqldb1 10:58:18 /data/mysql/mysql3306/logs]
  2. # mysqlbinlog -v --base64-output=decode-rows my3306_binlog.000052|more
  3. /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
  4. /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
  5. DELIMITER /*!*/;
  6. # at 4
  7. #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
  8. # Warning: this binlog is either in use or was not closed properly.
  9. ROLLBACK/*!*/;
  10. # at 123
  11. #180822 8:01:00 server id 1003306 end_log_pos 194 CRC32 0xb1bda518 Previous-GTIDs
  12. # e4382832-949d-11e8-97ba-080027793430:1-204
  13. # at 194
  14. #180822 8:10:59 server id 1003306 end_log_pos 259 CRC32 0xeced9ada GTID last_committed=0 sequence_number=1 rbr_only=yes
  15. /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
  16. SET @@SESSION.GTID_NEXT= 'e4382832-949d-11e8-97ba-080027793430:205'/*!*/;
  17. # at 259
  18. #180822 8:10:59 server id 1003306 end_log_pos 331 CRC32 0x6da7802a Query thread_id=2 exec_time=0 error_code=0
  19. SET TIMESTAMP=1534896659/*!*/;
  20. SET @@session.pseudo_thread_id=2/*!*/;
  21. SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
  22. SET @@session.sql_mode=1436549152/*!*/;
  23. SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
  24. /*!\C utf8 *//*!*/;
  25. SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=45/*!*/;
  26. SET @@session.lc_time_names=0/*!*/;
  27. SET @@session.collation_database=DEFAULT/*!*/;
  28. BEGIN
  29. /*!*/;
  30. # at 331
  31. #180822 8:10:59 server id 1003306 end_log_pos 384 CRC32 0xf239dd79 Table_map: `test`.`account` mapped to number 108
  32. # at 384
  33. #180822 8:10:59 server id 1003306 end_log_pos 440 CRC32 0xef6460fe Update_rows: table id 108 flags: STMT_END_F
  34. ### UPDATE `test`.`account`
  35. ### WHERE
  36. ### @1=137
  37. ### @2=14.98
  38. ### SET
  39. ### @1=138
  40. ### @2=14.98
  41. # at 440
  42. #180822 8:10:59 server id 1003306 end_log_pos 471 CRC32 0x360f05d0 Xid = 14
  43. COMMIT/*!*/;
  44. # at 471
  45. #180822 8:31:35 server id 1003306 end_log_pos 536 CRC32 0x662c8f17 GTID last_committed=1 sequence_number=2 rbr_only=no
  46. SET @@SESSION.GTID_NEXT= 'e4382832-949d-11e8-97ba-080027793430:206'/*!*/;
  47. # at 536
  48. #180822 8:31:35 server id 1003306 end_log_pos 615 CRC32 0xa728a60a Query thread_id=3 exec_time=0 error_code=0
  49. SET TIMESTAMP=1534897895/*!*/;
  50. BEGIN
  51. /*!*/;
  52. # at 615
  53. #180822 8:31:35 server id 1003306 end_log_pos 736 CRC32 0x7513aa73 Query thread_id=3 exec_time=0 error_code=0
  54. use `test`/*!*/;
  55. SET TIMESTAMP=1534897895/*!*/;
  56. update account set acct_num=139 where amount=14
  57. /*!*/;
  58. # at 736
  59. #180822 8:31:35 server id 1003306 end_log_pos 816 CRC32 0x1cd7f41c Query thread_id=3 exec_time=0 error_code=0
  60. SET TIMESTAMP=1534897895/*!*/;
  61. COMMIT
  62. /*!*/;
  63. SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
  64. DELIMITER ;
  65. # End of log file
  66. /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
  67. /*!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作为异常宕机或者介质故障后的数据恢复使用。

点赞 ( 0 )

0 条评论

发表评论

人生在世,错别字在所难免,无需纠正。

插入图片
s
返回顶部