0

We're in the process of implementing a point of sale (POS) solution where we have heavily modified an existing open source POS. The original POS was never created with replication/synchronization between terminals and we want to add this.

We are using MySQL 5.7 and require a solution to do the following:

  • each POS terminal needs to operate independently of other terminals or back office server - i.e. have it's own local database and still function, even if the network goes down

  • multi-master synchronization - all POS terminals should reflect the current state of the system

Example 1: you should be able to start an order on one POS terminal, and finish/modify it on another terminal.

Example 2: If you start an order for a restaurant table on one terminal, another waiter will see that that table is occupied, from another POS terminal.

  • scalable to n terminals - some customers will have 1 POS terminal, some 3 and some 100. The solution needs to scale to any number of POS terminals

I've looked at the following solutions:

  • MySQL Cluster
  • MySQL Replication
  • Maria DB Replication
  • Maria DB Gallera Cluster

It seems that MySQL semi synchronous replication could work, but I'm not sure this is the best setup.

I'd like some direction in terms of:

  • what technology to use
  • how best to set this up

1 Answer 1

1

I would go with Galera. It is self-healing after a single node dies and comes back online. If, however, the entire network dies (no machine can talk to any other machine), NDB Cluster may be the only solution since it repairs outages by user-defined "conflict resolution" algorithms.

Plain Replication has a lot of potential failings, and needs more hand-holding. Semi-sync requires that part of the network stay up. When semi-sync can't do its "sync" it quitely reverts to "async", thereby losing what you were expecting.

100 nodes in any replication technology will be a challenge. Going past 5-10 nodes needs special casing for larger networks. For plain Replication and semi-sync, "relay" servers are desirable; they take extra setup and require extra handling in failure situations. I have not heard of NDB or Galera being shaken down for larger networks that maybe a dozen nodes. Some people have multiple Galera clusters with async between the clusters.

You might be better off spending time and money to hardening the network first.

A large department store might have 10 checkout counters, each with 4 POS. That hints at hard-wired (very reliable) 4-node cluster of some kind, plus something more fragile between the clusters. If you use wifi, be sure to encrypt all traffic.

With plain Replication, a dual-Master, single-writer (or 3-node Galera cluster) is possibly the best. It does require that all POS be able to write to the Master, else they are offline. It also requires extra coding to deal with "critical reads" -- that is where you post something, then need to immediately read it back, and must be sure any replication lag is accounted for. (Note: I am not having any writes occur on the POS, only indirectly through the Master and Replication.)

Chew on all that. Sorry, I don't have a "Do this" answer.

2
  • Thanks a lot for the helpful reply, Rick. I'm going to go with Galera, as you advised. I have 3 choices: Galera with MySQL, MariaDB Cluster (which uses Galera) and Percona Xtradb Cluster (which uses Galera). I'm thinking MySQL with Galera because I'm OK with MySQL and I just need replication. Thoughts?
    – gomisha
    Dec 31, 2015 at 15:29
  • MySQL + Galera means that you must put the two together. MariaDB and PXC already have Galera "included" and actively pursue making the two work together. MySQL/MariaDB/Percona -- these are effectively "drop-in replacements" for one another.
    – Rick James
    Dec 31, 2015 at 18:04

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.