Setting up MySQL master-master replication Print

In master-master replication both servers accept writes and replicate to each other. It is used where a second server must be able to take over writes, not merely serve reads.

Consider master-slave first. Master-master doubles the failure modes: the same row changed on both servers at once produces a conflict that replication cannot resolve, and the two copies then diverge silently. Unless you genuinely need writes on both sides, Для чего нужна репликация Master-Slave is the safer arrangement.

Replication is also not a backup: a wrong DELETE is replicated faithfully within seconds. Keep real backups as well.

Note on syntax

MySQL 8 renamed these commands: CHANGE REPLICATION SOURCE TO, START REPLICA, SHOW REPLICA STATUS, with SOURCE_HOST, SOURCE_USER and so on. MariaDB and older MySQL use CHANGE MASTER TO, START SLAVE and SHOW SLAVE STATUS. The examples below use the older form; substitute the new names if you run MySQL 8 or later.

1. Configure both servers

In the configuration file — /etc/mysql/my.cnf or a file in /etc/mysql/conf.d/ on Debian and Ubuntu, /etc/my.cnf on RHEL-based systems — set on server 1:

[mysqld]
bind-address    = 203.0.113.10
server-id       = 1
log_bin         = /var/log/mysql/mysql-bin.log
binlog_do_db    = example

auto_increment_increment = 2
auto_increment_offset    = 1

And on server 2:

[mysqld]
bind-address    = 203.0.113.20
server-id       = 2
log_bin         = /var/log/mysql/mysql-bin.log
binlog_do_db    = example

auto_increment_increment = 2
auto_increment_offset    = 2

The settings that matter:

• server-id must be different on each server. Identical values break replication in ways that are hard to diagnose.
• bind-address — the address the server listens on, so that the other can reach it.
• binlog_do_db — the database to replicate. Repeat the line for each database.
• auto_increment_increment and auto_increment_offset — the step and starting point for auto-increment columns. With the values above, one server generates odd IDs and the other even ones, so simultaneous inserts on both cannot collide. Without this, they will.

Restart the service on both after editing:

systemctl restart mariadb

Opening MySQL to the network means restricting who can reach it. Allow port 3306 only from the other server's address, in the firewall — not to the internet at large. An exposed database port is found and attacked within hours.

2. Create the replication user on both servers

mysql -u root -p

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

Restrict the host rather than using '%' where you can — 'replication_user'@'203.0.113.20' — and use a strong password.

3. Copy the existing data first

Replication only carries changes made after it starts. If the databases are not already identical, the two servers will never converge.

On server 1:

mysqldump -u root -p --databases example --master-data=2 > /root/example.sql

Copy the file to server 2 and import it:

mysql -u root -p < /root/example.sql

--master-data=2 writes the binary log position into the dump as a comment, which is exactly the position to use in the next step.

4. Note the log positions

On each server:

SHOW MASTER STATUS;

Record the File and Position values from both. Each server needs the other's values.

5. Point each server at the other

On server 2, using the file and position from server 1:

CHANGE MASTER TO
  MASTER_HOST     = '203.0.113.10',
  MASTER_USER     = 'replication_user',
  MASTER_PASSWORD = 'password',
  MASTER_LOG_FILE = 'mysql-bin.000001',
  MASTER_LOG_POS  = 107;
START SLAVE;

On server 1, using the file and position from server 2:

CHANGE MASTER TO
  MASTER_HOST     = '203.0.113.20',
  MASTER_USER     = 'replication_user',
  MASTER_PASSWORD = 'password',
  MASTER_LOG_FILE = 'mysql-bin.000001',
  MASTER_LOG_POS  = 107;
START SLAVE;

Note that each server points at the other one's address. Pointing a server at itself is the single most common mistake here, and it produces a setup that appears configured and replicates nothing.

6. Check the status

SHOW SLAVE STATUS\G

On both servers, look for:

• Slave_IO_Running: Yes
• Slave_SQL_Running: Yes
• Seconds_Behind_Master: 0
• Last_Error empty

If either process is not running, Last_Error and Last_IO_Error say why — most often authentication, a firewall, or a wrong log position.

7. Test it

On server 1:

CREATE DATABASE example;
CREATE TABLE example.dummy (id varchar(10));

On server 2:

SHOW TABLES IN example;

The table should be listed. Now drop it on server 2:

DROP TABLE example.dummy;

and confirm on server 1 that it is gone. Replication working in both directions is what distinguishes this from master-slave.

Running it afterwards

• Monitor replication. It stops on the first error it cannot apply and does not restart itself. A broken replica that nobody noticed is worse than no replica at all, because you will rely on it.
• Watch for divergence. Compare row counts periodically; tools such as pt-table-checksum exist for this.
• Route writes to one server at a time where you can, keeping the second for failover. Most conflicts disappear if only one server is written to under normal conditions.

If you would like help designing a replication setup, describe your application and what you need it to survive in a ticket — the right arrangement depends heavily on both.