Multi-Master (n >=3 nodes) Paradigm

Question

I'm looking for a multi-master configuration for MySQL databases which is more secure/effective than using a circular fashion.

General Layout:

• Central Database (A)
• Periphery Databases (B-Z)

Interaction Requirements (A<-->(B-Z)):

• All periphery databases need read/write access to the central database.
• All changes made to the central database (whether it be from the server itself or a relayed periphery change) must propagate to all periphery databases.
• Periphery databases should not have any relation to other periphery databases.
• They should only be master and slave of the central database.
• Each periphery database should be able to operate with the host server lacking a current internet connection, relaying all changes to the central database upon reconnecting.

The data that is centralized is mostly static. The periphery database changes that get added are mostly sales records which don't necessarily need to reach down nodes immediately. All tables are using auto_increment_increment and auto_increment_offset to take care of any overwrite errors when merging bin logs.

Currently, a circular system is in place but this is definitely not ideal. I don't want periphery nodes to rely on each other or have to be scripted to reassign backup master databases to keep the linkage active during down times. I wish to have master-master setup between the central database and each individual periphery database.

If you feel as if a completely different paradigm is appropriate, feel free to recommend as such. Software being ran on the same network as each periphery server needs optimal speed with a fail-safe for local internet outages where all changes eventually make it to other periphery databases somehow.

If you can recommend another database software like MariaDB or PostgreSQL with support for what I want, please feel free to post the solution using their framework. As long as the database is relational, I can most likely manage to port.

Answer 1

Yes, running asynchronous clustering across 3 nodes sucks - indeed it can even suck across 2 nodes. Percona and MariaDB are both forks of MySQL, and both, along with MySQL itself are supported by Galera cluster.

Any of these 3 will you a drop in replacement for your current MySQL servers, but there are some caveats.

They should only be master and slave of the central database.

You seem somewhat confused about master/slave relationships. For high availability, scalability and performance you really want all the nodes to be equivalent or running with data partitioning/federation (but partitioning/federation is really hard to do). No "central database".

You didn't mention what talks to your database. If its PHP, there's an additional option in mysqlnd-ms

(note that switching to PstgreSQL, while an excellent product, will require a lot of regression testing and code rewrites).