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.
2
-
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:
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
-
1As 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
