David Ramsden

MySQL Multi-Master Replication

This guide was written whilst configuring two MySQL servers for multi-master (or master/master) replication, for use with Postfix and PowerDNS. If you're after a master-slave configuration (where only one server will accept updates) you may be able to adapt the information you find here.

The configuration used here should be compatible with MySQL 6.0 as well.

Software Used

The following software was used:

  • Ubuntu 10.04 LTS (64-bit).
  • MySQL 5.1.

Hosts

Two hosts. For the purpose of this guide they are:

  • server1.domain.local (192.168.1.1).
  • server2.domain.local (192.168.1.2).

OS Configuration

Minimal install, using XFS as the file system where the MySQL databases will be stored. XFS was chosen because the InnoDB database format was being used and there would be significantly more reads than writes to the databases.

I always use DNS names in all my configurations and to ensure the servers could see each other even if DNS was down, I added the names to the hosts file on each server. Example:

MySQL Configuration

The following should be completed on both hosts.

Install mysql-server.

Edit /etc/mysql/my.cnf and comment out the bind-address line:

This will make MySQL listen on all available interfaces. Consider firewalling.

Create a replication user in MySQL:

Configuration on Server 1

Create /etc/mysql/conf.d/replication.cnf:

The replicate-wild-ignore-table line ensures that the mysql database, that contains users, databases and hosts, does not get replicated. You may or may not want this. Personally I'd rather manage users etc on each host to ensure that if a problem occurs with replication I can always get in to a MySQL server.

Restart MySQL.

Login to MySQL as root and execute:

This points the master at the other host (i.e. server1 -> server2).

Configuration on Server 2

Create /etc/mysql/conf.d/replication.cnf:

This configuration file differs from the one on server1. The server-id and the auto_increment_offset are different. The auto_increment_offset helps to ensure that there are no primary key collisions.

Restart MySQL.

Login to MySQL as root and execute:

This points the master at the other host (i.e. server2 -> server1).

Checking Replication

There are two SQL statements to check replication:

All being well you should see Slave_IO_Running and Slave_SQL_Running as Yes and there should be nothing for Last_IO_Error.

Any databases (and their data) that existed on server1 should now appear on server2. And vice versa. Remember to create any logins that exist in the mysql database on the other hosts as this isn't replicated (unless you've decided to replicate the mysql database).