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|