Multi-Master ring replication

What is Multi-master ring replication

Multi-master "ring" replication means that you have 2 or more master where each master is replicating its data to one another master asynchronously. This is not to be confused with MariaDB Galera clusters which is a virtually synchronous multi-primary cluster for MariaDB.

The benefit of asynchronous replication compared to Galera cluster , is that ring replication is resilient against bad connections. If a connection goes down, all masters will continue to serve it's clients and data will automatically be synced when the link is again available.

The following picture shows one of the more advanced multi-master setups that is resilient against any master going down but can also handle 'human failures', like an accidental drop table, thanks to delayed slaves.

One should setup replication on each masters like one does in standard MariaDB replication. The replication setup among the masters should be a ring. In other words, each master should replicate to one other master and each master should only have one other master as a slave.

Each master can of course have one or more normal slaves. A master could also be a slave of other master not in the ring setup.

All MariaDB servers supports multi-master ring replication. In fact, when MySQL replication was originally designed around year 2000, it was to be a multi-master-ring replication solution for Yahoo to replicate from the East coast to the West coast.

Configuring the Masters

First follow the instructions in setup replication. The main thing to remember is to use the master_use_gtid=current_pos option for CHANGE MASTER.

The main things that are different for multi master ring replication are:

• Give every master and slave in the replication setup a unique server_id. This can be a number from 1 to 4294967295. It a good practice to ensure that you do not have any servers in your system with the same server_id!
• Use global transaction id (as described above)
• Give each master a unique gtid_domain_id. This will allow replication to apply transactions from different master in parallel independent from other masters.

Add the following into your my.cnf file for all masters and restart the servers.

[mariadbd]
# Replace the following with a unique id. All slaves of this master should have same
# gtid_domain_id to allow easy failover to a slave if needed
gtid_domain_id=1
#
# Let us assume there will never be more than 10 masters in a multi-master-ring setup
auto_increment_increment=10
# Set this to a different value 1-10 for each master. Could be same as gtid_domain_id
# This to ensure that all masters generate different values for AUTO_INCREMENT keys.
auto_increment_offset=1
#
# The following is needed to ensure the ALTER TABLE on another master will not
# break ring replication
slave_type_conversions=ALL_NON_LOSSY,ALL_LOSSY
#
# We cannot use semisync in ring replication as the masters need to be resilient against
# bad connections
rpl_semi_sync_master_enabled=0
#
# We have to log updates from other masters to the binary log.
log_slave_updates

Limitations when using ring replication

• MariaDB does not yet support conflict resolution for conflicting changes. It iis up to the application to ensure that there is never a conflicting insert/update/delete between the masters. The easiest setup is having each master work on different databases or table. If not, one must ensure:
  • Ensure you have an id (master-unique-id) for each row that unequally identifies the master who is responsible for this row. This should preferably be short and part of the primary key in each table. A good value for this would be the gtid_domain_id as this is unique for each local cluster.
  • Never insert rows with PRIMARY KEY or #UNIQUE KEY values that can be same on another master. This can be avoided by
    • Have the master-unique-id part of all primary and unique keys.
    • In case of AUTO_INCREMENT keys, have a different value for auto_increment_increment on each master.
    • use uuid_short() to generate unique values, like in create table t1 (a bigint unsigned default(uuid_short()) primary key).
  • Ensure that UPDATE and DELETE on each master only updates rows generated by this master.
• If several master are constantly generating and updating rows for common tables, one has t be extra careful with ALTER TABLE to ensure that any change one does will not cause conflicts when the ALTER TABLE is replicated to other servers. In particular one has to ensure that all masters and their slaves are configured with slave_type_conversions=ALL_NON_LOSSY,ALL_LOSSY