[Architecture] MySQL Log File-binary log: binary log & binary log Index

First find the two files,

Rocky@localhost> show variables like 'datadir';+---------------+-------------------------+| Variable_name | Value                   |+---------------+-------------------------+| datadir       | /home/mysql/mysql/data/ |+---------------+-------------------------+1 row in set (0.00 sec)Rocky@localhost> system ls -l /home/mysql/mysql/data/total 31804-rw-rw---- 1 mysql dba 18874368 Apr 14 01:27 ibdata1-rw-rw---- 1 mysql dba  5242880 Apr 15 20:15 ib_logfile0-rw-rw---- 1 mysql dba  5242880 Apr 12 12:37 ib_logfile1-rw-rw---- 1 mysql dba        6 Apr 15 20:15 localhost.localdomain.piddrwxr-xr-x 2 mysql dba     4096 Apr 12 12:35 mysql-rw-rw---- 1 mysql dba    27828 Apr 12 12:35 mysql-bin.000001-rw-rw---- 1 mysql dba   997144 Apr 12 12:35 mysql-bin.000002-rw-rw---- 1 mysql dba      150 Apr 12 13:17 mysql-bin.000003-rw-rw---- 1 mysql dba    27025 Apr 12 12:56 mysql-bin.000004-rw-rw---- 1 mysql dba   997144 Apr 12 12:56 mysql-bin.000005-rw-rw---- 1 mysql dba    27025 Apr 12 12:59 mysql-bin.000006-rw-rw---- 1 mysql dba   997144 Apr 12 12:59 mysql-bin.000007-rw-rw---- 1 mysql dba      126 Apr 12 13:18 mysql-bin.000008-rw-rw---- 1 mysql dba      126 Apr 13 00:14 mysql-bin.000009-rw-rw---- 1 mysql dba     1346 Apr 13 20:20 mysql-bin.000010-rw-rw---- 1 mysql dba      150 Apr 13 20:23 mysql-bin.000011-rw-rw---- 1 mysql dba      107 Apr 13 20:23 mysql-bin.000012-rw-rw---- 1 mysql dba      107 Apr 13 21:56 mysql-bin.000013-rw-rw---- 1 mysql dba      126 Apr 13 22:50 mysql-bin.000014-rw-rw---- 1 mysql dba      229 Apr 14 01:27 mysql-bin.000015-rw-rw---- 1 mysql dba      107 Apr 15 20:15 mysql-bin.000016-rw-rw---- 1 mysql dba      304 Apr 15 20:15 mysql-bin.index

(I) Definition

All modifications recordedDataQuery, including: query statement, execution time, resources consumed, and related transaction information

(Ii) Role

① Recovery
② Copy

(Iii) Related Parameters

There are many parameters. Here we will focus on one:

Binlog_format

Description: Log format
Nature: Dynamic parameter, session level, or global level
Set @ session. binlog_format = 'row' or
Set global binlog_format = 'row'
Value:
● Statement

Copy the SQL apply statement based on this format, similar to the Oracle logic standby

● Row

Replication Based on this format, redo apply similar to Oracle physical standby

Enable rowAdvantages:
① If we set the transaction isolation level of InnoDB to read commited and add row, we can get better results.Concurrency
② Better recovery and ReplicationReliability
Disadvantages:
① Rapid expansion of binary logs and high disk space overhead
② High network bandwidth requirements during replication


● Mixed

This is the default format, relatively tricky, usually running with statement, sometimes running with row

Rocky@localhost> show variables like 'binlog_format';+---------------+-------+| Variable_name | Value |+---------------+-------+| binlog_format | MIXED |+---------------+-------+1 row in set (0.00 sec)