I am experimenting on whether I can tweak the functionality of master-slave replication. I can write data in my master server which gets updated in the slave server but it cannot happen the other way. I want to find a way where I can write and update data via both the servers. Is it possible to do it ? If yes, what is the way to implement it ?
1
-
1By default, an update to a slave isn't replicated to a master. However a replication of master to slave of the same results will result in replication failure. Explaining a higher level goal may get you a better design.– danblackCommented Mar 13, 2020 at 6:25
Add a comment
|
1 Answer
You can use master-master replication which consists of two master-slave configurations: One master-slave where node 1 is master and node 2 is slave, and one where it's the other way around.
There are a couple of caveats with this, and it's a bit fragile: You need to set auto_increment_increment to 2 on both nodes and set auto_increment_offset to 1 on the one node and 2 on the other, IIRC. This way, you avoid INSERT collisions (assuming the tables use auto_increment). Here's an article that might be useful: MariaDB master/master GTID based replication with keepalived VIP
There is another solution called multi-master replication, and with MariaDB that can be achieved with MariaDB Galera Cluster. This is a more robust solution, although it does have some limitations - note e.g. that it only really works with the InnoDB storage engine. You would usually want to have an odd number of nodes (typically 3) to have quorum amongst the nodes in the event of a network split, although you can simulate this with 2 normal nodes and one Galera arbitrator.
