MySQL Forums
Forum List  »  Replication

Re: MySQL Replication lag in slave due to Delete query - Row Based Replication
Posted by: Vinieth S S
Date: September 17, 2021 05:49AM

Sorry for the late reply, have checked few other cases which took time.

Acutal Query
'Delete from Table where Pk in (2000ids)' by 2k batch

If I set binlog format to MIXED, even though the query is safe to execute in Statement format, MYSQL will only use RBR.

Because please check the below decision table, which I took from binlog.cc file.


Decision table for logging format
------------------------------------

The following table summarizes how the format and generated
warning/error depends on the tables' capabilities, the statement
type, and the current binlog_format.

     Row capable        N NNNNNNNNN YYYYYYYYY YYYYYYYYY
     Statement capable  N YYYYYYYYY NNNNNNNNN YYYYYYYYY

     Statement type     * SSSUUUIII SSSUUUIII SSSUUUIII

     binlog_format      * SMRSMRSMR SMRSMRSMR SMRSMRSMR

     Logged format      - SS-S----- -RR-RR-RR SRRSRR-RR
     Warning/Error      1 --2732444 5--5--6-- ---7--6--


My Case: 
Row Capable:       Y
Statement Capable: Y
Statement type:    S
Binlog_fomrat:     M

Logged format:    R

As the Logged format is R, it won't be go for SBR in my case. So setting SBR is needed, whereas MIXED can't be used.

Can you please share the points, why SBR it won't reliable in my case?



Edited 1 time(s). Last edit at 09/17/2021 10:12AM by Peter Brawley.

Options: ReplyQuote




Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.