bhuva nesh
asked on
How to sync bidirectionally on Mysql Database
In Below Scenerio, Will Master-slave replication should work?
I have local and live Server. Everyday i have to insert data from local machine which needs to be synced to live Server(1 time per day) and changed data needs to be sync back to local.
I have local and live Server. Everyday i have to insert data from local machine which needs to be synced to live Server(1 time per day) and changed data needs to be sync back to local.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok. Thanks for your suggestions
master master is feasible by configuring 2 separate master slave statement based replication in mysql. it will not work properly with row based.
a better alternative is to switch to a mariadb cluster. it is fully compatible with mysql clients. you'll need to add a 3rd member or possibly an arbitrator.
a better alternative is to switch to a mariadb cluster. it is fully compatible with mysql clients. you'll need to add a 3rd member or possibly an arbitrator.
To do this you'll setup a Master + Master replication.
Best use MariaDB rather than MySQL, as MariaDB fixes many replication problems.
And, also important, both these instances be on public IPs, so they can actually talk with each other.
Also, be sure you're running same MariaDB versions both places.
In fact, when I do this, I setup LXD containers on two different physical machines + each container has exactly the same OS level + software packages installed.
If you go the LXD route, you can also spin up any number of additional instances (LXD container clone) any time you like.
Best use MariaDB rather than MySQL, as MariaDB fixes many replication problems.
And, also important, both these instances be on public IPs, so they can actually talk with each other.
Also, be sure you're running same MariaDB versions both places.
In fact, when I do this, I setup LXD containers on two different physical machines + each container has exactly the same OS level + software packages installed.
If you go the LXD route, you can also spin up any number of additional instances (LXD container clone) any time you like.
ASKER
will follow up. Thanks for your suggestion.
If you go the LXD route, you can also spin up any number of additional instances (LXD container clone) any time you like.
true with a mariadb cluster. much more complex with a multimaster replication setup.
@david : replication loops are theorically detected in mariadb. this does not mean there is no issue with them. with more than 2 nodes, the mariadb cluster is an obvious choice. and it also handles geolocalised replications.
Any replication setup is complex, master-master or master-slave.
Unless you truly require replication, just make periodic backups + restore a backup in the advent of a database or machine crash.
Unless you truly require replication, just make periodic backups + restore a backup in the advent of a database or machine crash.
I'd recommend rather to have a replication and setup backup on slave to have least interruption/load to production.
i do not think #a42640891 really qualifies as an answer : asks a question and suggests an incomplete and very dangerous and error prone procedure.
#a42640938 is actually an answer : master slave will not work. master master will.
#a42640938 is also an answer
#a42640938 is also an answer
#a42640938 is actually an answer : master slave will not work. master master will.
#a42640938 is also an answer
#a42640938 is also an answer
ASKER
thanks
Have cron jobs that will call the following:
When you want it to start replicating:
mysql -u root -proot_password -e "start slave"
When it's time to stop replicating:
mysql -u root -proot_password -e "stop slave"