How to setup MySQL Master–Master replication in CentOS/RHEL 5.x/6.x

How to setup MySQL Master–Master replication in CentOS/RHEL 5.x/6.x

by -
0 2895

Introduction of MySQL replication

As we all are familiar with the concept of MySQL Master -Slave replication, where master server sync with slave server when any information write on Master Server, it goes to slave immediately. But any update does not change on master when any changes done on slave server.

This tutorial will cover step-by-step simple example of MySQL Master-Master replication. In this scenarios both server sync to each other, when any information update on any master server it automatically replicate on other.

To perform this process you will need two machines and two IP addresses one for master1 server and another for Master2 server

Server Information

Operating System : CentOS 6.3
Master1 Server IP Address: 192.168.0.62
Host Name : master1.kvit.in
Master2 Server IP Address : 192.168.0.63
Host Name : master2.kvit.in

Iptables service should be off on both Master1 & Master2 server

[root@master1 ~]# iptables -F
[root@master1 ~]# iptables -t nat -F
[root@master1 ~]# /etc/init.d/iptables save
[root@master1 ~]# /etc/init.d/iptables restart

Selinux should be disabled on both server

[root@master1 ~]# getenforce
Disabled

Step:1 MySQL installation on both Master1 & Master2 server

Install package on both Master1 & Master2 server for Replication

[root@master1 ~]# yum install mysql mysql-server -y


Start Mysql service on both server

[root@master1 ~]# service mysqld start
[root@master1 ~]# chkconfig mysqld on


Generate mysql root password on both server

[root@master1 ~]# /usr/bin/mysql_secure_installation

Step: 2 Configure MySQL Master1 Server (192.168.0.62)

Edit MySQL configuration file /etc/my.cnf and add below mentioned following entries in [mysqld] section. Make sure parameter which are in bold this should be changed with yours.

[root@master1 ~]# cp -p /etc/my.cnf /etc/my.cnf.org


[root@master1 ~]# vi /etc/my.cnf
[mysqld]
server-id=1
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-bin
binlog-do-db=db1
master-host = 192.168.0.63
master-user = shahzad         ## this user is created on master2 server
master-password = redhat    ## this password is created on master2 server
master-port = 3306

[root@master1 ~]# service mysqld restart


Create a user with replication slave privileges on master 1 server for master2 through MySQL master2 server will connect/synchronize with master1 server. 

[root@master1 ~]# mysql -u root –p
Syntax,
mysql>grant replication slave on *.* to ‘rep_user‘@192.168.0.63 identified by ‘rep_password‘;
Example,
mysql>grant replication slave on *.* to ‘shahzad’@192.168.0.63 identified by ‘redhat’;
mysql>select user from mysql.user;
mysql>start slave;
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

Step: 3 Configure MySQL Master2 Server (192.168.0.63)

Configure MySQL Master2 Server 

[root@master2 ~]# cp -p /etc/my.cnf /etc/my.cnf.org

[root@master2 ~]# vi /etc/my.cnf

[mysqld]
server-id=2
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
#old_passwords=1
master-host = 192.168.0.62
master-user = shahzad         ####this user is created on master1 server
master-password = redhat   ####this password is created on master1 server
master-port = 3306
log-bin #information for becoming master added
binlog-do-db=db1

[root@master2 ~]# service mysqld restart

Create a user with replication privileges on Maste2 Server for master1 connectivity

[root@master2 ~]# mysql -u root –p
Syntax,
mysql>grant replication slave on *.* to ‘rep_user’@192.168.0.62 identified by ‘rep_password’;
Example,
mysql>grant replication slave on *.* to ‘shahzad’@192.168.0.62 identified by ‘redhat’;
mysql>start slave;
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

Check the status of Binary File and  Position on Master2 Server

mysql> show master status;
+——————-+—————-+——————-+————————–+
| File                     |     Position    |   Binlog_Do_DB | Binlog_Ignore_DB |
+————————+—————-+——————-+———————-+
| mysqld-bin.000001 | 448          |   db1                  |                              |
+——————-+—————-+——————-+—————————+
1 row in set (0.00 sec)

Check status on Master1 server

mysql> show slave status \G;

Step: 4 Replication Testing

On Master1 Server

mysql>create database db1;
mysql>show databases;
mysql>use db1;
mysql>create table emp(name varchar(20), salary int);
mysql> show databases;
+————————+
| Database                  |
+————————+
| information_schema |
| db1                           |
| mysql                        |
+————————+
3 rows in set (0.00 sec)

Now, Go on Master2 Server and check the replicated data

mysql> show databases;
+————————+
| Database                   |
+————————-+
| information_schema  |
| db1                             |
| mysql                          |
+————————–+
3 rows in set (0.00 sec)

Successfully database is replicated from Master1 to Master2 server.

Now, create a table inside MySQL database “db1”  and check whether data is replicating from master2 server to master1 server or not.

mysql>use db1;
mysql>show tables
mysql>create table course(book varchar(20) semester int);

Go on Master1 Server and check the replicated data

mysql> show tables;
+—————+
| Tables_in_db1 |
+—————–+
| course             |
| emp                 |
+——————+
2 rows in set (0.00 sec)

Congratulation, you now configured MySQL Master to Master Replication.

Download PDF

NO COMMENTS

Leave a Reply

Required Captcha *