Fun MySQL fact of the day: the binary log is binary
Yesterday, we started to form a (somewhat broken) mental model of MySQL's binary log. Today, we'll start taking a look at the on-disk representation before diving right in and using some of MySQL's tools to do fun (and useful!) things.
As a start, let's connect to a development instance of MySQL and produce a binary log we can look at:
# First, explicitly tell MySQL to log the DML statements. As noted yesterday,
# this may or not be the default in your version. But either way, this
# demonstrates that MySQL is capable of changing formats dynamically, which can
# come in handy.
mysql> SET SESSION binlog_format='STATEMENT';
# So that we're looking at binary log file that is as small as possible, we can
# tell MySQL to flush and rotate the binary log, making all new changes get
# written into a new log file.
mysql> FLUSH BINARY LOGS;
# Next, we want to find out which binary log file is currently being written by
# MySQL. There are numerous ways, but this is the easiest.
mysql> SHOW MASTER STATUS;
+------------------+------------+-----------------------+
| File | Position | <not interesting ...> |
|------------------+------------+-----------------------|
| mysql-bin.000006 | 154 | <not interesting ...> |
+------------------+------------+-----------------------+
# Now, let's perform an insert into a table we know and love.
mysql> INSERT INTO test.my_table VALUES(0, 1, 7, 3, 9, now());
# And then let's flush the logs again so we have a file that's as small as
# possible, ideally containing only the previous INSERT.
mysql> FLUSH BINARY LOGS;
While this isn't something you want to do very often on a production server, over the next few days, you'll will want to get familiar with this ceremony of coercing MySQL to produce clean binary logs. When all is said and done, we can now got look at the MySQL binlog, mysql-bin.000006 (likely in /var/lib/mysql), and poke around a bit. Go cat this file. Go strings this file. Go hexdump this file. Any way you look at it, you'll notice it's mostly useless in the current format. And though MySQL does provide the necessary tooling to make the binary log useful to us, we're not going to use those until tomorrow.
In the meantime, your best bet is going to be hexdump and the MySQL binary log event structure documentation (https://dev.mysql.com/doc/internals/en/binary-log.html). Can you find the query?
$ hexdump -C mysql-bin.000006
00000000 fe 62 69 6e ec 9f e6 5c 0f 39 00 00 00 77 00 00 |.bin...\.9...w..|
00000010 00 7b 00 00 00 00 00 04 00 35 2e 37 2e 32 35 2d |.{.......5.7.25-|
00000020 32 38 2d 64 65 62 75 67 2d 6c 6f 67 00 00 00 00 |28-debug-log....|
00000030 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
00000040 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 13 |................|
00000050 38 0d 00 08 00 12 00 04 04 04 04 12 00 00 5f 00 |8............._.|
00000060 04 1a 08 00 00 00 08 08 08 02 00 00 00 0a 0a 0a |................|
00000070 2a 2a 00 12 34 00 01 41 42 7c 7e ec 9f e6 5c 23 |**..4..AB|~...\#|
00000080 39 00 00 00 1f 00 00 00 9a 00 00 00 80 00 00 00 |9...............|
00000090 00 00 00 00 00 00 97 7f da 91 f9 9f e6 5c 22 39 |.............\"9|
000000a0 00 00 00 41 00 00 00 db 00 00 00 00 00 01 00 00 |...A............|
000000b0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
000000c0 00 00 00 00 00 00 02 00 00 00 00 00 00 00 00 01 |................|
000000d0 00 00 00 00 00 00 00 a7 52 8d 4c f9 9f e6 5c 02 |........R.L...\.|
000000e0 39 00 00 00 52 00 00 00 2d 01 00 00 08 00 02 00 |9...R...-.......|
000000f0 00 00 00 00 00 00 04 00 00 24 00 00 00 00 00 00 |.........$......|
00000100 01 20 00 a0 55 00 00 00 00 06 03 73 74 64 04 21 |. ..U......std.!|
00000110 00 21 00 08 00 05 06 53 59 53 54 45 4d 0c fe 74 |.!.....SYSTEM..t|
00000120 65 73 74 00 42 45 47 49 4e 88 e1 8e 38 f9 9f e6 |est.BEGIN...8...|
00000130 5c 05 39 00 00 00 20 00 00 00 4d 01 00 00 00 00 |\.9... ...M.....|
00000140 02 1f ce 58 0c 00 00 00 00 60 db e4 1b f9 9f e6 |...X.....`......|
00000150 5c 02 39 00 00 00 7e 00 00 00 cb 01 00 00 00 00 |\.9...~.........|
00000160 02 00 00 00 00 00 00 00 04 00 00 24 00 00 00 00 |...........$....|
00000170 00 00 01 20 00 a0 55 00 00 00 00 06 03 73 74 64 |... ..U......std|
00000180 04 21 00 21 00 08 00 05 06 53 59 53 54 45 4d 0c |.!.!.....SYSTEM.|
00000190 fe 74 65 73 74 00 49 4e 53 45 52 54 20 49 4e 54 |.test.INSERT INT|
000001a0 4f 20 6d 79 5f 74 61 62 6c 65 20 56 41 4c 55 45 |O my_table VALUE|
000001b0 53 28 30 2c 20 31 2c 20 37 2c 20 33 2c 20 39 2c |S(0, 1, 7, 3, 9,|
000001c0 20 6e 6f 77 28 29 29 45 2b ed da f9 9f e6 5c 10 | now())E+.....\.|
000001d0 39 00 00 00 1f 00 00 00 ea 01 00 00 00 00 0e 00 |9...............|
000001e0 00 00 00 00 00 00 dc 66 67 71 |.......fgq|