MySql Binlog Update Statement Where Clause Has All Table Columns, Even If Client Uses Just Primary Key In Where Clause When Running Query

Question

On Examination of BinLog Found Following Statement : It has All table columns in where clause. The query I passed to mysql had only 1 Column- Primary Key Column, then why the log wrote all columns in BinLog? I have a particular problem with BinLog doing this : This is causing Recovery from BinLog to be very very Slow. I am not a MySQL DBA. Please Share Your Experience On This Subject.

UPDATE `ctbmysql`.`ctm`
### WHERE
###   @1=1139195549890498825
###   @2=1138051383057521436
###   @3=1615397172
###   @4=''
###   @5=1130000000662993985
###   @6=113
###   @7=''
###   @8=''
###   @9=1635370236
###   @10='49.128.173.183'
###   @11='49.128.173.146'
###   @12=''
###   @13=''
###   @14=''
###   @15=0
###   @16=1
###   @17=''
###   @18=''
###   @19=''
###   @20=0
###   @21='google-play'
###   @22=4217121370623809752
###   @23=''
###   @24=1
###   @25=''
###   @26=''
###   @27=0
### SET
###   @1=1139195549890498825
###   @2=1138051383057521436
###   @3=1615397172
###   @4=''
###   @5=1130000000662993985
###   @6=113
###   @7=''
###   @8=''
###   @9=1635453015
###   @10='150.242.24.246'
###   @11='49.128.173.146'
###   @12=''
###   @13=''
###   @14=''
###   @15=0
###   @16=1
###   @17=''
###   @18=''
###   @19=''
###   @20=0
###   @21=''
###   @22=4217121370623809752
###   @23=''
###   @24=1
###   @25=''
###   @26=''
###   @27=0

Answer 1

MySQL supports two different binary log formats as well as a mixture of both:

• STATEMENT causes logging to be statement based.
• ROW causes logging to be row based. This is the default.
• MIXED causes logging to use mixed format.

You can choose one by setting the binlog_format configuration option.

What you are seeing in your log is the row format. It lists the row to update (with all its values). This is not supposed to be your original query, it just looks similar. E.g. if your update affected more than one row, it would create more than one row-updates for your single original update query.

This is the intended and expected behaviour. From Advantages and Disadvantages of Statement-Based and Row-Based Replication:

RBR can generate more data that must be logged. To replicate a DML statement (such as an UPDATE or DELETE statement), statement-based replication writes only the statement to the binary log. By contrast, row-based replication writes each changed row to the binary log. If the statement changes many rows, row-based replication may write significantly more data to the binary log; this is true even for statements that are rolled back. This also means that making and restoring a backup can require more time.

Nevertheless, row bases replication is the default for a reason:

Advantages of row-based replication:

• All changes can be replicated. This is the safest form of replication.

Answer 2

parameter binlog_row_image may does help