preface

Most of mysql’s features are built around log files, but there are three most important ones

  • Redo log
  • The undo log
  • Binlog log

Pay attention to the public number, communicate together; Wechat search: sneak forward

1 the redo log

Innodb has a buffer pool to improve disk I/O performance. Data page reads are cached in the buffer pool, and transaction commits are updated in real time. Instead of real-time synchronization to disk (InnoDB is synchronized by 16KB per page, a transaction can involve multiple data pages, real-time synchronization will cause waste, random I/O). If a transaction exists in memory temporarily, there is a consistency problem. In order to avoid system crashes and ensure transaction persistence, we only need to persist the redo logs to disk.

Mini-Transaction (MTR)

  • As SQL statements are executed, multiple pages may be modified, clustered and secondary indexed pages may be updated, and redo processes may be split into indivisible groups (mini-Transactions). How does MTR understand it? For example, an INSERT statement might split a page, create a new leaf node, copy the data from the original page to the new data page, insert the new record, and add a directory entry pointing to the new page. This corresponds to multiple redo logs, which need to be done in the atomic MTR

Redo log flush time

The redo logs generated by MTR are copied to a log buffer (similar to a buffer pool). The timing of synchronization to disk is as follows:

  • When the total size of the log buffer reaches 50%, the logs are flushed to disk
  • When a transaction commits, it also needs to be synchronized to disk
  • Background threads are synchronized every second
  • Disabling the mysql service
  • When you checkpoit
    • Redo space is limited. If the redo log data page is synchronized to disk, the redo log can also be recycled. This recycling process is called checkpoint

2 the undo log

Transactions need to be atomic, which means that all operations in a transaction either complete or do nothing. What if the transaction is halfway through and something goes wrong? – Rollback. But how to rollback, by undo log. Undo logging is the inverse of SQL execution

  • Undo logging serves two purposes: rollback and multi-line versioning (MVCC)
  • A row in the data page is formatted as follows, where roll_point points to an undo log

  • Undo logs are usually deleted when a transaction commits, but are temporarily retained if the undo log is an MVCC service
  • A transaction can generate multiple undo logs. Mysql has a special undo page to save undo logs. Innodb allocates a separate undo page linked list for each transaction (up to 4 linked lists)

Transaction ID and TRx_ID

  • Mysql maintains a global variable in memory. Whenever a transaction is assigned trx_ID, it is allocated first and then increments by 1
  • For read-only transactions, trX_ID is assigned only when the temporary table it first creates is added, deleted, or modified
  • For read/write transactions, the transaction is assigned trx_ID only when it performs an add, delete, or alter operation (including the temporary table)

roll_pointer

  • Undo logs corresponding to update and DELETE statements contain trx_ID and roll_point. When multiple SQL statements are executed concurrently, undo logs are connected to the undo log version chain using roll_point according to the sequence of TRx_ID. MVCC is based on the undo log version chain

3 binlog log

  • The binlog file creates a new file with the startup of the service
  • Flush logs can be manually flushed to generate a new binlog file
  • You can view the binlog status by running the show master status command
  • Reset master Clears the binlog file
  • The mysqlBinlog tool allows you to view the contents of binlog logs
  • When DML is executed, mysql automatically records binlog

Binlog format

Binlogs are available in three formats: Statement, Row, and Mixed.

  • Statement
    • Each SQL entry for adding, deleting, or modifying data is recorded in a binlog
    • Advantages: You do not need to record the changes of each row, reducing the amount of binlog logs, saving I/O, and improving performance
    • Disadvantages: Since only executing statements are recorded, some information about each statement at the time of execution must be recorded in order for the statements to run correctly on the slave. In addition, mysql replication, like some specific functions, slave and master can be consistent and there are a lot of issues associated with that
  • Row
    • MySQL 5.1.5 just started to support row level replication. It does not record information about the context of SQL statements, only which records have been modified
    • Advantage: Binlog does not record context-specific information about the SQL statement being executed, only record which record has been modified. So the rowLevel log content clearly records the details of each row of data modification
    • Disadvantages: All executed statements, when logged, are recorded as changes per line, which can generate a large amount of log content
  • Mixed
    • In Mixed mode, general statement modification uses statment format to store binlogs. For example, for some functions, statement format cannot complete master/slave copy operations, so row format is used to store binlogs

Binlog Related operations

  • View the contents of a binlog file
