5

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.

jynus
  • 15,057
  • 2
  • 37
  • 46
dmgig
  • 291
  • 1
  • 4
  • 10

2 Answers2

4

I do not understand why an application requires the MIXED replication format, as MIXED is equal to STATEMENT-based, but changing to ROW format when executing "unsafe" queries. So, a MIXED-compatible application should be compatible with both STATEMENT and ROW.

As you can see here, if the the format detected (server-side) is ROW or MIXED, it tries to change it dynamically for the session only. In your case, it complains about one of the slaves here (it requires SUPER 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).

jynus
  • 15,057
  • 2
  • 37
  • 46
1

Another way that you can do checksum for all the tables is to use native mysql CHECKSUM TABLE.

CHECKSUM TABLE table1,table2,table3;

You need to run this command on both master node and replication node. Then compare both results in the spreadsheet.

The drawback of native checksum is it's SLOW. It's best to you plan downtime before doing this operation. Large DB could take up to 30 minutes or even an hour.

To speed up the operation, you can try to split your table list into multiple batches and then run checksum on different mysql connections.

(But if your DB is not too big, try to run everything in single command is simpler)

connection 1 -> CHECKSUM TABLE table1,table2,table3;
connection 2 -> CHECKSUM TABLE table4,table5,table6;

I experienced the limitation of the pt-table-checksum as well. It works fine for statement based but it doesn't gurantee any previous row based replication that happened in the past. So it doesn't suit the job if you have row-based replication setup.

Therefore, checksums will not replicate past any replicas using row-based replication that are masters for further replicas.

Turbo
  • 111
  • 3