MySQL-Master-Master-Replication

Master-Master replication, or “Multi-Master” replication has two or more hosts that are both master and slave to each other.

We use Master-Master replication for high availability. If we make changes to one MySQL host, then the changes are replicated to the other host, and vice versa.

We can also layer on top an IP virtual server, where a floating IP can point to either database. If one database goes down, then our applications are not affected.

To set up Master-Master replication, we first set up Master-Slave replication, then set up a Slave-Master replication.

Server Configuration

  • dr01 – master 1/slave 2
  • dr02 – master 2/slave 1

Set up Master-Slave Replication First

1. Set the MySQL root password on both hosts:

/usr/bin/mysqladmin -u root password 'mysqlpassword' -p


2. On dr01, set the following in /etc/mysql/my.cnf:

server-id = 1
log_bin            = mysql-bin
relay_log        = relay-bin
auto-increment-increment = 2
auto-increment-offset     = 1
innodb_log_file_size     = 256M
innodb_file_per_table
expire_logs_days    = 10
max_binlog_size         = 100M
binlog_ignore_db        = mysql

On dr02, set the following in /etc/mysql/my.cnf:

server-id = 2
log_bin            = mysql-bin
relay_log        = relay-bin
auto-increment-increment = 2
auto-increment-offset     = 2
innodb_log_file_size     = 256M
innodb_file_per_table
expire_logs_days    = 10
max_binlog_size         = 100M
binlog_ignore_db        = mysql
  • Note: The only difference between dr01 and dr02 is the “server-id” field and the auto-increment-offset field. Each MySQL host in a Master-Master replication needs to have a unique server-id set.

When running master-master replication using auto-increments you can see an issue of the tables getting out of sync if you have 2 applications writing to the same DB table at the same time.

That means that the events that are written to db-01 are numbered 1,3,5,7 etc (and replicated to db-02). Any events that are written to db-02 are numbered 2,4,6,8 etc (and replicated to db-01).

  • Note #2: We’re not replicating the “mysql” database, set via the binlog_ignore_db parameter.


3. On dr01, grant replication:

 mysql> grant replication slave on *.* to 'repl'@'%' identified by 'mysqlpassword';
 Query OK, 0 rows affected (0.00 sec)


4. On dr01, note down the file and the position from the output of “show master” status:

 mysql> show master status;
 +------------------+----------+--------------+------------------+
 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
 +------------------+----------+--------------+------------------+
 | mysql-bin.000002 |       98 |              | mysql            |
 +------------------+----------+--------------+------------------+
 1 row in set (0.00 sec)


5. Add the following on dr02:/etc/mysql/my.cnf:

master-host=192.168.1.11
master-user=repl
master-password=mysqlpassword
master-port=3306


6. Restart mysql on dr02:

/etc/init.d/mysql restart


7. On dr02, set the master variables and run:

mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=98;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G

The following should be set from the output of “show slave status”:

Slave_IO_State should be "Waiting for master to send 
Slave_IO_Running: 
Slave_SQL_Running: Yes

Set up Slave-Master Replication Second

Here’s where we make dr02 a master and dr01 a slave.


8. On dr02, run the following SQL:

mysql> grant replication slave on *.* to 'repl'@'%' identified by 'dave69.hatstand';
Query OK, 0 rows affected (0.00 sec)


9. Restart mysql:

/etc/init.d/mysql restart


10. On dr02, log into mysql and run:

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 |       98 |              | mysql            |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Record the file and position.


11. On dr01, add the following to dr01:/etc/mysql/my.cnf:

master-host = 192.168.1.12
master-user = repl
master-password = mysqlpassword
master-port = 3306


12. Restart mysql:

/etc/init.d/mysql restart


13. Log into mysql on dr01 and run:

mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=98;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G

The following should be set from the output of “show slave status”:

Slave_IO_State should be "Waiting for master to send 
Slave_IO_Running: 
Slave_SQL_Running: Yes


All done! If you make any SQL changes on dr01, it will be replicated to dr02 and vice versa.

The only thing that won’t be replicated is anything to do with the mysql.* database, tables, etc. This means users and security. You need to configure this on *both* nodes.

Leave a Reply

Your email address will not be published. Required fields are marked *