MySQL replication (2): binary log, binary log structure and content, mysql binary

MySQL replication (2): binary log, binary log structure and content, mysql binary

Generally, only statements that are about to be executed are written into binary logs. However, in some special cases, the additional information of the statement or the statement is written directly in place of the statement.

Binary Log Record Content

Role: record changes to tables in the database for replication and PITP (Instant recovery)

Observation Based on the copy action in the statement "SBR:

[root@localhost][(none)]> use boss;Database changed[root@localhost][boss]> create table tb1(text text);Query OK, 0 rows affected (0.32 sec)[root@localhost][boss]> insert into tb1 values("Yeah!Repliacation");Query OK, 1 row affected (0.06 sec)[root@localhost][boss]> select * from tb1;+-------------------+| text              |+-------------------+| Yeah!Repliacation |+-------------------+1 row in set (0.00 sec)[root@localhost][boss]> flush logs;Query OK, 0 rows affected (0.24 sec)[root@localhost][boss]> show binlog events;+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                              |+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+| mysql_bin.000014 |   4 | Format_desc    |     37306 |         123 | Server ver: 5.7.17-log, Binlog ver: 4                             || mysql_bin.000014 | 123 | Previous_gtids |     37306 |         154 |                                                                   || mysql_bin.000014 | 154 | Gtid           |     37306 |         219 | SET @@SESSION.GTID_NEXT= 'a0c06ec7-fef0-11e6-9f85-525400a7d662:1' || mysql_bin.000014 | 219 | Query          |     37306 |         320 | use `boss`; create table tb1(text text)                           || mysql_bin.000014 | 320 | Gtid           |     37306 |         385 | SET @@SESSION.GTID_NEXT= 'a0c06ec7-fef0-11e6-9f85-525400a7d662:2' || mysql_bin.000014 | 385 | Query          |     37306 |         457 | BEGIN                                                             || mysql_bin.000014 | 457 | Rows_query     |     37306 |         524 | # insert into tb1 values("Yeah!Repliacation")                     || mysql_bin.000014 | 524 | Table_map      |     37306 |         571 | table_id: 234 (boss.tb1)                                          || mysql_bin.000014 | 571 | Write_rows     |     37306 |         626 | table_id: 234 flags: STMT_END_F                                   || mysql_bin.000014 | 626 | Xid            |     37306 |         657 | COMMIT /* xid=42 */                                               || mysql_bin.000014 | 657 | Rotate         |     37306 |         704 | mysql_bin.000015;pos=4                                            |+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+11 rows in set (0.00 sec)

Event_type: event type. It has 35 event types starting from 5.6.12.

Binary logs are not a separate file. A set of binary log files that store the actual content and a binary log index file that tracks the location where binary log files are stored

 

Each binary log file starts with a format description event and ends with a log rotation event.
The format description event includes the server version number of the file to be modified. Server and binary log information, and a flag indicating whether the binary log file is properly disabled. If a binary log is being written, set the flag. Otherwise, clear the flag.
Damaged binary log files during the crash time and can be restored through replication)
Log rotation includes the name of the next binary log file and the file to which the binary log is written.

Each binary log has multiple binary log events, which are independent of each other and constitute the basic unit of binary logs.

Show binlon events only displays the content of the first binary log. If you want to view the binary log Content of the activity, you can view the binlog envents in 'master-bin.000002 '\ G.

[root@localhost][boss]> show binlog events in 'mysql_bin.000018';+------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------+| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                                |+------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------+| mysql_bin.000018 |   4 | Format_desc    |     37306 |         123 | Server ver: 5.7.17-log, Binlog ver: 4                               || mysql_bin.000018 | 123 | Previous_gtids |     37306 |         194 | a0c06ec7-fef0-11e6-9f85-525400a7d662:1-176                          || mysql_bin.000018 | 194 | Gtid           |     37306 |         259 | SET @@SESSION.GTID_NEXT= 'a0c06ec7-fef0-11e6-9f85-525400a7d662:177' || mysql_bin.000018 | 259 | Query          |     37306 |         331 | BEGIN                                                               || mysql_bin.000018 | 331 | Rows_query     |     37306 |         398 | # insert into tb1 values("Yeah!Repliacation")                       || mysql_bin.000018 | 398 | Table_map      |     37306 |         445 | table_id: 234 (boss.tb1)                                            || mysql_bin.000018 | 445 | Write_rows     |     37306 |         500 | table_id: 234 flags: STMT_END_F                                     || mysql_bin.000018 | 500 | Xid            |     37306 |         531 | COMMIT /* xid=265 */                                                |+------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------+8 rows in set (0.00 sec)[root@localhost][boss]> show master status;+------------------+----------+--------------+------------------+--------------------------------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                          |+------------------+----------+--------------+------------------+--------------------------------------------+| mysql_bin.000018 |      531 |              |                  | a0c06ec7-fef0-11e6-9f85-525400a7d662:1-177 |+------------------+----------+--------------+------------------+--------------------------------------------+1 row in set (0.00 sec)