How to Build a Reliable MySQL Master‑Master Cluster with Keepalived Failover

Prerequisites

Environment : Two servers (physical or virtual) that can communicate over an internal network.

Software : Install the same stable version of MySQL on both machines.

Backup : Back up all important data before making any changes in production.

Setup Process

1. Modify MySQL configuration files

Server A (master1) :

[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-do-db = your_database_name
sync_binlog = 1
binlog_format = mixed
relay_log = relay-bin
relay_log_index = relay-bin.index
auto_increment_increment = 2
auto_increment_offset = 1
# Optional: bind-address = your_server_ip
# Optional: port = your_port_number

Server B (master2) :

[mysqld]
server-id = 2
log-bin = mysql-bin
binlog-do-db = your_database_name
sync_binlog = 1
binlog_format = mixed
relay_log = relay-bin
relay_log_index = relay-bin.index
auto_increment_increment = 2
auto_increment_offset = 2
# Optional: bind-address = your_server_ip
# Optional: port = your_port_number

Replace your_database_name with the database you want to replicate. Use multiple binlog-do-db entries or replicate-do-db on the slave side for several databases.

2. Create a replication user

GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%' IDENTIFIED BY 'strong_password';
FLUSH PRIVILEGES;

Replace 'replication_user' and 'strong_password' with your chosen credentials.

3. Lock tables and obtain binary‑log positions

On Server A:

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

Record the File and Position values. Perform the same on Server B after unlocking tables on Server A with UNLOCK TABLES;.

4. Configure replication

On Server B, point it to Server A:

CHANGE MASTER TO
  MASTER_HOST='master1_ip_address',
  MASTER_USER='replication_user',
  MASTER_PASSWORD='strong_password',
  MASTER_LOG_FILE='master1_binlog_file',
  MASTER_LOG_POS=master1_binlog_position;

Replace placeholders with the actual IP, password, binlog file name, and position obtained earlier. Perform the symmetric CHANGE MASTER TO on Server A to point to Server B.

5. Start replication

START SLAVE;

Run the command on both servers.

6. Verify replication status

SHOW SLAVE STATUS \G;

Ensure Slave_IO_Running: Yes and Slave_SQL_Running: Yes. If not, inspect the Last_Error field for troubleshooting.

7. Install and configure keepalived

Install keepalived on each node and edit /etc/keepalived/keepalived.conf to define a virtual IP, priority, and health‑check scripts. Example configuration for the primary node:

global_defs {
  notification_email { [email protected] }
  notification_email_from [email protected]
  smtp_server 127.0.0.1
  smtp_connect_timeout 30
  router_id LVS_DEVEL
}

vrrp_instance VI_1 {
  state MASTER
  interface eth0
  virtual_router_id 51
  priority 100
  advert_int 1
  authentication {
    auth_type PASS
    auth_pass 1111
  }
  virtual_ipaddress {
    10.124.151.23
  }
}

On the secondary node, set state BACKUP and a lower priority. Ensure the correct network interface and virtual IP are specified.

8. Start keepalived

Enable and start the keepalived service on both servers.

9. Test failover

Simulate a failure of one server and verify that the virtual IP moves to the surviving node while MySQL remains reachable.

Testing Master‑Master Replication

Create or modify data on either MySQL instance and confirm that the changes appear on the other node.

Failover Strategy and Monitoring

Consider using tools such as MHA (Master High Availability Manager) for automated failover, and Percona Monitoring and Management (PMM) or custom scripts to continuously monitor replication health and server status.

Key Considerations

Data Consistency : Ensure both nodes start with identical data.

Auto‑increment Settings : Configure auto_increment_increment and auto_increment_offset to avoid primary‑key collisions.

Conflict Resolution : Plan how to handle simultaneous updates to the same row on both masters.

Security : Restrict the replication user’s host access and use strong passwords.

Network Latency : Monitor latency as it can cause replication lag.

Backup Strategy : Replication is not a substitute for regular backups; maintain a robust backup and recovery plan.