Description:
In MySQL binary log , we get the Master Pos at offset 13 as below.
.
#150519 19:24:24 server id 1 end_log_pos 4292699491 CRC32 0x91e2459d
# Position Timestamp Type Master ID Size Master Pos Flags
# ffdd64e1 58 f0 5b 55 13 01 00 00 00 82 00 00 00 63 65 dd ff 00 00
.
Based on this Master Pos , we position our Binary LogReader and read
different events. In case, if the Master Pos is written incorrectly , then we
end up reading events from the wrong offset and fail badly.
.
We see that the Master Pos is stored in 4 bytes , which is capable to store
offsets till 4 GB of offset values (4294967296). In case if we have the
binary log size exceeds 4 GB , we start getting the wrong offset values in
the Master Pos. We can see this in the example below.
.
.
'/*!*/;
# at 4292699361
#150519 19:24:24 server id 1 end_log_pos 4292699491 CRC32 0x91e2459d
# Position Timestamp Type Master ID Size Master Pos Flags
# ffdd64e1 58 f0 5b 55 13 01 00 00 00 82 00 00 00 63 65 dd ff 00 00
# ffdd64f4 48 00 00 00 00 00 01 00 02 42 54 00 08 53 43 45
|H........BT..SCE|
# ffdd6504 47 4c 5f 37 30 00 23 f6 05 04 f6 f6 f6 f6 f6 f6
|GL.70...........|
# ffdd6514 f6 12 12 12 12 12 fc fc fc fc fc fc fc fc fc fc
|................|
# ffdd6524 fc fc fc fc fc fc fc fc fc fc 2b 26 00 08 04 1e
|................|
# ffdd6534 05 1e 05 1e 0a 0f 05 26 00 24 05 0a 03 00 00 06
|................|
# ffdd6544 06 00 02 02 02 02 04 04 04 04 04 04 04 04 04 04
|................|
# ffdd6554 04 04 04 04 04 04 fe ff ff ff 07 9d 45 e2 91 |............E..|
# Table_map: `BT`.`SCEGL_70` mapped to number 72
# at 4292699491
#150519 19:24:24 server id 1 end_log_pos 310750 CRC32 0xe05d2b35
# Position Timestamp Type Master ID Size Master Pos Flags
# ffdd6563 58 f0 5b 55 1e 01 00 00 00 7b 58 27 00 de bd 04 00 00 00
# ffdd6576 48 00 00 00 00 00 01 00 02 00 23 ff ff ff ff ff
|H...............|
# ffdd6586 00 00 00 00 f8 80 00 00 00 00 00 00 00 00 00 00
|................|
# ffdd6596 00 00 00 00 b2 ab dc e1 10 61 36 8d 8b 40 71 6b
|.........a6...qk|
# ffdd65a6 de 6c 7f ff ff ff ff e4 49 2e e1 74 37 cc ff 47
|.l......I..t7..G|
# ffdd65b6 76 80 00 00 00 00 00 00 00 00 03 dd fb 00 b5 7d
|v...............|
# ffdd65c6 7f ff ff ff e0 d7 94 2f 29 d5 9b ac 3a f7 7f ff
|................|
# ffdd65d6 ff e1 c9 fe b1 06 7f ff ff ff ff ff ff ff ff ff
|................|
# ffdd65e6 ff ff ff ff ff fe 3c 80 00 00 00 00 00 00 00 00
|................|
# ffdd65f6 00 00 00 11 3f 00 52 f2 7f ff fa 3f ff 54 9a 9c
|......R......T..|
# ffdd6606 78 00 00 9b bd f4 00 00 b2 47 64 e8 5b 0a 60 40
|x........Gd.....|
# ffdd6616 92 a0 36 81 b9 0d 79 38 9c 9f 2a 00 00 d0 07 78
|..6...y8.......x|
# ffdd6626 72 5e 2e 4b 3a 4a 73 5b 59 62 6b 42 56 4c 61 5f
|r..K.Js.YbkBVLa.|
# ffdd6636 31 70 29 5e 5c 76 36 75 63 78 72 5e 2e 4b 3a 4a
|1p...v6ucxr..K.J|
# ffdd6646 73 5b 59 62 6b 42 56 4c 61 5f 31 70 29 5e 5c 76
|s.YbkBVLa.1p...v|
# ffdd6656 36 75 63 78 72 5e 2e 4b 3a 4a 73 5b 59 62 6b 42
|6ucxr..K.Js.YbkB|
# ffdd6666 56 4c 61 5f 31 70 29 5e 5c 76 36 75 63 78 72 5e
|VLa.1p...v6ucxr.|
# ffdd6676 2e 4b 3a 4a 73 5b 59 62 6b 42 56 4c 61 5f 31 70
|.K.Js.YbkBVLa.1p|
# ffdd6686 29 5e 5c 76 36 75 63 78 72 5e 2e 4b 3a 4a 73 5b
|...v6ucxr..K.Js.|
# ffdd6696 59 62 6b 42 56 4c 61 5f 31 70 29 5e 5c 76 36 75
|YbkBVLa.1p...v6u|
.
Above given are two events , to show how Master Pos gets wrong. Master Pos is
63 65 dd ff and when the size of the event is added to this , it exceeds 4
bytes limit (4 GB) and the next Master Pos gets wrong , which is de bd 04 00
as above.
.
As the MySQL binary log file contains only the committed transactions and
transactions do not spill over two different binary logs. So, if there is a
large transaction , with size more than 4 GB , it is capable to generate the
binlog file size more than 4 GB.
This is the scenario one of the customer is hitting.
How to repeat:
As the MySQL binary log file contains only the committed transactions and
transactions do not spill over two different binary logs. So, if there is a
large transaction , with size more than 4 GB , it is capable to generate the
binlog file size more than 4 GB.