[root@root log]# mysqlbinlog 'log.000001' /*! 50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; / *! 40019 SET @@session.max_insert_delayed_threads=0*/; / *! 50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*! * /.# at 4
#181214 14:44:48 server id 1 end_log_pos 120 CRC32 0x79b6cd10 Start: Binlog V 4, Server V 5.6.40-log created 181214 14:44:48 at startupROLLBACK/*! * /. BINLOG ' YDIUXA8BAAAAdAAAAHgAAAAAAAQANS42LjQwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAABgMhRcEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAARDN tnk= '/*! * /.# at 120
#181214 14:45:20 server id 1 end_log_pos 199 CRC32 0x10dec193 Query thread_id=1 exec_time=0 error_code=0SET TIMESTAMP=1544827520/*! * /. SET @@session.pseudo_thread_id=1/*! * /. SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*! * /. SET @@session.sql_mode=1075838976/*! * /. 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=8/*! * /. SET @@session.lc_time_names=0/*! * /. SET @@session.collation_database=DEFAULT/*! * /. BEGIN /*! * /.# at 199
#181214 14:45:20 server id 1 end_log_pos 303 CRC32 0x9ec5f952 Query thread_id=1 exec_time=0 error_code=0use `test`/*! * /. SET TIMESTAMP=1544827520/*! * /. insert into t1 values('8','7') /*! * /.# at 303
#181214 14:45:20 server id 1 end_log_pos 334 CRC32 0xfd659542 Xid = 10COMMIT/*! * /.# at 334
#181214 14:45:35 server id 1 end_log_pos 413 CRC32 0x43929486 Query thread_id=1 exec_time=0 error_code=0SET TIMESTAMP=1544827535/*! * /. BEGIN /*! * /.# at 413
#181214 14:45:35 server id 1 end_log_pos 517 CRC32 0x4f1284f2 Query thread_id=1 exec_time=0 error_code=0SET TIMESTAMP=1544827535/*! * /. insert into t1 values('9','7') /*! * /.# at 517
#181214 14:45:35 server id 1 end_log_pos 548 CRC32 0x67231f2b Xid = 20COMMIT/*! * /.# at 548
#181214 14:45:39 server id 1 end_log_pos 627 CRC32 0x82b39b3e Query thread_id=1 exec_time=0 error_code=0SET TIMESTAMP=1544827539/*! * /. BEGIN /*! * /.# at 627
#181214 15:00:48 server id 1 end_log_pos 1646 CRC32 0x7e89c8dc Stop
DELIMITER ;
# End of log fileROLLBACK /* added by mysqlbinlog */; / *! 50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; / *! 50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;Copy the code
  • View binlog records
mysql> show binlog events in 'log.000001';+------------+------+-------------+-----------+-------------+---------------------------------------------+ | Log_name |  Pos | Event_type | Server_id | End_log_pos | Info | +------------+------+-------------+-----------+-------------+---------------------------------------------+ | log.000001 | | 4 Format_desc | 1 | 120 | Server ver: 5.6.40 - log, Binlog ver: 4 | | log.000001 | 120 | Query | 1 | 199 | BEGIN | | log.000001 | 199 | Query | 1 | 303 | use `test`; insert into t1 values('8','7') | | log.000001 | 303 | Xid | 1 | 334 | COMMIT /* xid=10 */ | | log.000001 | 334 | Query |  1 | 413 | BEGIN | | log.000001 | 413 | Query | 1 | 517 | use `test`; insert into t1 values('9','7') | | log.000001 | 517 | Xid | 1 | 548 | COMMIT /* xid=20 */ | | log.000001 | 548 | Query |  1 | 627 | BEGIN | | log.000001 | 627 | Query | 1 | 732 | use `test`; insert into t1 values('10','7') | | log.000001 | 732 | Xid | 1 | 763 | COMMIT /* xid=30 */ | | log.000001 | 763 | Query | 1 | 842 | BEGIN | | log.000001 | 842 | Query | 1 | 947 | use `test`; insert into t1 values('11','7') | | log.000001 | 947 | Xid | 1 | 978 | COMMIT /* xid=40 */ +------------+------+-------------+-----------+-------------+---------------------------------------------+ 23 rows in Set (0.00 SEC)Copy the code

Redo log and binlog

  • Redo logs are unique to InnoDB; Binlog is implemented in the Server layer of MySQL and is available to all engines. A redo log is a physical log of what was changed on a data page
  • The binlog is the logical log that records the original logic of the statement, such as “add 1 to the c field on the line ID=2”.
  • Redo logs are written in a redo log cycle. Binlog can be appended. “Appending” means that the binlog file will be switched to the next one after being written to a certain size without overwriting the previous log

The redo log records transactions committed in two phases

  • If redo is not two-phase commit; If redo data is written first and binlog data is written later, data in the secondary database that depends on binlog synchronization will be lost. Binlog writes first and redo log writes later, resulting in uncommitted dirty changes from the library. Data inconsistency between master and slave libraries

Redo log, bin log, and crash recovery

A transaction in redolog is marked with prepare if it has undergone the prepare phase in the two-phase commit phase, or commit if it has undergone the COMMIT phase (at this point, both Redolog and binlog are down). Crash recovery logic is as follows:

  • 1. Copy the data page from the redo log disk to the data page. 2.
  • If the redo log transaction has a prepare flag but no COMMIT flag, the current transaction crashed during the commit phase. At this point, take the current transaction ID in Redolog (the identifier of the transaction falling disk in Redolog and binlog) to check whether the ID exists in binlog
    • If the current transaction ID is present in binlog, commit the transaction (copy the data page from redolog disk to the disk data page)
    • If there is no current transaction ID in binlog, roll back the transaction (use undolog to delete the corresponding transaction in redolog)

Corrections are welcome

Refer to the article

  • Mysql Binlog series (1) —- Binlog introduction
  • MySQL binlog description