Master-Master Replication With MySQL 5 On Fedora 8

Version 1.0
Author: Oliver Meyer <o [dot] meyer [at] projektfarm [dot] de>
Last edited 02/12/2008

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;

Share this page:

3 Comment(s)

Add comment

Comments

From: Anonymous at: 2008-11-21 16:03:19

I have looked on the forum, but no help. What do you use to get the code in, what program/software are you connecting to your SQL database with?

From: admin at: 2008-11-21 16:46:29

If you are on Linux, you can use a terminal. If you are on Windows, you can use an SSH client such as PuTTY (it's available for Linux as well):

http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html

From: Sushant Chawla at: 2010-09-21 05:13:26

This isa brilliant howto, I followed it step by step & in the last it worked in one shot as expected without any modifications.

 Good Work Meyer, keep it up :-)