Master master replication with MySQL

MySQL databases can be replicated using master-slave replication and also with master-master replication. There are plenty of tutorials online showing how to do master-slave but not many showing master-master replication and the most popular one I found was a little hard to follow. Therefore I've written this post to show how to do master-master replication with MySQL.

What is master-master replication?

Master-master replication (or multi-master replication) allows databases on two or more servers to remain in sync at all times. Any records that are inserted/updated/deleted in/from one database are automatically replicated to the other machine and all other machines in the master-master set up.

In comparison, with a master-slave setup, records modified on the master will be replicated on the slave but no modifications should ever be done to the slave server.

System I used to write this tutorial

I have set up two sets of master-master replicated servers using the information in this post. They were both on Debian 5 Lenny servers which runs MySQL 5.0. In both cases one server is located in Auckland, New Zealand and the other in the USA (one in Texas, the other in California).

In both cases these were clean server installs with no existing databases. I don't know how successfully you would be able to set up a master-master relationship on an already installed system.

Example setup settings

The example configuration below has two master MySQL servers with the IP addresses 10.1.1.1 and 10.1.1.2. When configuring your servers change these IP addresses to the actual IP addresses you will be using.

The following sections show an example configuration for each server and then an explanation for some of the settings below that.

Configuration for Master 1 - 10.1.1.1

In your my.cnf file (on Debian 5 it's located at /etc/mysql/my.cnf, on CentOS/RHEL 5 it's at /etc/my.cnf) add the following:

server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 500M
master-host = 10.1.1.2
master-user = replication
master-password = slave
master-port = 3306
auto_increment_increment = 2
auto_increment_offset = 1
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
replicate-ignore-db = mysql

And run the following SQL command as the root user:

grant replication slave on *.* to 'replication'@10.1.1.2 identified by 'slave';

Restart the MySQL server.

Configuration for Master 2 - 10.1.1.2

In your my.cnf file (on Debian 5 it's located at /etc/mysql/my.cnf add the following:

server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 500M
master-host = 10.1.1.1
master-user = replication
master-password = slave
master-port = 3306
auto_increment_increment = 2
auto_increment_offset = 2
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
replicate-ignore-db = mysql

And run the following SQL command as the root user:

grant replication slave on *.* to 'replication'@10.1.1.1 identified by 'slave';

Restart the MySQL server.

Now try creating a database, inserting some records from each server and you will see the records replicated across the two servers.

Username and password

master-user and master-password set the username and password used for replication. You may want to change it to something less obvious than my examples of "replication" and "slave". Change this in both the configuration and in the SQL grant statement.

Auto increment increment and offset

If you have auto incremental primary keys then the seed for the increment value needs to be different for each server, otherwise you could potentially have multiple servers attempt to insert records at the same time resulting in primary key conflicts.

You will notice in the configuration there is a setting for auto_increment_increment. This should be set to the number of servers there are. In the example in this post, there are 2 servers so it should be 2. If you intend to add other masters in the future then set it to a higher value.

The second setting is auto_increment_offset which should be set to the same as the server-id value. As an example, if server 1 inserted 5 records and then server 2 inserted 2 records the PKs inserted would be 1, 3, 5, 7, 9, 10, 12.

Replicate ignore DB

The replicate-ignore-db setting allows databases to be excluded from replication. In the examples above, the mysql database is excluded although you may want to allow replication for it. The mysql database handles user logins etc. I haven't actually tried myself running this particular database replicated and possibly it's best to leave it this way.

To have multiple databases excluded, have a separate line and option for each database to be excluded. For example, to exclude both mysql and somedb, do this:

replicate-ignore-db = mysql
replicate-ignore-db = somedb

Conclusion

As mentioned at the start I have used this configuration to set up two separate instances of MySQL master-master replication. In one case it's to keep a backup server in case something should go wrong; we can simply switch DNS and continue serving the websites off the other server. (The websites themselves are synchronized using rsync periodically)

In the other case there are websites being served off each of the servers: a few sites on one server and a few on the other with each server acting as a backup for the other. Again I simply need to switch DNS should something go wrong and no data is lost.

If you need to set up MySQL master-master replication then the instructions presented in this post should be all you need.

Related posts:

• Location of MySQL's my.cnf file
• PHP script to check MySQL replication status
• ERROR 1045 (28000): Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)
• Load data into MySQL with foreign key constraint issues
• Backing up MySQL with mysqldump