2

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.

  • I got lost on "slaves connected to each other". – Rick James Mar 16 '18 at 13:14
1

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

  • 1
    As an aside, overall the use of ring replication isn't thought to be ideal. You may have a specific need (or might have inherited it), I realize. Here is a discussion on that percona.com/blog/2014/10/07/… – percona-lorraine Mar 14 '18 at 19:41
  • Hey, Thanks for your feedback. I agree with you that this tool will not work, perhaps we can call it a bug as there is no channel replication setup, instead its a ring. Though I just joined them 2 days ago and was curious as if this can occur. I am in process of taking ring replication out of the scenario but will take time to convince the client. Thanks for pointing out though! – Mysql Consultancy Mar 15 '18 at 8:04
  • No problem I have added this to our bug reports if you want to follow it jira.percona.com/browse/PT-1505 – percona-lorraine Mar 15 '18 at 12:09
  • Hi again, one of our team observed an alternative way of using pt-osc to do what you need, I have updated the answer. Hope this helps. – percona-lorraine Mar 15 '18 at 15:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.