Master-Master Replication With MySQL 5 On Fedora 8

Want to support HowtoForge? Become a subscriber!
 
Submitted by o.meyer (Contact Author) (Forums) on Sun, 2008-02-17 19:06. :: Fedora | High-Availability | MySQL

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;


Please do not use the comment function to ask for help! If you need help, please use our forum.
Comments will be published after administrator approval.
Submitted by Anonymous (not registered) on Fri, 2008-11-21 17:03.
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?
Submitted by admin (registered user) on Fri, 2008-11-21 17:46.

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