Overview

MySQL replication is an essential feature for those seeking high availability, performance scalability, and redundancy of their database systems. In a typical replication setup, you have a primary master server and one or more slave servers. However, in a more complex setup with multiple masters, such as having 3 masters and 1 slave, you can achieve enhanced redundancy and load balancing.

Benefits and Use Cases

1. High Availability: With multiple masters, your database can tolerate the failure of one or more servers, increasing the robustness of your system.
2. Load Balancing: By directing read and write operations across multiple masters, you can distribute the load more evenly, which enhances performance.
3. Geographical Distribution: Situations requiring geographically dispersed databases can benefit from multi-master replication. Each master can handle operations closer to user locations, reducing latency.
4. Fault Tolerance: If one master fails, others can quickly assume its role without stopping database operations.

Technical Architecture

Master-Master Setup

In a multi-master replication, each master has also set up as a slave to the other masters, meaning replication is bi-directional. Each master records the binary logs needed to replicate data changes, and they all listen to each other's changes.

1Master1 <--> Master2 <--> Master3
2  |                    |
3  +--------------------+
4          |
5       Slave

Slave Configuration

The slave server subscribes to one of the master nodes, consuming its binary logs to stay synchronized. In a typical configuration, it is advisable to select a master that handles fewer write operations to avoid potential drawbacks from network-related lag or congestion.

Setup Example

Here is a brief guide to configuring a 3-masters, 1-slave MySQL replication:

Configuration Files

Master Configuration (my.cnf or my.ini):

1[mysqld]
2server-id=1  # Unique ID for the server
3log-bin=mysql-bin
4binlog-ignore-db=test  # Databases to ignore in the binary log
5log-slave-updates=1
6auto-increment-increment=3
7auto-increment-offset=1  # Offset (unique for each master)

Repeat this on each master with unique server-id and auto-increment-offset.

Slave Configuration:

1[mysqld]
2server-id=4
3relay-log=slave-relay-bin
4log-bin=mysql-bin

Replication User

On each master, create a user for replication and grant it the necessary privileges:

CREATE USER 'replicant'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replicant'@'%';

Setting Up Replication:

From Master's perspective:

1CHANGE MASTER TO
2MASTER_HOST='master2_ip', 
3MASTER_USER='replicant', 
4MASTER_PASSWORD='password', 
5MASTER_LOG_FILE='master-log-bin.000001',
6MASTER_LOG_POS=4;

Do a similar setup between Master1 and Master3, and Master2 and Master3.

Starting the Slave:

On the slave server, run:

1CHANGE MASTER TO
2MASTER_HOST='master_ip',
3MASTER_USER='replicant',
4MASTER_PASSWORD='password',
5MASTER_LOG_FILE='master-log-bin.000001',
6MASTER_LOG_POS=4;
7
8START SLAVE;

Note: Always execute SHOW MASTER STATUS on each master to get the MASTER LOG FILE and MASTER LOG POS.

Conflict Management

With multiple masters, conflict management becomes critical:

• Use InnoDB: InnoDB storage engine provides ACID compliance which helps resolve conflicts.
• Set Simple Rules: Decide which master is authoritative in the case of conflicts.
• Controlled Write Access: Ensure identical sets of data are not being modified by different masters simultaneously.

Table: Key Points

Conclusion

Implementing MySQL replication with 3 masters and 1 slave offers increased redundancy and load distribution advantages, but it demands careful planning and an understanding of potential conflicts. Ideal for enterprises that require resilient, high-performing database solutions.