Was sind die Unterschiede zwischen Redo-Log und Binlog in MySQL?

Vorwort

Es gibt sechs Arten von Protokolldateien in MySQL, nämlich: Redo-Protokoll (Redo-Protokoll), Rollback-Protokoll (Rückgängig-Protokoll), Binärprotokoll (Binlog), Fehlerprotokoll (Errorlog) und Protokoll für langsame Abfragen (langsames Abfrageprotokoll), allgemeines Abfrageprotokoll (allgemeines Protokoll), Relay-Protokoll (Relay-Protokoll).

1. Was ist Redo-Log?

Redo-Log, auch Redo-Log-Datei genannt, wird zum Aufzeichnen von Änderungen bei Transaktionsvorgängen verwendet. Es zeichnet den Wert nach der Datenänderung auf. Es wird unabhängig davon aufgezeichnet, ob die Transaktion übermittelt wurde. Wenn eine Instanz oder ein Medium ausfällt (Medienfehler), kann die Redo-Log-Datei nützlich sein. Wenn die Datenbank ausgeschaltet ist, verwendet die InnoDB-Speicher-Engine das Redo-Log, um den Zeitpunkt vor dem Ausschalten wiederherzustellen, um die Integrität sicherzustellen die Daten.

1.1 Name der Redo-Log-Datei

Jede InnoDB-Speicher-Engine verfügt über mindestens 1 Redo-Log-Dateigruppe (Gruppe), und jede Dateigruppe verfügt über mindestens 2 Redo-Log-Dateien, z. B. die Standarddateien ib_logfile0 und ib_logfile1.

1.2 Beeinflusst Redo-Log-Parameter

• innodb_log_file_size: Gibt die Größe jedes Redo-Logs an, der Standardwert ist 48 MB 2

• innodb_log_group_home_dir： Geben Sie den Pfad an, in dem sich die Protokolldateigruppe befindet. Der Standardwert ist ./, der sich auf das Datenverzeichnis datadir von MySQL bezieht.

• innodb_mirrored_log_groups: Geben Sie die Anzahl der Protokollspiegeldateigruppen an Der Standardwert ist 1, diese Funktion ist nicht implementiert und wurde in Version 5.6 aufgegeben, in Version 5.7 entfernt.

• Das Folgende zeigt eine Konfiguration über die Redo-Log-Gruppe:

  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 Wie stelle ich die Redo-Log-Größe ein?

Die Größeneinstellung der Redo-Log-Datei hat einen großen Einfluss auf die Leistung der InnoDB-Speicher-Engine.

Eine zu große Einstellung

• reduziert Prüfpunkte durch Erhöhen der Einstellungsgröße und verbessert gleichzeitig die E/A-Leistung aufgrund der sequentiellen E/A des Redo-Logs erheblich. Wenn jedoch unerwartet ein Problem in der Datenbank auftritt, beispielsweise eine unerwartete Ausfallzeit, muss das Protokoll erneut abgespielt und festgeschriebene Transaktionen wiederhergestellt werden. Wenn das Protokoll groß ist, dauert die Wiederherstellung sehr lange. Auch wenn wir es nicht akzeptieren können.

Zu klein einstellen

• Wenn eine Protokolldatei voll ist, wechselt innodb automatisch zu einer anderen Protokolldatei und löst einen Prüfpunkt in der Datenbank aus, was dazu führt, dass innodb fehlerhafte Seiten zwischenspeichert und die Leistung erheblich verringert von innodb.

