Shulou(Shulou.com)06/01 Report--

This article mainly introduces the relevant knowledge of "what is the difference between redo log and binlog in MySQL". The editor shows you the operation process through an actual case, the operation method is simple and fast, and it is practical. I hope this article "what is the difference between redo log and binlog in MySQL" can help you solve the problem.

Preface

There are six kinds 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), 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, recording the value after data modification, regardless of whether the transaction is committed or not. Redo log files can come in handy when instances and media fail (media failure). For example, if the database is powered off, the InnoDB storage engine will use redo log to restore to the moment before the power failure to ensure the integrity of the data.

1.1 redo log file name

Each InnoDB storage engine has at least 1 redo log filegroup (group), and each filegroup has at least 2 redo log files, such as the default ib_logfile0 and ib_logfile1.

1.2 affect redo log parameters

Innodb_log_file_size: specifies the size of each redo log. The default is 48MB.

Innodb_log_files_in_group: specifies the number of redo log files in the log filegroup. The default is 2.

Innodb_log_group_home_dir: specify the road strength where the log filegroup resides. The default is. /, which refers to the data directory datadir of mysql.

Innodb_mirrored_log_groups: specifies the number of log image filegroups. The default is 1. This feature is an unimplemented feature, abandoned in version 5.6 and deleted in version 5.7.

The following shows a configuration for redo log groups:

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 redo log files has a great impact on the performance of the InnoDB storage engine.

The setting is too large

After a large setting, the checkpoint is reduced, and because the redo log is a sequential Imax O, the performance of the Imax O is greatly improved. However, if there is an unexpected problem with the database, such as an unexpected downtime, the log needs to be replayed and the committed transaction needs to be restored, which can lead to a long recovery time if the log is large. Even to the point where we can't accept it.

The setting is too small

When a log file is full, innodb will automatically switch to another log file and trigger a database checkpoint, which will cause a small batch refresh of dirty pages in the innodb cache, which will significantly reduce the performance of innodb.

