1

We are currently investigating moving our current MySQL 5.5 Master > 5 x Slaves (with Statement Based Replication, apx 250GB data and growing) to a HA clustering option. Mainly to provide greater resilience, write scaling, and easier to carry out admin stuff like Optimising tables without taking the master offline.

We have ruled out (for the moment) MySQL ndb Cluster as it would require too many changes to our current database structure (large number of BLOB fields amongst other problems).

So we are looking at either Galera Cluster or Percona Cluster as the alternatives, as the they seem to do what we want, without 'too' steep a learning curve.

We would want to start off by setting the Cluster up as a Slave to the main Master (for testing etc), before moving over to the Cluster as our Primary system in the future. Is this possible? Reading through the documentation doesn't seem to really talk about this side of things (unless I've missed it).

Improve this question
3
  • Consider also MariaDB 10. It, too, contains Galera.
    – Rick James
    Commented Mar 4, 2015 at 2:31
  • Some tips on using Galera (PXC, MariaDB, etc): mysql.rjweb.org/doc.php/galera
    – Rick James
    Commented Mar 4, 2015 at 2:32
  • You mentioned write scaling. Note that Galera cluster only offers a moderate degree of write scaling: While you can write to all the nodes, each node contains the full dataset so each write set has to be replicated to all the other nodes. More details e.g. in Rick's article above.
    – dbdemon
    Commented Jan 2, 2018 at 22:50

1 Answer 1

Reset to default
3

Yes, it is very possible.

As the Documentation says, you should have a minimum of three nodes in a Percona XtraDB Cluster. You only need one node out of the three nodes.

  • Make sure all nodes in the PXC have unique server_ids.
  • Make sure the Master you are replicating from has a unique server_id from the PXC nodes
  • Make sure the master and every PXC node is configured with the following
  • Make sure the master and every PXC node is using binlog_format=ROW

Pick one node and set it up as you would a DB Server designated as a Slave via CHANGE MASTER TO.

If you are going to set up any replication filters, restrict it to just the PXC node that is your Slave. Personally, I would stay away from using replication filters altogether in this setup since one of Percona's Galera Instructors does not feel good about it.

Please be aware that data takes time to be on all nodes at the same instant of time (See Investigating MySQL Replication Latency in Percona XtraDB Cluster)

By the way, you can use any PXC as a Master to StandAlone Slaves as well

HEADS UP #1

Don't be surprised by replication lag in the PXC node acting as the MySQL Slave. After all, three nodes must write data and become eventually consistent before all committing changes together. See Follow these basics when migrating to Percona XtraDB Cluster for MySQL for more info.

HEADS UP #2

Since you are using MySQL 5.5, do not use PXC 5.6. Use PXC 5.5. Why ? Row-based binlogging in MySQL 5.5 can sometimes not be properly interpreted by a MySQL 5.6 mysqld process. This can break replication from MySQL 5.5 to MySQL 5.6. I wrote a post about this two months ago. The work around was to switch to binlog_format=STATEMENT, but you cannot do that for Percona XtraDB Cluster as it requires binlog_format=ROW.

Improve this answer
1
  • cheers for that, you make some good points. (I hadn't thought twice about using PXC 5.6 - I'll leave that for a future upgrade)
    – IGGt
    Commented Mar 3, 2015 at 16:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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