首頁  >  文章  >  資料庫  >  MySQL中redo log與binlog的差異有哪些

MySQL中redo log與binlog的差異有哪些

WBOY
WBOY轉載
2023-06-03 18:53:402043瀏覽

    前言

    MySQL中有六種日誌文件,分別是:重做日誌(redo log)、回溯日誌(undo log)、二進制日誌(binlog)、錯誤日誌(errorlog)、慢查詢日誌(slow query log)、一般查詢日誌(general log),中繼日誌(relay log)。

    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> 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的效能。

    [外鏈圖片轉存失敗,來源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-6gU4thAZ-1584783729918)(https://cache.yisu.com/upload/information /20220215/112/979584.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>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的参数

    • 指定每个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是循环使用。

    第五:binlog可以當作恢復資料使用,主從複製搭建,redo log作為異常宕機或媒體故障後的資料恢復使用。

    以上是MySQL中redo log與binlog的差異有哪些的詳細內容。更多資訊請關注PHP中文網其他相關文章!

    陳述:
    本文轉載於:yisu.com。如有侵權,請聯絡admin@php.cn刪除