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

Source: Internet
Author: User

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)

  

 

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.