Synchronization between branches and Main Servers

Hi Support
We are provisioning a multi branch infrastructure for our online and offline business.Offline business must always be available with local database of customer information and activities.offline(branches) and online business(data centers) are connected with an adsl connection -so not always reliable:This means we may have frequent disconnection- also avoiding static IP for branches is more preferable. I’ve attached a picture of problem.
[LIST=1]
[]I want to know what is the best option for branch and data center synchronization in percona?
[
]What is the best option for synchronization between data centers in different locations? and percona servers in data center themselves?
[/LIST]
I’ll appreciate all recommendations.

Hello Martin, welcome to the Percona community forum. :slight_smile:

So, first of all I need to check with you that you’re not a customer of Percona i.e. you don’t have a contract with us? Support is handled separately from the open source forum (which is where we are right now).

Also, to clarify that the Forum isn’t “support” in the traditional sense. We welcome community (non-Percona) dialogue here and there’s no guarantee that you will always get a full answer. Certainly, though the Percona engineers do their best, anything that is particular to a given implementation and would require in-depth investigation of an issue won’t be addressed via the Forum. By definition the answers here have to be generic.

SO… onto your question. This is one of those cases where the answers lie perilously close to consultancy, and I doubt you’ll get a full answer here though you might get some useful pointers. If you’re a customer, you can, of course, contact Percona via your Customer Success team. If not, but you’re interested in our professional services, then let me know and I’ll get someone to reach out.

Meanwhile… I’ll do some research and see if there are any blog posts or resources such as webinars that could help you with this. Will be back soon!

Hi Lorraine
Thanks for your reply. I don’t want a comprehensive reply. I am here to research and report to my manager about Percona’s Solution to our problem.If I found any solution, I’ll provide him a report of initial and ongoing costs.If agreed we’ll be able to have a customer contract with you and benefit full consultancy.

Regards

Hey no, that’s great. Not everyone understands the scope - I didn’t want you to be disappointed.:slight_smile:
Let me see what I can find that might help your research.

Thanks for your understanding :slight_smile:

I’ll update with some info tomorrow, I just want to dig around a little…

Hi Lorraine, Is there any new update?

Apologies, Martin! Here are a few links that might help in your research. These articles give some good summary information about replication options.

https://www.percona.com/blog/2017/06/20/the-mysql-high-availability-landscape-in-2017-the-elders/
https://www.percona.com/blog/2017/08/22/mysql-high-availability-landscape-2017-adults/
https://www.percona.com/blog/2017/09/15/the-mysql-high-availability-landscape-in-2017-the-babies/

This article talks about multi-master replication

https://www.percona.com/blog/2012/01/19/percona-xtradb-cluster-feature-2-multi-master-replication/

This one looks at multi-source replication

https://www.percona.com/blog/2018/03/30/multi-source-replication-performance-with-gtid/

And another:

https://www.percona.com/blog/2018/03/13/the-multi-source-gtid-replication-maze/

There should be some pointers to documentation in those blog posts.

In addition, you might want to take a look at a couple of our webinars:

https://www.percona.com/webinars/hazards-multi-writing-dual-master-setup (note that this one is old and might have been superseded, but nevertheless will probably give you some good questions to answer)

https://www.percona.com/resources/webinars/percona-xtradb-cluster-pxc-101 - an introduction to Percona XtraDB Cluster

Here’s a couple of solution briefs that are aimed at the decision maker:

https://www.percona.com/resources/solution-brief/always-online-critical-website-percona-xtradb-cluster
https://www.percona.com/resources/solution-brief/never-lose-mysql-data-percona-xtradb-cluster-aws-ec2-environment

If you digest those, hopefully you’ll be able to pinpoint the kind of architecture that you want to support and from there you might find other searches on our blog and forum.

Thanks Lorraine, what I got from those is that it is better to use percona xtradb cluster with galera for inside of data centers.
between data centers , we can use galera but it will increase point of failure and transaction time?!(if one of nodes was faulty, synchronized transactions won’t work?!
Between branches and datacenter we must use an async solution like master-master replication, but it seems that master-master replication is depreciated in favor of group replication?!
another question is if is it possible to setup replication for table or tables instead of whole database?

Hi Martin, glad those helped.

The cluster solutions aren’t designed for geographically dispersed implementations, it’s true, since the network would have a big impact on their ability to function. You’ve already mentioned that you have network challenges.

So, depending on your set up these are quite in-depth discussions that are specific to your application and it might be that it’s pretty much out of scope for this Forum: an answer to something like this is inevitably “it depends”. There may be other good solutions for your circumstance. Again, if you’d like to discuss what we can offer professionally, let me know, not a problem.

Meanwhile, that said, someone could perhaps answer about tables vs databases, master-master vs group etc. I will see if I can find someone.