Not the answer you need?
Register and ask your own question!

Does pt-table-checksum work in circular replication environments?

rodrigorodrigo ContributorCurrent User Role Novice
Hello there,

I have 3 masters in circular replication. I run pt-table-checksum in one of the masters only.
A->B->C->A

The queries do get replicated the "next" server B but I don't think they are being replicated "properly" to the third server C. The checksums are always identical to the B server.

Thanks,
Rodrigo.

Comments

  • psongpsong Contributor Inactive User Role Beginner
    Please check binlog_format on server B. If it is row-based, then that would be the reason for server C to always have identical checksum to B while it should not.
  • rodrigorodrigo Contributor Current User Role Novice
    Hello there!

    We are using MIXED binlog_format in all our servers. Would that be a problem?
  • rodrigorodrigo Contributor Current User Role Novice
    We are using MySQL 5.6.17 and Percona toolkit 2.2.12
  • omhlomhl Entrant Current User Role Beginner
    Hey guys,

    Our production db are also setup in the same way using mixed. We use mixed due to needing to connect with SQL over linked server connections and other benefits.

    Were you able to find the answer to OP's question?

    Thanks,
    Oscar
  • mirfanmirfan Database Administrator Inactive User Role Beginner
    It's chain replication A->B->C->A and pt-table-checksum works great only with STATEMENT format for this particular setup AFAIK. You can read further here on LIMITATION section. http://www.percona.com/doc/percona-toolkit/2.2/pt-table-checksum.html#limitations
    Checksumming relies on binlog_format=STATEMENT. B receives changes from it’s Master A, which will be STATEMENT (pt-table-checksum will force this into STATEMENT binlog_format in it’s session) B executes the checksumming queries. So far so good.
    But then B will also write the changes to it’s own binary log, so it can replicate to C but It will write checksum queries as ROW events in binary log so It won't replicate to C.
    pt-table-checksum only works properly when STATEMENT based formatting is used (to generate the checksum), if it’s ROW/MIXED based, the row change to the checksums table is is just replicated from it’s master, no checksumming happens..
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.