“This is the fourth day of my participation in the Gwen Challenge.
Let me start by saying where the log is generated. Binlog logs are generated in the SQL layer, and redo and undo logs are generated in the engine layer, so binlog logs are logged before redo undo.
1.binlog
Binlog Log application scenario
1>. Synchronization between primary and secondary databases.
2>. Data recovery.
Binlog Indicates the contents of the record
The binlog method of recording operations is a logical statement.
Binlog records all database TABLE structure changes (e.g. CREATE, ALTER TABLE…). And table data changes (INSERT, UPDATE, DELETE…) .
Binlog contains two types of log files: Binary log index files (.index files) that record all binary files, and binary log files (.00000* files) that record all DDL and DML statements (except data query statements) of the database.
Mysql has three types of binlog: STATEMENT, ROW, and MIXED
1.1 Row Level Row mode
Logs record the modified form of each row of data, and then modify the same data on the slave side.
Advantage: In row level mode, bin-log does not record context-specific information about the SQL statement executed, only which one is modified. So the rowLevel log content clearly records the details of each row of data modification. Disadvantages: Row levels, when logged, all executed statements are logged as modifications per line, resulting in a large amount of log content.
1.2 Statement Level (Default)
Every SQL that modifies data is logged in the master’s bin-log. When the slave is replicated, the SQL process parses the same SQL as the original SQL executed by the master and executes it again
Advantages: The advantages of statement level are that the disadvantages of row level are solved. It does not need to record the changes of every row, reducing the amount of bin-log, saving I/O, and improving the performance, because only the details of the statement executed on the Master and the information about the execution context are required. Disadvantages: Due to the fact that only statements are recorded, there are many cases of MySQL replication problems at the statement level, mainly when certain functions or functions are used to modify data.
1.3 Mixed auto mode
In Mixed mode, MySQL will treat the log format of the record according to the specific SQL Statement executed, that is, to choose between Statement and Row. If the SQL statement is indeed an UPDATE or DELETE statement that modifies data, all row changes will still be recorded.
Redo log is different from binlog
Most importantly, redo logs ensure persistence in transaction features.
As we know, there are binary logs in MySQL that record write operations and are used for data recovery, but there are fundamental differences between the two: (1) different functions: Redo log is used for crash recovery. Binlog is used for point-in-time recovery to ensure that the server can recover data based on point in time. Binlog is also used for primary/secondary replication.
(2) Different layers: Redo log is implemented by InnoDB storage engine, while binlog is the SQL layer of MySQL
Redo logs are physical logs. They are disk based pages. The contents of a binlog are binary and, depending on the binlog_format parameter, may be based on SQL statements, the data itself, or a combination of the two.
(4) Different writing timing: Binlog is written when the transaction is committed; The timing of redo log writing is relatively variable: as mentioned earlier, fsync is called to flush redo log when a transaction commits; This is the default policy and can be changed by changing the innodb_flush_LOG_at_trx_COMMIT parameter, but the persistence of the transaction is not guaranteed. In addition to transaction commit time, there are other flush times: for example, the master thread flusher the redo log once per second. This makes the commit time much faster than the commit time.
The undo log records the reverse of the redo log logic, facilitating rollback.