[Die Übertragung des externen Linkbildes ist möglicherweise über einen Anti-Leeching-Mechanismus verfügt. Es wird empfohlen, das Bild zu speichern und direkt hochzuladen (img-6gU4thAZ-1584783729918)(https://cache.yisu.com/). upload/information/20220215/112 /979584.png)]

Wie stelle ich es ein?

• Weitere Informationen finden Sie im offiziellen Dokument im Kapitel „Optimieren der InnoDB-Redo-Protokollierung“.

Stellen Sie die Redo-Protokolldatei so ein, dass sie groß ist, sogar so groß wie der Pufferpool. Wenn die Redo-Log-Dateien von InnoDB voll sind, löst es einen Prüfpunkt der Datenbank aus und schreibt die fehlerhaften Daten im Pufferpool auf die Festplatte. Kleine Redo-Log-Dateien können viele unnötige Schreibvorgänge auf die Festplatte verursachen. Während in früheren Versionen große Redo-Log-Dateien zu langen Wiederherstellungszeiten führten, erfolgt die Wiederherstellung jetzt schneller und große Redo-Log-Dateien können bedenkenlos verwendet werden.

Erwägen Sie eine Erhöhung der Protokollpuffergröße. Ein großer Protokollpuffer ermöglicht die Ausführung großer Transaktionen, bevor die Transaktion festgeschrieben wird, ohne dass das Protokoll auf die Festplatte geschrieben werden muss. Wenn Sie also Transaktionen haben, die viele Zeilen aktualisieren, einfügen oder löschen, kann eine Vergrößerung des Protokollpuffers Festplatten-E/A einsparen. Verwenden Sie die Konfigurationsoption innodb_log_buffer_size, um die Protokollpuffergröße zu konfigurieren.

Die Einstellung des Parameters innodb_log_write_ahead_size gibt die Blockgröße vor dem Redo-Log-Schreiben an. InnoDB schreibt die Datei ib_logfile in einem Blockformat von 512 Bytes, das Dateisystem verwendet jedoch im Allgemeinen eine Blockeinheit von 4096 Bytes. Befindet sich der zu schreibende Protokolldateiblock nicht im Betriebssystem-Cache, muss der entsprechende 4096-Byte-Block in den Speicher eingelesen, 512 Byte davon geändert und der Block dann wieder auf die Festplatte geschrieben werden. Wenn der aktuell in die Datei geschriebene Offset kein ganzzahliges Vielfaches dieses Werts ist, müssen Sie 0 hinzufügen und weitere Daten schreiben. Wenn die geschriebenen Daten auf die Blockgröße der Festplatte abgestimmt sind, kann auf diese Weise direkt auf die Festplatte geschrieben werden, ohne dass die drei Schritte Lesen, Ändern und Schreiben erforderlich sind.

2. Was ist Binlog? Binlog zeichnet alle Vorgänge auf, die die MySQL-Datenbank ändern, umfasst jedoch keine Vorgänge wie SELECT und SHOW, da solche Vorgänge die Daten selbst nicht ändern. Wenn der Vorgang selbst keine Änderung an der Datenbank verursacht, wird der Vorgang auch im Binärprotokoll protokolliert. Zum Beispiel:

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)

Wie Sie dem obigen Beispiel entnehmen können, führt die MySQL-Datenbank zunächst einen Aktualisierungsvorgang durch und das zurückgegebene Ergebnis zeigt, dass Changed 0 ist, was bedeutet, dass der Vorgang keine Änderungen an der Datenbank verursacht hat. Aber indem wir Binlog-Ereignisse in „…“ anzeigen, können wir sehen, dass sie tatsächlich im Binärprotokoll aufgezeichnet werden.

Wenn Sie SELECT- und SHOW-Vorgänge aufzeichnen möchten, können Sie nur das Abfrageprotokoll --general_log[={0|1}](1 ist aktiviert) verwenden

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是循环使用。

Fünftens: Binlog kann zur Datenwiederherstellung verwendet werden, die Master-Slave-Replikation wird eingerichtet und das Redo-Log kann zur Datenwiederherstellung nach ungewöhnlichen Ausfallzeiten oder Medienfehlern verwendet werden.

Das obige ist der detaillierte Inhalt vonWas sind die Unterschiede zwischen Redo-Log und Binlog in MySQL?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!