Finding statement based replication in the binlog vs row based

MySQL & MariaDB Other MySQL® Questions
1

Seeing something odd with pt-table-checksum in that the ts field in percona.checksums is identical across all replicas. If it is truly using statement based replication shouldn’t these be slightly different? Either way, I wanted to confirm 100% by checking the binlog but doing a search for the update statement in the binlog that pt-table-checksum uses to save the checksum information returns no results unless I enable --verbose to mysqlbinlog … but then that shows all the update statements I KNOW are using row based (the server default) based replication.

I’m simply doing:

mysqlbinlog binlog.000006 | grep -iP ‘\b(update|insert)\b’ > ~/blah.log

Which is blank.

Shouldn’t this show the update statement at a minimum?

2

Hey @Matthew_Lenz_L1T,
I looked at some binlog events and found one chunk example:

root@localhost:[(none)]> SHOW BINLOG EVENTS IN 'binlog.000100' FROM 556800928 LIMIT 10;
...
| binlog.000100 | 556801406 | Query      |         1 |   556802087 | use `www`; 
REPLACE INTO `percona`.`checksums` (db, tbl,
chunk, chunk_index, lower_boundary, upper_boundary,
this_cnt, this_crc) SELECT 'www', 'tbUA', '75',
'PRIMARY', '21693559', '22000703', COUNT(*) AS cnt,
COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `id`,
`createddt`, `createduser`, `user`, convert(`type` using
utf8mb4), convert(`view` using utf8mb4), CRC32(`data`),
convert(`queuedemail` using utf8mb4))) AS UNSIGNED)), 10,
16)), 0) AS crc FROM `www`.`tbUA` FORCE
INDEX(`PRIMARY`) WHERE ((`id` >= '21693559')) AND ((`id` <=
'22000703')) /*checksum chunk*/

Note there is no ts field in the checksum query. The ts field is handled by the schema of the checksums table:

   ts             TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP
 ON UPDATE CURRENT_TIMESTAMP,

The column is populated on INSERT/REPLACE, not by the SQL itself. Additionally, the precision of the ts column is only 1s. If replication (whether statement or row) takes < 1s to occur, then the ts will be the same across the chain, provided all servers are sync’d with some ntp.

mysqlbinlog binlog.000006 | grep -iP ‘\b(update|insert)\b’ > ~/blah.log

Add ‘replace’ to this as pt-t-c uses it.

Checking replica binlogs, I came to same conclusion that the transactions are re-written into ROW. I believe any intermediate replica/sources would need to be in MIXED mode, so that received STATEMENT events remain STATEMENT, and received ROW remains ROW.

3

I was originally using replace in the grep statement. This is on a direct replica of the current primary and still no statements in the binlog.