[external link image transfer failed. The origin server may have hotlink protection mechanism. It is recommended to save the image and upload it directly (https://cache.yisu.com/upload/information/20220215/112/979584.png)]).

How to set it?

Refer to the Optimizing InnoDB Redo Logging' section of the official document

Set the redo log file to be very large, even as large as the buffer pool. When InnoDB writes the redo log file full, it triggers a checkpoint of the database and writes dirty data from the buffer pool to disk. Small redo log files can cause a lot of unnecessary disk writes. Although large redo log files resulted in lengthy recovery time in previous versions, recovery is now faster and large redo log files can be safely used.

Consider increasing the size of the log buffer. Large log buffers can run large transactions before they are committed without writing logs to disk. Therefore, if you have a transaction that updates, inserts, or deletes many rows, making the log buffer larger can save disk Imax O. Use the innodb_log_buffer_size configuration option to configure the log buffer size.

Sets the innodb_log_write_ahead_size parameter, which represents the block size before redo log is written. InnoDB writes ib_logfile files in an aligned manner of 512 bytes per block, but file systems typically use 4096 bytes as a block unit. If the log file block you are about to write is not in OS Cache, you need to read the corresponding 4096 bytes of block into memory, modify the 512 bytes, and then write the block back to disk. When the offset currently written to the file cannot be divisible by this value, the offset is filled with 0 and more data is written. In this way, when the written data is aligned with the disk block size, you can directly write the disk without the three steps of read-modify-write.

two。 What is binlog?

Binlog records all operations that make changes to the MySQL database, but does not include operations such as SELECT and SHOW, which do not modify the data itself. Then, if the operation itself does not cause a change to the database, the operation is also written to the binary log. For example:

Root@localhost [(none)] 08:30:14 > set binlog_format = 'STATEMENT';root@localhost [(none)] 08:30:26 > use test;Database changedroot@localhost [test] 08:30:33 > select * from account +-+-+ | acct_num | amount | +-+-+ | 14.98 | 1937.50 | 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: 0root@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 | 1003306 | e4382832-949d-11e8-97ba-080027793430:1-204| my3306_binlog.000052 | 194 | Gtid | 1003306 | 259 | SET @ @ SESSION.GTID_NEXT= 'e4382832-949d-11e8-97baashi 080027793430205' | my3306_binlog.000052 | 259 | | Query | 1003306 | 331 | BEGIN | | my3306_binlog.000052 | 331 | Table_map | 1003306 | 1003306 | table_id: 108 (test.account) | | my3306_binlog.000052 | 384 | Update_rows | 1003306 | | table_id: 108 flags: STMT_END_F | | my3306_binlog.000052 | 440 | Xid | 1003306 | COMMIT / * xid=14 * / | my3306_binlog.000052 | 471 | Gtid | 1003306 | 536 | SET @ @ SESSION. GTID_NEXT= 'e4382832-949d-11e8-97baRa080027793430 my3306_binlog.000052 | my3306_binlog.000052 | Query | 1003306 | BEGIN | | my3306_binlog.000052 | 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 a update operation, and the returned result shows that the Changed is 0, which means that the operation does not cause a change in the database. But through show binlog events in '...' You can see that it is indeed recorded in the binary log.

If you want to record SELECT and SHOW operations, you can only use the query log-- general_log [= {0 | 1}] (1 is enabled)

2.1 binlog file name

The binary log can be started by configuring the parameter-- log-bin [= name]. If not specified, the default binlog log file name is the host name, the suffix is the serial number of binlog, and the default road strength is the data directory (datadir). You can also specify an absolute path, such as:

# cat / etc/my.cnf | grep log-binlog-bin = / data/mysql/mysql3306/logs/my3306_binlog# cd / data/mysql/mysql3306/logs# ls-ltotal 60 my3306_binlog.000046-rw-r- 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.index2.2 parameters affecting binlog

Max_binlog_size: specifies the maximum value for a single binlog file. The default value is 1g and the maximum value is 1g. If this value is exceeded, a new binlog file with the suffix name of + 1 is generated and logged to the .index file.

Binlog_cache_size: when using a transaction table storage engine (such as the innodb storage engine), all uncommitted binlog logs are recorded in a cache, and then the cached binlog is written to the binlog file when the transaction commits. The size of the cache is determined by binlog_cache_size, and the default size is 32K.

Binlog_cache_size is based on session, that is, when a thread starts a transaction, MySQL automatically allocates a cache with the size of binlog_cache_size, so you need to be very careful not to set this value too large.

When the record of a transaction is larger than the set binlog_cache_size, MySQL will write the log in the cache to a temporary file, so the value cannot be set too small.

How to set it up?

Check the status of the binlog_cache_use,binlog_cache_disk_use through the show global status command to determine whether the current binlog_cache_size setting is appropriate.

Check the status of the binlog_cache_use,binlog_cache_disk_use through the show global status command to determine whether the current binlog_cache_size setting is appropriate. Binlog_cache_use: records the number of times binlog is written using cache binlog_cache_disk_use: records the number of times binlog is written using temporary files. Example: 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) use cache 33553 times The temporary file is used 0 times. Indicates that the cache size of 32KB is sufficient for the current MySQL database.

Max_binlog_cache_size: if the transaction requires more than many bytes of memory, the server generates more concurrent transactions for storage errors than "max_binlog_cache_size" bytes. The minimum value is 4096 bytes and the maximum possible value is 16EB (exabytes). The recommended maximum value is 4GB; this is because MySQL cannot currently use binary log locations greater than 4GB.

Expire_logs_days: indicates that the binlog file automatically deletes the N-day-old file. The default value is 0, which means that it will not be deleted automatically, with a maximum value of 99. To delete the binlog file manually, you can use the purge binary logs statement. For example:

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 MASTER 46 Parge BINARY LOGS BEFORE now ()-interval 3 days

Binlog_rows_query_log_events: not enabled by default. When binlog_rows_query_log_events is enabled, the original SQL statement can be recorded in binary log.

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 | 1003306 | e4382832-949d-11e8-97ba-080027793430:1-206| my3306_binlog.000056 | 194 | Gtid | 1003306 | 259 | SET @ @ SESSION.GTID_NEXT= 'e4382832-949d-11e8-97baashi 080027793430207' | my3306_binlog.000056 | 259 | | Query | 1003306 | 331 | BEGIN | | my3306_binlog.000056 | 331 | Table_map | 1003306 | table_id: 108 (test.t) | | my3306_binlog.000056 | 375 | Update_rows | | 1003306 | 1003306 | table_id: 108 flags: STMT_END_F | | my3306_binlog.000056 | 421 | Xid | 1003306 | 452 | COMMIT / * xid=16 * / | my3306_binlog.000056 | Gtid | 1003306 | 517 | SET @ @ SESSION.GTID_NEXT= 'e4382832-949d-11e8-97baLie080027793430 my3306_binlog.000056 | my3306_binlog.000056 | Query | 1003306 | BEGIN | | my3306_binlog.000056 | Table_map | 1003306 | 633 | table_id: 108 (test.t) | | my3306_binlog.000056 | 633 | Write_rows | 1003306 | 1003306 | 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-97baLib 080027793430 SET 209' | | my3306_binlog.000056 | Query | 1003306 | 841 | BEGIN | my3306_binlog.000056 | 841 | Rows_query | 1003306 | | | my3306_binlog.000056 | # 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 is when the binlog_rows_query_log_events option is enabled The original SQL statement of the record.

Sync_binlog:sync_binlog= [N] means that the buffer is synchronized to disk without writing N times. If N is set to 1, sync_binlog means that binary logs are written synchronously to disk. After MySQL5.7.7, the default is 1. It will have some impact on the IO system of the database, but you can get the largest high available rows.

Binlog_checksum: this parameter is used to write binlog for verification. There are two values [crc32 | none]. Default is crc32.

Binlog-do-db: indicates the database that needs to be written to the log. The default is empty, which means that all libraries are synchronized.

Binlog-ignore-db: ignore the database written to the log. The default is empty, which means that all libraries are synchronized.

Log-slave-update: indicates the binlog obtained and executed from the master end, which is written into your own binlog file. It is generally used in master= > slave= > slave architecture.

Binlog_format: the format of the record binlog. [statement,row,mixed], after MySQL5.7.7, the default is row.

Storage causes support for binlog format:

[external link image transfer failed. The origin server may have hotlink protection mechanism. It is recommended to save the image and upload it directly (https://cache.yisu.com/upload/information/20220215/112/979585.png)]).

2.3 View binlog

Use the mysqlbinlog program to view, for example:

[root@mysqldb1 10:58:18 / data/mysql/mysql3306/logs] # mysqlbinlog-v-- base64-output=decode-rows my3306_binlog.000052 | more than 50530 SET @ @ SESSION.PSEUDONG SLAVEMETDE1 / 50003 SET @ OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER / * / # at 4 end_log_pos 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 18822 8:01:00 server id 1003306 end_log_pos 194 CRC32 0xb1bda518 Previous-GTIDs# e4382832-949d-11e8-97ba-080027793430:1-20 at 1944180822 8:10:59 server id 1003306 end_log_pos 259 CRC32 0xeced9ada GTID last_committed=0 sequence_number=1 rbritte on the cards, server id 1003306 end_log_pos 259 rbrittems on the cards, such as the set @ @ SESSION.GTID_NEXT= 'e4382832-949d-11e8-97baanth080027793430 set @ @ SESSION.GTID_NEXT= 'e4382832-949d-11e8-97baanth080027793430 # at 259 minutes 180822 8:10:59 server id 1003306 end_log_pos 331 CRC32 0x6da7802a Query thread_id=2 exec_time=0 error_code=0SET timestamppers 1534896659Compact set @ @ session.pseudoreadythreaded cards 2packs set @ @ session.foreign_key_checks=1, @ @ session.sql_auto_is_null=0, @ @ session.unique_checks=1, @ @ session. SET @ @ session.auto_increment_increment=1, @ @ session.autoincrement incrementalization offsetbacks 1 racemaker raceme paddle!\ C utf8 * / / *! * /; SET @ @ session.session setclientconnectionsession.collationalization serverationconnection3title session. Collationalization timestamp namespace 45 canvass.session. Session. Collection timekeeper namespace zero lead lead racemaker set @ session.collationalization database accounting DEULTGULTGULTGULTGULTGUBULTGUBINGUBINGG # at 331 "180822 8:10:59 server id 1003306 end_log_pos 384 CRC32 0xf239dd79 Table_map: `test`.`roomt` mapped to number 10" 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`.`roomt` # WHERE### @ 1" 137 "# @ 2" 14.98 "# SET### @ 1" 138 "# @ 2" 14.9 "at 440 million 180822 8:10:59 server id 1003306 end_log_pos 471 CRC32 0x360f05d0 Xid = 14COMIT # at 471 "180822 8:31:35 server id 1003306 end_log_pos 536 CRC32 0x662c8f17 GTID last_committed=1 sequence_number=2 rbr_only=noSET @ @ SESSION.GTID_NEXT= 'e4382832-949d-11e8-97babaLib 080027793430 Wrap 206According to server id # at 536" 180822 8:31:35 server id 1003306 end_log_pos 615 CRC32 0xa728a60a Query thread_id=3 exec_time=0 error_code=0SET timespace 1534897895Univ # at 615 "180822 8:31:35 server id 1003306 end_log_pos 736 CRC32 0x7513aa73 Query thread_id=3 exec_time=0 error_code=0use `test` / *! * /; SET timestamp 1534897895; SET timestamp 1534897895; SET TIMESTAMP1534897895; SET TIMESTAMP1534897895According to at 736 180822 8:31:35 server id 1003306 CRC32 0x1cd7f41c Query thread_id=3 exec_time=0 error_code=0SET timespace 1534897895GUBE SET @ @ SESSION.GTID_NEXT= 'AUTOMATIC' / * added by mysqlbinlog * / *! * /; DELIMITER; # End of log filewise / 50003 SET completion TYPENETION TYPENAND the difference between redo log and binlog

First: redo log is generated at the InnoDB storage engine layer, while binlog is generated at the upper layer of the MySQL database, and binary logs are generated not only for the INNODB storage engine, but also for any storage engine changes to the database in the MySQL database.

Second, the content forms of the two kinds of log records are different. The binlog of MySQL is a logical log, and its record is the corresponding SQL statement. The redo logs at the innodb storage engine level are physical logs.

Third: the two kinds of logs are different from the point in time at which the records are written to disk, and the binary log is written only once after the transaction is committed. On the other hand, the redo log of the innodb storage engine is constantly written during the transaction, and the log is not written in the order in which the transaction is committed.

Binary logs are recorded only when the transaction commits, and for each transaction, only when the transaction commits, and for each transaction contains only one log for the corresponding transaction. As for the redo log of the innodb storage engine, because its record is a physical operation log, each transaction corresponds to multiple log entries, and the redo log writes of the transaction are concurrent, not written when the transaction is committed, and the order in which they are recorded in the file is not the order in which the transaction starts.

Fourth: binlog is not recycled, after being full or restarted, a new binlog file will be generated, and redo log is recycled.

Fifth: binlog can be used as recovery data, master-slave replication build, and redo log can be used as data recovery after abnormal downtime or media failure.

This is the end of the content about "what is the difference between redo log and binlog in MySQL". Thank you for reading. If you want to know more about the industry, you can follow the industry information channel. The editor will update different knowledge points for you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.