0

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
SET SQL_LOG_BIN=0;

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 Aug 18 at 18:09
  • REPLACE = DELETE based on all unique keys, followed by one INSERT. – Rick James Aug 18 at 18:10
  • Are the binlogs turned on on the Slave(s)? – Rick James Aug 18 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 Aug 18 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. – Al Amin Chayan Aug 19 at 3:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.