MySQL复制(二):二进制日志、二进制日志的结构和内容

通常只有即将执行完毕的语句才会写入到二进制日志中。但是一些特殊情况：语句附加的信息或直接代替语句被写入。

二进制日志记录的内容

作用：记录数据库中表的更变，用于复制和PITP(即时恢复)

基于语句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：事件类型，到5.6.12开始，已经有35中事件类型二进制日志并不是一个单独文件，由一组存储实际内容的二进制日志文件和一个跟踪二进制日志文件存储位置的二进制日志索引文件

每一个二进制日志文件以格式描述事件开始(format description event),以日志轮换事件（rotate event）结束格式描述事件包括：改文件的服务器版本号。服务器及二进制日志信息，还有一个标记，标记二进制日志文件是否正确关闭。如果正在写入二进制日志，则设置该标记，否则清除该标记（这样可以检测出在奔溃时间中损坏的二进制日志文件，并允许通过复制进行恢复）日志轮换包括：下一个二进制日志文件的名称及告知二进制日志继续写入到哪个文件每个二进制日志有多个二进制日志事件，各个事件之间相互独立，同时也构成了二进制日志的基本单元。SHOW BINLON EVENTS只会显示第一个二进制日志的内容。如果想看活动的二进制日志内容，可以SHOW 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)