The set up I work with is MySQL 5.5 in a master-slave replication configuration, but we have to use "MIXED" replication (statement and row-based) because we use an application that requires it, and it happens to be on our same server.
We had replication stop recently, and I want to make sure we have recovered properly.
I downloaded and installed the Percona Toolkit, but it doesn't look like I'll be able to use pt-table-checksum in this case, specifically because of the row-based replication:
2.27.5 LIMITATIONS Replicas using row-based replication 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. The tool automatically checks the
binlog_format on all servers. See
–[no]check-binlog-format . (Bug 899415)
Are there any other options for doing a checksum on row-based replication tables?
I have found this on the Percona Forum which says it will work, but I cannot have it change my cnf file as it claims it will do. It also warns, when I attempt to run it, the following:
Replica mysql-b.example.com has binlog_format MIXED which could cause
pt-table-checksum to break replication. Please read "Replicas using
row-based replication" in the LIMITATIONS section of the tool's
documentation. If you understand the risks, specify
–no-check-binlog-format to disable this check.
Best Answer
I do not understand why an application requires the
MIXED
replication format, asMIXED
is equal toSTATEMENT
-based, but changing toROW
format when executing "unsafe" queries. So, aMIXED
-compatible application should be compatible with bothSTATEMENT
andROW
.As you can see here, if the the format detected (server-side) is
ROW
orMIXED
, it tries to change it dynamically for the session only. In your case, it complains about one of the slaves here (it requiresSUPER
privileges).Assuming you do not have a 3-node chain replication, or you use the slave's binlog for anything, you can ignore the warning with
--no-check-binlog-format
. No need to change the my.cnf. The question is, will the session-only change to statement affect your "application that requires MIXED"?pt-table-checksum
requires its own calculations to be done as statements, as a hash has to be calculated independently on the master and the slave independently and synchronously (not in time, but in relation with other queries).