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-checksumrequires statement-based replication, and it setsbinlog_format=STATEMENTon 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:
- 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', ...);
- Run a
REPLACE INTOquery 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?
REPLACE=DELETEbased on all unique keys, followed by oneINSERT. – Rick James Aug 18 '19 at 18:10