This guide assumes familiarity with basic MySQL replication concepts and focuses exclusively on configuring a master-master replication setup using Docker containers. For master-slave replication, refer to other resources.

Understanding Master-Master Replication

In traditional master-slave replication, writes are restricted to the master to avoid data inconsistency. Master-master replication resolves this by allowing both servers to act as both master and slave—enabling bidirectional write operations while maintaining synchronization.

Configuration Steps

Step 1: Stop Existing Slave Processes

Access the current slave container and halt replication:

docker exec -it mysql-slave bash
mysql -u root -p
STOP SLAVE;

Step 2: Update MySQL Configuration Files

Primary Node (mysql-master)

Edit /etc/mysql/my.cnf inside the mysql-master container:

[mysqld]
server-id=100
log-bin=mysql-bin
relay-log=master-relay-log
auto_increment_increment=2
auto_increment_offset=1

Restart MySQL within the container:

service mysql restart

Secondary Node (mysql-slave)

Edit /etc/mysql/my.cnf inside the mysql-slave container:

[mysqld]
server-id=101
log-bin=mysql-bin
relay-log=mysql-relay-log
auto_increment_increment=2
auto_increment_offset=2

Note: auto_increment_offset is set to 2 here to avoid primary key conflicts.

Restart MySQL:

service mysql restart

Step 3: Configure Replication Users and Links

Create Replication User on mysql-slave

Inside the mysql-slave MySQL console:

CREATE USER 'replica_user'@'%' IDENTIFIED BY 'secure_password';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replica_user'@'%';

Get Binary Log Coordinates from mysql-slave

Run on mysql-slave:

SHOW MASTER STATUS;

Record the values for File (e.g., mysql-bin.000001) and Position (e.g., 617).

Configure mysql-master to Replicate from mysql-slave

On mysql-master, execute:

CHANGE MASTER TO
  MASTER_HOST='<mysql-slave-ip>',
  MASTER_USER='replica_user',
  MASTER_PASSWORD='secure_password',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=617,
  MASTER_CONNECT_RETRY=30;

Replace <mysql-slave-ip> with the actual IP address of the mysql-slave container (use docker inspect to find it if needed).

Start Replication on Both Nodes

On mysql-master:

START SLAVE;

Verify status:

SHOW SLAVE STATUS\G

Ensure both Slave_IO_Running and Slave_SQL_Running show Yes.

Then, on mysql-slave, also run:

START SLAVE;

Troubleshooting

If you encounter the eror:

ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

Resolve it by resetting the slave configuration:

RESET SLAVE;
START SLAVE;