Master-Master Replication With MySQL 5 On Fedora 8

Labels: Posted by EdGe on Sunday, February 17, 2008 at 11:52 PM

This document describes how to set up master-master replication with MySQL 5 on Fedora 8. Since version 5, MySQL comes with built-in support for master-master replication, solving the problem that can happen with self-generated keys. In former MySQL versions, the problem with master-master replication was that conflicts arose immediately if node A and node B both inserted an auto-incrementing key on the same table. The advantages of master-master replication over the traditional master-slave replication are that you don't have to modify your applications to make write accesses only to the master, and that it is easier to provide high-availability because if the master fails, you still have the other master.

This howto is a practical guide without any warranty - it doesn't cover the theoretical backgrounds. There are many ways to set up such a system - this is the way I chose.

1 Preparation

For this howto I set up two Fedora 8 systems (minimal installation without gui etc.) with the following configuration.

1.1 System 1

Hostname: server1.example.com
IP: 192.168.0.100

1.2 System 2

Hostname: server2.example.com
IP: 192.168.0.200

2 MySQL

2.1 Needed Packages On Both Systems

If you haven't installed MySQL on both systems you can install it (client & server) via:

yum -y install mysql mysql-server

2.2 MySQL Server Initial Start On Both Systems

Start the MySQL server.

/etc/init.d/mysqld start

2.3 MySQL Root Password

2.3.1 Both Systems

Set a password for the MySQL root-user on localhost.

mysqladmin -u root password %sql_root_password%

2.3.2 System 1

Set a password for the MySQL root-user on server1.example.com.

mysqladmin -u root -h server1.example.com password %mysql_root_password%

2.3.3 System 2

Set a password for the MySQL root-user on server2.example.com.

mysqladmin -u root -h server2.example.com password %mysql_root_password%

2.4 MySQL Replication User

2.4.1 System 1

Create the replication user that System 2 will use to access the MySQL database on System 1.

mysql -u root -p

GRANT REPLICATION SLAVE ON *.* TO 'slave2_user'@'%' IDENTIFIED BY '%mysql_slaveuser_password%';
FLUSH PRIVILEGES;
quit;

2.4.2 System 2

Create the replication user that System 1 will use to access the MySQL database on System 2.

mysql -u root -p

GRANT REPLICATION SLAVE ON *.* TO 'slave1_user'@'%' IDENTIFIED BY '%mysql_slaveuser_password%';
FLUSH PRIVILEGES;
quit;

2.5 Database On System 2

I proceed on the assumption that the database exampledb is already existing on System 1 - containing tables with records. So we have to create an empty database with the same name as the existing database on System 1.

mysql -u root -p

CREATE DATABASE exampledb;
quit;