MySQL Master-Master-Slave-Slave Replication

Here we are going to create a Master -> Master -> Slave -> Slave replications architecture.

database_structure

I am expecting here , you have MySQL installed and set-up as normal.

 

 

 

  • Master 1 will be known as Master 1 and Slave 2 with IP 10.1.1.1
  • Master 2 will be known as Master 2 and Slave 1 with IP 10.1.1.2
  • Slave 1 will be known as Slave 3 with IP 10.1.1.3
  • and Slave 2 will be known as Slave 4 with IP 10.1.1.4
  • Replication access to all the DB will be granted.

Master 1

Modify your MySQL config file, usually named my.cnf or mysql.cnf , located in /etc directory. (vi /etc/my.conf)

Add the following lines to [mysqld]

[mysqld]
server-id=1
auto_increment_offset=1
auto_increment_increment=2

log-bin
binlog-ignore-db=mysql
binlog-ignore-db=test
log-slave-updates

Save and close.

You should note that I have included auto_increment_offset*and *auto_increment_increment. auto_increment_offset is the same as server-id in my case, it does as the name suggests – offsets the auto increment value. auto_increment_increment should be set to the number of servers you have as masters, in this example we have 2.

Now restart MySQL.

Master 2 (Slave 1)

Modify your MySQL config file.

Add the following in [mysqld]

[mysqld]
server-id=2
auto_increment_offset=2
auto_increment_increment=2

log-bin
binlog-ignore-db=mysql
binlog-ignore-db=test
log-slave-updates

master-host = 10.1.1.1
master-user = replication
master-password = password
master-port = 3306

Save and restart MySQL.

Now open a MySQL prompt and run the following queries

START SLAVE;
SHOW SLAVE STATUSG;

Slave_IO_Running and Slave_SQL_Running must be set to Yes.

Master 1 (Slave 2)

Open a MySQL prompt and run the following query

SHOW MASTER STATUS;

You should see a master record has been created.

Now we need to configure Master 1 to run as Slave 2.

Modify MySQL config and add the following lines to [mysqld]

[mysqld]
master-host = 10.1.1.2
master-user = replication
master-password = password
master-port = 3306

Save and restart MySQL.

Open a MySQL prompt and run the following queries

START SLAVE;
SHOW SLAVE STATUSG;
Slave_IO_Running and Slave_SQL_Running must be set to Yes.

Slave 3 and Slave 4

Now that you have Master – Master replication set up it’s time to attach the slaves.

I am going to do the following

  • make Slave 3 slave of Master 1
  • and Slave 4 a slave of Master 2.

Slave 3

Open your MySQL config file, under [mysqld] put the following

[mysqld]
server-id=3

master-host = 10.1.1.1
master-user = replication
master-password = password
master-port = 3306

Save and restart MySQL.

Open a MySQL prompt and run the following queries

START SLAVE;
SHOW SLAVE STATUSG;

  

Slave_IO_Running and Slave_SQL_Running must be set to Yes.

Slave 4

Open your MySQL config file, under [mysqld] put the following

[mysql]
server-id=4

master-host = 10.1.1.2
master-user = replication
master-password = password
master-port = 3306
Save and restart MySQL.

Open a MySQL prompt and run the following queries

START SLAVE;
SHOW SLAVE STATUSG;

Slave_IO_Running and Slave_SQL_Running must be set to Yes.

 

Related Posts

  • 76
    Some times MySQL replication creates problems and slave could not sync properly from master. It may cause with lots of reason. Here is a way to fix it.   Warning: After using this tutorial, All of your bin-log files will be deleted, So if you want, you may take a backup…
    Tags: slave, mysql, master
  • 48
    Here we will help you to setup master-slave replication between MySQL servers. Setup Details: Master Server: 10.0.10.12 Slave  Server: 10.0.10.18 Database: empmaster 1. Setup MySQL Master Server Create an mysql account on Master server with REPLICATION SLAVE privileges to which replication client will connect to master. Block write statement on…
    Tags: mysql, slave, master
  • 38
    I tried the following steps: Log in as super user or use sudo Open /etc/mysql/my.cnf using gedit Check the all the configuration Find bind-address, and change its value to the database server host machine's IP address. For me, it was localhost or 127.0.0.1 Save and close the file. Come back…
    Tags: mysql, mysqld
  • 38
    Here we will help you to set up Master-Master replication between MySQL servers. In this setup if any changes made on either server will update on an other one. Setup Details: Master-1: 10.0.10.12 Master-2: 10.0.10.18 Database: empmaster Step 1. Set Up MySQL Master-1 Server Edit MySQL configuration file and add the following…
    Tags: mysql, master, slave
  • 35
    Once replication has been started it should execute without requiring much regular administration. Depending on your replication environment, you will want to check the replication status of each slave periodically, daily, or even more frequently. Checking Replication Status: The most common task when managing a replication process is to ensure…
    Tags: slave, master, mysql

Satya Prakash

VOIP Expert: More than 8 years of experience in Asterisk Development and Call Center operation Management. Unique Combination of Skill Set as IT, Analytics and operation management.

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: