Using Pt-online schema change on multi master MYSQL

Posted on

Question :

We are using two MYSQL servers in ring replication where master is also a slave of the other master. Both have slaves connected to each other.
We want to run pt-online-schema-change to add couple of columns but we are not sure if it may break the replication due to ring replication in place. Any advice would be great.

Answer :

If you use a dsn table as a recursion method, then pt-online-schema-change can do the job for you. This is documented here:

https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html#cmdoption-pt-online-schema-change-recursion-method

If you use the hosts method, though, pt-osc will detect that SHOW SLAVE STATUS returns more than 1 row, but it considers the topology is using replication channels. You would get this error:

This server returned more than one row for SHOW SLAVE STATUS but "channel"
was not specified on the command line at bin/pt-online-schema-change line 4494.

For setting up topologies to test different circumstances, dbdeployer is a useful tool https://github.com/datacharmer/dbdeployer#replication-topologies.

Disclosure: I work for Percona

Leave a Reply

Your email address will not be published. Required fields are marked *