Link to home
Start Free TrialLog in
Avatar of bhuva nesh
bhuva nesh

asked on

How to configure master-master replication?

In below scenerio, how mysql master- master replication can be used?

3 local xampp servers in different location
1 live server
Avatar of lenamtl
lenamtl
Flag of Canada image

I would use REST API and web service.

There are several tutorial on this topic on Udemy
https://www.udemy.com/
https://www.digitalocean.com/community/tutorials/how-to-configure-mysql-group-replication-on-ubuntu-16-04 provides good multi-master setup instructions.

Be sure to sync your files too. For example, if you have a PHP application interacting with your database, then you must sync your files using something like lsynd or csync2 or roll your own sync mechanism.

You must also write guards in your PHP code which match the PHP code versioning to your database versioning, so during periods of time when database structure changes + PHP files haven't yet synced, you must defer PHP files interacting with the database, which is of one versions format while your PHP expects a different version.

There are ways around this + they require basically shutting down everything + syncing all PHP files across all instances + then restarting App.

Downtime is usually measured in seconds + this has to be though through as App source files (whatever language) + database structural format will desync anytime you have a substantial App logic change.
_______

This said...

From how you asked your question, I think you might be thinking about multi-master (master/master) replication slightly differently than is best.

In a multi-master replication setup, you'll have 4x live servers (not 1 live + 3x spare). All servers/containers or however you're running your database instances will all be masters, hence they can all (at your option) be used as live servers... theoretically... because if this is possible depends heavily on your database App.

First step of this process is to consider the App running on top of your database instances, specifically one of these data access patterns...

1) Read mainly Apps are simple.

2) Write mainly Apps can quickly overwhelm the replication path + cause massive slowness + data desync issues.

3) CMS Apps like WordPress have session management issues.

Session management means you must ensure the session management tables live in the database, so if a person logs into one database instance + then moves between other instances during their session, all site interaction works seamlessly, independent of which database instance the visitors browser points to at any given moment in time. Luckily WordPress is tooled specifically to handle this situation. Other CMS Apps will fail abysmally or require massive rework for this to work.

Start by describing your App first.

Tip: There's almost always a way to design an App to use only one database instance + after you mess around with multi-master or just master-slave replication for a few years, you will always look for the design option that allows you to escape this vortex of endless time drain.
Avatar of bhuva nesh
bhuva nesh

ASKER

I have three Mysql XAMPP local servers only for update purpose and data will sync in live server on everyday evening. Hope this link will help@https://www.digitalocean.com/community/tutorials/how-to-configure-mysql-group-replication-on-ubuntu-16-04
If you want to grab the data from Live to a local instances, try to avoid master-master or any mechanism that may push data to production for that matter. From Live to Local, set it master-slave.
To push changes on production use alternate and manual* approach.
Yes. I can try master-slave but all three mysql machines are in different location( EX: CBE, CHENNAI, Bangalore) where data should be inserted while there is no network access and the same data will be synchronized to live server only after 6PM everyday. Please suggest one solution to do seamless sync.
hmm so the requirement is still not 100% clear...

you have 3 machines: CBE, Chennai, Bangalore
All are independent from each other?

So what you want is replicate from prod to each of these servers?
prod ---> CBE
prod ---> Chennai
prod ---> Bangalore

and this replication will work only at 6 PM everyday?

Now you said, data will be synchronized to live server everyday... so is that like you want actually the reverse:
CBE -> prod
Chennai -> prod
Bangalore -> prod

please clarify.
Yes. this is what i have to do on the local system which dont have network access upto 6PM.

At 6PM, All data wants to be sync in live automatically .

CBE -> prod
Chennai -> prod
Bangalore -> prod
Generally speaking using replication to push changes to production is really a risky design. You're blind to the changes being replicated to production until you really go and verify binary logs every single time you enable it. A single drop database command accidentally executed on any of the master is going to create havoc on production.
Now, I wrote that just to avoid "you-didn't-tell-me-that-earlier!!" but the task is do-able.

I'd still wonder on following questions which surely are important to understand the problem:
Is this some kind of data-feed?
Are all those three servers going to write into same table/database?
What size that data are? Do you think you can use some kind of change control if the size of the data are small? Eg. design/structural changes

Anyhow.... not to extend much... your solution could be:

A) Replication
1. Use multi source replication
or Write a custom script to manage & setup replication from all three masters (been there done that)

2. Start replication only when the-time-is-right.

B) Files/SQL
1. Collect changes/data files on all three source server
2. Execute manually / by script to be running on master - treating as production release
Just like to affirm theGhost_k8's comment...

Generally speaking using replication to push changes to production is really a risky design.

Having worked with database replication since the early 1990s, I can say this is a horribly bad idea.

If you're going to run a multi-master system, then run a full multi-master system, what you've described is actually 4x systems...

1) prod -> nowhere
2) CBE -> prod
3) Chennai -> prod
4) Bangalore -> prod

Before you start this project, likely good for you to hire someone who's worked with replicated data for a long time, to assist with your initial design + setup.

This person will go through a discovery phase with you to determine best design practices.

To accomplish the work you're describing requires far more detail + consideration than can occur comfortably in an EE thread.

This will likely resolve many problems before they occur.
Thanks for your quick solution. I have done Master-master replication and galera cluster configuration on live servers but i dono what exactly to do when there is no network access on local server.  As suggested, I will go with multi source replication or through windows script.
"when there is no network access on local server" >> one another way is to collect binary logs / changes to a file and ship them to production through whatever-means-of-network is accessible. (Which is solution B in my comment)
I have tested Multi Source replication in local environment. its synced automatically when network is connected  but keeping Production server as slave is not good.

I will follow option 2 as you mentioned.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.