We have a master slave replication [MySQL 5.6]. However In slave database 2 rows is missing in one table. I've tried to solve this using Percona Toolkit as suggested here.

But pt-table-checksum requires statement-based replication, and it sets binlog_format=STATEMENT on the master, but due to a MySQL limitation replicas do not honor this change. Therefore, checksums will not replicate past any replicas using row-based replication that are masters for further replicas.

So what if:

  1. Insert the rows directly into the slave database

INSERT INTO `tbl_name` (`id`, `column1`, ...)
VALUES ( 4321, 'some val', ...),
       ( 4344, 'some val2', ...);
  1. Run a REPLACE INTO query in Master database
REPLACE INTO `tbl_name` (`id`, `column1`, ...)
VALUES ( 4321, 'some val', ...);

REPLACE INTO `tbl_name` (`id`, `column1`, ...)
VALUES ( 4344, 'some val2', ...);

Can someone suggest which method is safe and reliable. Is this the right way to solve the issue. Is there any risk of doing this?

  • "will not replicate past..." -- Please show us your entire topology and explain which servers need which fixes.
    – Rick James
    Commented Aug 18, 2019 at 18:09
  • REPLACE = DELETE based on all unique keys, followed by one INSERT.
    – Rick James
    Commented Aug 18, 2019 at 18:10
  • Are the binlogs turned on on the Slave(s)?
    – Rick James
    Commented Aug 18, 2019 at 18:11
  • Seems like #1 is sufficient and safe. Seems like #2 is redundant (but possibly easier for the toolkit to perform).
    – Rick James
    Commented Aug 18, 2019 at 18:15
  • @RickJames thanks. #1 will be run on slave database and #2 will be run on master database. Is is good to run insert query in slave database? By conversion it should be on read only mode. Commented Aug 19, 2019 at 3:03


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.