Working with MySQL binary logs


Since this is an area not everyone has to deal with, I thought a quick one-liner was in order.

MySQL can have one of three different binary log formats, which are used to facilitate replication.  MySQL masters have binary logging enabled and the binary logs are streamed to all the replicas, where the logs are then applied.  The three log formats are:

  • Statement: Actually stores the DML statements in the log
  • Row: Only stores the changes, in a compressed, binary format (much faster to stream and apply)
  • Mixed: Uses row-based when possible, statement when it cannot (some transactions might include cross-schema traffic, temporary tables, etc., and thus are unsafe for row-based logs)

Statement-based log files are really easy to read.  Using the transparently named MySQL utility mysqlbinlog, you simply pass it the name of the binary log on the command line and it will spit out all the statements in the log.

Binary logs with row-based logging are a different beast.  If you run the above example on a row-based log, you get something like this:

 [fsdbaadm@dbutils:/home/fsdbaadm/work] mysqlbinlog binarylog.106647 | head -20
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#201117 12:15:13 server id 1  end_log_pos 123 CRC32 0x98c1ece5  Start: binlog v 4, server v 5.7.25-28-31.35-log created 201117 12:15:13
BINLOG '
MRO0Xw8BAAAAdwAAAHsAAAAAAAQANS43LjI1LTI4LTMxLjM1LWxvZwAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AeXswZg=
'/*!*/;
# at 123
#201117 12:15:13 server id 1  end_log_pos 154 CRC32 0xa0653737  Previous-GTIDs
# [empty]

Not particularly useful.

This will output a much more readable version:

[fsdbaadm@dbutils:/home/fsdbaadm/work] mysqlbinlog --base64-output=decode-rows -vv binarylog.106647 | grep "###" | sed 's/###//g' | head -30
INSERT INTO `smap01`.`tokens`
SET
@1=217236393 /* INT meta=0 nullable=0 is_null=0 */
@2='4C_qlK48qEZqZ_YRY-AT1KhZiaCdyn9HJaXYiVBgzLIHFzmR-znD2PGaknkV' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
@3='_LsanxNrWtCwTPtyaFbANKN4BS4YSO3cOHblad_o4yaJ9SRE6MEtN7pj6gJi' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
@4='2d565e62-b044-43e6-849f-859a91765351' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
@5='app' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
@6=1605638713 /* INT meta=0 nullable=1 is_null=0 */
@7=1606846513 /* INT meta=0 nullable=1 is_null=0 */
@8=NULL /* VARSTRING(1020) meta=1020 nullable=1 is_null=1 */
@9='2020-11-17 18:15:13' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
@10='2020-11-17 18:15:13' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
INSERT INTO `smap01`.`tokens`
SET
@1=217236394 /* INT meta=0 nullable=0 is_null=0 */
@2='WfSinOhrHnpi7XAvI005CerPyMFG1GLKN73N3CnWkYFg5AzYb6tNfOneESEO' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
@3='jyn1c_-aQ_MgiPRaC2LvAkx58kwm0jJLZ-AAS40zuvXmb3cZ4bCd9HWZrB4J' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
@4='2d565e62-b044-43e6-849f-859a91765351' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
@5='app' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
@6=1605638714 /* INT meta=0 nullable=1 is_null=0 */
@7=1606846514 /* INT meta=0 nullable=1 is_null=0 */
@8=NULL /* VARSTRING(1020) meta=1020 nullable=1 is_null=1 */
@9='2020-11-17 18:15:14' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
@10='2020-11-17 18:15:14' /* DATETIME(0) meta=0 nullable=1 is_null=0 */